> ## Documentation Index > Fetch the complete documentation index at: https://docs.cockroachlabs.com/llms.txt > Use this file to discover all available pages before exploring further. # SASL OAUTHBEARER (OAuth 2.0) Authentication for SQL Clients CockroachDB supports SASL OAUTHBEARER ([RFC 7628](https://www.rfc-editor.org/rfc/rfc7628)) authentication for SQL client connections. With OAUTHBEARER, a SQL client presents an OAuth 2.0 bearer token issued by your identity provider (IdP), such as Okta, Keycloak, Microsoft Entra ID, or Google, and CockroachDB validates the token and maps it to a SQL user. The token must be a JWT. CockroachDB cannot validate opaque access tokens. For example, Google issues opaque access tokens, so the device flow does not work with Google. Only a Google ID token presented by a driver can be validated. The protocol matches the OAuth support in PostgreSQL 18, so `psql` 18, `libpq` 18, and PostgreSQL-compatible drivers that implement OAUTHBEARER can authenticate to CockroachDB without any CockroachDB-specific client logic. This page describes how to configure a cluster for OAUTHBEARER authentication, how to connect with `cockroach sql`, `psql`, and other clients, and how to manage users that the cluster provisions automatically. ## Overview CockroachDB offers two ways to authenticate SQL clients with tokens from an external IdP: * **SASL OAUTHBEARER** (this page): the client sends a bearer token in a standard SASL exchange. The client can obtain the token itself, or `cockroach sql` and `psql` 18 can run an interactive [OAuth 2.0 device authorization flow](https://www.rfc-editor.org/rfc/rfc8628) in a browser and connect with the resulting token. Use this method when your clients support OAUTHBEARER natively. * **[Cluster single sign-on (SSO) using JWTs](https://docs.cockroachlabs.com/docs/stable/sso-sql.md)**: the client sends a JSON Web Token (JWT) in place of a password. Use this method with clients that do not implement OAUTHBEARER but accept a long password. Both methods validate the same token properties (issuer, signing key, expiry, and audience), and both support [automatic user provisioning](#step-4-configure-user-creation) and [group-based role synchronization](#step-5-enable-group-based-authorization-optional). The two methods are configured with separate cluster settings and separate [host-based authentication (HBA)](https://docs.cockroachlabs.com/docs/stable/security-reference/authentication.md#authentication-configuration) methods, so you can enable either or both. The methods differ in two ways: OAUTHBEARER requires the identity claim to hold exactly one value (refer to [Step 1](#step-1-configure-cluster-settings)), and OAUTHBEARER does not cache JWKS keys between logins. When OAUTHBEARER authentication is enabled, a node handles each login as follows: 1. The client connects over TLS and requests a SQL user. The server advertises the `OAUTHBEARER` SASL mechanism. 2. The client sends a bearer token. If the client has no token yet, it sends an empty token, and the server responds with the information the client needs to obtain one (refer to [Error response](#error-response)). 3. The server reads the token principal from the configured claim, before the token is verified, and, if an identity map is configured, maps it to a SQL username. The result must match the SQL user that the client requested. 4. If the user does not exist and [automatic user provisioning](#option-1-automatic-user-provisioning-recommended) is enabled, the server verifies the token's signature against the issuer's JSON Web Key Set (JWKS) and checks its issuer, then creates the user. 5. The server fully validates the token: signature, issuer, expiry, and audience. 6. If [group-based authorization](#step-5-enable-group-based-authorization-optional) is enabled, the server synchronizes the user's role memberships from the token's group claim. Authentication events are recorded in the [`SESSIONS`](https://docs.cockroachlabs.com/docs/stable/logging#sessions) logging channel. > [!NOTE] > > OAUTHBEARER authentication cannot be used for the `root` user or other [reserved identities](https://docs.cockroachlabs.com/docs/stable/security-reference/authorization.md#reserved-identities). Credentials for `root` must be managed separately using password or certificate authentication to ensure continuous administrative access regardless of IdP availability. ## Prerequisites * An identity provider that issues JWT-format OAuth 2.0 access tokens and publishes its signing keys as a JWKS, typically through an OpenID Connect discovery document at `https://{issuer URL}/.well-known/openid-configuration`. * A cluster that is fully upgraded to v26.4. The `oauth` HBA method is gated on the v26.4 cluster version. If you configure it before the upgrade is finalized, the `SET CLUSTER SETTING` statement fails with an `ObjectNotInPrerequisiteState` error. * A secure cluster. The `oauth` HBA method is accepted only on TLS connections. * The ability to update cluster settings. Refer to [`SET CLUSTER SETTING`: Required permissions](https://docs.cockroachlabs.com/docs/stable/set-cluster-setting#required-privileges). * SQL users that correspond to your external identities, unless you enable [automatic user provisioning](#option-1-automatic-user-provisioning-recommended). To create users, you must have access to the [`admin` role](https://docs.cockroachlabs.com/docs/stable/security-reference/authorization#admin-role). * For the interactive device flow with `cockroach sql` or `psql`, an OAuth client registered with your IdP as a **public** client (no client secret) with the device authorization grant enabled, and an issuer that is reachable over HTTPS. Before you begin, enable authentication logging to help confirm your configuration or troubleshoot issues. Refer to [Troubleshooting](#troubleshooting). ## Configuration ### Step 1: Configure cluster settings OAUTHBEARER authentication is controlled by the [cluster settings](https://docs.cockroachlabs.com/docs/stable/cluster-settings.md) in the following table. Each setting applies only to OAUTHBEARER logins; the [`server.jwt_authentication.*` settings](https://docs.cockroachlabs.com/docs/stable/sso-sql#cluster-settings) for cluster SSO using JWTs are separate. | Cluster Setting | Description | Default | | - | - | - | | [`server.oauthbearer_authentication.enabled`](https://docs.cockroachlabs.com/docs/stable/cluster-settings#setting-server-oauthbearer-authentication-enabled) | Enables or disables SASL OAUTHBEARER login for the SQL interface. Must be set to `true`. | `false` | | [`server.oauthbearer_authentication.issuers`](https://docs.cockroachlabs.com/docs/stable/cluster-settings#setting-server-oauthbearer-authentication-issuers) | The accepted token issuers. Can be any of the following: The format is detected automatically. A token whose `iss` claim is not in this list is rejected. | (empty) | | [`server.oauthbearer_authentication.issuers.custom_ca`](https://docs.cockroachlabs.com/docs/stable/cluster-settings#setting-server-oauthbearer-authentication-issuers-custom-ca) | A PEM-encoded custom root CA certificate used to verify the issuer's TLS certificate when fetching a JWKS or querying the userinfo endpoint. Set this when your IdP uses a private CA. | (empty) | | [`server.oauthbearer_authentication.audience`](https://docs.cockroachlabs.com/docs/stable/cluster-settings#setting-server-oauthbearer-authentication-audience) | The accepted audience values, as a single string or a JSON array of strings. A token is accepted only if its `aud` claim contains one of these values. Tokens are rejected when this setting is empty, so you must set it to the audience your IdP places in tokens for CockroachDB, typically the OAuth client ID. | (empty) | | [`server.oauthbearer_authentication.jwks`](https://docs.cockroachlabs.com/docs/stable/cluster-settings#setting-server-oauthbearer-authentication-jwks) | A static JSON Web Key Set containing the public signing keys of your issuers. Ignored when `server.oauthbearer_authentication.jwks_auto_fetch.enabled` is `true`. | `{"keys":[]}` | | [`server.oauthbearer_authentication.jwks_auto_fetch.enabled`](https://docs.cockroachlabs.com/docs/stable/cluster-settings#setting-server-oauthbearer-authentication-jwks-auto-fetch-enabled) | If `true`, each node fetches signing keys from the issuer's discovery document (or from the JWKS URI given in `server.oauthbearer_authentication.issuers`) on every login, so you do not need to maintain `server.oauthbearer_authentication.jwks` as the IdP rotates keys. | `false` | | [`server.oauthbearer_authentication.claim`](https://docs.cockroachlabs.com/docs/stable/cluster-settings#setting-server-oauthbearer-authentication-claim) | The token claim whose value identifies the user. Can be a simple claim name such as `email`, or an [RFC 6901 JSON Pointer](https://www.rfc-editor.org/rfc/rfc6901) such as `/kubernetes.io/serviceaccount/uid` for a nested claim. When empty, the `sub` claim is used. The claim must hold exactly one value; a multi-valued claim is rejected. This differs from the JWT method, which accepts whichever value matches the requested user. | (empty) | | [`server.oauthbearer_authentication.scope`](https://docs.cockroachlabs.com/docs/stable/cluster-settings#setting-server-oauthbearer-authentication-scope) | The OAuth scope advertised to clients in the [error response](#error-response) when a token is missing or rejected. Discovery-driven clients such as `psql` request this scope from the IdP. This setting is not used to validate tokens. | (empty) | | [`server.oauthbearer_authentication.client.timeout`](https://docs.cockroachlabs.com/docs/stable/cluster-settings#setting-server-oauthbearer-authentication-client-timeout) | The timeout for HTTP calls the node makes during OAUTHBEARER authentication, such as fetching a JWKS or querying the userinfo endpoint. | `15s` | | [`server.oauthbearer_authentication.authorization.enabled`](https://docs.cockroachlabs.com/docs/stable/cluster-settings#setting-server-oauthbearer-authentication-authorization-enabled) | Enables [group-based role synchronization](#step-5-enable-group-based-authorization-optional) from the token's group claim. | `false` | | [`server.oauthbearer_authentication.group_claim`](https://docs.cockroachlabs.com/docs/stable/cluster-settings#setting-server-oauthbearer-authentication-group-claim) | The token claim that contains the user's groups, used for role synchronization. | `groups` | | [`server.oauthbearer_authentication.userinfo_group_key`](https://docs.cockroachlabs.com/docs/stable/cluster-settings#setting-server-oauthbearer-authentication-userinfo-group-key) | The JSON key to read from the issuer's userinfo endpoint when the group claim is absent from the token. | `groups` | | [`security.provisioning.oauth.enabled`](https://docs.cockroachlabs.com/docs/stable/cluster-settings#setting-security-provisioning-oauth-enabled) | Enables [automatic user provisioning](#option-1-automatic-user-provisioning-recommended) on first OAUTHBEARER login. | `false` | A minimal configuration enables the method, trusts one issuer, fetches that issuer's signing keys automatically, restricts the audience, and names the claim that holds the username: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} SET CLUSTER SETTING server.oauthbearer_authentication.enabled = true; SET CLUSTER SETTING server.oauthbearer_authentication.issuers = 'https://auth.example.com'; SET CLUSTER SETTING server.oauthbearer_authentication.jwks_auto_fetch.enabled = true; SET CLUSTER SETTING server.oauthbearer_authentication.audience = 'cockroachdb-sql'; SET CLUSTER SETTING server.oauthbearer_authentication.claim = 'email'; ``` To accept more than one issuer, set `server.oauthbearer_authentication.issuers` to a JSON array, or to a JSON object when an issuer's JWKS is not discoverable from its `.well-known/openid-configuration` endpoint: | Type | Example | | - | - | | Single issuer URL | `'https://auth.example.com'` | | Array of issuer URLs | `'["https://auth.example.com", "https://accounts.google.com"]'` | | Map of issuer URLs | `'{ "issuer_jwks_map": { "https://auth.example.com": "https://auth.example.com/oauth2/v1/keys", "https://accounts.google.com": "https://www.googleapis.com/oauth2/v3/certs" } }'` | > [!NOTE] > > Discovery-driven clients such as `psql` 18 rely on the server to advertise the issuer's OpenID configuration endpoint, and the server does so only when exactly one issuer is configured. If you configure several issuers, clients must be told the issuer explicitly (for example, with `--oauth-issuer` in `cockroach sql`), or must obtain a token on their own. If you prefer static signing keys, leave `server.oauthbearer_authentication.jwks_auto_fetch.enabled` at `false` and set `server.oauthbearer_authentication.jwks` to the JWKS published by your issuer. IdPs rotate their signing keys periodically, so you must update the static key set before the previous keys expire, or OAUTHBEARER logins will fail. Automatic fetching avoids this maintenance. Because the identity map and the HBA configuration can contain information you may not want all users to see, consider enabling redaction of those settings. Refer to [Sensitive settings](https://docs.cockroachlabs.com/docs/stable/cluster-settings.md#sensitive-settings). ### Step 2: Configure host-based authentication (HBA) To enable OAUTHBEARER for a set of users, add a rule with the `oauth` method to the [host-based authentication (HBA)](https://docs.cockroachlabs.com/docs/stable/security-reference/authentication.md#authentication-configuration) configuration in the cluster setting `server.host_based_authentication.configuration`. The simplest rule allows every user from every address to authenticate with a bearer token: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} SET CLUSTER SETTING server.host_based_authentication.configuration = 'host all all all oauth'; ``` The `oauth` method accepts two options, each of which may appear at most once on a rule: * `map=`: apply the named [identity map](#step-3-map-identities-optional) from `server.identity_map.configuration` to translate the token principal into a SQL username. Without `map=`, the value of the configured claim is used directly as the SQL username, so it must equal the username the client requests. * `issuer=`: accept only tokens issued by this issuer on this rule. The URL must be one of the issuers configured in `server.oauthbearer_authentication.issuers`. Without `issuer=`, a token from any configured issuer is accepted. Use this option to pair different issuers with different identity maps or user sets. For example, the following configuration maps principals from `https://auth.example.com` through the `crdb` identity map, and falls back to certificate or password authentication for anyone else: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} SET CLUSTER SETTING server.host_based_authentication.configuration = ' host all all all oauth map=crdb issuer=https://auth.example.com host all all all cert-password'; ``` Keep the following in mind: * The `oauth` method is accepted only on TLS connections. A client that matches an `oauth` rule over a non-TLS connection is rejected with `method "oauth" required for this user, but unusable over this connection type`. * Any option other than `map` and `issuer` is a configuration error, and the `SET CLUSTER SETTING` statement fails. * Setting an `oauth` rule before every node is running v26.4 and the upgrade is finalized fails with an `ObjectNotInPrerequisiteState` error. * `root` and other [reserved identities](https://docs.cockroachlabs.com/docs/stable/security-reference/authorization.md#reserved-identities) cannot log in with OAUTHBEARER, whether or not an identity map is used. The mandatory `root` rule continues to allow `root` to authenticate with a certificate or password. ### Step 3: Map identities (optional) If the usernames in your tokens do not match your SQL usernames exactly, configure an identity map in the cluster setting `server.identity_map.configuration` and reference it from the HBA rule with `map=`. The format follows PostgreSQL's [`pg_ident.conf`](https://www.postgresql.org/docs/current/auth-username-maps.html): each line is a space-separated triple: ` ` * `` is the name you pass to `map=` in the HBA rule. One configuration can hold several maps. * `` is matched against the value of the claim named by `server.oauthbearer_authentication.claim`. A value that starts with `/` is interpreted as a case-insensitive regular expression; anchor it with `^` and `$` to avoid partial matches. The expression may contain one capturing group. * `` is the SQL user the principal may log in as. It can include `\1` to substitute the capturing group. > [!NOTE] > > This differs from the identity map used by [cluster SSO using JWTs](https://docs.cockroachlabs.com/docs/stable/sso-sql.md#identity-map-configuration), where the first field is the issuer URL rather than a map name. For OAUTHBEARER, pin a rule to an issuer with the `issuer=` HBA option instead. For example, the following map lets any `@example.com` email address log in as the SQL user named by the part before the `@`, and lets the fixed subject `1232316645658094244789` log in as `roach`: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} SET CLUSTER SETTING server.identity_map.configuration = ' crdb /^(.*)@example\.com$ \1 crdb 1232316645658094244789 roach'; ``` With this map and the rule `host all all all oauth map=crdb`, a token whose `email` claim is `alice@example.com` can log in as `alice`. The client still requests the SQL user to connect as (for example, `--user=alice`); the login fails if the mapped principal does not resolve to that user. ### Step 4: Configure user creation CockroachDB supports two approaches for the creation of users who authenticate with OAUTHBEARER: #### Option 1: Automatic user provisioning (recommended) With automatic user provisioning, CockroachDB creates a SQL user during the user's first successful OAUTHBEARER authentication. This eliminates the need to pre-create accounts for every identity in your IdP. When enabled: * Before the user is created, the token's signature is verified and its issuer is checked, so only identities from your configured issuers can be provisioned. Expiry and audience are checked after provisioning, as part of full token validation. * The new user receives a `PROVISIONSRC` role option set to `oauth:`, for example `oauth:https://auth.example.com`. * The `estimated_last_login_time` column of [`SHOW USERS`](https://docs.cockroachlabs.com/docs/stable/show-users) is tracked for auditing. * Auto-provisioned users cannot change their own passwords; their credentials are managed by the IdP. The username is the mapped token principal and must meet the CockroachDB [requirements for usernames](https://docs.cockroachlabs.com/docs/stable/create-user.md#user-names). > [!NOTE] > > Before you enable automatic user provisioning, Cockroach Labs recommends that you enable [group-based authorization](#step-5-enable-group-based-authorization-optional) and create the matching roles. This ensures that new users are members of the intended roles, with the privileges they confer, on their first login. Auto-provisioned users receive roles only for groups that already exist as CockroachDB roles. **To enable automatic user provisioning:** ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} SET CLUSTER SETTING security.provisioning.oauth.enabled = true; ``` To view and manage auto-provisioned users, refer to [Manage auto-provisioned users](#manage-auto-provisioned-users). #### Option 2: Manual or scripted user creation Alternatively, create users before they authenticate. The username must match the token principal after the identity map is applied. ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} CREATE ROLE alice LOGIN; GRANT developers TO alice; ``` To create users in bulk, produce a `.sql` file with one [`CREATE ROLE`](https://docs.cockroachlabs.com/docs/stable/create-role) statement per user and run it with [`cockroach sql --file`](https://docs.cockroachlabs.com/docs/stable/cockroach-sql#execute-sql-statements-from-a-file). ### Step 5: Enable group-based authorization (optional) CockroachDB can synchronize a user's role memberships from the groups in their token on every OAUTHBEARER login, in the same way that [JWT authorization](https://docs.cockroachlabs.com/docs/stable/jwt-authorization.md) does for cluster SSO using JWTs. 1. Create CockroachDB roles whose names match your IdP group names, and grant privileges to those roles. Group names are normalized with case folding and Unicode normalization (NFC) before matching, so they typically match lowercase role names. ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} CREATE ROLE developers; GRANT ALL ON DATABASE app TO developers; CREATE ROLE analysts; GRANT SELECT ON DATABASE analytics TO analysts; ``` 2. Enable authorization and, if your IdP uses a claim other than `groups`, name the claim: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} SET CLUSTER SETTING server.oauthbearer_authentication.authorization.enabled = true; SET CLUSTER SETTING server.oauthbearer_authentication.group_claim = 'groups'; SET CLUSTER SETTING server.oauthbearer_authentication.userinfo_group_key = 'groups'; ``` On each login: * The groups are read from the `group_claim` claim. If that claim is absent or malformed, the node queries the issuer's userinfo endpoint with the access token and reads the `userinfo_group_key` key from the response. If the issuer's discovery document has no `userinfo_endpoint`, the login fails with `No userinfo endpoint`. If the userinfo lookup fails, the login is rejected. If the userinfo response does not contain the `userinfo_group_key` key, all of the user's direct role memberships are revoked and the login is rejected. * The claim is expected to be a JSON array of strings, for example `["developers", "analysts"]`. If it is a single string, it is split on commas only, so `"developers,analysts"` yields two groups, while a name containing spaces, such as `"data platform"`, is one group. Identity providers that convey multiple groups should return a JSON array. * Each group that matches an existing role is granted to the user. Groups that do not match a role are silently skipped. * Roles that the user holds [directly](https://docs.cockroachlabs.com/docs/stable/security-reference/authorization.md#direct-member) but that no longer match a group are revoked. Roles held only [indirectly](https://docs.cockroachlabs.com/docs/stable/security-reference/authorization.md#indirect-member) through another role are not revoked individually. * If the group list exists but is empty, all of the user's direct role memberships are revoked and the login is rejected with `OAUTHBEARER authorization: empty group list`. * Any role can be synchronized, including privileged roles such as `admin`. An IdP group named `admin` grants the `admin` role on login, and removing the user from that group revokes it. Control membership in such groups carefully in your IdP. For IdP-specific guidance on exposing groups in tokens for Okta, Keycloak, and Microsoft Entra ID, refer to [JWT Authorization](https://docs.cockroachlabs.com/docs/stable/jwt-authorization.md#step-2-configure-idp-specific-settings); the same claim configuration applies to OAUTHBEARER tokens. ## Connect to the cluster Clients can authenticate with OAUTHBEARER in three ways. ### Connect with `cockroach sql` using the device flow [`cockroach sql`](https://docs.cockroachlabs.com/docs/stable/cockroach-sql) can obtain a token for you by running the [OAuth 2.0 device authorization grant](https://www.rfc-editor.org/rfc/rfc8628) against your IdP. Pass the issuer URL and the public OAuth client ID, and optionally the scopes to request: ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} cockroach sql \ --url "postgresql://alice@db.example.com:26257/defaultdb?sslmode=verify-full" \ --certs-dir=certs \ --oauth-issuer=https://auth.example.com \ --oauth-client-id= \ --oauth-scope="openid email" ``` The shell fetches the issuer's discovery document, requests a device code, and prints a verification URL and a one-time code (opening a browser where one is available). After you sign in with your IdP, the shell receives the token and completes the SASL OAUTHBEARER exchange. No token is written to disk. | Flag | Environment variable | Description | | - | - | - | | `--oauth-issuer` | `COCKROACH_OAUTH_ISSUER` | The OAuth/OIDC issuer URL. Its discovery document must advertise a device authorization endpoint. | | `--oauth-client-id` | `COCKROACH_OAUTH_CLIENT_ID` | The public OAuth client identifier registered with the IdP for the device flow. | | `--oauth-scope` | `COCKROACH_OAUTH_SCOPE` | Optional space-separated scopes to request. Some identity providers, such as Okta, require a scope. | Keep the following in mind: * `--oauth-issuer` and `--oauth-client-id` must be specified together. * The connection must use `sslmode=verify-full`. The shell refuses to send a bearer token over a plaintext connection, or over a TLS connection that does not verify the server's identity, with an error such as `refusing to send an OAuth token over a plaintext connection` and the hint `use sslmode=verify-full`. * The issuer and the endpoints in its discovery document must use `https`. For local development against an IdP without TLS, set the environment variable `COCKROACH_OAUTH_ALLOW_INSECURE_ISSUER=true`. This relaxes only the check on the issuer's endpoints, with a warning; the `sslmode=verify-full` requirement for the connection to CockroachDB still applies. Do not use this setting in production. * The SQL user in the connection URL must be the user that the token principal maps to. ### Connect with `psql` 18 or `libpq` PostgreSQL 18 `libpq`, and therefore `psql` 18 and clients built on `libpq`, implement OAUTHBEARER with a built-in device flow. Supply the issuer and client ID as connection parameters: ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} psql "host=db.example.com port=26257 dbname=defaultdb user=alice sslmode=verify-full sslrootcert=certs/ca.crt oauth_issuer=https://auth.example.com oauth_client_id= require_auth=oauth" ``` `libpq` verifies that `oauth_issuer` matches the OpenID configuration endpoint that the server advertises in its [error response](#error-response). CockroachDB advertises that endpoint only when exactly one issuer is configured in `server.oauthbearer_authentication.issuers`, so the `libpq` device flow requires a single-issuer configuration. If `server.oauthbearer_authentication.scope` is set, `libpq` requests that scope; otherwise, set `oauth_scope` in the connection string. For the full list of OAuth connection parameters, including `oauth_client_secret` and `oauth_scope`, refer to the [PostgreSQL `libpq` documentation](https://www.postgresql.org/docs/18/libpq-connect.html#LIBPQ-PARAMKEYWORDS). ### Connect with a driver and a pre-obtained token Drivers that implement SASL OAUTHBEARER, such as [`pgx`](https://github.com/jackc/pgx) for Go, let your application supply a bearer token through a token-provider callback in the connection configuration. Your application obtains the token from the IdP by whatever OAuth flow suits it (for example, the client credentials grant for a service account) and the driver presents it when the server advertises `OAUTHBEARER`. The SQL user in the connection configuration must be the user that the token principal maps to through `server.oauthbearer_authentication.claim` and the identity map. Clients that do not implement OAUTHBEARER can still authenticate with a token by presenting it as a password. Refer to [Cluster Single Sign-on (SSO) using JWTs](https://docs.cockroachlabs.com/docs/stable/sso-sql.md). ## Manage auto-provisioned users When automatic user provisioning is enabled, you can identify and manage auto-provisioned users with the following methods. For additional patterns, such as finding dormant accounts and cleaning up users removed from your IdP, refer to [Manage auto-provisioned users](https://docs.cockroachlabs.com/docs/stable/ldap-authentication.md#manage-auto-provisioned-users) on the LDAP authentication page; the same statements apply with an `oauth:` source. ### View provisioned users Auto-provisioned users can be identified by their `PROVISIONSRC` role option using the [`SHOW USERS WITH SOURCE`](https://docs.cockroachlabs.com/docs/stable/show-users) syntax: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} SHOW USERS WITH SOURCE = 'oauth:https://auth.example.com'; ``` ``` username | options | member_of | estimated_last_login_time -----------+----------------------------------------------+--------------+-------------------------------- alice | {PROVISIONSRC=oauth:https://auth.example.com} | {developers} | 2026-09-14 19:18:00.201402+00 bob | {PROVISIONSRC=oauth:https://auth.example.com} | {analysts} | 2026-09-13 14:22:15.102938+00 (2 rows) ``` Replace `https://auth.example.com` with your issuer URL. The source must match the full `PROVISIONSRC` value exactly; pattern matching is not supported. ### Bulk-drop dormant provisioned users To de-provision auto-provisioned users at scale, use [`DROP PROVISIONED ROLES`](https://docs.cockroachlabs.com/docs/stable/drop-provisioned-roles). For example, to drop up to 100 users that were provisioned by your issuer and have not logged in since a specific date: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} DROP PROVISIONED ROLES WITH SOURCE = 'oauth:https://auth.example.com', LAST LOGIN BEFORE '2026-01-01' LIMIT 100; ``` Preview the matching users first by running [`SHOW USERS`](https://docs.cockroachlabs.com/docs/stable/show-users) with the same clauses. ### Restrictions on auto-provisioned users Users created through automatic provisioning have specific restrictions: * **Password changes**: Auto-provisioned users cannot change their own passwords using `ALTER USER`, even if the cluster setting `sql.auth.change_own_password.enabled` is `true`. * **`PROVISIONSRC` modification**: The `PROVISIONSRC` role option cannot be modified or removed once set. * **Authentication method**: These users must authenticate through their IdP; password-based authentication is not available. ## How it works ### Authentication sequence 1. **SASL exchange**: The client connects over TLS. If an `oauth` HBA rule matches, the server advertises the `OAUTHBEARER` mechanism and the client sends an RFC 7628 initial response containing `auth=Bearer `. 2. **Identity mapping**: The node parses the token and, before verifying it, reads the claim named by `server.oauthbearer_authentication.claim` (or `sub`), applies the identity map named by the rule's `map=` option if any, and confirms that the result includes the SQL user the client requested and is not a reserved identity. 3. **Provisioning**: If the user does not exist and `security.provisioning.oauth.enabled` is `true`, the node verifies the token's signature against the issuer's JWKS (static or auto-fetched) and checks that its `iss` claim is one of the configured issuers, then creates the user with `PROVISIONSRC = oauth:`. 4. **Token validation**: The node confirms that the token's `iss` claim is one of the configured issuers (and matches the rule's `issuer=` option, if set), verifies the signature against the issuer's JWKS, and checks the expiry and the `aud` claim against `server.oauthbearer_authentication.audience`. With automatic fetching enabled, the node fetches the issuer's discovery document and JWKS on every login; keys are not cached. 5. **Role synchronization**: If `server.oauthbearer_authentication.authorization.enabled` is `true`, the node reconciles the user's direct role memberships with the groups in the token. ### Error response When a token is missing or rejected, the server replies with an `AuthenticationSASLContinue` message whose body is the JSON error response defined in [RFC 7628 ยง3.2.2](https://www.rfc-editor.org/rfc/rfc7628#section-3.2.2): ```json theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} {"status":"invalid_token","scope":"openid email","openid-configuration":"https://auth.example.com/.well-known/openid-configuration"} ``` | Field | Meaning | | - | - | | `status` | Always `invalid_token`. | | `scope` | The value of `server.oauthbearer_authentication.scope`. Omitted when that setting is empty. | | `openid-configuration` | The OpenID configuration endpoint of the configured issuer. Included only when exactly one issuer is configured in `server.oauthbearer_authentication.issuers`. | Discovery-driven clients such as `libpq` deliberately send an empty token first to receive this response, then use the `openid-configuration` and `scope` values to obtain a token and reconnect. After sending the response, the server waits briefly for the client's acknowledgement and then closes the connection with a generic authentication error. The detailed reason for the rejection is written only to the [`SESSIONS`](https://docs.cockroachlabs.com/docs/stable/logging#sessions) log, so that unauthenticated clients cannot learn about your configuration or which users exist. Malformed SASL messages, such as a request for channel binding (`OAUTHBEARER-PLUS`) or a non-empty authorization identity, are framing errors: the connection fails without an error response. ## Troubleshooting Enable [`SESSIONS` logging](https://docs.cockroachlabs.com/docs/stable/logging#sessions) to preserve data that will help troubleshoot OAUTHBEARER issues: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} SET CLUSTER SETTING server.auth_log.sql_sessions.enabled = true; ``` > [!NOTE] > > Once all functionality is configured and tested successfully, Cockroach Labs recommends disabling session logging to conserve system resources. To view the logs, open `cockroach-sql-auth.log` from your [logging directory](https://docs.cockroachlabs.com/docs/stable/configure-logs.md#logging-directory). Clients receive only a generic error; the specific cause is in the log. Potential issues to investigate may pertain to: * **`ObjectNotInPrerequisiteState` when setting the HBA configuration**: The cluster is not yet fully upgraded to v26.4. Finish the upgrade and finalize it, then set the `oauth` rule. * **`method "oauth" required for this user, but unusable over this connection type`**: The client connected without TLS. Connect with `sslmode=verify-full`. * **Invalid issuer**: The token's `iss` claim is not in `server.oauthbearer_authentication.issuers`, or does not match the `issuer=` option on the matching HBA rule. * **Invalid audience**: The token's `aud` claim does not contain a value in `server.oauthbearer_authentication.audience`, or that setting is empty. Configure the IdP to issue tokens with an audience for CockroachDB and set the setting to match. * **Signing key not found**: With static keys, `server.oauthbearer_authentication.jwks` does not contain the key that signed the token. With automatic fetching, the node could not reach the issuer's JWKS endpoint, possibly because of a private CA (set `server.oauthbearer_authentication.issuers.custom_ca`) or a timeout (`server.oauthbearer_authentication.client.timeout`). * **Token principal does not map to the requested user**: The claim named by `server.oauthbearer_authentication.claim` is missing from the token (the login fails with a missing-claim error; `sub` is used only when that setting is empty), or the identity map does not translate it to the SQL user the client requested. Check the `map=` option on the HBA rule and the lines in `server.identity_map.configuration`. * **User does not exist**: The mapped user has not been created and `security.provisioning.oauth.enabled` is `false`. * **`refusing to send an OAuth token over a plaintext connection`**: `cockroach sql` was run with the device flow but without `sslmode=verify-full`. * **Issuer must use `https`**: The device flow in `cockroach sql` rejected a plaintext issuer or endpoint. Use an HTTPS issuer, or for local development only, set `COCKROACH_OAUTH_ALLOW_INSECURE_ISSUER=true`. * **`psql` cannot discover the issuer**: More than one issuer is configured, so the server does not advertise an `openid-configuration` endpoint. Use a single issuer, or use a client that is told the issuer explicitly. * **Empty group list or userinfo lookup failed**: Group-based authorization is enabled and the user has no groups in the token, or the userinfo endpoint could not be queried. Refer to [Step 5](#step-5-enable-group-based-authorization-optional). ## Security considerations 1. **Trust only known issuers**: Any valid token from an issuer in `server.oauthbearer_authentication.issuers` can log in as, and with provisioning enabled create, a SQL user. Keep the list minimal, and use the `issuer=` HBA option to restrict which issuer applies to which users. 2. **Restrict the audience**: Set `server.oauthbearer_authentication.audience` to a value that your IdP issues only for CockroachDB, so that tokens minted for other services cannot be replayed against the cluster. 3. **Prefer automatic key fetching**: Enable `server.oauthbearer_authentication.jwks_auto_fetch.enabled` so that key rotation at the IdP cannot lock users out or leave stale keys trusted, and set `server.oauthbearer_authentication.issuers.custom_ca` rather than disabling TLS verification when the IdP uses a private CA. 4. **Require verified TLS on clients**: Bearer tokens are credentials. `cockroach sql` enforces `sslmode=verify-full`; configure other clients the same way. 5. **Maintain backup authentication**: Keep a password or certificate for administrative users so that an IdP outage does not lock you out. `root` cannot use OAUTHBEARER. 6. **Review provisioned users and synchronized roles**: Audit `SHOW USERS WITH SOURCE` regularly, and remember that group-based authorization synchronizes every matching role, including `admin`. 7. **Tokens are not stored**: CockroachDB validates each token in memory and does not persist it. Keep token lifetimes short at the IdP. ## See also * [Cluster Single Sign-on (SSO) using JWTs](https://docs.cockroachlabs.com/docs/stable/sso-sql.md) * [JWT Authorization](https://docs.cockroachlabs.com/docs/stable/jwt-authorization.md) * [LDAP Authentication](https://docs.cockroachlabs.com/docs/stable/ldap-authentication.md) * [SQL Authentication](https://docs.cockroachlabs.com/docs/stable/security-reference/authentication.md) * [`cockroach sql`](https://docs.cockroachlabs.com/docs/stable/cockroach-sql) * [Cluster Settings](https://docs.cockroachlabs.com/docs/stable/cluster-settings.md) * [`CREATE USER`](https://docs.cockroachlabs.com/docs/stable/create-user) * [`SHOW USERS`](https://docs.cockroachlabs.com/docs/stable/show-users) * [`DROP PROVISIONED ROLES`](https://docs.cockroachlabs.com/docs/stable/drop-provisioned-roles)