skip to content

What must be in place before a PostgreSQL 18 pg_hba.conf oauth record can admit a single sign-on user, and what does PostgreSQL itself not provide?

level: seniorimportance: nice to knowfreq 20%

answer

  1. new login method in 18
  2. a validator module you load
  3. issuer and scope are required
  4. device flow needs libcurl

basics

~20 s

PostgreSQL 18's oauth method needs a validator library in oauth_validator_libraries (empty by default, which refuses OAuth logins), issuer and scope on the pg_hba.conf record, and a token-capable client. PostgreSQL ships no validator and no authorization server.

solid answer

~50 s

The `oauth` method is new in PostgreSQL 18: the client presents an OAuth 2.0 bearer token through the OAUTHBEARER SASL mechanism. On the server, the `pg_hba.conf` record needs `issuer` (the HTTPS issuer identifier, or its `/.well-known/` discovery URL) and `scope`. It can also set `validator`, `map` or the advanced `delegate_ident_mapping=1`. A **validator module** listed in `oauth_validator_libraries` checks the token. That parameter is empty by default, which refuses every OAuth connection, and PostgreSQL ships no validator, so you build or obtain one that validates the token, checks its scopes and returns the user's identity. That identity must equal the requested role or be mapped through `pg_ident.conf`, and the role must already exist. On the client, libpq needs `oauth_issuer` and `oauth_client_id`, and its built-in device-authorization flow exists only if libpq was built with libcurl. PostgreSQL is neither the authorization server nor an OIDC client.

code

ini · 3 lines
ini
# postgresql.conf
# a validator you build or obtain; PostgreSQL ships none
oauth_validator_libraries = 'clinic_oauth_validator'

go deeper

for a junior

Recall that PostgreSQL 18 added an oauth login method and that it relies on an outside identity provider for tokens.

for a middle

Name the pieces: issuer and scope on the pg_hba.conf record, a validator in oauth_validator_libraries, and libpq's oauth_issuer and oauth_client_id.

for a senior

Explain what the validator must check, why an empty oauth_validator_libraries refuses logins, how identities map to roles, and the latency cost of online checks within authentication_timeout.

for a principal

Weigh SSO through OAuth against cert, Kerberos or password logins: who owns the validator code, how revocation works online versus offline, and how much new stack the team must maintain.

## What 18 added PostgreSQL 18 introduced the `oauth` authentication method, the first way to log in with a token issued by an outside identity provider rather than a password, certificate or Kerberos ticket. The client sends an OAuth 2.0 **bearer token** (RFC 6750) using the OAUTHBEARER SASL mechanism. The server hands the token to a **validator module**, which decides whether it is genuine and which user it represents. PostgreSQL 17 and earlier have no such method. How OAuth grants and discovery work in general belongs to the OAuth protocol topics; this answer covers the PostgreSQL pieces. ## Server side: the pg_hba.conf record | Option | Required | Purpose | |---|---|---| | `issuer` | yes | HTTPS issuer identifier of the authorization server, or a `/.well-known/` URL pointing straight at its discovery document | | `scope` | yes | space-separated scopes the token must carry for this database | | `validator` | only if several libraries are loaded | which library from `oauth_validator_libraries` checks the token | | `map` | no | `pg_ident.conf` map from the token's user name to roles | | `delegate_ident_mapping` | no, advanced | `1` skips `pg_ident.conf` and lets the validator decide; cannot be combined with `map` | Without `map` or `delegate_ident_mapping`, the user name the validator returns must **exactly** match the requested role. A bearer token is a credential, so put these records on `hostssl` lines and have clients verify the server certificate. ## The validator module - `oauth_validator_libraries` lists the validator libraries. It can only be set in `postgresql.conf` (it takes effect on reload) and is **empty by default, which means OAuth connections are refused**. - PostgreSQL ships **no validator**. You write one in C, exporting `_PG_oauth_validator_module_init`, or obtain one for your provider. - The documentation gives a validator three jobs: 1. **Validate the token.** Either online, by asking the provider (token introspection), or offline, by checking the signature, issuer, audience and validity period against the provider's keys. 2. **Authorize the client.** Check that the token's scopes cover database access for this record. 3. **Report the identity.** Return whether the token is authorized and the authenticated user name. - Online checks need a network call on every login, and it must finish within `authentication_timeout` (default one minute). Offline checks are fast but cannot see a token revoked centrally. ## Client side - libpq needs the `oauth_issuer` and `oauth_client_id` connection parameters, which come from your OAuth provider. - libpq's **built-in flow** is the OAuth Device Authorization grant (RFC 8628): it prints a URL and a code, and the user approves in a browser, which also works over SSH. It exists only if PostgreSQL was built with libcurl (`--with-libcurl` or Meson's `-Dlibcurl`) and the optional module is installed, and it is not supported on Windows. - Applications can plug in their own flow through libpq hooks, for example to reuse a token they already hold. - The server's `issuer`, the discovery document's issuer and the client's `oauth_issuer` must match **exactly**. This blocks mix-up attacks, in which a token meant for one authorization server is sent to another. ## What PostgreSQL does not do 1. It is not an **authorization server**: it never issues tokens or logs users in at the provider. 2. It is not an **OIDC client**, although it is designed to work alongside OpenID Connect providers. 3. It does not **validate tokens itself**: without a loaded validator, no OAuth login succeeds. 4. It does not **create roles**: the role named in the connection must already exist, with its grants managed as usual. ## Rolling it out at the clinic - Leave password or certificate logins in place for service accounts and administrators. OAuth suits people, and libpq's built-in device flow needs a person to approve the login in a browser. - Because the first matching record decides, an `oauth` record placed above a password record for the same roles and subnet replaces password login for those users completely. Order the records on purpose, and check the result in `pg_hba_file_rules` before reloading. - Include the validator's code and configuration in security review alongside `pg_hba.conf`. A lax validator lets in anyone who holds any token from the issuer.

  • Online or offline token validation: what changes for the database?
    Online validation (token introspection) costs a network call to the provider on every login, which must finish within `authentication_timeout`, but it lets the provider revoke a stolen token centrally. Offline validation checks the signature, issuer, audience and expiry locally. It is fast but cannot see a revocation, so short token lifetimes have to make up for it.
  • Why does libpq insist that the issuer match exactly?
    The issuer is highly privileged during the handshake. The server's `issuer` setting, the discovery document's issuer identifier and the client's `oauth_issuer` must be identical, with no difference in case or format. This blocks mix-up attacks, in which a client is tricked into sending a token meant for one authorization server to another.

  • PostgreSQL 18 includes a built-in validator for JWT access tokens.
  • PostgreSQL acts as the OAuth authorization server and issues tokens.
  • An oauth login creates the database role on first use.
  • Any libpq build can run the device authorization flow.
  • The oauth method is already available in PostgreSQL 17.

Assumes PostgreSQL 18, which introduced the oauth pg_hba.conf method and oauth_validator_libraries; PostgreSQL 17 has neither.