CockroachDB TIL: Volume 13
Cover topics such as diagnosing cert issues and looking at client verification of server certificate differences in the cockroach and the psql clients.
Join the DZone community and get the full member experience.Join For Free
- Volume 1
- Volume 2
- Volume 3
- Volume 4
- Volume 5
- Volume 6
- Volume 7
- Volume 8
- Volume 9
- Volume 10
- Volume 11
- Volume 12
- Topic 1: Diagnose certificate-based authentication
- Topic 2: Differences between
psqlclients for client verification of server certificates
- Topic 3: Capture the node and the CA certificate expiration programmatically
- Topic 4: Check the client certificate expiration
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:
When I attempted to connect, I received an error:
psql: error: connection to server at "psedge.global" (184.108.40.206), 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:firstname.lastname@example.org: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 sql --url "postgres://username:email@example.com: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. # firstname.lastname@example.org: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 CERTIFICATE----- ... -----END CERTIFICATE-----
Save it as a new file and then use it in the connection string with
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 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@example.com: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:firstname.lastname@example.org: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
psql Clients for Client Verification of Server Certificates
While I was writing the first topic, I noticed a slight difference in behavior between the
psql clients when it comes to client verification of server certificates. Unlike the
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
sslcrl or the environment variables
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@example.com:26257/defaultdb?sslmode=require"
It will work. Now let's switch the sslmode to
cockroach sql --url "postgres://username:firstname.lastname@example.org:26257/defaultdb?sslmode=verify-full"
It will also work. Now let's switch to the
It works. And
psql: error: connection to server at "artem-serverless-3312.g8z.cockroachlabs.cloud" (220.127.116.11), 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.
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.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.
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.
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)
Published at DZone with permission of Artem Ervits. See the original article here.
Opinions expressed by DZone contributors are their own.