F.31. OAuth 2.0 Basic Token Validator for PostgreSQL#

F.31. OAuth 2.0 Basic Token Validator for PostgreSQL
F.31. OAuth 2.0 Basic Token Validator for PostgreSQL
Prev UpAppendix F. Additional Supplied Modules and ExtensionsHome Next

F.31. OAuth 2.0 Basic Token Validator for PostgreSQL #

F.31.1. Overview
F.31.2. Requirements
F.31.3. Compilation and Installation
F.31.4. Configuration
F.31.5. Token Validation Logic
F.31.6. Extensibility

F.31.1. Overview #

This module implements a simple OAuth 2.0 token validator for built-in support of the Device Authorization Flow. The validator performs minimal validation by:

  • Extracting the sub (subject) and scope fields from the JWT payload.

  • Comparing the token's scopes with those required by the pg_hba.conf entry.

  • Mapping the authentication identity sub to a database role using pg_ident.conf.

  • Allowing or denying access based on matching of sub and scope.

F.31.2. Requirements #

  • PostgreSQL version 18 or higher configured with --with-libcurl flag.

F.31.3. Compilation and Installation #

  1. Compile and install the validator dynamic library. Run the following commands in the main directory: 

    make
make install

    The validator will be installed in the <postgres>/lib directory under the filename oauth_validator.so.

  2. Add the validator in postgresql.conf using the setting oauth_validator_libraries: 

    oauth_validator_libraries='oauth_validator'

  3. Configure the file pg_ident.conf. For example: 

    # MAPNAME    SYSTEM-USERNAME                           PG-USERNAME
oauthmap    "7cf5b11f-adb2-4e67-83b7-5c75f7f1e6ee"     "mydbuser"

  4. Configure the file pg_hba.conf. For example: 

    local    all    all    oauth issuer="https://<address>/.well-known/openid-configuration" scope="openid postgres" map="oauthmap"

F.31.4. Configuration #

  • postgresql.conf must contain the validator module in the oauth_validator_libraries entry.

  • pg_hba.conf must specify oauth as the authentication method and define oauth_scope.

  • pg_ident.conf must contain mappings between JWT sub values and PostgreSQL roles.

F.31.4.1. Example of pg_ident.conf Entry #

# MAPNAME    SYSTEM-USERNAME                           PG-USERNAME
oauthmap    "7cf5b11f-adb2-4e67-83b7-5c75f7f1e6ee"     "mydbuser"

If the token contains sub value “7cf5b11f-adb2-4e67-83b7-5c75f7f1e6ee”, and validation passes, PostgreSQL will map it to the mydbuser role using the oauthmap entry.

F.31.4.2. Example of pg_hba.conf Entry #

local    all    all    oauth issuer="https://<address>/.well-known/openid-configuration" scope="openid postgres" map="oauthmap"

F.31.5. Token Validation Logic #

The core validation logic is implemented through the validate_token function. It performs the following steps:

  1. Parsing the token payload: The raw token string is parsed to extract its payload. If the token is malformed or the payload cannot be extracted, validation fails.

  2. Extracting JWT claims: The payload must contain both:

    • sub: Subject (used to identify the user)

    • scope: Space-separated list of scopes granted by the token

    If either field is missing, validation fails.

  3. Comparing scopes: The scopes from the entry oauth_scope in pg_hba.conf are compared with the scopes granted by the token. If some are missing in the token, validation fails.

    In the above configuration example, validation is successful if the token contains both openid and postgres scopes.

  4. Setting authorization result: The res->authorized flag is set to true if scopes match; otherwise, it is set to false.

  5. Assigning authentication identity: The sub value is assigned to res->authn_id, which PostgreSQL uses to identify the authenticated user.

  6. Mapping authentication identity: This sub value is then matched outside of this module against entries in pg_ident.conf to determine the actual database role the user is allowed to assume.

    If matching fails, validation fails.

  7. Otherwise, validation is successful and the client is authorized and successfully connected to the database.

F.31.6. Extensibility #

This basic implementation can be extended with additional checks or custom logic, such as:

  • Validating token signatures

  • Validating token expiration (exp)

  • Validating audience (aud) or issuer (iss)

  • Fetching user roles dynamically

Prev Up Next
F.30. mchar Home F.32. online_analyze