Single Sign-on (SSO) for DB Console

On this page

Single sign-on (SSO) for DB Console allows a CockroachDB user to access the DB Console in a secure cluster via an OpenID Connect (OIDC) client and an external identity provider. When SSO is configured and enabled, the DB Console login page will display an OAuth login button in addition to the password access option.

CockroachDB's OIDC implementation for DB Console uses the authorization code grant type to authenticate a user.

Configuration

Prerequisites

• IdP: You must have access to an OAuth 2.0 identity provider and client. Refer to Step 1: Provision an OIDC client.

• CockroachDB cluster: You must have access to one of the following:

  • A self-hosted cluster enabled with a valid CockroachDB Enterprise license.
  • A CockroachDB Advanced cluster.

Step 1: Provision an OIDC client

These steps demonstrate how to create an OIDC auth client in Google Cloud Platform to use for SSO authentication to the DB Console.

1. Open the Credentials page for your account at Google APIs.

2. Click + CREATE CREDENTIALS and select OAuth client ID. Specify a web application from the pulldown menu.

3. Note the client ID and client secret of the OAuth 2.0 client: you will need to configure your cluster to use these values.

4. Add your cluster's callback URL to the list of Authorized redirect URIs. On a local cluster, this will be https://{ your cluster's domain }:8080/oidc/v1/callback. Subsequently, when configuring your cluster, you will need to ensure that the cluster setting server.oidc_authentication.redirect_url has the same value.

   • For a self-hosted cluster, the domain is localhost.
   • For an Advanced cluster, open the DB Console from the cluster’s Tools tab in the Cloud Console, and use the host portion of that URL as your domain.

Step 2: Configure cluster settings

You must have the required privileges to update your cluster settings. Refer to SET CLUSTER SETTING.

Cluster settings

Configure the cluster settings in the following table to enable SSO authentication to the DB Console. Refer to Update your cluster settings.

Update your cluster settings

Open a SQL shell to your cluster:

cockroach sql --certs-dir=certs --host={your cluster url}:26257

Specify the client ID and client secret you obtained earlier:

SET CLUSTER SETTING server.oidc_authentication.client_id = '\<client id\>';
SET CLUSTER SETTING server.oidc_authentication.client_secret = '\<client secret\>';

Specify the OAuth provider URL:

SET CLUSTER SETTING server.oidc_authentication.provider_url = 'https://accounts.google.com';

Specify the callback URL to redirect the user to the CockroachDB cluster. On a local cluster, this will be https://localhost:8080/oidc/v1/callback. For a Advanced cluster, replace localhost with your cluster's domain, which can be found by opening the DB Console from the Tools tab in your cluster's page in Cloud Console.

SET CLUSTER SETTING server.oidc_authentication.redirect_url = 'https://{your cluster url}:8080/oidc/v1/callback';

Specify the following values to obtain an OIDC identifier that will be mapped to a SQL user.

Request the openid scope (required) and the email scope (needed when using email as the claim key):

SET CLUSTER SETTING server.oidc_authentication.scopes = 'openid email';

Specify the email field from the ID Token:

SET CLUSTER SETTING server.oidc_authentication.claim_json_key = 'email';

Use a regular expression that will extract a username from email that you can match to a SQL user. For example, '^([^@]+)@cockroachlabs\.com$' extracts the characters that precede @cockroachlabs.com in the email address.

SET CLUSTER SETTING server.oidc_authentication.principal_regex = '^([^@]+)@cockroachlabs.com$';

Step 3: Configure user creation

CockroachDB supports two approaches for the creation of users who will authenticate via OIDC:

Option 1: Automatic user provisioning (recommended)

New in v26.1: With automatic user provisioning, CockroachDB creates users automatically during their first successful OIDC authentication. This eliminates the need for custom scripting to create user accounts.

To enable automatic user provisioning:

SET CLUSTER SETTING security.provisioning.oidc.enabled = true;

When enabled:

• Users are created automatically upon successful OIDC authentication.
• All auto-provisioned users receive a PROVISIONSRC role option set to oidc:{provider_url}.
• The estimated_last_login_time is tracked for auditing purposes.
• Auto-provisioned users cannot change their own passwords (managed via OIDC only).

Option 2: Manual/scripted user creation

You can manage users by directly creating them before OIDC authentication is used. This approach provides explicit control over user creation.

To create a single user:

CREATE USER docs;

The SQL username you specify must match the identifier extracted by your principal_regex from the OIDC token. For example, if you configured claim_json_key = 'email' and principal_regex = '^([^@]+)@cockroachlabs\.com$', a user with the email address docs@cockroachlabs.com will need the SQL username docs.

Step 4: Enable OIDC authentication

SET CLUSTER SETTING server.oidc_authentication.enabled = true;

Log in to a cluster's DB Console with SSO

From the user's perspective, once the cluster is properly configured to an identity provider, the sign-in flow is as follows:

1. A user opens the cluster's DB Console, and clicks the Log in with your OIDC provider button on the page.
2. The user is redirected to an external identity provider.
3. The user authenticates successfully with the provider, which completes the OAuth flow.
4. The user is redirected to the CockroachDB cluster.
5. CockroachDB creates a web session for the SQL user in a new browser tab.
6. In the original browser tab, the user is redirected to the DB Console Cluster Overview page.

Manage auto-provisioned users

When automatic user provisioning is enabled, you can identify and manage auto-provisioned users as follows:

View provisioned users

Auto-provisioned users can be identified by their PROVISIONSRC role option:

-- View all auto-provisioned users (users with PROVISIONSRC role option)
SELECT * FROM [SHOW USERS] AS u
WHERE EXISTS (
  SELECT 1 FROM unnest(u.options) AS opt
  WHERE opt LIKE 'PROVISIONSRC=oidc:%'
);

  username |                     options                     | member_of    |   estimated_last_login_time
-----------+-------------------------------------------------+--------------+--------------------------------
  alice    | {PROVISIONSRC=oidc:https://accounts.google.com} | {developers} | 2025-08-04 19:18:00.201402+00
  bob      | {PROVISIONSRC=oidc:https://accounts.google.com} | {analysts}   | 2025-08-03 14:22:15.102938+00
(2 rows)

Users provisioned via OIDC will have PROVISIONSRC=oidc:{provider_url} in their options column.

Last-login tracking for usage and dormancy

The estimated_last_login_time column in the SHOW USERS output tracks when users last authenticated. This can help identify dormant accounts that may need review or removal.

To identify potentially dormant auto-provisioned users:

SELECT u.username, u.estimated_last_login_time
FROM [SHOW USERS] AS u
WHERE EXISTS (
  SELECT 1 FROM unnest(u.options) AS opt
  WHERE opt LIKE 'PROVISIONSRC=oidc:%'
) AND (
  u.estimated_last_login_time IS NULL OR
  u.estimated_last_login_time < NOW() - INTERVAL '90 days'
)
ORDER BY u.estimated_last_login_time DESC NULLS LAST;

Clean up users removed from the identity provider

Auto-provisioned users who have been removed or deactivated in your identity provider will not be automatically removed from CockroachDB. To identify and clean up these orphaned accounts:

Step 1: Export auto-provisioned users from CockroachDB

-- Export list of auto-provisioned usernames for comparison with your IdP
SELECT u.username FROM [SHOW USERS] AS u
WHERE EXISTS (
  SELECT 1 FROM unnest(u.options) AS opt
  WHERE opt LIKE 'PROVISIONSRC=oidc:%'
);

Step 2: Cross-reference with your identity provider

Use your organization's identity management tools to verify which of these users still exist in your IdP. The specific method depends on your identity provider (Okta, Google Workspace, Azure AD, etc.).

Step 3: Remove orphaned users

Before dropping users confirmed to no longer exist in your identity provider, check for any privileges that were granted directly to the user:

-- Check for direct grants to the user (privileges inherited through roles won't block DROP USER)
SHOW GRANTS FOR username;

If any direct grants exist, revoke them before dropping the user. For users confirmed to no longer exist in your identity provider:

-- Remove users that no longer exist in the identity provider
DROP USER username1, username2, username3;

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 OIDC; password-based authentication is not available.

Attempting to change the password of an auto-provisioned user will result in an error:

ALTER USER provisioned_user WITH PASSWORD 'newpassword';

ERROR: cannot alter PASSWORD/PROVISIONSRC option for provisioned user provisioned_user

Configure role-based access control

Once OIDC authentication is configured, you can optionally enable automatic role synchronization to automatically grant and revoke CockroachDB roles based on group memberships from your identity provider.

For detailed instructions, refer to Configure OIDC Authorization for DB Console.

See also

• DB Console Overview
• Cluster Single Sign-on (SSO) using JSON web tokens (JWTs)
• Configure OIDC Authorization for DB Console
• JWT Authorization
• LDAP Authorization