DSM 9.0.1 – Postgres Database Access via Client Certificates
Another new feature in Data Services Manager 9.0.1 is the ability to setup access to a Postgres database using Client Certificates. This security features removes the need for “password” based authentication. In order to be able to use this functionality, the Postgres database must already be configured with external/custom certificates. This includes the PEM formatted certificate chain that has the server leaf cert, any intermediate certs and of course the Certificate Authority (CA). It also includes the PEM Private Key. With the custom server certificates associated with the database in place, we can proceed with updating the pg_hba.conf (host based authentication) to include a client certificate based authentication method for one or more users. For example, if I wanted user cormac to be able to access the database using client certificates, I might add an entry similar to the following to the pg_hba.conf. This can be done by adding the entry to the Host Based Authentication (HBA) section of the database in the DSM UI:
hostssl all cormac 10.x.x.0/22 cert clientcert=verify-full
Note that it is also required that you only use hostssl with client certs. Be aware that this method cannot be used for system users, such as the database admin configured via DSM (e.g., pgadmin). Custom host based authentication entries are inserted into the pg_hba.conf file with a priority lower than system users. Thus, system users such as pgadmin, with its authentication method scram-sha-256 for passwords, will be evaluated before any client certificate based entry is reached in the configuration.
The next step is to create a client certificate for the user in question. This is what they will use to logon to the database. There are various methods to do this. I am creating my user keys and certificates using openssl. The important part is to ensure that the Common Name (CN) part of the Distinguished Name (DN) in the client certificate is set to the username that you wish to grant database access to via certificate authentication. Here is an example of the output created when I ran the CSR (Certificate Signing Request) for my user certificate using openssl.
% openssl req -config config/openssl.cnf \ -key leaf-keys/cormac.usr.key.pem \ -new -sha256 -out csr/cormac.usr.csr \ -out certs/cormac.usr.cert You are about to be asked to enter information that will be incorporated into your certificate request. What you are about to enter is what is called a Distinguished Name or a DN. There are quite a few fields but you can leave some blank For some fields there will be a default value, If you enter '.', the field will be left blank. ----- Common Name []:cormac Country Name (2 letter code) [IE]: State or Province Name [Cork]: Locality Name [Cork]: Organization Name [VCF Division]: Organizational Unit Name [OCTO]: Email Address [cormac.hogan@broadcom.com]:
As you can see, I set the CN, or Common Name, to ‘cormac’. Once the CSR is created, I can proceed with the creation of the client certificate for my user. I am using the same CA as that provided to the database when it was configured with custom server certificates.
% openssl ca -config config/openssl.cnf -extensions usr_cert \ -days 375 -notext -md sha256 -in csr/cormac.usr.csr \ -out certs/cormac.usr.cert.pem Using configuration from config/openssl.cnf Enter pass phrase for /Users/chogan/CERTS-HOWTO/UsingOpenSSL-for-Postgres/private/ca.key.pem: ***** Check that the request matches the signature Signature ok Certificate Details: Serial Number: 4119 (0x1017) Validity Not Before: Aug 29 08:59:49 2025 GMT Not After : Sep 8 08:59:49 2026 GMT Subject: countryName = IE stateOrProvinceName = Cork organizationName = VCF Division organizationalUnitName = OCTO commonName = cormac emailAddress = cormac.hogan@broadcom.com X509v3 extensions: X509v3 Basic Constraints: CA:FALSE Netscape Cert Type: SSL Client, S/MIME Netscape Comment: OpenSSL Generated Client Certificate X509v3 Subject Key Identifier: 45:54:6E:52:06:54:51:C6:A1:98:00:8D:EB:61:0D:84:E2:D2:DD:D2 X509v3 Authority Key Identifier: 82:22:38:34:35:F2:85:30:0F:1A:3B:7E:BD:62:9D:E4:2E:06:7C:DF X509v3 Key Usage: critical Digital Signature, Non Repudiation, Key Encipherment X509v3 Extended Key Usage: TLS Web Client Authentication, E-mail Protection Certificate is to be certified until Sep 8 08:59:49 2026 GMT (375 days) Sign the certificate? [y/n]:y 1 out of 1 certificate requests certified, commit? [y/n]y Write out database with 1 new entries Database updated
For those interested, here is the usr_cert extension section from my openssl.cnf, which seems to work quite well for me when creating client certificates.
[ usr_cert ] # Extensions for client certificates (`man x509v3_config`). basicConstraints = CA:FALSE nsCertType = client, email nsComment = "OpenSSL Generated Client Certificate" subjectKeyIdentifier = hash authorityKeyIdentifier = keyid,issuer keyUsage = critical, nonRepudiation, digitalSignature, keyEncipherment extendedKeyUsage = clientAuth, emailProtection
Now we need to create a login for the user to the database, and grant some permissions. We can login as the original pgadmin by picking up the connection string from DSM. There is no need to provide the ‘cormac’ role with a password as the login will be via authenticated by certificate only. After creating the role, I can check that it is added to the pg_roles table correctly.
$ psql postgresql://pgadmin:VMware123%21@pg-01.rainpole.com:5432/pg-01 psql (16.10 (Ubuntu 16.10-0ubuntu0.24.04.1), server 16.9 (VMware Postgres 16.9.0)) SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off) Type "help" for help. pg-01=# CREATE ROLE cormac WITH LOGIN SUPERUSER; CREATE ROLE pg-01=# GRANT ALL PRIVILEGES ON DATABASE "pg-01" to "cormac"; GRANT pg-01=# GRANT ALL ON SCHEMA public TO "cormac"; GRANT pg-01=# SELECT rolname FROM pg_roles WHERE rolsuper = true; rolname --------------------------- postgres pgautofailover_replicator pgadmin cormac (4 rows) pg-01=# SELECT rolname FROM pg_roles WHERE rolcanlogin = true; rolname --------------------------- postgres pgautofailover_monitor pgautofailover_replicator postgres_exporter pgadmin pgappuser pgrouser pgrwuser cormac (9 rows) pg-01=# \q
Let’s now check to see if the user can login using just the certificates and keys created earlier.
$ psql "host=pg-01.rainpole.com port=5432 dbname=pg-01 \ sslmode=verify-full \ sslcert=cormac.usr.cert.pem \ sslkey=pg-01.rainpole.com.no-pass.pem \ sslrootcert=ca.cert.pem" psql (16.10 (Ubuntu 16.10-0ubuntu0.24.04.1), server 16.9 (VMware Postgres 16.9.0)) SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off) Type "help" for help. pg-01=# \conninfo You are connected to database "pg-01" as user "cormac" on host "pg-01.rainpole.com" (address "10.x.x.92") at port "5432". SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off) pg-01=#
Success. I have been able to logon to the Postgres database using just certificates, and without providing a password. If you made any mistakes when creating the roles or granting the permissions, you can use the REVOKE … FROM command and DROP USER command to roll back any changes and try again. However, if you did everything correctly in the setup, you should now be able to use certificates to login to the Postgres database.
A common question I get is around the difference between using verify-full vs. verify-ca for the sslmode. From the official docs, it seems like both options require the client to present a valid (trusted) SSL certificate, while verify-full additionally enforces that the CN (Common Name) in the certificate matches the username. The sslmode parameter specifies what the client expects from the server, for example verify-ca specifies that the client requires SSL and verifies that the server’s certificate is signed by a trusted CA.
Note that when the client certificate method is verify-full, you cannot omit any of the required certificate fields or the login will fail. Here are some examples. Also note that psql is looking in some default locations if certain parameters if they are not specified on the command line. Below you will see (1) a failure when the CA is not provided, (2) a failure when the client key is not provided and (3) a failure when the client certificate is not provided.
$ psql "host=pg-01.rainpole.com port=5432 dbname=pg-01 \
sslmode=verify-full \
sslcert=cormac.usr.cert.pem \
sslkey=pg-01.rainpole.com.no-pass.pem"
psql: error: connection to server at "pg-01.rainpole.com" (10.x.x.92), port 5432 failed:
root certificate file "/home/cormac/.postgresql/root.crt" does not exist
Either provide the file, use the system's trusted roots with sslrootcert=system,
or change sslmode to disable server certificate verification.
$ psql "host=pg-01.rainpole.com port=5432 dbname=pg-01 \
sslmode=verify-full \
sslcert=cormac.usr.cert.pem \
sslrootcert=ca.cert.pem"
sql: error: connection to server at "pg-01.rainpole.com" (10.x.x.92), port 5432 failed:
certificate present, but not private key file "/home/cormac/.postgresql/postgresql.key"
$ psql "host=pg-01.rainpole.com port=5432 dbname=pg-01 \
sslmode=verify-full \
sslkey=pg-01.rainpole.com.no-pass.pem \
sslrootcert=ca.cert.pem"
psql: error: connection to server at "pg-01.rainpole.com" (10.x.xx.92), port 5432 failed:
FATAL: connection requires a valid client certificate
