SSO to CockroachDB clusters using JWT

Secure authentication is a fundamental requirement when evaluating a database product. Architecture and Security teams prefer capabilities which could somehow be managed centrally, ideally using existing security tools in the enterprise tech stack.

Over decades of advancements in the OLTP database ecosystem, we’ve seen a number of solutions emerge for secure authentication. Whether it be PKI/certificates, LDAP integration with enterprise identity directory, GSSAPI/Kerberos, SCRAM and so on, those mechanisms have enabled organizations to adopt a variety of new databases over time. We also support most of those capabilities in CockroachDB that are utilized by the majority of our security-conscious customers.

But most of those solutions are either kludgy to implement & manage across a large number of systems and users, or are just half secure. Like with Kerberos, the overall setup and application integration is quite complex, whereas Certificates are hard to scale & continuously update without requiring increasing oversight. Similarly, LDAP is a poor man’s secure solution as the users are required to provide their enterprise password to the database which then validates it with the enterprise directory. Nonetheless those are manageable problems when your database is deployed in your own infrastructure in cloud or on-premises. But it’s a different ball game when one is looking to adopt a fully-managed DBaaS.

The problems mentioned above make the old solutions unpalatable for SaaS or cloud-native databases. Compared to the SAML and OIDC based authentication mechanisms for other kinds of web-based products, the needle hasn’t moved much on advancing that posture for databases.

We at Cockroach Labs have thought about this a lot and realized sometime back that customers who are adopting fully-managed cloud-native DBaaS need a new kind of secure authentication mechanism that works using modern identity protocols. To that end we are excited to share that starting with CockroachDB 22.2 users can now use JWT (Json Web Token) for SQL authentication to CockroachDB clusters. This mode of authentication is a first for SQL OLTP databases. We charted this innovation path to help our customers reason about database authentication the same way as they do about other web-based products. One could use either third-party JWT issuers (identity providers) or bring their own custom issuers.

Why use JWT for SQL authentication?

Secure authentication to a database cluster does not have a one size fits all solution. And that is the reason we’ve earlier invested in providing varied capabilities like User/Client Certificates, GSSAPI/Kerberos, SCRAM etc. to CockroachDB users. If an organization already relies on Hashicorp Vault for its credential management, they can also use the Postgresql Secrets Engine to manage dynamic secrets for SQL users. But companies that are increasingly adopting cloud and fully-managed DBaaS seek a mechanism that can integrate with their modern OIDC identity providers and help them manage database authentication from one central place. 

We hear the following questions from such customers:

• We use GCP service accounts and their credentials to access the GCP-native services. Could we do the same with CockroachDB as well?
• Is there a way to use application identities managed by Okta to access the CockroachDB clusters, rather than having to create separately managed SQL users?
• Could we use our custom JWT issuer to allow accessing CockroachDB clusters without having to use a proxy service in between?
• We would like our users to use refreshable short-lived credentials to access their clusters. Do you provide a mechanism to accomplish that?

JWT based authentication has been designed to address all of the above requirements with CockroachDB (both cloud and self-hosted). It enables seamless authentication using the cloud-native identity providers and custom JWT issuers, plus helps avoid managing another set of credentials per user, thus reducing “something you know” footprint in an organization. It also supports refreshable short-lived tokens thus reducing the data exfiltration risk associated with credential leaks.

How does JWT based SQL authentication work?

We’ve developed a novel authentication mechanism that is applicable to both CockroachDB Cloud and the customer-managed / self-hosted clusters, and for both human & application users.

For CockroachDB Cloud, human users can use the existing Basic or Cloud Organization SSO configuration to access their clusters using the Cloud CLI. In this case, CockroachDB Cloud generates JWTs for human users, which are used by the Cloud CLI under the hood to access a cluster. The generation and use of JWTs is abstracted from the human users to keep it simple.

Application users can use external JWT issuers (including OIDC identity providers like Okta, GCP, Azure, AWS EKS etc.) to generate JWTs and then use those to access CockroachDB Cloud or self-hosted CockroachDB clusters in a headless manner. These tokens will have an applicable lifetime as configured in the token issuer policies, and thus could also be used as an auth mechanism with short-lived credentials that could be refreshed periodically.

Cloud CLI flow

To make the login experience to a CockroachDB Cloud cluster as seamless as possible, you can login to such a cluster using the latest Cloud CLI’s cluster sql command with the **\--sso** flag. This will launch your browser to authenticate yourself to CockroachDB Cloud. Once that is complete or if you are already logged in to CockroachDB Cloud, you will be directly signed in to the cluster. If a SQL user matching your CockroachDB Cloud user does not already exist in the cluster, the Cloud CLI will prompt you to create one before completing the sign-in process.

Let’s go through an example of how this works. When you try SSO to a new cluster for the first time, it may prompt you to add your IP address to the cluster allowlist if your organization has configured that capability for the cluster.

Once you’ve added your IP address to the cluster allowlist and retry opening the SQL session, it may prompt you to create your auto-generated SQL user in the cluster if not done yet.

The password for the SQL user is required for recovery purposes. Once that user has been created in the cluster and you retry signing in, it should succeed and open up a SQL session for you. 

The steps to allowlist your IP address and create the SQL user should not be needed for subsequent sessions (unless your IP address changes). Under the hood, CockroachDB Cloud populates your cluster with the required JWT related cluster settings and generates a signed JWT for your user. Once the Cloud CLI has the JWT, it is seamlessly passed to the cluster in the password field with the option **\--crdb:jwt_auth_enabled=true** to indicate to the cluster to use JWT authentication.

External issuer support

We developed the external issuer support with application users in mind, but it could be used by human users too (though we recommend the Cloud CLI flow for those users if accessing CockroachDB Cloud clusters). This capability could be used for both CockroachDB Cloud or Self-hosted clusters. In order for a cluster to validate a JWT signed by an external issuer, the cluster needs to know a certain amount of information about the JWTs that are expected. This includes the acceptable keys (in JWKS format), issuers, and audiences. These values are provided via the **server.jwt_authentication.*** cluster settings.

Once those cluster settings have been set, a user can login to the cluster in the normal way, except that a JWT will be required as the password and **\--crdb:jwt_auth_enabled=true** option should be set in the cluster connection configuration to apply JWT authentication. The JWT will be validated against the cluster settings set earlier and it will be valid for a SQL username that is the same as the subject field of the token.

Some third party token issuers such as GCP, Azure, AWS EKS, and Okta, provide JWTs that do not have subjects that are meaningful or are valid SQL usernames. In order to use these token issuers, you can map the subjects in the JWTs to meaningful SQL usernames using the **server.identity_map.configuration**\ cluster setting. Using this setting, you can map token subjects to SQL usernames based on the issuer of the token. The field **map-name** must be equal to the token issuer, otherwise no mapping will be done.

We’ve validated this capability to work for GCP Service Accounts, Azure Managed Identities, AWS EKS Service Accounts and Okta Users. Feel free to refer the below articles from Cockroach Labs technical field team on how to use some of the mentioned external issuers:

• SSO using Okta tokens from Python to CockroachDB
• SSO using Okta tokens from Golang to CockroachDB
• SSO to CockroachDB using auto-refresh of Okta tokens
• SSO to CockroachDB using GCP Service Account
• SSO to CockroachDB using Azure Managed Identity

How to get started

If you don’t have a CockroachDB cluster yet, get started by creating one by following these instructions for CockroachDB Dedicated or these ones for Self-hosted CockroachDB. Once the cluster is set up, refer to these instructions to use JWT authentication for your cluster. 

To try out the Cloud CLI flow with a CockroachDB Cloud cluster, we would recommend using Basic or Cloud Organization SSO. And to use the external issuer support, ensure that you have a JWT issuer ready (ideally from the ones we’ve validated) and you configure the relevant cluster settings to validate the JWTs.