PostgreSQL 18 OAuth Authentication: How Would You Design a Safe Database Connection Plan?

Prompt and context

Your team is upgrading a multi-tenant analytics platform to PostgreSQL 18 and wants applications to connect with OAuth 2.0 bearer tokens instead of long-lived passwords. Explain PostgreSQL 18's OAuth boundary, connection handshake, role mapping, and rollout plan. Call out the places where authorization can accidentally expand or an issuer can be confused.

What the interviewer evaluates

• Whether you distinguish the OAuth client, authorization server, resource server, and database role.
• Whether you can connect pg_hba.conf, validators, scopes, maps, and libpq settings into one data flow.
• Whether you recognize discovery, exact issuer matching, TLS, and token-lifetime risks.
• Whether you can propose a reversible migration instead of simply saying “replace passwords with tokens.”

Clarifying questions

1. Are callers people, backend services, or both? The principal affects public versus confidential clients.
2. Does each tenant map to a separate database role? Can one token represent multiple roles?
3. Does the provider expose a standard discovery document and bearer-token validator?
4. Can legacy clients keep using SCRAM, and how long is the audit and rollback window?

30-second answer

I would treat PostgreSQL as the resource server, libpq/psql as the OAuth client, and the external identity system as the authorization server; the database does not issue tokens. The server enables oauth in pg_hba.conf with an exact issuer, scopes, and validator, using map when external identities must map to database roles. The client uses discovery and presents a bearer token; the validator checks signature, issuer, audience, expiry, and scopes. I would keep SCRAM during a tenant-by-tenant rollout, measure rejection reasons, and remove the OAuth rule to roll back.

Deep dive

1. Define the trust boundary

PostgreSQL 18 adds an OAuth authentication method, not an authorization server. The provider authorizes users and issues tokens; PostgreSQL validates a token and decides whether a connection may assume a database role. Treating the database as a login UI or storing refresh tokens there would broaden its responsibility.

2. Configure server rules

Add an oauth rule in pg_hba.conf for the target network and database, with issuer, scope, optional validator, and optional map. The issuer must match the discovery document character for character. If several validators are installed, select one explicitly. Put the rule before broad password rules so the request cannot fall through to an unintended method.

3. Handle discovery and the handshake

libpq uses oauth_issuer to retrieve discovery metadata and then obtains a token as required by the server. The client issuer must equal the HBA issuer. PostgreSQL documents this exact comparison as a defense against mix-up attacks. Never put bearer tokens in connection logs, process arguments, or durable configuration.

4. Validate tokens and roles

A validator should check the signature, issuer, lifetime, audience, and required scopes, then return an auditable external identity. Without map, the validated username must equal the requested role. For multi-tenancy, use an explicit least-privilege map with deny-by-default behavior. delegateidentmapping bypasses standard pg_ident.conf mapping and should only be used when the validator itself performs complete role authorization.

5. Roll out and roll back

Verify discovery, TLS, scopes, and expired-token behavior outside production. Add OAuth HBA rules for a small service group while retaining SCRAM. Measure success, expiry, issuer mismatch, and mapping rejection separately. Expand after pools, jobs, and operations scripts can refresh tokens. Roll back by removing the OAuth rule or restoring SCRAM while retaining old credentials until every pool has switched.

High-quality sample answer

I would draw four actors: the service is the client, the identity platform is the authorization server, PostgreSQL is the resource server, and a database role is the local authorization target. The HBA oauth method names the issuer, scopes, and validator and maps the token identity to a tenant role. I would avoid delegateidentmapping unless the validator proves role authorization itself. Clients would use oauth_issuer discovery, enforce TLS, and keep tokens out of logs. During a tenant-by-tenant rollout, SCRAM remains available, while dashboards separate issuer, scope, expiry, and mapping failures. Removing the OAuth rule is an immediate rollback. This uses the native PostgreSQL 18 capability while keeping token issuance, database authorization, and migration control separate.

Common mistakes

• Claiming that PostgreSQL issues OAuth tokens, confusing the resource and authorization servers.
• Checking only a JWT signature and skipping issuer, audience, expiry, or scope checks.
• Assuming a same-domain issuer is sufficient and ignoring exact discovery matching.
• Enabling delegateidentmapping without explaining validator-side role authorization.
• Writing bearer tokens to connection logs, traces, or long-lived environment variables.
• Removing SCRAM in one step and leaving pools and batch jobs without a rollback path.

Follow-up questions and answers

Why must the issuer match exactly?

The client builds a discovery URL from the issuer and compares the returned issuer with its configuration. Case, formatting, and path changes can fail the connection; strict comparison also prevents a client from being redirected to an unintended authorization server.

How is the role chosen without a map?

The username returned by the validator must exactly equal the role requested by the connection. Multi-tenant deployments usually need an explicit map with deny-by-default behavior so an identity cannot fall into a privileged role automatically.

How do you preserve availability when OAuth fails?

Keep short-lived SCRAM rollback credentials and migrate pools gradually. Monitor failures by type, then remove the OAuth HBA rule and restore the prior rule if discovery, scopes, or validator behavior is wrong.