[FEATURE] Renewable PostgreSQL passwords for new connections #3638

Problem

Managed PostgreSQL services increasingly authenticate with short-lived tokens sent as the password: Microsoft Entra ID on Azure Database for PostgreSQL, RDS/Aurora IAM, and Cloud SQL IAM.

ateapi reads its password once, from the DSN. pgxpool closes connections after MaxConnLifetime (default 1 hour) or MaxConnIdleTime (default 30 minutes), and atepg does not override either. Once the token expires, every replacement connection reuses it and fails authentication. Existing sessions keep working, because PostgreSQL authenticates only at connect time.

poolConfig already re-parses the DSN in BeforeConnect, but only to refresh TLS material. It copies just TLSConfig and Fallbacks, and it installs the hook only when TLS is configured (atepg.go).

Seam

Resolve the password for each new physical connection in BeforeConnect, not per query or pool checkout. Keep this behind one small internal function, so the source can change later without touching pool setup:

• v1 (below): take Password from the freshly parsed DSN, which reads a passfile.
• Later, for PostgreSQL 18 OAUTHBEARER: the same source can set ConnConfig.OAuthTokenProvider, which pgx v5.10 (already pinned upstream) calls only when the server offers that SASL mechanism.

Core keeps the endpoint, TLS verification, pooling, migrations, and SET ROLE. It gains no cloud SDKs.

v1 transport: libpq passfile on an in-memory volume

This uses the passfile approach from #2238 (RDS IAM, not merged), but splits responsibilities differently. Upstream takes only the provider-neutral core change. Each provider's installer deploys its own token sidecar. Core and ate-setup gain no cloud-specific modes, images, or settings.

Upstream (core)

• In poolConfig, BeforeConnect also copies fresh.Password. pgx's ParseConfig reads the passfile on every parse, and only when the DSN has no password. Install the hook whenever the DSN sets passfile, not only with TLS. If the file is briefly unreadable, keep the last password; an expired one then shows up as an ordinary authentication failure.
• ateapi validates at startup in passfile mode: reject a DSN that also contains a password, anything other than sslmode=verify-full, and multiple hosts or fallbacks. Tokens must never be sent over unverified TLS or to an unexpected host. DSNs without a passfile behave exactly as today.
• Document the contract: the DSN carries no password and names a passfile. Whatever runs in the pod keeps that file current and replaces it atomically.
• No ate-setup change is needed. It already passes DSNs through opaquely to the API Secret, and its own comment expects password-less DSNs for IAM authentication.

Provider (installer)

The provider's installer, which already customizes ate-api-server, adds:

• An emptyDir with medium: Memory, mounted read-only into ate-api-server and writable by the sidecar.
• A native sidecar (an init container with restartPolicy: Always) that obtains tokens with the pod's workload identity, writes the passfile (write, then rename), and refreshes well before expiry. A startup probe gates ateapi on the first token; a liveness probe catches a stale file.
• DSNs with no password and passfile=/run/<provider>/pgpass, and database principals mapped to the cloud identities (see below).

The passfile can hold one line per login (host:port:db:user:token), and pgx matches on user. So owner and runtime keep separate identities; there's no need for one shared token for both pools, as *:*:*:* would force.

For example, the AKS installer already renders a workload-identity native sidecar (the Azure Blob objectstore-plugin) into upstream's ate-api-server manifest before running ate-setup. An Entra token sidecar uses the same mechanism, so it survives full and component redeploys.

Why this is enough for v1: it's a few lines in core, works the same for Entra, RDS IAM, and Cloud SQL IAM, and keeps every cloud SDK, image, and refresh policy out of upstream.

What it doesn't do: core has no notion of expiry; a stale token surfaces only as a failed new connection. If expiry-aware caching becomes necessary, add an exec transport modeled on client-go's ExecCredential (status.token plus expirationTimestamp). A plugin registry, a socket transport, and provider modes in ate-setup are out of scope.

Database principals (#3184)

pkg/postgressetup/setup.sql creates login roles as LOGIN NOINHERIT NOSUPERUSER NOCREATEDB NOCREATEROLE NOREPLICATION, and checks roles that already exist. Running it against PostgreSQL 17 showed:

• Default pre-created login (INHERIT): rejected ("conflicts with the required attributes").
• Pre-created LOGIN NOINHERIT login: accepted, granted its group role, and reruns are idempotent.
• Empty password input: runs ALTER ROLE … PASSWORD NULL, clearing any existing password. That suits token-only logins.
• Same login for owner and runtime: accepted, but that login becomes a member of both groups, so runtime connections could SET ROLE to the owner.

On Azure, a workable order needs no SQL change: let setup.sql create the NOINHERIT login with an empty password, then attach the Entra mapping with the documented SECURITY LABEL FOR "pgaadauth" ON ROLE … IS 'aadauth,oid=…,type=service'. This hasn't been verified on a live Entra-enabled server. pgaadauth_create_principal(isAdmin => true) adds CREATEROLE/CREATEDB, which setup.sql rejects.

Acceptance criteria

Upstream (core change):

1. Unit tests: with a rotated passfile, new connections on every pool (owner, read/write, watch, probe) use the new password, and existing connections are unaffected. Concurrent pools never mix up owner and runtime passwords.
2. Startup validation rejects a DSN password combined with passfile, sslmode other than verify-full, and multiple hosts or fallbacks. Modes without a passfile behave exactly as today.
3. Tokens never appear in logs, traces, metrics, events, or install records.
4. Integration test against real PostgreSQL: rotate the password and passfile, force new connections with a short MaxConnLifetime, and verify they authenticate.

Provider evidence (outside upstream CI, from at least one provider installer):

5. Cloud acceptance: sustained load across at least one real token expiry, with default pool lifetimes. Report connection churn, authentication failures (expected: zero) and p99 latency. A short successful install doesn't demonstrate renewal.

Related

• ateapi: Support RDS/Aurora IAM database authentication through a sidecar #2238: RDS IAM passfile sidecar (closed, not merged); the core change here follows it.
• [FEATURE] Extensible DB Identity Setup #3184: database identity and bootstrap ownership.
• [EPIC] Make repository Provider Neutral #3161: provider-neutral core.
• [FEATURE] Establish the ate-setup provider interface for extension #3189: provider installer interface.