SQL Client Certificate Authentication for CockroachDB Advanced

​

Provision a PKI hierarchy for SQL authentication in your cluster

​

Initialize your Vault workstation

1. 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 .
2. 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.
3. Initialize your shell for Vault.
   export VAULT_ADDR= # your Vault cluster's Public URL
   export VAULT_NAMESPACE="admin"
   
4. Authenticate with your admin token.
   vault login
   

​

Create the certificate authority (CA) certificate

1. 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/
   
2. 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:

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-----"

3. 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

1. 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
   
2. Create a PKI private key and public certificate for the root user.

vault write "cockroach_client_ca/issue/client" \
common_name=root \
--format=json > "${SECRETS_DIR}/clients/certs.json"

3. 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"
   
4. 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

1. 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
   
2. 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-----",
   }
   

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

1. 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
   
2. 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-----",
   }
   

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

1. 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
   
2. 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": ""
   }
   

​

Authenticate a SQL client using certificate authentication

1. From your cluster’s overview page, https://cockroachlabs.cloud/cluster/{ your cluster ID }, click the Connect button.
2. 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.
3. 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.
4. 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"