CockroachDB clusters allow users to authenticate with Single Sign-on (SSO), both to the DB Console, and for SQL client access.
Cluster single sign-on (SSO) enables users to access the SQL interface of a CockroachDB cluster (whether provisioned on CockroachDB Cloud or Self-Hosted) with the full security of single sign-on (SSO), and the choice of a variety of cloud-based or customer-managed identity providers (IdPs).
Dedicated clusters can provision their users with JWTs via the DB Console. This allows users to authenticate to a cluster by signing in to their IdP (for example, Okta or Google) with a link embedded in the DB Console. This flow provisions a JWT that a user can copy out of the DB Console UI and use in a SQL connection string to authenticate to the cluster.
Cluster single sign-on for the DB Console is supported on Self-Hosted Enterprise and Dedicated clusters. Serverless clusters do not support cluster single sign-on, because they do not have access to the DB Console. However, Serverless clusters can use Cluster Single Sign-on (SSO) using ccloud
and the CockroachDB Cloud Console.
The page describes how to configure a cluster for cluster single sign-on using JWTs and then how users can authenticate using the JWTs. If you're a user ready to sign in to the DB Console with JWTs, you can skip the configuration section:
Prerequisites
You must have your cluster pre-configured for OIDC/SSO authentication for DB Console. Use the Single Sign-on (SSO) for DB Console guide to set this up.
SQL users/credentials:
- You must have the ability to update your cluster settings, which can be achieved in several ways. Refer to
SET CLUSTER SETTING
: Required permissions . - A SQL user that corresponds with your external identity must be pre-provisioned on the cluster. To provision such users, you must have access to the
admin
role.
- You must have the ability to update your cluster settings, which can be achieved in several ways. Refer to
Configure your cluster for SSO
Prerequisites:
You must have the ability to update your cluster settings, which can be achieved in several ways. Refer to SET CLUSTER SETTING
: Required permissions
Cluster Settings
You must configure the cluster settings in the following table to enable JWT authentication to your cluster. Refer to the Update your cluster settings section to configure your cluster settings.
Cluster Setting | Description |
---|---|
server.jwt_authentication.enabled |
Defaults to false , must be set to true to enable embedded JWT generation.. |
server.jwt_authentication.jwks |
A list of public signing keys for allowed IdPs; must include your IdP's key. If server.jwt_authentication.jwks_auto_fetch.enabled is true , keys are fetched automatically and server.jwt_authentication.jwks is ignored. |
New in v23.1: | |
server.jwt_authentication.jwks_auto_fetch.enabled |
If true , public signing keys are automatically fetched from the domains specified in server.jwt_authentication.issuers for each session, and server.jwt_authentication.jwks is ignored. Defaults to false . |
server.jwt_authentication.issuers |
A list of accepted token issuers; must include your IdP. |
server.jwt_authentication.audience |
This must match server.oidc_authentication.client_id ; refer to Single Sign-on (SSO) for DB Console. |
server.jwt_authentication.claim |
Which JWT field will be used to determine the user identity in CockroachDB; normally set either to email , or sub (subject). |
server.oidc_authentication.generate_cluster_sso_token.enabled |
Enables token generation; must be set to true . |
server.oidc_authentication.generate_cluster_sso_token.use_token |
Selects which part of the received OIDC credentials to display. |
server.identity_map.configuration |
Takes an Identity Map configuration. |
server.sql_host |
This display value informs users the host for their SQL connections. Default: localhost . |
server.sql_port |
This display value informs users the port for their SQL connections. Default: 26257 . |
Update your cluster settings
In order for this feature to work, Single Sign-on (SSO) for DB Console and cluster SSO must both be configured with the same IDP.
You can update your cluster settings with the SET CLUSTER SETTING
SQL statement.
You can also view all of your cluster settings in the DB Console.
Enable JWT SQL authentication to your cluster.
SET CLUSTER SETTING server.jwt_authentication.enabled = true;
Add your IdP's formal
issuer
name to your cluster's list of accepted token issuers. Theissuer
name is usually the issuer's domain and must match theiss
field in the JWT itself.This must match your cluster's configured value for
server.oidc_authentication.provider_url
. Refer to Single Sign-on (SSO) for DB Console. Issuers are expected to publish their configuration athttps://{ domain }/.well-known/openid-configuration
. For example:- CockroachDB Cloud's IdP configuration can be viewed publicly at:
https://cockroachlabs.cloud/.well-known/openid-configuration
. Theissuer
ishttps://cockroachlabs.cloud
.- For Google Cloud Platform, the
openid-configuration
can be found athttps://accounts.google.com/.well-known/openid-configuration
. Theissuer
ishttps://accounts.google.com
.SET CLUSTER SETTING server.jwt_authentication.issuers = 'https://accounts.google.com';
Set
server.jwt_authentication.audience
to the ID of your cluster as specified by the IdP, or a JSON array of such names. This must matchserver.oidc_authentication.client_id
; refer to Single Sign-on (SSO) for DB Console.Warning:Many third-party token issuers, including GCP and Azure, will by default create tokens with a generic default audience. It is recommended to limit the scope of access tokens as much as possible, so if possible, we recommend issuing tokens with only the required audience value corresponding to the
audience
configured on the cluster.By extension, if your provider allows you to specify scopes or permissions on the token, you should specify these as restrictively as possible, while still allowing for the functions intended for the service account or user.
SET CLUSTER SETTING server.jwt_authentication.audience = '984901724939-njig7lkv7k724rbv2hllvr4of8ul7th7.apps.googleusercontent.com';
Set
server.jwt_authentication.claim
to the field in the JWT that the cluster will use as the identity of the SQL user. To use the email field in the JWT, setserver.jwt_authentication.claim
toemail
.SET CLUSTER SETTING server.jwt_authentication.claim = 'email';
Set
server.jwt_authentication.jwks
to one or more signing keys which are valid to sign JWKS, in JWK format, containing the public keys for SSO token issuers/IdPs that will be accepted by your cluster. This list must include a given IdP, or the cluster will reject JWTs issued by it. IdPs are expected to serve their public certificates and other required information athttps://{ domain }/.well-known/openid-configuration
.IdPs such as Google rotate their signing keys periodically. You must update your cluster with a new signing key before the previous one expires, or your SQL clients will be unable to connect with cluster SSO. We recommend updating this cluster setting with the current key daily to avoid this scenario. Alternatively, you can enable
server.jwt_authentication.jwks_auto_fetch.enabled
to automatically fetch signing keys from the issuer instead of maintaining a static list of signing keys. Ifserver.jwt_authentication.jwks_auto_fetch.enabled
is enabled, thenserver.jwt_authentication.jwks
is ignored.Dedicated customers:
By default, your cluster's configuration will contain the CockroachDB Cloud's own public key, allowing CockroachDB Cloud to serve as an IdP. This is required for SSO with
ccloud
. When modifying this cluster setting, you must include the CockroachDB Cloud public key in the key set, or SSO withccloud
will no longer work.The public key for can be found at
https://cockroachlabs.cloud/.well-known/openid-configuration
.For example:
curl --silent https://accounts.google.com/.well-known/openid-configuration | jq .jwks_uri | xargs curl
{ "keys": [ { "alg": "RS256", "use": "sig", "e": "AQAB", "kty": "RSA", "kid": "7c0b6913fe13820a333399ace426e70535a9a0bf", "n": "lWXY0XOj_ikSIDIvGOhfuRhQJAAj6BWsbbZ6P-PXRclzV32-QLB4GZHPPcH37Lou5pQsTQPvTETAfCLnglIRSbP8x1zA5tUakRlm5RiGF4kcWh5k60x8u0Uslx-d6EueKuY-KLHUVDuMULlHkYAScIdYnXz-Cnr6PFZj8RQezzdPVPH53Q8a_Z9b-vpGzsMS5gszITb-72OQNokojXdPVctl5WzSx-JnWbJxPiwHx_dSWgmTnyiYrZLqrqfampGdroaamtIXy0W8CAe0uCqcD1LunpfX-Q-RD1IycxnEaXSuUKhNhCcxtHWrozEyeD23Zja2WlcvHdYuTzyrvrvS9Q" }, { "kid": "6f7254101f56e41cf35c9926de84a2d552b4c6f1", "e": "AQAB", "alg": "RS256", "use": "sig", "n": "oUriU8GqbRw-avcMn95DGW1cpZR1IoM6L7krfrWvLSSCcSX6Ig117o25Yk7QWBiJpaPV0FbP7Y5-DmThZ3SaF0AXW-3BsKPEXfFfeKVc6vBqk3t5mKlNEowjdvNTSzoOXO5UIHwsXaxiJlbMRalaFEUm-2CKgmXl1ss_yGh1OHkfnBiGsfQUndKoHiZuDzBMGw8Sf67am_Ok-4FShK0NuR3-q33aB_3Z7obC71dejSLWFOEcKUVCaw6DGVuLog3x506h1QQ1r0FXKOQxnmqrRgpoHqGSouuG35oZve1vgCU4vLZ6EAgBAbC0KL35I7_0wUDSMpiAvf7iZxzJVbspkQ", "kty": "RSA" } ] }
SET CLUSTER SETTING server.jwt_authentication.jwks = '{ "keys": [ { "alg": "RS256", "use": "sig", "e": "AQAB", "kty": "RSA", "kid": "7c0b6913fe13820a333399ace426e70535a9a0bf", "n": "lWXY0XOj_ikSIDIvGOhfuRhQJAAj6BWsbbZ6P-PXRclzV32-QLB4GZHPPcH37Lou5pQsTQPvTETAfCLnglIRSbP8x1zA5tUakRlm5RiGF4kcWh5k60x8u0Uslx-d6EueKuY-KLHUVDuMULlHkYAScIdYnXz-Cnr6PFZj8RQezzdPVPH53Q8a_Z9b-vpGzsMS5gszITb-72OQNokojXdPVctl5WzSx-JnWbJxPiwHx_dSWgmTnyiYrZLqrqfampGdroaamtIXy0W8CAe0uCqcD1LunpfX-Q-RD1IycxnEaXSuUKhNhCcxtHWrozEyeD23Zja2WlcvHdYuTzyrvrvS9Q" }, { "kid": "6f7254101f56e41cf35c9926de84a2d552b4c6f1", "e": "AQAB", "alg": "RS256", "use": "sig", "n": "oUriU8GqbRw-avcMn95DGW1cpZR1IoM6L7krfrWvLSSCcSX6Ig117o25Yk7QWBiJpaPV0FbP7Y5-DmThZ3SaF0AXW-3BsKPEXfFfeKVc6vBqk3t5mKlNEowjdvNTSzoOXO5UIHwsXaxiJlbMRalaFEUm-2CKgmXl1ss_yGh1OHkfnBiGsfQUndKoHiZuDzBMGw8Sf67am_Ok-4FShK0NuR3-q33aB_3Z7obC71dejSLWFOEcKUVCaw6DGVuLog3x506h1QQ1r0FXKOQxnmqrRgpoHqGSouuG35oZve1vgCU4vLZ6EAgBAbC0KL35I7_0wUDSMpiAvf7iZxzJVbspkQ", "kty": "RSA" } ] }';
Instead of setting
server.jwt_authentication.jwks
to a list of static signing keys, you can setserver.server.jwt_authentication.jwks_auto_fetch.enabled
totrue
to enable automatic fetching of signing keys for the issuers specified inserver.jwt_authentication.issuers
. Signing keys are fetched from the issuer'shttps://{ domain }/.well-known/openid-configuration
endpoint.Set your Identity Map. Refer to Identity Map configuration.
SET CLUSTER SETTING server.identity_map.configuration = 'https://accounts.google.com /^(.*)@cockroachlabs\.com$ \1' ;
Enable token generation.
This will also cause the token generation button to appear in the UI.
SET CLUSTER SETTING server.oidc_authentication.generate_cluster_sso_token.enabled = true;
Set
use_token
, which determines how the user's identity will be displayed.It can be set to either
id_token
oraccess_token
, depending on the structure of the your JWT as determined in your IDP configuration.SET CLUSTER SETTING server.oidc_authentication.generate_cluster_sso_token.use_token = id_token;
How CockroachDB determines the SQL username from a JWT
server.jwt_authentication.claim
determines which field to use to identify the external user. This must match a SQL user via the identity map.server.identity_map.configuration
maps that claim (along with the token’s issuer) to a SQL username.
Identity Map configuration
Th cluster setting server.identity_map.configuration
holds your cluster's identity map configuration, which tells your cluster how to map external identities to SQL users. This is functionally similar to PostgreSQL's User Name Map.
Each line potentially maps many external identities to one SQL user, and a configuration can contain multiple lines, so the mapping can be many-to-many. The cluster checks to see if the SQL username given in the connection request matches the identity in the token by any of the lines in the Identity Map.
The format of an identity map configuration is a space-separated triple consisting of:
<external issuer> <external user ID> <SQL username>
Examples:
https://accounts.google.com /^(.*)@cockroachlabs\.com$ \1
Maps every
cockroachlabs
email to a SQL user with the same username. That is, the part of the email address to the left of the@
). For example,docs@cockroachlabs.com
becomesdocs
.https://accounts.google.com 1232316645658094244789 roach
Maps a single external identity with the hard-coded ID to the SQL user
roach
.https://accounts.google.com /^([9-0]*)$ gcp_\1
Maps each GCP-provisioned service account to a SQL user named
gcp_{ GCP user ID }
. For example,gcp_1234567
for a service account with ID1234567
.
Authenticate to your cluster
Once ConsoleDB SSO and Cluster SSO with JWTs are enabled and your cluster is properly configured (including mapping authorized external users to SQL roles), users can self-provision auth tokens through a sign-in flow embedded in the DB Console. These tokens (JWTs) are intended as short-lived credentials, and although their expiry depends on the IdP configuration, it is usually 1 hour.
This example uses cockroach sql
, but you can use any SQL client that supports sufficiently long passwords.
Obtain a token.
Go to your cluster's DB Console and click the Generate JWT auth token for cluster SSO button
Use the token in place of a password in your database connection string.
cockroach sql --url "postgresql://{SQL_USERNAME}:{JWT_TOKEN}@{CLUSTER_HOST}:26257?options=--crdb:jwt_auth_enabled=true" --certs-dir={CLUSTER_CERT_DIR}
Welcome to the cockroach SQL interface...
What's Next?
- Learn more about Authentication in CockroachDB.
- This Cockroach Labs blog post covers and provides further resources for a variety of auth token-issuing use cases, including using Okta and Google Cloud Platform to issue tokens.