DSM 2.1.3: Retrieving database CA to enable secure client connections

I recently has a question about retrieving the Certificate Authority (CA) from a PostgreSQL database which has been provisioned by Data Services Manager (DSM). The customer in question wanted his clients to use the verify-ca option on database connections for additional security. To allow secure connections with verify-ca, the CA needs to be downloaded from the database to the device where the client is making the connection from. In this post, we will go through how to get the certificate so that the Verify-CA option is used to make client connections to a PostgreSQL database secure. Note that this is a DSM Managed Certificate, and is not a custom certificate. For more information on custom certificates with DSM databases, check out this previous blog post on the topic.

Step 1: DSM Gateway API Access

This process will require access to the DSM Gateway API. In the DSM UI, navigate to the top right hand side of the window, click on the user and selected “Download DSM API Kubeconfig”, as shown below.

Set the KUBECONFIG environment variable in your shell to the path of the downloaded Kubeconfig file. You will now be able to run kubectl commands to the DSM Gateway API server.

Step 2: Retrieve Certificate Authority from database

Once access to the DSM Gateway API server has been accomplished, the next step is to display the list of Kubernetes secrets. Every database on the system will have its own unique secret for storing its own CA. The name of the secret includes the name of the database, making it easy to identify. Here are a few examples from my environment.

% kubectl get secret                                                 
NAME                        CREATED AT
.
.
pg-albert-pg1-aria          2024-10-16T22:32:00Z
pg-doug-pg-via-api-01       2024-10-10T20:32:47Z
pg-hbaref-test              2024-10-22T12:31:37Z
.
.

Assume that the database to which we wish to securely connect is hbaref-test. The secret which contains the CA is pg-hbaref-test. You can display the contents of a particular secret using the following command:

% kubectl get secret pg-hbaref-test -o yaml

The CA is stored in a field called under the data parameter called ca.crt. To retrieve the field with the encoded CA, decode it, and store it in the local directory in a file called root.crt, run the following command:

% kubectl get secret pg-hbaref-test -o "jsonpath={.data.ca\\.crt}" | base64 -d > root.crt

Step 3: Connect to the database

If this is a Linux system and the root.crt file containing the CA is stored in $HOME/.postgresql/root.crt, then you do not need to specify the path to this file when running psql commands with the verify-ca option. This command will automatically check the .postgresql folder in the $HOME directory for the CA if “sslmode=verify-ca” is included in the command line. The path to the CA can also be specified in the command using the option “sslrootcert=<path-to-cert>”, as shown in this psql command:

% psql -U pgadmin -h 192.168.0.101 -p 5432 \
"dbname=hbaref-test sslmode=verify-ca sslrootcert=/Users/chogan/root.crt" 

Password for user pgadmin: ********
psql (16.3, server 16.4 (VMware Postgres 16.4.0))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

hbaref-test=#

Success! We have connected to the database with the verify-ca option. Similarly, if you are a user of a database client tool such as pgAdmin 4, a similar situation will occur if you try to use the verify-ca option for SSL mode without having the CA stored locally.

Again, retrieve the CA as shown above using the DSM Gateway API and save it locally in the file $HOME/.postgresql/root.crt. This will allow pgAdmin 4 to provide a secure client connection to the database. Alternatively, you can add another Connection Parameter in the pgAdmin 4 UI called Root Certificate and point it to the CA if it is not stored in the default location.

Thanks for reading this far. While you are here, please check out our Data Services Manager sessions at VMware Explore in Barcelona next month. You can find out the details of the sessions by checking out an earlier blog post here.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.