Introduction

I shared a patch some time ago that adds a feature on libpq to allow user to supply multiple client certificate pairs. The feature is capable of choosing one client certificate to send to the server (if it requests one) based on the server’s trusted CA certificate settings during TLS handshake. Refer to this blog here that explains more on the principles of chains of trust. The latest patch of the feature can be found in here.

In this blog, I will explain how to prepare several different certificates pairs to test and verify this feature. If you would like to follow along, please download the v2 patch here and apply it to current PostgreSQL master development branch (commit id 3ff01b2b6e7e8627b191a2c8c2690c8ea2f0820d) and follow the procedure below.

Compile PostgreSQL

Once the patch is downloaded and applied, we will need to compile PostgreSQL. I use this ./configure options for the compilation, which also turns on debug mode.

./configure --prefix=$PWD/highgo --enable-debug --with-openssl --enable-tap-tests CFLAGS=-O0

Please note that the feature is only compiled when PostgreSQL is linked to OpenSSL version 1.1.1 or above.

PostgreSQL Server Settings

I will configure the PostgreSQL server to use the certificates that are already available in the SSL test folder src/test/ssl/ssl as of today (2024-03-28). This is handy because we do not have to generate additional certificates for the server in order to run the test.

Assuming that we have already done the initdb, let’s update the settings in postgresql.conf to enable ssl.

ssl = on
ssl_ca_file = '/home/caryh/postgres/src/test/ssl/ssl/root_ca.crt'
ssl_cert_file = '/home/caryh/postgres/src/test/ssl/ssl/server-cn-only.crt'
ssl_key_file = '/home/caryh/highgo/postgres/src/test/ssl/ssl/server-cn-only.key'

Please note that:

• the certificate configured by ssl_cert_file will be sent to the client for verification
• the server will use the CA certificate configured by ssl_ca_file to verify the certificate sent from the client (If the server requests the client to send one).
• root_ca.crt contains only the root CA; it does not include the intermedia CAs.
• This is sometimes known as “mutual authentication” or “mutual certificate verification”

We will also need to configure pg_hba.conf to enforce the client to use SSL and also requests a client certificate from it:

local   all             all                                     trust
# IPv4 local connections:
hostssl    all             all             127.0.0.1/32            trust clientcert=verify-ca
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust

Please note the hostssl line:

• it enforces the client connecting from local address 127.0.0.1 to use ssl only
• clientcert=verify-ca requests the client to also present a client certificate in which the server will verify its trust against its own configured ssl_ca_file.

The PostgreSQL server shall be ready to be started at this point.

PostgreSQL Client Settings

We will use psql + libpq to simulate our client connections. Make sure these are compiled and built with the “v2 multiple client certificate patch” applied. If you have not downloaded it, you can get it from here.

Single Client Certificate Case

This is the simplest case where we simply supply the client with only 1 client certificate + private key pair just like before. The new feature will not be triggered because there is only 1 pair of certificate and it should connect just fine.

highgo/bin/psql -h 127.0.0.1 -d \
    "sslmode=prefer dbname=postgres \
     sslrootcert=src/test/ssl/ssl/both-cas-1.crt \
     sslcert=src/test/ssl/ssl/client+client_ca.crt \
     sslkey=src/test/ssl/ssl/client-encrypted-pem.key \
     sslpassword=dUmmyP^#+"

psql (17devel)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

postgres=#

Please note that:

• client uses “both-cas-1.crt” to verify server’s certificate
• client sends “client+client_ca.crt” to the server for verification. This certificate contains both the client certificate and the intermediate CA that issues it. The server will be able to “complete the chain” with these 2 certificate + the ssl_ca_file that it already has, ensuring a good trust.
• the private key is encrypted so a password is also supplied.

Prepare Multiple Client Certificate

To test multiple client certificate case, we will have to prepare additional certificates that are issued by a different CA, which is completely unrelated the certificates under src/test/ssl/ssl directory. We will supply these new certificates to psql and it should be able to pick the right one to send to the server. Let’s do that now:

Create a new CA certificate called rootCA1:

mkdir -p demoCA/newcerts
touch demoCA/index.txt
echo '01' > demoCA/serial

openssl req -new -nodes -out rootCA1.csr -keyout rootCA1.key -subj "/C=CA/ST=BC/L=VAN/O=IDO/OU=DEV/CN=rootCA1"
openssl x509 -req -in rootCA1.csr -days 3650 -extensions v3_ca -signkey rootCA1.key -out rootCA1.crt

The above generates a new CA certificate called “rootCA1.crt” with its signing key “rootCA1.key”

Issue a new client certificate from rootCA1

openssl req -new -nodes -out client1.csr -keyout client1.key -subj "/C=CA/ST=BC/L=VAN/O=IDO/OU=DEV/CN=client1"
openssl ca -batch -days 365 -keyfile rootCA1.key -cert rootCA1.crt -out client1.crt -infiles client1.csr

The above generates a new client certificate called “client1.csr” with private key “client1.key“

Let’s create another but with encrypted private key:

Create a new CA certificate called rootCA2:

openssl req -new -nodes -out rootCA2.csr -keyout rootCA2.key -subj "/C=CA/ST=BC/L=VAN/O=IDO/OU=DEV/CN=rootCA2"
openssl x509 -req -in rootCA2.csr -days 3650 -extensions v3_ca -signkey rootCA2.key -out rootCA2.crt

Issue a new client certificate from rootCA2 with encrypted private key

enter “password” when prompted to enter a password to decrypt a private key

openssl genrsa -aes256 -passout pass:password -out client2.key 2048
openssl req -new -key client2.key -out client2.csr -subj "/C=CA/ST=BC/L=VAN/O=IDO/OU=DEV/CN=client2"
openssl ca -batch -days 365 -keyfile rootCA2.key -cert rootCA2.crt -out client2.crt -infiles client2.csr

What we have so far?

first root CA group:

• rootCA1.crt
• client1.crt
• client1.key

second root CA group:

• rootCA2.crt
• client2.crt
• client2.key – password

Test Multiple Client Certificate

Now that we have additional client certificate, we can include them in psql client:

highgo/bin/psql -h 127.0.0.1 -d \
"sslmode=prefer dbname=postgres sslrootcert=src/test/ssl/ssl/both-cas-1.crt \
sslcert=certblog/client1.crt,certblog/client2.crt,src/test/ssl/ssl/client+client_ca.crt \
sslkey=certblog/client1.key,certblog/client2.key,src/test/ssl/ssl/client-encrypted-pem.key \
sslpassword=,password,dUmmyP^#+"

psql (17devel)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

postgres=#

and it should connect just fine as well. Please note that we provide sslcert, sslkey and sslpassword as comma-separated list of files and passwords. Their ordering must be consistent. the certificate listed in location 2 of sslcert is assumed to work with private key listed in location 2 of sslkey, which can be decrypted using the password in location 2 of sslpassword. If the private key is not encrypted, we put empty in the respective location.

If we were to remove the right certificate pair from this list, the connection should fail:

highgo/bin/psql -h 127.0.0.1 -d \
"sslmode=prefer dbname=postgres sslrootcert=src/test/ssl/ssl/both-cas-1.crt \
sslcert=certblog/client1.crt,certblog/client2.crt \
sslkey=certblog/client1.key,certblog/client2.key \
sslpassword=,password"

psql: error: connection to server at "127.0.0.1", port 5432 failed: Server requests a client certificate but no suitable certificate is found from sslcert list provided
FATAL:  connection requires a valid client certificate
connection to server at "127.0.0.1", port 5432 failed: FATAL:  no pg_hba.conf entry for host "127.0.0.1", user "caryh", database "postgres", no encryption

If we were to give inconsistent list of certificates, libpq should complain too:

highgo/bin/psql -h 127.0.0.1 -d \
> "sslmode=prefer dbname=postgres sslrootcert=src/test/ssl/ssl/both-cas-1.crt \
> sslcert=certblog/client1.crt,certblog/client2.crt \
> sslkey=certblog/client1.key,certblog/client2.key \
> sslpassword=,password,"

psql: error: could not match 3 sslpassword to 2 sslkey values

Summary

What we have discussed so far is the basic principle of the multiple client certificate feature. The feature basically allows a user to input multiple client certificates via sslcert option, private keys and passwords via sslkey and sslpassword. The feature will automatically choose the best one to connect by looking at server’s list of trusted CA names sent during TLS handshake. This would be a convenient feature in a scenario that one client is tasked to communicate with multiple PostgreSQL servers with different SSL configurations.