gem-hunter

pg_session_jwt is a PostgreSQL extension designed to handle authenticated sessions through a JWT. When configured with a JWK (JSON Web Key), it verifies JWT authenticity. When operating without a JWK, it can fall back to using PostgREST-compatible JWT claims parameter.

JWK can only be set at postmaster startup, from the configuration file, or by client request in the connection startup packet (e.g., from libpq's PGOPTIONS variable), whereas the JWT can be set anytime at runtime. When a JWK is configured, the extension stores the JWT in the database for later retrieval and exposes functions to retrieve the user ID (the sub subject field) and other parts of the payload. When no JWK is configured, it falls back to using the PostgREST-compatible request.jwt.claims parameter for user identification.

The goal of this extension is to provide a secure and flexible way to manage authenticated sessions in a PostgreSQL database. The JWTs can be generated by third-party auth providers, and then developers can leverage either the validated JWT (when using JWK) or PostgREST-compatible JWT claims (when not using JWK) for Row Level Security (RLS) policies, or to retrieve the user ID for other purposes (column defaults, filters, etc.).

This extension powers Neon RLS, but it is portable to any Postgres provider or self-managed Postgres deployments.

• Initialize JWT sessions using a JWK (JSON Web Key) for secure JWT validation.

• Flexible authentication modes - use either JWK-validated JWTs or PostgREST-compatible JWT claims.

• Retrieve the user ID or other session-related information directly from the database.

• Organization helpers for Neon Auth (auth.organization(), auth.organization_id()) when using the Neon Auth organization plugin.

• Simple JSONB-based storage and retrieval of session information.

The auth schema mixes portable helpers (work with most JWT issuers) and Neon Auth–specific helpers.

Other auth providers (Auth0, Clerk, Supabase, Firebase, Cognito, etc.) do not share a standard organization claim name or shape in access tokens. Many do not include an active organization in the JWT at all. For those providers:

• Use auth.jwt() and extract whatever claim your issuer documents (for example a custom namespaced claim).
• Do not rely on auth.organization() unless your tokens intentionally include Neon Auth’s "o" object: {"id": "<uuid>", "slug": "<string>", "role": "<member-role>"}.
• When "o" is absent or not a JSON object, auth.organization() and auth.organization_id() return SQL NULL. RLS policies that compare against them therefore fail closed (no rows match), which is usually desirable.

There is no plan to normalize organization data across providers inside this extension; auth.jwt() remains the generic escape hatch.

The extension can be used in two modes: with JWK validation or with PostgREST-compatible JWT claims.

When using JWK validation, you need to initialize the pg_session_jwt.jwk parameter before using the extension. This can be done using libpq connect options:

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

In this mode, you'll need to:

1. Initialize the session with auth.init()
2. Set the JWT using auth.jwt_session_init(jwt)
3. Use auth.user_id() or auth.session() to access the validated JWT data

When operating without JWK, the extension works out of the box with PostgREST-compatible JWT claims. No initialization is needed - simply ensure your JWT claims are available as request.jwt.claims parameter and use auth.user_id() to access the subject claim.

This mode provides seamless integration with PostgREST's JWT handling, allowing you to use the same JWT claims for both PostgREST and database functions.

pg_session_jwt exposes four main functions:

Initializes a session using JWK stored in pg_session_jwt.jwk run-time parameter. Only needed when using JWK validation mode.

Initializes the JWT session with the provided jwt as a string. Only needed when using JWK validation mode, where the JWT must be signed by the JWK that was initialized with auth.init().

Retrieves JWT session data. The behavior depends on whether a JWK is defined:

• When JWK is defined:

  • Returns the entire validated JWT payload as JSONB.
  • The JWT must be properly signed and validated.
  • Contains all claims from the JWT (sub, role, etc.).

• When JWK is not defined:

  • Falls back to using the value from PostgREST-compatible request.jwt.claims parameter.
  • Returns the claims as JSONB if the parameter is set and contains valid JSON.
  • Returns JSON null if request.jwt.claims is not set, is empty, or contains invalid JSON.

This dual behavior allows for flexible session management while maintaining security when JWK is available, and compatibility with PostgREST JWT claims when operating without JWK.

Alias for auth.session()

Returns the user ID associated with the current session. The behavior depends on whether a JWK is defined:

• When JWK is defined:

  • Returns the value from the "sub" ("subject") field of the JWT.
  • The JWT must be properly signed and validated.

• When JWK is not defined:

  • Falls back to using the value from the "sub" field in the PostgREST-compatible request.jwt.claims parameter.
  • Returns NULL if request.jwt.claims is not set, is empty, or does not contain a valid string in its "sub" field.

This dual behavior allows for flexible authentication scenarios while maintaining security when JWK is available, and compatibility with PostgREST JWT claims when operating without JWK.

Similar to auth.user_id() but returns UUID. Expects "sub" to be UUID, otherwise returns NULL.

Neon Auth only. See Auth provider compatibility for portable vs Neon-specific functions.

Returns the active organization from the JWT "o" claim (Neon Auth organization plugin). The claim shape is:

{"id": "<uuid>", "slug": "<string>", "role": "<member-role>"}

Behavior matches auth.jwt() / auth.session() for JWK-validated JWTs vs PostgREST request.jwt.claims:

• Returns the full "o" object as JSONB when present and is a JSON object.
• Returns SQL NULL when the JWT is missing, invalid, has no "o" claim, or "o" is not a JSON object.

Neon Auth only.

Returns auth.organization() ->> 'id' parsed as uuid, for direct comparison with Neon Auth organization_id columns (same invalid-UUID → NULL behavior as auth.uid()). Returns SQL NULL when there is no active organization, when "id" is missing, or when "id" is not a valid UUID string.

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

-- Role-gated (optional)
CREATE POLICY team_admin_update ON team
  FOR UPDATE
  USING (
    organization_id = auth.organization_id()
    AND (auth.organization() ->> 'role') IN ('admin', 'owner')
  );

A copy-paste snippet for Neon docs lives in docs/organization-rls-snippet.md.

In order to enable audit logs set pg_session_jwt.audit_log as on.

This project is licensed under the Apache License 2.0. See the LICENSE file for details.

For issues, questions, or support, please open an issue on the GitHub repository.

Neon adheres to the securitytxt.org standard for transparent and efficient security reporting. For details on how to report potential vulnerabilities, please visit our Security reporting page or refer to our security.txt file.

If you have any questions about our security protocols or would like a deeper dive into any aspect, our team is here to help. You can reach us at security@neon.tech.