OIDC Single Sign-On

Authenticate users via external OpenID Connect providers (Okta, Auth0, Keycloak, etc.) without storing passwords in NodeDB.

Overview

NodeDB validates JWT bearer tokens against your OIDC provider's public key set (JWKS), maps JWT claims to database access, and creates ephemeral authenticated sessions. No permanent user records are required for SSO logins.

Wire-protocol scope: OIDC bearer tokens work on the native protocol and HTTP. pgwire (PostgreSQL protocol) does not support bearer tokens natively and uses SCRAM-SHA-256 only. If you need pgwire OIDC, configure a pgwire proxy or run separate SCRAM users alongside SSO.

Registering a Provider

Register your OIDC provider's details:

CREATE OIDC PROVIDER okta ISSUER 'https://yourorgname.okta.com/' JWKS_URI 'https://yourorgname.okta.com/.well-known/oauth2/default/v1/keys' AUDIENCE 'https://nodedb.example.com'
  CLAIM MAPPING
    WHEN email = null SET DEFAULT_DATABASE = 1
    WHEN groups = 'engineering' SET ADD DATABASES [2] ADD ROLES ['data_analyst'];

Fields:

Syntax:

  • ISSUER '<url>' — Token issuer URL (e.g., https://accounts.google.com)
  • JWKS_URI '<url>' — Public key set URL for signature validation
  • AUDIENCE '<aud>' — (optional) Expected JWT aud claim; mismatches are rejected
  • CLAIM MAPPING WHEN ... [SET DEFAULT_DATABASE = <id>] [ADD DATABASES [...]] [ADD ROLES [...]] — Rules mapping JWT claims to NodeDB identity

NodeDB fetches the JWKS once, caches it in memory, and refreshes it when:

  • A token arrives with an unknown key ID (kid)
  • The cache TTL expires (typically 24 hours)

Claim Mapping

Claim mapping rules translate JWT claims into NodeDB identity fields and access grants. Each rule in the CLAIM MAPPING clause specifies:

  • WHEN <claim> = '<value>' — JWT claim name and value to match (e.g., WHEN email = 'alice@example.com' or WHEN groups = 'engineering')
  • SET DEFAULT_DATABASE = <db_id> — (optional) set as the user's default database (numeric ID)
  • ADD DATABASES [<id>, ...] — (optional) grant access to these databases (numeric IDs)
  • ADD ROLES ['<role>', ...] — (optional) assign these roles to the session (quoted role names)

Example: An Okta JWT like:

{
  "sub": "00u1234567890abcdef",
  "email": "alice@acme.com",
  "groups": ["engineering", "admins"],
  "org_id": 1
}

Maps to:

CREATE OIDC PROVIDER okta ISSUER 'https://yourorgname.okta.com/' JWKS_URI 'https://...' AUDIENCE 'api'
  CLAIM MAPPING
    WHEN email = 'alice@acme.com' SET DEFAULT_DATABASE = 2
    WHEN groups = 'engineering' ADD DATABASES [3] ADD ROLES ['data_analyst']
    WHEN groups = 'admins' ADD ROLES ['cluster_admin'];

The session inherits all matching rules' effects. If multiple rules grant database access, the union is used.

Failure modes:

  • Missing claim → no effect (rule skipped)
  • Claim value doesn't match the rule's claim_value → rule skipped
  • Unknown role in add_roles → error at provider creation time

Updating a Provider

Replace the claim mapping rules after creation:

ALTER OIDC PROVIDER okta SET CLAIM MAPPING
    WHEN email = 'alice@acme.com' SET DEFAULT_DATABASE = 2
    WHEN dept_name = 'sales' ADD DATABASES [5] ADD ROLES ['sales_role'];

ALTER OIDC PROVIDER ... SET CLAIM MAPPING replaces the full rule set. To change the issuer, JWKS URI, or audience, drop and recreate the provider.

Changes take effect immediately for new logins. Existing sessions tied to the provider remain valid until token expiry.

Listing Providers

See all configured OIDC providers:

SHOW OIDC PROVIDERS;

Output includes provider name, issuer, audience, JWKS URI, claim mapping rules, and creation timestamp.

Removing a Provider

Drop a provider:

DROP OIDC PROVIDER IF EXISTS okta;

Existing sessions using tokens from that provider are revoked at their next request.

Validating JWT Tokens

NodeDB validates tokens in this order:

  1. Decode header → extract kid (key ID), alg (algorithm), iss (issuer)
  2. Look up provider by issuer
  3. Fetch/cache JWKS → retrieve public key for kid
  4. Validate signature → verify JWT signature matches the public key
  5. Check audience → aud claim must match the provider's configured audience
  6. Check expiry → exp claim must be in the future
  7. Apply claim mapping → extract fields, map to identity fields
  8. Build identity → create ephemeral AuthenticatedIdentity (no database user record needed)

Supported algorithms: RS256, ES256 (RSA and ECDSA).

End-to-End Example

Step 1: Configure the Provider

CREATE OIDC PROVIDER auth0
  ISSUER 'https://your-tenant.us.auth0.com/'
  JWKS_URI 'https://your-tenant.us.auth0.com/.well-known/jwks.json'
  AUDIENCE 'https://api.example.com'
  CLAIM MAPPING
    WHEN email = null SET DEFAULT_DATABASE = 1
    WHEN https://api.example.com/roles = 'readwrite' ADD ROLES ['readwrite'];

Step 2: Get a Token from Auth0

User logs in via your web app, Auth0 issues a JWT:

eyJhbGciOiJSUzI1NiIsInR5cCI6IkpXVCJ9.
eyJzdWIiOiIxMjM0NTY3ODkwIiwiZW1haWwiOiJqb2huQGFjbWUuY29tIiwi
aHR0cHM6Ly9hcGkuZXhhbXBsZS5jb20vcm9sZXMiOlsicmVhZHdyaXRlIl0s
ImF1ZCI6Imh0dHBzOi8vYXBpLmV4YW1wbGUuY29tIiwiaWF0IjoxNTE2MjM5MDIyLCJleHAiOjE3MTYyMzkwMjJ9.
...signature...

Step 3: Authenticate to NodeDB

Send the token in the Authorization header:

curl -H "Authorization: Bearer eyJhbGciOi..." \
     http://localhost:6480/v1/query \
     -d '{"sql": "SELECT 1"}'

Or via native protocol (e.g., with nodedb-client) on port 6433:

let auth = AuthMethod::OidcBearer {
    token: "eyJhbGciOi...".to_string(),
    provider: "auth0".to_string(),
};
let session = nodedb_client::connect("localhost:6433", auth).await?;

Step 4: Query as the Authenticated User

NodeDB validates the token and applies claim mapping rules:

Token decoded and validated:
  email = "john@acme.com"
  https://api.example.com/roles = "readwrite"
  audience = "https://api.example.com"  ✓
  iss = "https://your-tenant.us.auth0.com/"  ✓
  exp > now()  ✓

Claim mapping applied:
  Rule 1 (email): matches → default_database = production
  Rule 2 (roles = readwrite): matches → add_roles = [readwrite]

Session created:
  username = "john@acme.com" (from email claim)
  default_database = "production"
  roles = [readwrite]

Query executed as authenticated session

Token Lifetime and Session Close

Sessions created via OIDC inherit the token's lifetime:

session.token_expiry = jwt.exp

When a session's token expires, the connection closes with error TOKEN_EXPIRED. The user must re-authenticate with a fresh token.

If both an idle timeout and token expiry are configured on the database, the session closes at whichever comes first:

close_at = min(idle_deadline, token_expiry)

Token Refresh

OIDC bearer tokens typically have short lifetimes (minutes to hours). Refresh is handled entirely by the client:

  1. Client detects token expiry (or error TOKEN_EXPIRED from NodeDB)
  2. Client calls Auth0's token refresh endpoint (if available)
  3. Client reconnects to NodeDB with the new token
  4. New session created with the new token's expiry

NodeDB does not participate in token refresh — it validates whatever token is presented.

Audit Trail

Every OIDC login is recorded:

SHOW AUDIT WHERE event_type = 'auth_success' AND database = 'sales';

Audit record includes:

  • Provider name (e.g., auth0)
  • JWT subject (sub claim) for forensics
  • Username (from claim mapping)
  • Timestamp
  • Client IP

Failures are also logged:

SHOW AUDIT WHERE event_type = 'auth_failure' AND reason LIKE '%oidc%';

Required Role

Creating, altering, or dropping OIDC providers requires:

CREATE OIDC PROVIDER — ClusterAdmin or Superuser
ALTER OIDC PROVIDER  — ClusterAdmin or Superuser
DROP OIDC PROVIDER   — ClusterAdmin or Superuser

Regular users cannot modify providers.

Troubleshooting

Token rejected: invalid signature

  • JWKS cache may be stale; it will auto-refresh on next unknown key ID
  • Check that the issuer matches exactly (including trailing /)

Token rejected: audience mismatch

  • JWT aud claim doesn't match the configured audience. Check both the token and the provider config.

Login succeeds but permission denied immediately

  • Claim mapping may have failed (missing required claim). Check the claim-mapping rules and the JWT contents.

OIDC provider not found

  • Provider was dropped or the provider field in the login request is misspelled. List providers: SHOW OIDC PROVIDERS

See authentication for general auth troubleshooting.

View page sourceLast updated on Jun 10, 2026 by Farhan Syah