CockroachDB TIL: Volume 13

Previous Articles

• Volume 1
• Volume 2
• Volume 3
• Volume 4
• Volume 5
• Volume 6
• Volume 7
• Volume 8
• Volume 9
• Volume 10
• Volume 11
• Volume 12

Topics

• Topic 1: Diagnose certificate-based authentication
• Topic 2: Differences between cockroach and psql clients for client verification of server certificates
• Topic 3: Capture the node and the CA certificate expiration programmatically
• Topic 4: Check the client certificate expiration

Topic 1: Diagnose Certificate-Based Authentication

I was using CockroachDB Serverless with a product called PolyScale.ai for an article I was writing and I ran into a strange issue with certificate-based AuthN. When you provision the instance of their service, it provides a client connection string.

I used the connection string to connect:

psql "postgres://username:[email protected]:5432/defaultdb?sslmode=require"

When I attempted to connect, I received an error:

psql: error: connection to server at "psedge.global" (104.248.109.242), port 5432 failed: SSL error: certificate verify failed

The error message confused me as it was pointing to a port. What made everything more confusing was that the service reported it had connected just fine.

I thought the issue was with the CA cert Cockroach relies on and I was stuck until I reached out to the engineering team. My team suggested to run the following command to verify the certificate coming from PolyScale:

/opt/homebrew/Cellar/openssl@3/3.1.0/bin/openssl s_client -showcerts -connect psedge.global:5432 -starttls postgres

We need OpenSSL version 3.1 or later - the one I had by default on my OSX laptop did not work. The response of the command will show the certificate details.

depth=2 C = US, O = DigiCert Inc, OU = www.digicert.com, CN = DigiCert Global Root CA
verify return:1
depth=1 C = US, O = DigiCert Inc, CN = DigiCert TLS RSA SHA256 2020 CA1
verify return:1
depth=0 C = US, ST = California, L = Redwood City, O = "Polyscale, Inc", CN = *.polyscale.global
verify return:1
---
Certificate chain
 0 s:C = US, ST = California, L = Redwood City, O = "Polyscale, Inc", CN = *.polyscale.global
   i:C = US, O = DigiCert Inc, CN = DigiCert TLS RSA SHA256 2020 CA1
   a:PKEY: rsaEncryption, 2048 (bit); sigalg: RSA-SHA256
   v:NotBefore: Oct  4 00:00:00 2022 GMT; NotAfter: Jul 28 23:59:59 2023 GMT

CockroachDB Serverless uses Let's Encrypt for the certificate authority. Although not necessary, you can download the CA cert and store it in your filesystem.

That's the certificate I had in my filesystem. Similarly, you can use the above command to view the details of the certificate:

/opt/homebrew/Cellar/openssl@3/3.1.0/bin/openssl s_client -showcerts -connect artem-serverless-3312.g8z.cockroachlabs.cloud:26257 -starttls postgres

Server certificate
subject=CN = *.cockroachlabs.cloud
issuer=C = US, O = Let's Encrypt, CN = R3
---
No client certificate CA names sent
Peer signing digest: SHA256
Peer signature type: ECDSA
Server Temp Key: X25519, 253 bits

The workaround here is to remove the root.crt as it was causing conflicts with the PolyScale cert. As soon as you remove the cert, you can authenticate successfully.

psql "postgres://username:[email protected]:5432/defaultdb?sslmode=require"
psql (15.3 (Homebrew), server 13.0.0)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

defaultdb=> 

At this point, you can also use the cockroach client.

cockroach sql --url "postgres://username:[email protected]:5432/defaultdb?application_name=19592894-26b0-42f7-aea8-2b4e1cb8ec24&sslmode=require" 
#
# Welcome to the CockroachDB SQL shell.
# All statements must be terminated by a semicolon.
# To exit, type: \q.
#
# Client version: CockroachDB CCL v22.2.9 (aarch64-apple-darwin21.2, built 2023/05/08 13:26:12, go1.19.6)
# Server version: CockroachDB CCL v22.2.7 (x86_64-pc-linux-gnu, built 2023/03/28 19:47:29, go1.19.6)

warning: server version older than client! proceed with caution; some features may not be available.

# Cluster ID: 20735006-5a39-49c3-29b8-3d4c8befcbf8
#
# Enter \? for a brief introduction.
#
[email protected]:5432/defaultdb> 

Now what if we'd like to specify the cert we wanted to use to connect? We can capture the cert coming from PolyScale and store it in our filesystem.

/opt/homebrew/Cellar/openssl@3/3.1.0/bin/openssl s_client -showcerts -connect psedge.global:5432 -starttls postgres | openssl x509 -text

Copy the output into a new file with the BEGIN and END included.

-----BEGIN CERTIFICATE-----
...
-----END CERTIFICATE-----

Save it as a new file and then use it in the connection string with sslrootcert=file argument.

psql "postgres://username:[email protected]:5432/defaultdb?sslmode=require&sslrootcert=polyscale.crt"

psql (15.3 (Homebrew), server 13.0.0)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

defaultdb=> 

We can even combine the certs into a single super cert by copying the contents of both certs including the BEGIN and END, i.e.:

-----BEGIN CERTIFICATE-----
...
-----END CERTIFICATE-----
-----BEGIN CERTIFICATE-----
...
-----END CERTIFICATE-----

Once we have both certs stored in this cert, we can connect without any issues.

cockroach sql --url "postgres://username:[email protected]:5432/defaultdb?sslmode=verify-full&sslrootcert=/Users/artem/.postgresql/super.crt"

And likewise, use the same cert to connect to CockroachDB:

cockroach sql --url "postgres://username:[email protected]:26257/defaultdb?sslmode=verify-full&sslrootcert=/Users/artem/.postgresql/super.crt"

The lesson here is either have all certs in your default directory and explicitly specify them with sslrootcert or remove sslrootcert from the connection string and make sure your filesystem does not have any residual certs.

Topic 2: Differences Between cockroach and psql Clients for Client Verification of Server Certificates

While I was writing the first topic, I noticed a slight difference in behavior between the cockroach and psql clients when it comes to client verification of server certificates. Unlike the cockroach client, psql client expects a CA certificate in the default location (from the PostgreSQL docs).

To allow server certificate verification, one or more root certificates must be placed in the file ~/.postgresql/root.crt in the user's home directory (on Microsoft Windows, the file is named %APPDATA%\postgresql\root.crt.). Intermediate certificates should also be added to the file if they are needed to link the certificate chain sent by the server to the root certificates stored on the client.

Certificate Revocation List (CRL) entries are also checked if the file ~/.postgresql/root.crl exists (%APPDATA%\postgresql\root.crl on Microsoft Windows).

The location of the root certificate file and the CRL can be changed by setting the connection parameters sslrootcert and sslcrl or the environment variables PGSSLROOTCERT and PGSSLCRL. sslcrldir or the environment variable PGSSLCRLDIR can also be used to specify a directory containing CRL files.

cockroach client will not rely on the default ~/.postgresql/root.crt location to make the AuthN. Case in point, use the following connection string while not having any certs in the default directory:

cockroach sql --url "postgres://username:[email protected]:26257/defaultdb?sslmode=require" 

It will work. Now let's switch the sslmode to verify-full.

cockroach sql --url "postgres://username:[email protected]:26257/defaultdb?sslmode=verify-full" 

It will also work. Now let's switch to the psql client.

psql "postgres://username:[email protected]:26257/defaultdb?sslmode=require" 

It works. And sslmode-verify?

psql "postgres://username:[email protected]:26257/defaultdb?sslmode=verify-full" 

psql: error: connection to server at "artem-serverless-3312.g8z.cockroachlabs.cloud" (34.138.180.55), port 26257 failed: root certificate file "/Users/artem/.postgresql/root.crt" does not exist
Either provide the file or change sslmode to disable server certificate verification.

We need access to the root certificate, as it cannot be implicitly read from the internet.

Download the cert and sslmode-verify will now work.

The lesson here is if you want to verify the server certificate using the psql client, make sure the certificate is stored in the filesystem. The cockroach client is more forgiving in that regard.

Topic 3: Check the Node and the CA Certificate Expiration Programmatically

A customer asks: "How do you check the client certificate expiration programmatically? You can of course scrape the node metrics endpoint and capture the info, you can also navigate to the Advanced Debug page and look at the certificate page, but is there a way to check using SQL?" 

The answer is: yes.

We expose the certificate metrics using the following names, security.certificate.expiration.ca and security.certificate.expiration.node for the certificate authority and node certificates respectively. CockroachDB persists this data in a table crdb_internal.kv_node_status. We can issue the following query to retrieve this information.

-- CA cert
SELECT to_timestamp((metrics->>'security.certificate.expiration.ca')::FLOAT)::TIMESTAMPTZ FROM crdb_internal.kv_node_status;  

-- node cert
SELECT to_timestamp((metrics->>'security.certificate.expiration.node')::FLOAT)::TIMESTAMPTZ FROM crdb_internal.kv_node_status;  

  2028-05-20 15:26:26+00
  ...
  2028-05-20 15:26:25+00

It will output for every node in the cluster. Now you can build your own monitoring and alerting solution to alarm you when your certificates are about to expire, that's if you don't offload the certificate management to an external system. For more information, refer to the Metrics documentation.

Topic 4: Check the Client Certificate Expiration

In the previous topic, we discussed how to check node and certificate authority expiration. We left out the client certificates because keeping this information in the database is not feasible. There may be thousands of client certs. We are going to cover how to check the client certificate expiration below. Keep in mind this is an area of active development and the proper user experience is not yet realized.

I used the following tutorial to create a cluster, CA, node, and client certificates.

The ca.cnf file has a field that controls the cert expiry. Any node and client certs using this ca.cnf configuration file will honor the expiration date specified in this file.

[ CA_default ]
default_days = 1

Skipping the rest of the tutorial to the step where we create a second client user, we can use the same commands from the tutorial to generate a client certificate.

openssl genrsa -out certs/client.expired.key 2048
chmod 400 certs/client.expired.key 
openssl req -new -config client.cnf -key certs/client.expired.key -out client.expired.csr -batch
openssl ca -config ca.cnf -keyfile my-safe-directory/ca.key -cert certs/ca.crt -policy signing_policy -extensions signing_client_req -out certs/client.expired.crt -outdir certs/ -in client.expired.csr -batch

The output will contain the expiration date.

Using configuration from ca.cnf
Check that the request matches the signature
Signature ok
The Subject's Distinguished Name is as follows
organizationName      :ASN.1 12:'Cockroach'
commonName            :ASN.1 12:'expired'
Certificate is to be certified until May 27 17:23:04 2023 GMT (1 days)

Now that we have a client cert, we can use the cockroach cert command to list out all of the client certificates in the certificate directory and their expiration.

cockroach cert --certs-dir certs list

Certificate directory: expired_certs
  Usage  |  Certificate File  |      Key File      |  Expires   |     Notes     | Error
---------+--------------------+--------------------+------------+---------------+--------
  Client | client.expired.crt | client.expired.key | 2023/05/27 | user: expired |
(1 row)

It does not show the time of day when the client certificate is going to expire. We can use the openssl x509 -text command to inspect the certificate for a more detailed expiry.

openssl x509 -text -in expired_certs/client.expired.crt | grep "Not After"

  Not After : May 27 17:23:04 2023 GMT

We have an open issue to surface a metric to expose clients with expiring certs. We can discuss how to automate the check when the metric is available.

Finally, one more method to check for cert expiration is with the openssl verify command.

openssl verify -CAfile certs/ca.crt expired/client.expired.crt

O = Cockroach, CN = expired
error 10 at 0 depth lookup:certificate has expired
expired/client.expired.crt: verification failed: 10 (certificate has expired)