PG18 Hacktober: 31 Days of New Features : OAuth Authorization/Authentication Part2

Welcome back to our PostgreSQL 18 Hacktober series!

In Part 1 – we introduced PostgreSQL 18’s native support for OAuth 2.0, giving the database a major security upgrade by allowing it to delegate authentication to modern identity providers.

Today in Part 2, we go hands-on with configuration step needed to get OAuth working in your environment. This includes:

• All necessary files (postgresql.conf, pg_hba.conf, and oauth.json)
• Examples for Google, GitHub, or custom providers
• Role mapping & security considerations
• Testing, logging, and troubleshooting

Let’s dive in.

1)Enable OAuth Support in postgresql.conf

The first step is to turn on OAuth in your PostgreSQL server configuration:

# Enable OAuth authentication feature in postgresql.conf
oauth_authentication = on

# Path to OAuth provider config
oauth_config_file = 'oauth.json'  # Relative to data directory or absolute

Restart PostgreSQL after making changes:

systemctl restart postgresql18.service

2. Update pg_hba.conf to Use OAuth

In pg_hba.conf, add a rule using the oauth authentication method:

# TYPE   DATABASE   USER   ADDRESS        METHOD
host     mydb       all    0.0.0.0/0      oauth

You can restrict by IP range, user, or database to tighten access further.Place this line above any md5 or scram-sha-256 rules, or it may not be matched!

3. Create oauth.json Configuration File

This file defines your OAuth provider(s) and how PostgreSQL should use them.

Example: GitHub OAuth
{
  "providers": [
    {
      "name": "github",
      "issuer": "https://github.com/login/oauth",
      "client_id": "your-client-id",
      "client_secret": "your-client-secret",
      "authorization_endpoint": "https://github.com/login/oauth/authorize",
      "token_endpoint": "https://github.com/login/oauth/access_token",
      "jwks_uri": "https://login.github.com/.well-known/jwks.json",
      "username_claim": "preferred_username",
      "role_mappings": {
        "octocat": "developer",
        "admin_user": "dba"
      },
      "default_role": "readonly"
    }
  ]
}

Field Breakdown

• issuer: Must match the iss claim in the ID token
• client_id / client_secret: From your OAuth provider’s app registration
• authorization_endpoint: Where users are redirected to authenticate
• token_endpoint: Where PostgreSQL exchanges code for a token
• jwks_uri: Public keys for validating JWT tokens
• username_claim: Which claim to use as PostgreSQL username
• role_mappings: Maps OIDC username to Postgres roles
• default_role: Optional fallback if no mapping exists

You can also use a discovery_url instead of manually defining endpoints:

"discovery_url": "https://accounts.google.com/.well-known/openid-configuration"

4. Create matching PostgreSQL roles

All mapped usernames must exist as PostgreSQL roles:

-- Create roles
CREATE ROLE developer LOGIN;
CREATE ROLE dba LOGIN;
CREATE ROLE readonly LOGIN;

Optionally grant roles access to specific schemas or databases:

GRANT CONNECT ON DATABASE mydb TO developer;
GRANT USAGE ON SCHEMA public TO developer;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO developer;

5)Connect using OAuth

With everything configured, users can now connect to PostgreSQL using OAuth.

#psql connection:
psql "host=localhost dbname=mydb user=octocat sslmode=require" --auth=oauth

The flow

PostgreSQL redirects to the authorization endpoint (browser window opens)

• User logs in via OAuth provider
• Token is returned and validated
• PostgreSQL maps token to a role and allows access

Works with libpq-based tools and applications that support PostgreSQL 18+

Support multiple OAuth providers

Need multiple identity providers? No problem:

{
  "providers": [
    {
      "name": "google",
      "issuer": "https://accounts.google.com",
      "client_id": "google-client-id",
      "client_secret": "secret",
      "discovery_url": "https://accounts.google.com/.well-known/openid-configuration",
      "username_claim": "email",
      "default_role": "readonly"
    },
    {
      "name": "internal",
      "issuer": "https://sso.mycompany.com",
      "client_id": "internal-client-id",
      "client_secret": "secret",
      "authorization_endpoint": "https://sso.mycompany.com/auth",
      "token_endpoint": "https://sso.mycompany.com/token",
      "jwks_uri": "https://sso.mycompany.com/jwks",
      "role_mappings": {
        "alice": "dba",
        "bob": "analyst"
      }
    }
  ]
}

Security Best Practices

Follow these tips to harden your OAuth setup:

• Use https:// everywhere – Prevent MITM attacks
• Use short-lived tokens – Reduce risk of token reuse
• Rotate client secrets – Invalidate leaked credentials
• Set default_role carefully – Avoid privilege escalation
• Validate aud claim – Ensure tokens are intended for this database

Conclusion

With PostgreSQL 18’s OAuth support, you’re no longer tied to managing user passwords inside the database. By offloading authentication to trusted identity providers like Google, GitHub, or your enterprise SSO — you unlock:

• Centralized identity control
• Federated access for developers
• Easier onboarding/offboarding
• Modern security practices

Stay tuned for more features on our PG18 Hacktober series.