Enabling SSL on vROPS vPostgreSQL

I recently spent a lot of time digging into the question of “How do I enable a PKI signed cert on the vROPS vPostgreSQL database to make my security team happy?”  As I often do, I started my journey with a simple google search: “vrops vpostgres ssl“.  This search lead me to the helpful page: Enabling TLS on Localhost Connections.  Hooray, I have my answer.  However, as I read the document, asked others about it, and thought more about the subject – things didn’t add up.

After spending a bit more time with google, I found that vROPS has two postgres databases on the master node and the replica node. The purpose of these is laid out pretty well on paktpub.  The key take away is that one database (data) listens only locally, while the other (repl) is publicly reachable. So in a scenario where you are looking to upgrade the certificate for the postgres database your security team is hounding you about, it is in fact the repl database you want to update.

I took the liberty of adapting the instructions for updating the certificate for the repl database to see what happened.

DISCLAIMER: Before making these changes in production, please consult with VMware Support to ensure that the changes you make, particularly if you choose to implement this on the repl database, are supported changes.

DISCLAIMER: These changes do not persist through a vROPS upgrade.

**NOTE: **This guide describes updating a single vROPS node.  You must update this on both the master and replica node for the repl database.  If you wish to update the data database, you must repeat this process on *all* nodes of the cluster. **Remote Collectors do not have a postgres instance**

The first thing you need to do is to create files:

• server.crt – this is the signed PKI certificate from your CA
• server.key – the private key for the above signed cert
• cacert.crt – the full certificate chain from your CA

Then you will want to copy those files to the /storage/db/vcops/vpostgres/repl directory (and/or the data directory if you wish to secure local connections).

After you have created the files and put them in the right directory, you need to change the permissions and file ownership to ensure that they are readable by the postgres process.  If you look closely, you’ll notice that in my screenshot below, I’m performing the permission change in the data directory.  You will want to repeat and validate this for all DB instances you want to have listening with the PKI cert.

After populating the files, and setting their permissions you will need to edit the postgresql.conf file.  One thing you will notice is that the data instance does not listen on TLS/SSL by default, so the associated lines are commented out.  The repl instance is configured for TLS, but is using a self-signed cert.

You will want to uncomment and edit, or edit the appropriate lines to reference your desired settings.  The lines to look for are:

• ssl make sure this is uncommented and the value is set to on
• ssl_key_file make sure this is uncommented and the value is set to server.key
• ssl_cert_file make sure this is uncommented and the value is set to server.crt
• ssl_ca_file make sure this is uncommented and the value is set to cacert.crt

You can use full file path names if you want, and the file names I have used are arbitrary, you can use whatever you like as long as you are consistent.

The final step before we check our work is to restart the postgres service.  You can take drastic action and reboot vROPS, or you can just restart the needed services.  You only need to restart a db instance if you made changes to it.

Once you have restarted the service it’s time to do a checkout.  The first checkout is simple: validate postges is alive and listening.  You can do this by becoming the postgres user and using psql to validate the database is alive and well.  I recommend using the command: /opt/vmware/vpostgres/current/bin/psql -d vcopsdb -p 5433 -c ‘show ssl’. You will want to modify the command to port 5432 if you are checking the local database instance.  If you were successful psql will return something similar to the following screen capture.

The final checkout is to validate that the certificate is presented, is the certificate you are expecting.  The simplest method is to use a python script to do this.  The proper syntax is:
python :5433 | openssl x509 -noout -subject .  If you want to check the local instance, instead use 5432 as the port.  The script will return an error message – when TLS is not enabled, or the subjectname of the cert – when TLS is enabled.

Congratulations, you’ve replaced the self-signed certs for vPostgreSQL on vROPS with PKI signed certs.