Securing PostgreSQL client connections in VMware Data Services Manager v1.5 using TLS

by Posted on

I had an interesting question recently about how best to secure client access to databases that are provisioned via VMware Data Services Manager (DSM). The good news is that we can achieve secure client connections in a single step, directly from the DSM Provider UI. Simply navigate to the database view, and in the Details page, there is a Security section. In the Actions drop-down from this page, we can very simply enable “Client TLS”. What this means is that clients will no longer be able to connect to the database with SSL. Only SSL connections will be allowed. Below is a screenshot of what this configuration looks like in the Provider UI:

So what is actually going on under the covers? It is quite simple really. For PostgreSQL databases, we are modifying the pg_hba.conf file. We change the hostnossl entry from:

hostnossl all all 0.0.0.0/0 md5

to

hostnossl all all 0.0.0.0/0 reject

This means that, before enabling “Client TLS”, all clients were able login to all databases from any IP address without using encrypted communication. All clients needed to do was provide a valid password. After enabling “Client TLS”, clients can now only login to the database using SSL/TLS encrypted communication. This is apparent during the connection attempt, and if the connection info is queried afterwards:

db-admin [ ~ ]$ psql -h xx.xx.xx.xx -U dbaas "dbname=dbaas"
postgresql 11:41:05.00
postgresql 11:41:05.00 Welcome to the Bitnami vmware-postgresql container
postgresql 11:41:05.01 Subscribe to project updates by watching https://github.com/bitnami/containers
postgresql 11:41:05.01 Submit issues and feature requests at https://github.com/bitnami/containers/issues
postgresql 11:41:05.02

Password for user dbaas: ********
psql (15.2, server 13.10)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, compression: off)
Type "help" for help.

dbaas=> \conninfo
You are connected to database "dbaas" as user "dbaas" on host "XX.XX.XX.XX" at port "5432".
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, compression: off)
dbaas=>

We can see that this is an SSL connection using the protocol is TLS v1.2. We can also see the cipher that is being used. Thus, connections to the database are now encrypted. Interestingly, I did not have to put anything into the psql connection statement to choose SSL, such as sslmode=require. Secure connections occur automatically. The root certificate, as well as the server certificate and key and whether or not SSL is enabled is configured automatically in the postgresql.conf configuration file, e.g.

# - SSL -
ssl = on
ssl_ca_file = '/bitnami/postgresql/cert/dbengine-ca.pem'
ssl_cert_file = '/bitnami/postgresql/cert/dbengine-server-cert.pem'
ssl_key_file = '/bitnami/postgresql/cert/dbengine-server-key.pem'

Note that PostgreSQL supports other client connection types, such as certificate only connections which do not require passwords. We do not currently implement that method via the UI in DSM today. Therefore we still require the database user to always provide a password, as shown above. Note also that enabling TLS, and modifying the pg_hba.conf automatically restarts the database. Thus it should be considered a maintenance task on a production database. However, you would certainly want to give serious consideration to enabling client TLS on any production databases deployed via DSM.

Disabling “Client TLS” in the DSM Provider UI simply reverts the changes in the pg_hba.conf, removing the reject entry against the hostnossl and adding the md5 entry back, allowing unencrypted logins using passwords once more.

Another option: verify-ca

Another option available to improve client security is the verify-ca SSL mode. To use this mode, you would first need to download the root certificate of authority from the database via the Provider UI, as shown here:

Now you can use a more advanced psql connection string to connect to the database. When this mode is specified using sslmode=verify-ca, the root certificate downloaded from the Provider in the previous step must also be included in the command using sslrootcert=.

$ psql -h XX.XX.XX.XX -U dbaas "dbname=dbaas sslmode=verify-ca"
psql: error: connection to server at "xx.xx.xx.xx", port 5432 failed: root certificate file "/home/cormac/.postgresql/root.crt" does not exist
Either provide the file or change sslmode to disable server certificate verification.


$ psql -h XX.XX.XX.XX -U dbaas "dbname=dbaas sslmode=verify-ca sslrootcert=root.crt"
Password for user dbaas: ********
psql (14.8 (Ubuntu 14.8-0ubuntu0.22.04.1), server 15.2)
WARNING: psql major version 14, server major version 15.
 Some psql features might not work.
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

dbaas=> \conninfo
You are connected to database "dbaas" as user "dbaas" on host "xx.xx.xx.xx" at port "5432".
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)

Published by Cormac