pg_oidc_validator

Overview

Configure oauth_validator_libraries=pg_oidc_validator; 1.1.0 adds discovery_url_override; RPM is available on EL10 only while DEB covers all supported Debian and Ubuntu targets.

Version

Build

You can build the RPM / DEB packages for pg_oidc_validator using pig build:

pig build pkg pg_oidc_validator         # build RPM / DEB packages

Install

You can install pg_oidc_validator directly. First, make sure the PGDG and PIGSTY repositories are added and enabled:

pig repo add pgsql -u          # Add repo and update cache

Install the extension using pig or apt/yum/dnf:

pig install pg_oidc_validator;          # Install for current active PG version

pig ext install -y pg_oidc_validator -v 18  # PG 18

dnf install -y pg_oidc_validator_18       # PG 18

apt install -y postgresql-18-pg-oidc-validator   # PG 18

Preload:

shared_preload_libraries = 'pg_oidc_validator';

Usage

Sources:

• pg_oidc_validator 1.1.0 README
• pg_oidc_validator 1.1.0 Keycloak example
• pg_oidc_validator 1.1.0 validator source
• PostgreSQL 18 OAuth authentication
• PostgreSQL 18 libpq OAuth support

pg_oidc_validator 1.1.0 is a PostgreSQL 18 OAuth validator module that validates JWT access tokens against an OpenID Connect provider. It is a server library with no control file or SQL extension, so do not run CREATE EXTENSION.

Configure the Server

Load the module in postgresql.conf, then restart PostgreSQL:

oauth_validator_libraries = 'pg_oidc_validator'

Add an OAuth rule to pg_hba.conf; the issuer and required scope must match the provider. Use hostssl outside a strictly local test:

hostssl  all  all  127.0.0.1/32  oauth  issuer=https://id.example.com/realms/postgres scope="openid postgres" validator=pg_oidc_validator

Reload PostgreSQL after HBA or validator-setting changes; adding the module to oauth_validator_libraries itself requires a restart.

The default authenticated identity claim is sub. To return another stable string claim for role matching, configure:

pg_oidc_validator.authn_field = 'email'

Version 1.1.0 also provides pg_oidc_validator.discovery_url_override. It changes where discovery metadata and JWKS are fetched without changing the issuer used to validate the JWT iss claim; this is useful when an OIDC provider has different internal and external URLs. Both validator settings are reloadable with SIGHUP.

Without map= in the HBA rule, the selected claim must exactly equal the requested PostgreSQL role. Use a named pg_ident.conf mapping when provider identities and database roles differ; the validator does not create roles.

Connect with libpq

An OAuth-capable libpq client can start the provider’s device authorization flow:

psql 'host=127.0.0.1 dbname=app user=alice oauth_issuer=https://id.example.com/realms/postgres oauth_client_id=postgres-client'

Use oauth_client_secret only when the registered client requires it. The client identifier, requested scope, issuer, and provider configuration must agree.

Provider and Security Boundaries

• Keycloak must enable the OAuth 2 device flow for command-line clients.
• Microsoft Entra ID requires a tenant-specific v2 issuer and custom scopes; use the full scope name in pg_hba.conf.
• Google is not usable through libpq’s built-in device flow, though custom clients may work.
• Dex does not emit OAuth scopes; an explicitly empty scope="" disables scope validation, which weakens the normal check.
• The client oauth_issuer must exactly match the HBA issuer and the discovery document. Treat the issuer and any pg_oidc_validator.discovery_url_override endpoint as trusted security boundaries, and require verified TLS for database and provider connections.
• Token validation does not replace PostgreSQL grants, role membership, or row-level security.
• Pigsty RPM packages are limited to EL10; DEB packages cover the supported Debian and Ubuntu targets. PostgreSQL 18 is required.