---
id: w1/m17/t003
title: Access & connection — IP allowlist + PgBouncer pooler strings + Postgres users
worker: worker1
status: done
estimate: 45m
depends_on: []
---

## Objective

Close the access/connection gap for managed Postgres: (1) an `ipAllowList` that gates the **external** URL (today bex has only a `public` on/off toggle — a real security hole for public DBs), (2) a PgBouncer **pooler** via a CNPG `Pooler`, surfacing pooler connection strings in `connection-info` (the fields are already stubbed), and (3) Postgres `users` CRUD (Render `/postgres/{id}/users`).

## Context

- `w1/011`: "`ipAllowList` (bex has only a `public` on/off toggle — a real security gap for public DBs), Postgres `users`, and the PgBouncer pooler connection strings (needs a CNPG `Pooler`; the connection-info shape already has the pooler fields stubbed)."
- External path today: Traefik TCP + SNI on `<id>.db.bex.co`, opt-in via `spec.public` — see `docs/ADR009-postgresql-management.md` §3/§7 ("Require sslmode and a per-DB IP allowlist. Render exposes exactly this."). The allowlist must gate this external route, not the internal `-rw` Service.
- `connection-info` shape + password surfacing: `docs/ADR006-bex-api.md` Postgres section; the pooler fields are already stubbed in the connection-info struct.
- Postgres feature: `lego/backend/internal/postgres/` (`service.go` + adapters); CRD `lego/types/v1alpha1/database_types.go`; controller `lego/operator/internal/controller/database_controller.go`.
- CNPG `Pooler` CRD provides PgBouncer; its `-pooler` Service gives the pooled connection string.

## Steps

1. Add `spec.ipAllowList []string` to the `Database` CRD; in the controller, apply it to the external route (Traefik router source-range middleware / CNPG `pg_hba`-equivalent) so only listed CIDRs reach the public endpoint; internal `-rw` path unaffected.
2. Project a CNPG `Pooler` (PgBouncer) for the Database and populate the stubbed pooler fields in `connection-info` (internal + external pooled strings) from the `-pooler` Service.
3. Add Postgres `users` CRUD: Core verbs (`ListUsers`/`CreateUser`/`DeleteUser`, optional password rotation) provisioning roles in the Cluster; adapters REST `/v1/postgres/{name}/users`, GraphQL, MCP.
4. Add adapter fragments for the IP allowlist (read/set) across REST/GraphQL/MCP; update `docs/ADR009-postgresql-management.md` + `docs/ADR006-bex-api.md`; prettier the markdown.

## Files

- `lego/types/v1alpha1/database_types.go`
- `lego/operator/internal/controller/database_controller.go`
- `lego/backend/internal/postgres/service.go`
- `lego/backend/internal/postgres/rest.go`
- `lego/backend/internal/postgres/graphql.go`
- `lego/backend/internal/postgres/mcp.go`
- `docs/ADR009-postgresql-management.md`, `docs/ADR006-bex-api.md`

## Acceptance criteria

- [ ] `spec.ipAllowList` gates the external URL — a client outside the allowlist is refused, one inside connects; the internal `-rw` path is unchanged. Set/read the allowlist over REST/GraphQL/MCP.
- [ ] `connection-info` returns populated pooler connection strings (internal + external) backed by a CNPG `Pooler` (PgBouncer).
- [ ] Postgres `users` CRUD works over all three adapters — create a role, list it, delete it; passwords never appear in logs/errors.
- [ ] `make test` green; docs updated + prettier-clean.

## Out of scope

- Dashboard access-control panel (that's t004).
- Fine-grained per-user GRANTs / row-level policies (basic role CRUD only).
- The `pg_sni_proxy` broad-client compatibility work (still deferred per the ADR).
