SQL Client Certificate Authentication for CockroachDB Advanced

On this page

SQL clients may authenticate to CockroachDB Advanced clusters using public key infrastructure (PKI) security certificates as an alternative to authenticating using a username and password or using Cluster Single Sign-on (SSO) using CockroachDB Cloud Console or Cluster Single Sign-on (SSO) using JSON web tokens (JWTs).

Note:

We recommend that CockroachDB Cloud Console users log in with Single Sign-On (SSO), optionally with two-factor authentication (2FA) enabled for the SSO provider. This prevents potential attackers from using stolen credentials to access or tamper with your critical data.

CockroachDB Cloud Basic SSO supports SSO with GitHub, Google, and Microsoft. Cloud Organization SSO provides additional configuration and flexibility, and includes support for OIDC or SAML protocols, autoprovisioning, and limiting the email domains that can use a given authentication method.

Visit your CockroachDB Cloud Console's account settings page and switch to SSO to improve the security of your cluster.

This page describes how to administer public key infrastructure (PKI) for a CockroachDB Advanced cluster, using HashiCorp Vault PKI Secrets Engine.

Refer to Transport Layer Security (TLS) and Public Key Infrastructure (PKI) for an overview of PKI certificate authentication in general and its use in CockroachDB.

Refer to Authenticating to CockroachDB Cloud for an overview of authentication in CockroachDB Cloud, both at the level of the organization and at the cluster.

Provision a PKI hierarchy for SQL authentication in your cluster

There are many ways to create, manage, and distribute digital security certificates. Cockroach Labs recommends using a secure secrets server such as HashiCorp Vault, which can be used to securely generate certificates without revealing the CA private key.

Refer to: CockroachDB - HashiCorp Vault Integration

Alternatively, you can generate certificates using CockroachDB's cockroach cert command or with OpenSSL. However, generating certificates this way and manually handling cryptographic material comes with considerable additional risk and room for error. PKI cryptographic material related to your CockroachDB Cloud organizations, particularly in any production systems, should be handled according to a considered policy appropriate to your security goals.

Initialize your Vault workstation

Install Vault on your workstation. Your workstation must be secure to ensure the security of the PKI hierarchy you are establishing. Consider using a dedicated secure jumpbox, as described in PKI Strategy.
Obtain the required parameters to target and authenticate to Vault.
1. Option 1: If using a development Vault server (suitable only for tutorial/testing purposes), start the Vault development server and obtain your admin token locally on your CA admin jumpbox by running vault server --dev.
2. Option 2: If using HashiCorp Cloud Platform (HCP):
  1. Go to the HCP console, choose Vault from the Services menu and then select your cluster.
  2. Find the Public Cluster URL for your Vault cluster. This will be set as the VAULT_ADDR environment variable, in the following step.
  3. Generate an admin token by clicking Generate token. This will be set as the VAULT_TOKEN environment variable, in the following step.
3. Option 3: If using a Vault deployment internal to your organization, contact your Vault administrator for a token and the appropriate endpoint.

Initialize your shell for Vault.

export VAULT_ADDR= # your Vault cluster's Public URL
export VAULT_NAMESPACE="admin"

Authenticate with your admin token.
```
vault login
```

Create the certificate authority (CA) certificate

This CA certificate will be used to configure your cluster's Trust Store. Any client certificate signed by the CA identified by this certificate will be trusted and can authenticate to your cluster.

Create a PKI secrets engine to serve as your client CA.

vault secrets enable -path=cockroach_client_ca pki

Success! Enabled the pki secrets engine at: cockroach_client_ca/

Generate a root credential pair for the CA. Certificates created with this CA/secrets engine will be signed with the private key generated here and held within Vault; this key cannot be exported, safeguarding it from being leaked and used to issue fraudulent certificates. The CA public certificate is downloaded in the resulting JSON payload.

Refer to: Vault documentation - PKI Secrets Engine: Setup and Usage
```
vault write \
cockroach_client_ca/root/generate/internal \
ttl=1000h \
--format=json > "${SECRETS_DIR}/certs/cockroach_client_ca_cert.json"
```
The public certificate can be found in the JSON file created by Vault at .data.certificate. You can extract it, for example, using the jq utility:

Note:

On macOS, you can install jq from Homebrew: brew install jq
```
cat "${SECRETS_DIR}/certs/cockroach_client_ca_cert.json" | jq .data.certificate
```
```
"-----BEGIN CERTIFICATE-----\nMIIC8TCCAdmgA123IUBMV/L6InS7DmJCWv4eyDwazEihkwDQYJKoZIhvcNAQEL\nBQAwADAeFw0yMzA0MTgxNzI5MzhaFw0yM
...
wGcWyVh822aQtH7+zucWQkvNXkdAwxjo8qD8XcxWLB5/Pj9XVM\n/5Na4xRIi+sgdMOgPpSm5a+gbUrjwa18LXxX9kc2aOEHTqpssQ==\n-----END CERTIFICATE-----"
```

Format a public certificate JSON for upload.

Create a JSON file that includes your certificate as the value for the x509_pem_cert key. You will use this JSON file to upload the certificate to CockroachDB Cloud. In this example, replace the certificate with the contents of your certificate.

{
  "x509_pem_cert": "-----BEGIN CERTIFICATE-----\nMIIDfzCCAmagAwIBAgIBADANBgkqhkiG9w0BAQ0FADBZMQswCQYDVQQGEwJ1czEL\nMzE1MjMyNTMxWjBZMQswCQYDVQQGEwJ1czELMAkGA1UECAwCV0ExDTALBgNVBAoM\nBHRlc3QxDTALBgNVBAMMBHRlc3QxEDAOBgNVBAcMB1NlYXR0bGUxDTALBgNVBAsM...\n-----END CERTIFICATE-----"
}

Create a PKI role and issue credentials for the client

You can authenticate to a cluster using the private key and public certificate previously signed by the CA as long as the cluster's trust store includes the corresponding CA.

Define a client PKI role in Vault:

vault write cockroach_client_ca/roles/client \
allow_any_name=true \
client_flag=true \
enforce_hostnames=false \
allow_ip_sans=false \
allow_localhost=false \
max_ttl=48h

Create a PKI private key and public certificate for the root user.

Note:

CockroachDB takes the name of the SQL user to be authenticated from the common_name field.
```
vault write "cockroach_client_ca/issue/client" \
common_name=root \
--format=json > "${SECRETS_DIR}/clients/certs.json"
```

Extract the client key and certificate pair from the payload.

echo -e $(cat "${SECRETS_DIR}/clients/certs.json" | jq .data.private_key | tr -d '"') > "${SECRETS_DIR}/clients/client.root.key"
echo -e $(cat "${SECRETS_DIR}/clients/certs.json" | jq .data.certificate | tr -d '"') > "${SECRETS_DIR}/clients/client.root.crt"

Ensure that the key file is owned by and readable only by the current user. CockroachDB will reject requests to authenticate using keys with overly-permissive permissions.
```
chmod 0600  ${SECRETS_DIR}/clients/client.root.key
chown $USER ${SECRETS_DIR}/clients/client.root.key
```

Upload a certificate authority (CA) certificate for a CockroachDB Advanced cluster

Add a CA certificate to your cluster's trust store for client authentication. Client certificates signed using the private key corresponding to this certificate will be accepted by your cluster for certificate-based client authentication.

Refer to Transport Layer Security (TLS) and Public Key Infrastructure (PKI): The CockroachDB certificate Trust Store

Tip:

The Cluster Administrator or Org Administrator Organization role is required to manage the CA certificate for a CockroachDB Advanced cluster.

Submit the asynchronous request, supplying your cluster ID, API key, and the path to the certificate JSON with your CA certificate, as described in Create the certificate authority (CA) certificate.

A 200 successful response code indicates that the asynchronous request was successfully submitted, but does not guarantee that the operation (configuring the CA certificate) successfully completed. You must confirm success with a follow-up GET request, as described in the next step.
```
curl --request POST \
  --url ${COCKROACH_SERVER}/api/v1/clusters/${CLUSTER_ID}/client-ca-cert \
  --header "Authorization: Bearer ${API_KEY}" \
  --header 'content-type: application/json' \
  --data "@cockroach_client_ca_cert.json"
```
```
200 OK
```

Confirm success of the operation with the following GET request.

curl --request GET \
  --url ${COCKROACH_SERVER}/api/v1/clusters/${CLUSTER_ID}/client-ca-cert \
  --header "Authorization: Bearer ${API_KEY}"

PENDING indicates that the operation is still in process.

{
  "status": "PENDING",
  "x509_pem_cert": ""
}

IS_SET indicates that the operation completed successfully, confirming the configured public CA cert.

{
  "status": "IS_SET",
  "x509_pem_cert": "-----BEGIN CERTIFICATE-----\nMIIDfzCCAmagAwIBAgIBADANBgkqhkiG9w0BAQ0FADBZMQswCQYDVQQGEwJ1czEL\nMAkGA1UECAwCV0ExDTALBgNVBAoMBHRlc3QxDTALBgNVBAMMBHRlc3QxEDAOBgNV\nBAcMB1NlYXR0bGUxDTALBgNVBAsMBHRlc3QwHhcNMjMwMzE2MjMyNTMxWhcNMjQw\n
...\n-----END CERTIFICATE-----",
}

Add the cockroach_client_ca_cert resource block to your Terraform template and apply the change:

resource "cockroach_client_ca_cert" "yourclustername" {
  id = cockroach_cluster.example.id
  x509_pem_cert = file("cockroach_client_ca_cert.json")
}

Update the CA certificate for a cluster

Warning:

Clients must be provisioned with client certificates signed by the cluster's CA prior to the update, or their new connections will be blocked.

This section shows how to replace the CA certificate used by your cluster for certificate-based client authentication. To roll out a new CA certificate gradually instead of following this procedure directly, CockroachDB supports the ability to include multiple CA certificates for a cluster by concatenating them in PEM format. This allows clients to connect as long as the client certificate is signed by either the old CA certificate or the new one. PEM format requires a blank line in between certificates.

Tip:

The Cluster Administrator or Org Administrator Organization role is required to manage the CA certificate for a CockroachDB Advanced cluster.

Submit the asynchronous request, supplying your cluster ID, API key, and the path to the certificate JSON with your CA certificate, as described in Create the certificate authority (CA) certificate.

A 200 successful response code indicates that the asynchronous request was successfully submitted, but does not guarantee that the operation (configuring the CA certificate) successfully completed. You must confirm success with a follow-up GET request, as described in the next step.
```
curl --request PATCH \
  --url ${COCKROACH_SERVER}/api/v1/clusters/${CLUSTER_ID}/client-ca-cert \
  --header "Authorization: Bearer ${API_KEY}" \
  --header 'content-type: application/json' \
  --data "@cockroach_client_ca_cert.json"
```
```
200 OK
```

Confirm success of the operation with the following GET request.

curl --request GET \
  --url ${COCKROACH_SERVER}/api/v1/clusters/${CLUSTER_ID}/client-ca-cert \
  --header "Authorization: Bearer ${API_KEY}"

PENDING indicates that the operation is still in process.

{
  "status": "PENDING",
  "x509_pem_cert": ""
}

IS_SET indicates that the operation completed successfully, confirming the configured public CA cert.

{
  "status": "IS_SET",
  "x509_pem_cert": "-----BEGIN CERTIFICATE-----\nMIIDfzCCAmagAwIBAgIBADANBgkqhkiG9w0BAQ0FADBZMQswCQYDVQQGEwJ1czEL\nMAkGA1UECAwCV0ExDTALBgNVBAoMBHRlc3QxDTALBgNVBAMMBHRlc3QxEDAOBgNV\nBAcMB1NlYXR0bGUxDTALBgNVBAsMBHRlc3QwHhcNMjMwMzE2MjMyNTMxWhcNMjQw\n
...\n-----END CERTIFICATE-----",
}

Update the cockroach_client_ca_cert resource block in your Terraform template, then run terraform apply.

resource "cockroach_client_ca_cert" "yourclustername" {
  id = cockroach_cluster.example.id
  x509_pem_cert = file("cockroach_client_ca_cert.json")
}

Delete the certificate authority (CA) certificate for a cluster

This section shows how to remove the configured CA certificate from the cluster.

Warning:

After this operation is performed, clients can no longer authenticate with certificates signed by this CA certificate.

Tip:

The Cluster Administrator or Org Administrator Organization role is required to manage the CA certificate for a CockroachDB Advanced cluster.

Submit the asynchronous DELETE request, supplying your cluster ID, API key, and the path to the certificate JSON with your CA certificate, as described in Create the certificate authority (CA) certificate.

A 200 successful response code indicates that the asynchronous request was successfully submitted, but does not guarantee that the operation (configuring the CA certificate) successfully completed. You must confirm success with a follow-up GET request, as described in the next step.
```
curl --request DELETE \
  --url ${COCKROACH_SERVER}/api/v1/clusters/${CLUSTER_ID}/client-ca-cert \
  --header "Authorization: Bearer ${API_KEY}"
```
```
200 OK
```
Confirm success of the operation with the following GET request.
```
curl --request GET \
  --url ${COCKROACH_SERVER}/api/v1/clusters/${CLUSTER_ID}/client-ca-cert \
  --header "Authorization: Bearer ${API_KEY}"
```
PENDING indicates that the operation is still in process.
```
{
  "status": "PENDING",
  "x509_pem_cert": ""
}
```
NOT_SET indicates that the operation completed successfully, confirming that no CA cert is currently set.
```
{
  "status": "NOT_SET",
  "x509_pem_cert": ""
}
```

To delete the client CA cert on a cluster, remove the cockroach_client_ca_cert resource block from your Terraform template, then run terraform apply.

Authenticate a SQL client using certificate authentication

To use certificate authentication for a SQL client, you must include the filepaths to the client's private key and public certificate. The public certificate must be signed by a CA that the cluster has been configured to trust. Refer to Upload a certificate authority (CA) certificate for a CockroachDB Advanced cluster.

From your cluster's overview page, https://cockroachlabs.cloud/cluster/{ your cluster ID }, click the Connect button.
Copy the command listed under Download CA Cert and run it locally to download the required public certificate, which your client will use to verify the identity of the cluster.
Obtain your choice of connection string or CLI connection command for your cluster from the UI. This connection string is designed for password authentication and must be modified.
1. Remove the placeholder password from the connection string.
2. Construct the full connection string by providing the paths to sslrootcert (the cluster's public CA certificate), sslcert (the client's public certificate, which must be signed by the CA specified in sslrootcert), and sslkey (the client's private key).
  
  Refer to: Provision a PKI hierarchy for SQL authentication in your cluster .

Connect using the cockroach sql command or the SQL client of your choice:

cockroach sql --url "postgresql://root@flooping-frogs-123.gcp-us-east1.crdb.io:26257/defaultdb?sslmode=verify-full&sslrootcert=${HOME}/Library/CockroachCloud/certs/2186fbdb-598c-4797-a463-aaaee865903e/flooping-frogs-ca.crt&sslcert=${SECRETS_DIR}/clients/client.root.crt&sslkey=${SECRETS_DIR}/clients/client.root.key"

Was this helpful?

Pricing

Contact us

Sign In

SQL Client Certificate Authentication for CockroachDB Advanced

Provision a PKI hierarchy for SQL authentication in your cluster

Initialize your Vault workstation

Create the certificate authority (CA) certificate

Create a PKI role and issue credentials for the client

Upload a certificate authority (CA) certificate for a CockroachDB Advanced cluster

Update the CA certificate for a cluster

Delete the certificate authority (CA) certificate for a cluster

Authenticate a SQL client using certificate authentication