Here's how companies using Azure can allow access to CockroachDB for all approved users by dynamically mapping users in Azure Active Directory to a matching SQL user created in CockroachDB.See how DoorDash delivers
The goal of this blog post is to demonstrate how companies using Azure can allow access to CockroachDB for all approved users. We will accomplish this by mapping users with the appropriate role in Azure Active Directory to a matching SQL user created in CockroachDB.
To do that, we will be using JSON Web Tokens (JWTs), the Azure CLI, and CockroachDB’s SQL client. This approach provides several benefits, including:
In the first section, we will cover creating the necessary prerequisite resources, and then move on to connecting to the database in the subsequent section.
We have made the assumption that our Azure account has already been created, our active directory is populated, and that our Azure CLI has been installed and configured appropriately. If this is not the case for you, you’ll need to take care of those things before proceeding.
Thanks to the excellent documentation available on Cockroach Labs’s website, we will not cover the creation steps in detail here. Instead I will provide links to the appropriate documentation.
NOTE: As of this writing, the required functionality only exists in CockroachDB version 23.1.5 and up. There may be future backports, but the required cluster setting
server.jwt_authentication.claim was not introduced until 23.1.5.
This blog will cover connecting to CockroachDB securely using certificates. We do not recommend starting the cluster with the
--insecure flag in production.
Once our cluster is created, we must ensure we can connect securely using the methods listed inside of each documentation link. After connecting, we will need to create SQL users with the appropriate roles and permissions. Details for this process can be found in the documentation. We will not be able to connect to CockroachDB using JWTs if we do not first create the SQL users in CockroachDB.
In this section, we will cover the appropriate steps for generating our JWT, decoding the token to get the relevant information we need to configure our database, and then connecting to CockroachDB via the
cockroach sql command.
First up, we need that token! The easiest way to manage this for Azure Active Directory is to use the Azure CLI. If needed, installation steps can be found here, but we will not cover the installation in this blog.
az ad app list --output table
az account get-access-token --resource <AppId>
Copy the value in the response for the key “access_token” and base64 decode it into a JSON object. In this object, we care about the following keys:
Navigate to “https://login.microsoftonline.com/<tid_from above>/discovery/v2.0/keys” and copy the entirety of the output and put it aside. I will refer to this variable as <keys> for the rest of the documentation.
Now that we have our <aud>, <iss>, and <keys> values, let’s configure CockroachDB to use them.
In order to configure CockroachDB to log in using JWTs, we will need to configure the following cluster settings:
We will get into the reasons why in just a little bit, but it’s important to note that the
jwt_authentication.claim setting is only available in CockroachDB v23.1.5 and up. It is not necessary to use this setting if we only want to map a single Azure Active Directory user to a single CockroachDB SQL user, but it is necessary to do dynamic mapping for multiple users.
In order to set these cluster settings, we’ll use the following SQL commands:
SET CLUSTER SETTING server.jwt_authentication.enabled=true; SET CLUSTER SETTING server.jwt_authentication.issuers='["<iss>"]'; SET CLUSTER SETTING server.jwt_authentication.audience='["<aud>"]'; SET CLUSTER SETTING server.jwt_authentication.jwks='<keys>'; SET CLUSTER SETTING server.jwt_authentication.claim='upn'; SET CLUSTER SETTING server.identity_map.configuration='<iss> /^(\S+)(?:@) \1';
The first four should be fairly easy to intuitively understand, but let’s talk about the last two.
By default, CockroachDB uses the “sub” key in our decoded access token as our claim. This is great when statically mapping a single identity provider user to a single CockroachDB user, but doesn’t work all that well when dynamically mapping multiple users.
In order to make that process easier, we are going to change the claim to the “upn” key (User Principal Name), which should be unique for all users. The reason we want to do this is that we can use regex to strip the domain name from this value and then map the remaining username to our CockroachDB username. This is what we accomplish with the
identity_map.configuration setting above.
Now that our cluster is properly configured, let’s connect!
In order to connect to our cluster, we will need a SQL client. The CockroachDB binary comes with a built-in client, and Cockroach Labs also offers a standalone SQL client. Whichever option we choose, we should ensure that we have installed it properly on our machine by running
cockroach --version. If the command is successful, move on to the next step.
In order to connect to our cluster, we will need to make sure that we have access to our certificates. In order to get the certificates you can follow the instructions in the Deploy a Secure Cluster Documentation for a local or self-hosted cluster or use the Connect To A CockroachDB Cloud Cluster Documentation for our cloud offerings. Once we have access to our certificates, we will want to note the path to where they reside on our machine. We will refer to this path for the remainder of this blog as <certs_path>.
Now we’re ready to connect. In order to do so, we will be using the
cockroach sql command which will be:
cockroach sql --url='postgresql://<username>:<access_token>@<url>:26257/<db_name>?sslmode=verify-full&options=--crdb:jwt_auth_enabled=true' --certs-dir=<certs_path>
Let’s break this down:
Note: Without the
--crdb:jwt_auth_enabled=true option, we will not be able to connect to CockroachDB with JWTs.
That does it! We have now linked our Azure Active Directory users to our CockroachDB users.
This story was originally published in ITPro Today: Merger Maneuvers in the Multi-Cloud.
Software mergers and …Read more
More than 500 attendees from around the globe recently swarmed into New York for RoachFest23, the annual user conference …Read more
Each October, RoachFest gathers together application owners, architects, engineers, and operators running their …Read more