Skip to content

Use OAuth identity for Postgres connections via certificate passthrough #9673

@ardentperf

Description

@ardentperf

When pgAdmin is configured with OAuth2 authentication and the backend PostgreSQL server is also configured with OAuth2 (or any auth method where the database username matches the OAuth username claim), there is currently no way to connect to PostgreSQL as the logged-in user without manually entering credentials. Users end up with two separate identities: one for pgAdmin, one for the database.

pgbouncer solves this exact problem using certificate authentication with pg_ident.conf map=all: a single privileged client certificate authenticates to PostgreSQL on behalf of any user, with the actual database user supplied separately. The certificate acts as a trusted proxy credential. This proposal adds the same mechanism to pgAdmin as an opt-in per-server toggle: Use OAuth identity for database connection.

How it works:

When the toggle is enabled on a server, pgAdmin:

  1. Verifies the current pgAdmin user is authenticated via OAuth2 (raises an error otherwise - internal, LDAP, Kerberos, and webserver auth are all rejected).
  2. Substitutes the OAuth username claim as the PostgreSQL user, ignoring the stored server username.
  3. Connects using a system-configured client certificate (OAUTH_PASSTHROUGH_SSL_CERT / OAUTH_PASSTHROUGH_SSL_KEY in config_system.py) rather than any user-supplied certificate or password.
  4. Omits the password entirely from the connection string.

The privileged certificate is stored in config_system.py - not in the pgAdmin database - so it is set by the system administrator and never user-accessible. PostgreSQL must be configured with hostssl ... cert map=oauth in pg_hba.conf and an appropriate pg_ident.conf mapping.

Configuration:

# config_system.py (set by the server administrator)
OAUTH_PASSTHROUGH_SSL_CERT = '/etc/pgadmin/passthrough.crt'
OAUTH_PASSTHROUGH_SSL_KEY  = '/etc/pgadmin/passthrough.key'

PostgreSQL side (example):

# pg_hba.conf
hostssl  all  all  0.0.0.0/0  cert  map=oauth

# pg_ident.conf
oauth  pgadmin-proxy  /^(.*)$/  \1

Security properties:

  • The certificate can only be used through the passthrough code path — the gate function checks auth_source == 'oauth2' and is_authenticated before building the connection string.
  • Any non-OAuth login attempting to connect to a passthrough-enabled server gets a clear error.
  • The stored server username and password fields are ignored and disabled in the UI when the toggle is on.
  • The certificate never appears in the pgAdmin UI or database.

Relation to existing issues:

This overlaps in motivation with #9540 (using the OAuth username claim for shared server connections) and #9261 (Entra ID token forwarding to Azure PostgreSQL). This cert-based approach works with any OAuth provider and does not require token refresh handling.

Metadata

Metadata

Assignees

No one assigned

    Labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions