Setting up Ocient OAuth/OIDC SSO with Auth0

A step-by-step guide for a system administrator to put an Ocient System behind OAuth 2.0 / OpenID Connect single sign-on, covering the things that are easy to get wrong:

1. An SSO-only connectivity pool (no password logins on the public endpoint).
2. Multiple databases — configure SSO once on system and have every other database (sandbox, etc.) inherit it.
3. Separate identity-provider applications for the two very different login styles: interactive device-grant (humans, headless CLIs) and machine-to-machine (scripts, services) — Ocient and OAuth force this split.

Scope. Concrete steps are written for Auth0 as the identity provider (IdP), because the app-type rules and quirks below are Auth0-specific. But the Ocient-side mechanics — issuer formatting, the azp/client_id rule, how a token is matched to an integration, multi-database fallback, the connectivity pool — apply to any OIDC provider (Okta, Entra ID, Keycloak, Google). Where a point is Auth0-only it is flagged.

This guide is IdP + DDL only. It does not cover provisioning the IdP as code (Terraform) or any one organization's specific automation.

0. Concepts that will bite you if you skip them

Read these once; every troubleshooting symptom below traces back to one of them.

• A token is matched to an integration by its azp (authorized party), which must equal that integration's client_id. Adding "extra audiences" relaxes the aud check but not azp. Consequence: a token minted by application X can only be validated by an Ocient integration whose client_id is X.

• One IdP application cannot serve both login styles. Device Authorization grant (device_code) requires a public app; Client Credentials grant requires a confidential app. Auth0 (and the OAuth specs) won't let one app be both. So you will create two applications and two Ocient integrations.

• The SQL Nodes do OIDC server-side. During the auth-code and token flows the node itself fetches the provider's discovery document, JWKS, and (for some flows) the token endpoint. Therefore every SQL Node needs outbound internet access to the IdP, and a valid TLS certificate (OIDC requires TLS; the node acts as an HTTPS server during the browser redirect). If the node can't reach the IdP, the user's browser shows a misleading Could not resolve host: auth.example.com — but it's the node that can't resolve it, not the browser.

• The issuer must have NO trailing slash. Ocient builds the discovery URL by appending /.well-known/openid-configuration. https://auth.example.com → correct; https://auth.example.com/ → https://auth.example.com//.well-known/… → 404. (Token iss is still validated against the discovery document's own issuer, which Auth0 reports with the slash — that's fine and expected.)

• An integration lives on a database, and databases inherit upward to system. A database with no integration of its own falls back to the system database's integration. A database with even one integration stops looking and uses only its own. Put shared integrations on system.

• The token (machine) flow resolves the integration by the connected database's primary — not by the connectivity pool, and not by identityprovider alone. (More in §6.)

• Config changes need a rolehostd restart. CREATE/ALTER SSO INTEGRATION and connectivity-pool changes take effect only after restarting rolehostd on the SQL Node(s).

1. Prerequisites

• An Ocient System (v25+ for multiple integrations per database; verified on v28) with admin access (admin@system or a user with the Security Administrator role on system).
• SQL Node(s) with: a valid TLS cert/key at /var/opt/ocient/server.crt / server.key, and outbound network access to your IdP.
• An IdP custom domain so issued tokens carry your domain as iss (Auth0: Branding → Custom Domains). Example: auth.example.com.
• A client host with pyocient and/or the Ocient JDBC driver.

Throughout: replace auth.example.com (IdP), db.example.com (advertised SQL endpoint), 10.0.0.20 (SQL Node private IP), and the database/integration/group names with your own.

2. Part A — Identity provider (Auth0)

You will create two applications plus one API.

2a. Interactive application (humans + headless CLIs)

This drives the browser Authorization Code flow and the Device Authorization flow that pyocient/JDBC use on machines without a browser.

• Applications → Create Application → Native. Native is a public client (PKCE, no client authentication) — required for device_code.
  • Auth0-specific: enabling device_code on any other app type fails with "Application Type must be native when device_code is enabled."
• Grant types: Authorization Code, Refresh Token, Device Code.
• Allowed Callback URLs: the loopback ports the drivers use for the auth-code flow, and your Ocient web-UI callback if you use it:

  http://localhost:7050, http://127.0.0.1:7050, https://db.example.com/v1/callback
  

• Note the Client ID and Client Secret — even though native apps are "public," Ocient's CREATE SSO INTEGRATION accepts a client_secret and uses it where the flow allows.

Trade-off (Auth0): public/native clients are treated as untrusted, so Auth0 re-displays the consent screen on every login and never remembers the grant. If you do not need device-grant (every user has a browser), use a Regular Web (confidential) app with Authorization Code instead — it skips consent. You lose headless device-grant but gain no-consent logins. You cannot have both on one app.

2b. Machine-to-machine application + API (scripts/services)

Client Credentials needs a confidential app and an API (resource server) to issue tokens for.

• Applications → APIs → Create API. Give it an identifier (this becomes the token audience), e.g. https://db.example.com. It does not need to be a real URL. Set a short token expiration (e.g. 24h) — these tokens get handed to end users.
• Applications → Create Application → Machine to Machine, authorize it for the API above. This app has Client Credentials only.
• Note its Client ID and Client Secret — these live only in your server-side secret store (never in a browser or repo).

2c. (Optional) group/role claims for finer access

Interactive logins can carry a custom claim that Ocient maps to a database group (see groups_claim_ids in §3). In Auth0 a post-login Action can stamp it:

exports.onExecutePostLogin = async (event, api) => {
  api.idToken.setCustomClaim("https://example.com/groups", event.authorization?.roles ?? []);
  api.accessToken.setCustomClaim("https://example.com/groups", event.authorization?.roles ?? []);
};

Important: post-login Actions run for interactive logins only. The client-credentials (M2M) flow has no user and runs no post-login Action, so M2M tokens carry no group claim. Give M2M access via the integration's default_group instead (§3) — do not rely on a claim for machine tokens.

2d. Mint M2M tokens from the custom domain

So the token's iss is https://auth.example.com/ (matching your integration), always request from the custom domain token endpoint:

curl -s https://auth.example.com/oauth/token \
  -H 'content-type: application/json' \
  -d '{"grant_type":"client_credentials",
       "client_id":"<M2M_CLIENT_ID>",
       "client_secret":"<M2M_CLIENT_SECRET>",
       "audience":"https://db.example.com"}'
# -> { "access_token": "<JWT>", "expires_in": 86400, ... }

The JWT will have iss=https://auth.example.com/, aud=https://db.example.com, and azp=<M2M_CLIENT_ID>. Those three facts are what Ocient checks.

3. Part B — Ocient: groups, grants, and the two integrations

Connect as an admin to the system database (password handshake, not SSO):

pyocient "ocient://admin@system:<password>@10.0.0.20:4050/system?tls=unverified"

3a. Groups + cross-database grants

SSO users are placed in a group; the group carries the privileges. Groups are qualified to the integration's database (system here), and you grant them privileges on the user databases (sandbox, …) with cross-database grants.

CREATE GROUP readonly;            -- read-only (the default for SSO users)
CREATE GROUP readwrite;           -- elevated, optional

-- Cross-database grants: a system group gets privileges on another database.
GRANT USE    ON DATABASE "sandbox" TO GROUP "readonly";
GRANT SELECT ON DATABASE "sandbox" TO GROUP "readonly";
-- VIEW is what makes objects visible in the catalog (SHOW TABLES, schema
-- browsing). Without it a read-only user can SELECT but sees no tables.
GRANT VIEW TABLE  ON DATABASE "sandbox" TO GROUP "readonly";
GRANT VIEW SCHEMA ON DATABASE "sandbox" TO GROUP "readonly";
GRANT VIEW VIEW   ON DATABASE "sandbox" TO GROUP "readonly";

GRANT USE, SELECT, CREATE TABLE, CREATE VIEW, CREATE SCHEMA
  ON DATABASE "sandbox" TO GROUP "readwrite";

3b. The interactive integration (device-grant / auth-code)

CREATE SSO INTEGRATION sso_interactive PROTOCOL oidc
  issuer        = "https://auth.example.com",          -- NO trailing slash
  client_id     = "<INTERACTIVE_CLIENT_ID>",
  client_secret = "<INTERACTIVE_CLIENT_SECRET>",
  default_group = "readonly",                          -- everyone who logs in
  additional_scopes    = ["email", "profile", "offline_access"],
  additional_audiences = ["https://auth.example.com/"],-- the userinfo audience
  groups_claim_ids      = ["https://example.com/groups"],
  groups_claim_mappings = { "readwrite-role" = "readwrite" };

• default_group is the key to "everyone is read-only by default": any validated login with no matching group still lands in readonly.
• groups_claim_mappings elevates users whose token carries a matching group value (from the Action in §2c).

3c. The machine-to-machine integration

A second integration whose client_id is the M2M app (because azp must match — §0):

CREATE SSO INTEGRATION sso_m2m PROTOCOL oidc
  issuer        = "https://auth.example.com",
  client_id     = "<M2M_CLIENT_ID>",
  client_secret = "<M2M_CLIENT_SECRET>",
  default_group = "readonly",
  -- Required for token-only (no-browser) authentication:
  enable_id_token_authentication = true,
  -- Accept the M2M token's audience (the API identifier from §2b):
  additional_audiences = ["https://db.example.com", "https://auth.example.com/"],
  groups_claim_ids      = ["https://example.com/groups"],
  groups_claim_mappings = { "readwrite-role" = "readwrite" };

3d. Make the interactive integration the database default

SET designates the primary integration for a database. Make the interactive one primary on system so ordinary resolution (and device-grant) works everywhere by default. The M2M one stays a non-primary integration on system, selected explicitly at connect time (§6).

ALTER DATABASE system SET SSO INTEGRATION sso_interactive;

Both integrations now exist on system; sso_interactive is primary. Do not make sso_m2m the primary — doing so makes every default login (including the web UI and device-grant) try to authenticate with the confidential M2M app, which has no device_code grant, and interactive login breaks.

3e. To change an integration later

Use the top-level statement (a common trap):

ALTER SSO INTEGRATION sso_m2m SET additional_audiences = ["https://db.example.com"];

ALTER DATABASE … ALTER SSO INTEGRATION is only for assigning an integration to a database; using it to set properties fails with "syntax error … missing 'security' at 'sso'."

4. Part C — The SSO-only connectivity pool

A connectivity pool publishes the address clients connect to and can require SSO, so the public endpoint refuses password logins.

CREATE CONNECTIVITY_POOL IF NOT EXISTS sso_pool
  SOURCE_ADDRESS '0.0.0.0/0' PRIORITY 10
  SSO INTEGRATION sso_interactive REQUIRE_SSO true
  PARTICIPANTS ((NODE sql0
    LISTEN_ADDRESS '10.0.0.20' LISTEN_PORT 4051
    ADVERTISED_ADDRESS 'db.example.com' ADVERTISED_PORT 4050
    OPENAPI_PORT 443 OPENAPI_ADVERTISED_ADDRESS 'db.example.com' OPENAPI_ADVERTISED_PORT 443));

Notes and gotchas:

• REQUIRE_SSO true makes this endpoint reject username/password ("This connectivity pool requires SSO authentication"). Good — that's the point of an SSO-only endpoint.
• The pool's SSO INTEGRATION governs the interactive flow on this pool. (The token flow ignores it — §6.)
• LISTEN_PORT is the node's port; ADVERTISED_ADDRESS/PORT is what the node hands back to the client for redirects, so it must be the public name/port that routes back to this node (commonly a load balancer / TLS terminator in front).
• Connectivity pools do not support dynamic IPs — if a node's IP changes you must ALTER CONNECTIVITY_POOL … ALTER PARTICIPANT.
• Grammar gotchas: omit LISTEN_PORT rather than passing NULL; the participants list is a parenthesised list of parenthesised entries.

5. Part D — Multiple databases (inherit from system)

This is the payoff of putting integrations on system:

• Create user databases normally: CREATE DATABASE sandbox; (and others later).
• Do not assign an integration to them. Because they have none, they fall back to system, inheriting both sso_interactive (primary) and sso_m2m.
• Grant each database's privileges to the system groups with the cross-database grants from §3a (repeat per database).
• A new database added next year automatically works the same way — just add its grants. You configure SSO once, on system.

If you ever assign an integration directly to sandbox, it stops inheriting from system and uses only its own integrations. Usually you don't want that.

Apply everything: restart rolehostd on the SQL Node(s) so the integrations and pool take effect.

6. Part E — Connecting

6a. Interactive (device-grant — no local browser needed)

The driver prints a URL and a code; the user approves in any browser.

# pyocient
pyocient "ocient://db.example.com/sandbox?handshake=SSO&ssoOAuthFlow=deviceGrant"

# JDBC CLI
connect to jdbc:ocient://db.example.com:4050/sandbox;handshake=SSO;ssoOAuthFlow=deviceGrant;user=;password=;

Leave user/password empty — handshake=SSO drives it. Omit ssoOAuthFlow to let the driver auto-pick auth-code (browser present) vs device-grant (not).

6b. Machine-to-machine (pre-obtained token)

Mint a token (§2d), then present it via Ocient's SSO Token Flow. Two things must be set together, and this is the subtle part:

# pyocient
pyocient "ocient://id_token@system:<JWT>@db.example.com:4050/sandbox?handshake=SSO&identityprovider=sso_m2m&tls=on"

# JDBC CLI
connect to jdbc:ocient://db.example.com:4050/sandbox;handshake=SSO;identityprovider=sso_m2m;user=id_token@system;password=<JWT>;

• user=id_token@system — the username is the token type (id_token), and the @system suffix routes token validation to the system database's integration scope, where both integrations live. (Use access_token only if your IdP exposes a token-introspection endpoint — Auth0 does not, so use id_token; the JWT works as the "id token" here because enable_id_token_authentication = true.)
• identityprovider=sso_m2m — selects the M2M integration within that scope (instead of the system primary, sso_interactive).
• You still connect to database=sandbox, so queries are unqualified and run as readonly.

Why both are required: the token flow resolves the integration from the connected database's primary. A plain sandbox connection falls back to the system primary (sso_interactive) without a name lookup, so identityprovider alone is silently ignored there. The @system qualifier is what makes the node look in the system scope by name, where identityprovider can then pick sso_m2m. (identityprovider is honored only when the named integration is in the resolved scope.)

Because the M2M token maps to default_group = readonly, it can only SELECT; writes are denied. There is no user, so audit logs identify it by iss::sub.

7. Troubleshooting — symptom → cause

8. What generalizes beyond Auth0

These are not Auth0-specific — expect them with any OIDC provider:

• The azp == client_id matching rule, and therefore two apps → two integrations for device-grant vs machine tokens.
• Integrations live on a database; databases inherit upward to system.
• The token flow picks the integration by the connected database's primary; select a non-primary one with user=<tokentype>@<database> + identityprovider=<name>.
• SQL Nodes need TLS and outbound access to the IdP (server-side OIDC).
• default_group is how you make "authenticated ⇒ read-only by default" work.
• Restart rolehostd to apply changes.

Provider-specific pieces to re-map for another IdP:

• App types: the public-vs-confidential split exists everywhere, but the names differ (Auth0 Native / Machine-to-Machine; Okta Native / Service; Entra public client flag + client credentials).
• Introspection: if your IdP does publish an introspection_endpoint, the user=access_token path becomes available and you may not need enable_id_token_authentication.
• Custom claims for groups: every provider has its own mechanism (Auth0 Actions, Okta claims, Entra app-roles/optional claims). The Ocient side (groups_claim_ids/groups_claim_mappings) is identical.
• Custom domain / issuer: make sure machine tokens are minted from the same issuer host the integration validates against.