SQL clients may authenticate to CockroachDB Dedicated 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 (JWT).
Cockroach Labs recommends using single sign on (SSO) for authentication of human users where possible, and JWT or security certificate for authentication of your application users.
This page describes how to administer public key infrastructure (PKI) for a CockroachDB Dedicated 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.
- 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
. Option 2: If using HashiCorp Cloud Platform (HCP):
- Go to the HCP console, choose Vault from the Services menu and then select your cluster.
- Find the Public Cluster URL for your Vault cluster. This will be set as the
VAULT_ADDR
environment variable, in the following step. - Generate an admin token by clicking Generate token. This will be set as the
VAULT_TOKEN
environment variable, in the following step.
Option 3: If using a Vault deployment internal to your organization, contact your Vault administrator for a token and the appropriate endpoint.
- 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
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 thejq
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 Dedicated 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.
The Cluster Administrator or Org Administrator Organization role is required to manage the CA certificate for a CockroachDB Dedicated 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-upGET
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
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.
The Cluster Administrator or Org Administrator Organization role is required to manage the CA certificate for a CockroachDB Dedicated 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-upGET
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.
After this operation is performed, clients can no longer authenticate with certificates signed by this CA certificate.
The Cluster Administrator or Org Administrator Organization role is required to manage the CA certificate for a CockroachDB Dedicated 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-upGET
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 Dedicated 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.
- Remove the placeholder password from the connection string.
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 insslrootcert
), andsslkey
(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"