Single Sign-on for DB Console

Single sign-on (SSO) allows a CockroachDB user to access the DB Console in a secure cluster via 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 supports SSO via OpenID Connect (OIDC), an authentication layer built on top of OAuth 2.0.

Requirements

• An external OAuth 2.0 identity provider

Login flow

This SSO implementation uses the authorization code grant type to authenticate a user.

1. A user opens the DB Console and clicks on the OAuth login button.
2. The user is redirected to an external identity provider.
3. The user successfully authenticates with the provider, completing the OAuth flow.
4. The user is redirected to the CockroachDB cluster via a callback URL.
5. The authorization code in the callback query is exchanged for an ID Token.
6. CockroachDB matches the ID Token to a registered SQL user.
7. CockroachDB creates a web session for the SQL user.
8. The user is redirected to the DB Console Cluster Overview.

Cluster settings

The following OIDC cluster settings are used to configure and enable SSO.

Cluster Setting	Example Value
server.oidc_authentication.enabled	true
server.oidc_authentication.client_id	'32789079457-g3hdfw8cbw85obi5cb525hsceaqf69unn.apps.googleusercontent.com'
server.oidc_authentication.client_secret	'6Q_jmRMgrPNOc_mN91boe-9EP'
server.oidc_authentication.redirect_url	'https://localhost:8080/oidc/v1/callback'
server.oidc_authentication.provider_url	'https://accounts.google.com'
server.oidc_authentication.scopes	'openid profile email'
server.oidc_authentication.claim_json_key	'email'
server.oidc_authentication.principal_regex	'^([^@]+)@[^@]+$'
server.oidc_authentication.autologin	true

• enabled is a Boolean that enables or disables SSO.
• client_id and client_secret are generated by the external identity provider.

• redirect_url specifies the callback URL that redirects the user to CockroachDB after a successful authentication. This can be the address of a node in the cluster or the address of a load balancer that routes traffic to the nodes. You must append the path with /oidc/v1/callback. Accepts either a single URL string (for example, https://{ your cluster's domain }:8080/oidc/v1/callback) or a JSON object with the following format, which supports region-based OIDC authentication with a callback URL configured per region:
  
  

  {
    "redirect_urls": {
      "us-east-1": "https://{cluster_ip_address}:8080/oidc/v1/callback",
      "eu-west-1": "example.com"
    }
  }
  

• provider_url specifies the OAuth issuer identifier. Ensure that the URL does not have a terminating /. For more information, see the OIDC specification. Note that CockroachDB appends the required /.well-known/openid-configuration by default. You do not need to include it.

• scopes is a space-delimited list of the OAuth scopes being requested for an Access Token. The openid scope must be included.

• claim_json_key is the key of the field to be extracted from the external identity provider's ID Token and mapped to a SQL user. This field is likely to be a standard claim such as email from which a username is extracted via principal_regex.

• principal_regex is a regular expression applied to the field specified by claim_json_key. It is used to extract an identifier that can be mapped to a SQL user. For example:

  • ^([^@]+)@[^@]+$ matches any email address (defined as a string containing one @ sign) and extracts a username from the string to the left of @.
  • ^(.+)$ maps the claim directly to a principal.

• autologin is a Boolean. When true, upon loading the DB Console login page, the browser will automatically initiate the OIDC authentication process by redirecting to /oidc/v1/login instead of waiting for the user to log in manually or click the OIDC login button.

Example (Google OAuth 2.0)

These steps demonstrate how to enable SSO authentication for the DB Console on a local secure cluster using Google's OAuth 2.0 implementation.

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 can find these in the dialog that appears after you create the client, or in the details view for the client:

   

4. Add the callback URL to the list of Authorized redirect URIs. On a local cluster, this will be https://localhost:8080/oidc/v1/callback. You will later set server.oidc_authentication.redirect_url to the same value.

5. Open a SQL shell to the cluster on node 1:

   cockroach sql --certs-dir=certs --host=localhost:26257
   

6. 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\>';
   

7. Specify the OAuth issuer identifier:

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

8. Specify the callback URL to redirect the user to the CockroachDB cluster:

   SET CLUSTER SETTING server.oidc_authentication.redirect_url = 'https://localhost:8080/oidc/v1/callback';
   

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

   Request the openid and email scopes from the Access Token:

   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$';
   

10. Create a SQL user that will log into the DB Console. The SQL username you specify must match the identifier obtained in the previous step. For example, a user with the email address maxroach@cockroachlabs.com will need the SQL username maxroach:

    CREATE USER maxroach;
    

11. Finally, enable OIDC authentication:

    SET CLUSTER SETTING server.oidc_authentication.enabled = true;
    

    When the user accesses the DB Console, they will be able to log in with their Google account.