pg_session_jwt

Overview

Version

Build

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

pig build pkg pg_session_jwt         # build RPM / DEB packages

Install

You can install pg_session_jwt 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_session_jwt;          # Install for current active PG version

pig ext install -y pg_session_jwt -v 18  # PG 18
pig ext install -y pg_session_jwt -v 17  # PG 17
pig ext install -y pg_session_jwt -v 16  # PG 16
pig ext install -y pg_session_jwt -v 15  # PG 15
pig ext install -y pg_session_jwt -v 14  # PG 14

dnf install -y pg_session_jwt_18       # PG 18
dnf install -y pg_session_jwt_17       # PG 17
dnf install -y pg_session_jwt_16       # PG 16
dnf install -y pg_session_jwt_15       # PG 15
dnf install -y pg_session_jwt_14       # PG 14

apt install -y postgresql-18-pg-session-jwt   # PG 18
apt install -y postgresql-17-pg-session-jwt   # PG 17
apt install -y postgresql-16-pg-session-jwt   # PG 16
apt install -y postgresql-15-pg-session-jwt   # PG 15
apt install -y postgresql-14-pg-session-jwt   # PG 14

Create Extension:

CREATE EXTENSION pg_session_jwt;

Usage

Sources: README, v0.5.0 tag, control file

pg_session_jwt handles authenticated sessions through JWTs. When configured with a JWK, it verifies JWT authenticity. Without a JWK, it falls back to PostgREST-compatible request.jwt.claims.

CREATE EXTENSION pg_session_jwt;

Mode 1: JWK Validation

Set the JWK at connection time via libpq options:

export PGOPTIONS="-c pg_session_jwt.jwk=$MY_JWK"

Then within the session:

SELECT auth.init();                        -- Initialize with JWK
SELECT auth.jwt_session_init('eyJ...');    -- Set and validate the JWT
SELECT auth.user_id();                     -- Get the 'sub' claim
SELECT auth.session();                     -- Get full JWT payload as JSONB

Mode 2: PostgREST-Compatible (No JWK)

Works out of the box with PostgREST. No initialization needed:

SELECT auth.user_id();   -- Returns 'sub' from request.jwt.claims
SELECT auth.session();   -- Returns full claims as JSONB

Functions

Configuration

RLS Example

CREATE POLICY user_isolation ON my_table
    USING (user_id = auth.user_id());

For Neon Auth organization-scoped policies, use the o claim helpers:

CREATE POLICY team_select ON team
  FOR SELECT
  USING (organization_id = auth.organization_id());

Version Notes

The v0.5.0 README adds Neon Auth organization helpers and explicitly separates portable helpers such as auth.jwt(), auth.user_id(), and auth.uid() from Neon-specific auth.organization() and auth.organization_id(). Other auth providers should use auth.jwt() and extract provider-specific claims directly.