Skip to content

feat(postgres): single listener fronting database-routed upstreams#179

Merged
mslipper merged 7 commits into
mainfrom
feat/postgres-database-routing
Jun 5, 2026
Merged

feat(postgres): single listener fronting database-routed upstreams#179
mslipper merged 7 commits into
mainfrom
feat/postgres-database-routing

Conversation

@mslipper

@mslipper mslipper commented Jun 5, 2026

Copy link
Copy Markdown
Contributor

Reworks the Postgres proxy from one-listener-per-upstream to a single listener that fronts many database-routed upstreams. The top-level postgres: block is one object with a listen address, one shared client credential, and a list of upstreams (each a required database, a first-class dsn, and an optional injected role):

postgres:
  listen: ":5432"
  client:
    user: app_user
    password_env: PG_PROXY_PASSWORD
  upstreams:
    - database: appdb
      dsn: {type: env, var: PG_APPDB_DSN}
      role: tenant_role
    - database: analyticsdb
      dsn: {type: env, var: PG_ANALYTICS_DSN}
      role: analytics_role

The client selects an upstream by the database name it sends in its startup message and authenticates with the listener's single shared credential. Routing is by database alone, so credentials are not per-database. The client must name a database explicitly (no fallback to the user name); an unspecified or unmatched database is rejected with a FATAL 3D000.

Each upstream's database must equal the database its DSN connects to. The proxy validates this when dialing: it rejects a DSN that names no database, and rejects a database/DSN mismatch, both with 3D000. So a client connecting with dbname=X always lands on database X upstream.

In managed mode the control-plane-synced upstreams are layered onto the single listener. Each sync entry carries {foreign_id, database, dsn, role?} (no client credentials). The listener knobs (bind address and shared client credential) come from the local YAML block when present, otherwise from IRON_PROXY_PG_LISTEN / IRON_PROXY_PG_CLIENT_USER / IRON_PROXY_PG_CLIENT_PASSWORD. A synced upstream whose database collides with a local one (or another synced one) is dropped.

This is a breaking config change; the example config and integration testdata are updated. Verified end-to-end against real Postgres (-integration): routing to distinct databases with per-upstream role enforcement and RLS scoping, database/DSN mismatch rejection, and unknown-database rejection all pass.

mslipper added 7 commits June 4, 2026 22:28
Replace the one-listener-per-upstream model with a single listener that
fronts multiple routes. The client's startup database parameter selects the
route (falling back to the user name, per libpq); each route has its own
upstream DSN, client credentials, and optional injected role. An unknown
database is rejected with a FATAL 3D000.

Split the compiled Policy into Listener (name, listen, routes keyed by
database) and Route (upstream, credentials, role). Managed mode now derives
all synced upstreams as routes under one control-plane listener bound to
IRON_PROXY_PG_LISTEN, with per-route client credentials from
IRON_PROXY_PG_<FOREIGN_ID>_CLIENT_USER/_CLIENT_PASSWORD; the sync entry gains
an optional database field defaulting to foreign_id.
…it database

Address review feedback: the proxy now runs exactly one postgres listener.
The top-level postgres: block is a single object with a `listen` address and a
`routes` list (no longer a list of named listeners). Clients must name a
database explicitly — the proxy no longer falls back to the user name — and an
unspecified or unmatched database is rejected with FATAL 3D000.

LoadFromNode/Compile now yield a single *Listener; the Manager owns one server.
In managed mode the synced routes are layered onto the single listener: the
bind address comes from the local YAML listener when present, otherwise
IRON_PROXY_PG_LISTEN, and a synced route whose database collides with a local
route (or another synced route) is dropped.
Per review: the per-database entries are now `upstreams` (plural) and the DSN is
a first-class field on each entry rather than nested under `upstream.dsn`:

  postgres:
    listen: ":5432"
    upstreams:
      - database: appdb
        dsn: {type: env, var: PG_APPDB_DSN}
        client: {user: app_user, password_env: PG_APPDB_PROXY_PASSWORD}
        role: tenant_role

The compiled Route type is renamed Upstream throughout (Listener.Upstream/
Upstreams, NewManagedUpstream, etc.). No behavior change.
A client connecting with dbname=X must land on database X upstream, otherwise
current_database() and friends silently diverge from what the client asked for.
dialUpstream now parses the resolved DSN and refuses to connect when its
database differs from the upstream's routing database, surfacing a FATAL 3D000
to the client. The check runs at connection time since the DSN is a
lazily-resolved secret source.

Reworks the multi-upstream integration test to route to two distinct real
databases (adds a second database to the fixture) and adds end-to-end coverage
of the mismatch rejection, plus unit tests for the check.
The per-upstream database is now required in the control-plane sync path too
(PostgresFromSync errors when it's absent rather than defaulting to foreign_id),
matching the YAML config which already required it. dialUpstream additionally
rejects a DSN that names no database (FATAL 3D000) so the database/DSN match
check is never comparing against an empty value.
… per-upstream env vars

Managed mode no longer reads per-upstream client credentials from
IRON_PROXY_PG_<FOREIGN_ID>_CLIENT_USER/_CLIENT_PASSWORD. Each sync entry now
carries client_user and client_password inline (both required), so the control
plane delivers the complete upstream and the proxy needs no per-upstream
configuration. The single listener bind address is unchanged: local YAML
postgres.listen, or IRON_PROXY_PG_LISTEN. Removes the now-unused pgEnv helper.
…er-upstream creds

Routing is keyed on database, so per-upstream client credentials add nothing.
Move the client user/password to the listener: YAML carries one top-level
`client: {user, password_env}`, and managed mode reads a single shared
IRON_PROXY_PG_CLIENT_USER / IRON_PROXY_PG_CLIENT_PASSWORD (or inherits the local
listener's credential). Upstreams now carry only database, dsn, and role; the
sync entry drops client_user/client_password. Auth verifies against the
listener's shared credential. Synced upstreams are deduped before building,
keeping the local-merge and env-built paths consistent.
@mslipper mslipper merged commit feb3242 into main Jun 5, 2026
7 checks passed
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Labels

None yet

Projects

None yet

Development

Successfully merging this pull request may close these issues.

1 participant