1. Overview

PostgreSQL is a robust open-source database management system, earning the distinction of DBMS of the Year 2023. Users choose for PostgreSQL due to various reasons, such as SQL support, Query Optimization, and Reliability, etc. In this blog, I will guide you through the process of setting up a TLS connection between a PostgreSQL server and the psql client using self-signed certificates. Here is the general view of the TLS connection setup.

2. Compile PostgreSQL with OpenSSL

Let’s begin by compiling PostgreSQL from the source code, giving you full control in your production environment. I used Ubuntu 22.04 for this demonstration. First, clone the PostgreSQL source code from GitHub and configure it with OpenSSL enabled.

git clone https://github.com/postgres/postgres.git
cd postgres/
./configure --prefix=/tmp/pgapp --with-openssl

If you encounter an error, such as the one below, install libssl-dev on Ubuntu with sudo apt-get install libssl-dev or the corresponding OpenSSL libraries on your operating system.

checking for CRYPTO_new_ex_data in -lcrypto... no
configure: error: library 'crypto' is required for OpenSSL

Once the configuration is successful, build and install PostgreSQL with make -j && make install.

To verify the newly built PostgreSQL, open a terminal, set up paths for binaries and libraries, initialize the database, start PostgreSQL, and connect with psql.

export LD_LIBRARY_PATH=/tmp/pgapp/lib
export PATH=/tmp/pgapp/bin:$PATH

initdb -D pgdata

pg_ctl -D pgdata -l logfile start

$ psql -d postgres
psql (17devel)
Type "help" for help.

postgres=# 

If you can connect to PostgreSQL using psql as shown above, you are ready to proceed to the next step.

3. Generate Self-signed Certificates

When dealing with TLS connections, we typically refer to the client and server. For echo peer, three pieces of information are required: Certificate Authority (CA), Entity Certificate (cert), and Private Key (key). CA is the common trust between the server and client, the Certificate contains a Public Key signed by CA, and the Private Key is paired with the Public Key.

To verify TLS connection, we need the open-source security library OpenSSL to generate certificates and keys. Ensure you have a relatively newer version installed.

$ openssl version
OpenSSL 3.0.2 15 Mar 2022 (Library: OpenSSL 3.0.2 15 Mar 2022)

If not, install OpenSSL on Ubuntu with sudo apt install openssl or the corresponding command for your OS.

3.1 Create a Root CA

First, create your own CA using the command below. You can refer to the official document

$ openssl req -new -nodes -out ca.csr -keyout ca.key -subj "/CN=ca.example.com"

This command generates a Certificate Signing Request (CSR) containing the Public Key and a corresponding Private Key. Ensure the Private Key restricts access to the current user only. If not, manually adjust access using chmod og-rwx ca.key.

Second, self-sign this CSR to generate the root CA.

$ openssl x509 -req -in ca.csr -days 3650 -extfile /etc/ssl/openssl.cnf -extensions v3_ca -signkey ca.key -out ca.crt
Certificate request self-signature ok
subject=CN = ca.example.com

Once the CA certificate is generated, you should have three files: ca.csr is unnecessary, ca.crt is for publication to clients and servers, and ca.key must be kept secret. Typically, the root CA is not used to sign entity certificates; instead, an intermediate or sub-CA should be generated.

$ ls -l
-rw-rw-r-- 1 david david 1127 Jan  5 14:32 ca.crt
-rw-rw-r-- 1 david david  899 Jan  5 14:32 ca.csr
-rw------- 1 david david 1704 Jan  5 14:32 ca.key

3.2 Create a Sub CA

To align with production practices, generate a Sub-CA signed by the root CA. Use this Sub-CA to sign certificates for the Client (psql) and Server (postgres).

$ openssl req -new -nodes -out sub-ca.csr -keyout sub-ca.key -subj "/CN=sub-ca.example.com"
$ openssl x509 -req -in sub-ca.csr -days 1825 -extfile /etc/ssl/openssl.cnf -extensions v3_ca -CA ca.crt -CAkey ca.key -CAcreateserial -out sub-ca.crt
...
$ ls sub-ca*
sub-ca.crt  sub-ca.csr  sub-ca.key

3.3 Create Certificates for Server and Client

Now that the Sub-CA is generated, create certificates for the postgres server and psql client.

$ openssl req -new -nodes -out server.csr -keyout server.key -subj "/CN=pg-server.example.com"
$ openssl x509 -req -in server.csr -days 365 -CA sub-ca.crt -CAkey sub-ca.key -CAcreateserial -out server.crt

$ openssl req -new -nodes -out client.csr -keyout client.key -subj "/CN=client.example.com"
$ openssl x509 -req -in client.csr -days 365 -extfile /etc/ssl/openssl.cnf -CA sub-ca.crt -CAkey sub-ca.key -CAcreateserial -out client.crt

Since we plan to use certificates signed by the Sub-CA, a common practice is to load only the root CA to both postgres server and psql client. Concatenate the entity certificate and Sub-CA into one certificate.

$ cat client.crt sub-ca.crt > client-1.crt
$ cat server.crt sub-ca.crt > server-1.crt

Assuming all actions were performed in the /tmp/tls/ folder, you should have the following files:

$ ls -ltr
-rw------- 1 david david 1704 Jan  5 14:32 ca.key
-rw-rw-r-- 1 david david  899 Jan  5 14:32 ca.csr
-rw-rw-r-- 1 david david 1127 Jan  5 14:32 ca.crt
-rw------- 1 david david 1708 Jan  5 14:33 sub-ca.key
-rw-rw-r-- 1 david david  903 Jan  5 14:33 sub-ca.csr
-rw-rw-r-- 1 david david 1131 Jan  5 14:33 sub-ca.crt
-rw------- 1 david david 1704 Jan  5 14:34 server.key
-rw-rw-r-- 1 david david  907 Jan  5 14:34 server.csr
-rw-rw-r-- 1 david david 1021 Jan  5 14:35 server.crt
-rw------- 1 david david 1704 Jan  5 14:35 client.key
-rw-rw-r-- 1 david david  903 Jan  5 14:35 client.csr
-rw-rw-r-- 1 david david 1017 Jan  5 14:36 client.crt
-rw-rw-r-- 1 david david 2148 Jan  5 14:37 client-1.crt
-rw-rw-r-- 1 david david 2152 Jan  5 14:37 server-1.crt

Before starting to set up the postgres server and psql client, you can quickly check your certificate using the command openssl x509 -in client-1.crt -text -noout. This command is very useful for investigating TLS connection issues.

$ openssl x509 -in client-1.crt -text -noout
Certificate:
    Data:
        Version: 1 (0x0)
        Serial Number:
            06:ce:42:32:e2:04:ac:58:eb:b6:fa:3f:63:82:c1:6d:41:40:2d:23
        Signature Algorithm: sha256WithRSAEncryption
        Issuer: CN = sub-ca.example.com
        Validity
            Not Before: Jan  5 19:06:42 2024 GMT
            Not After : Jan  4 19:06:42 2025 GMT
        Subject: CN = client.example.com
        Subject Public Key Info:
            Public Key Algorithm: rsaEncryption
                Public-Key: (2048 bit)
                Modulus:
                    00:91:b6:b4:12:59:b2:1b:9d:35:d8:76:53:05:bb:
                    1c:0c:ba:b0:9c:a7:9a:94:8e:30:78:69:69:e1:d8:
                    e5:79:b8:1f:19:7c:51:ed:1b:d3:bc:ad:41:34:4d:
                    8e:5e:3e:62:ee:ad:54:66:81:f9:d8:6f:56:13:15:
                    b6:db:61:71:11:20:9c:30:88:59:df:33:4a:fd:7e:
                    e5:e8:56:e1:3d:12:ff:91:8a:70:10:9d:ec:4d:8b:
                    73:6b:7d:bb:ad:82:2d:a2:b0:dc:2c:e9:40:4f:58:
                    6a:29:2a:d0:35:c1:71:4a:05:a0:cc:f1:7e:81:63:
                    b1:6b:c2:e0:e9:91:9e:49:db:63:b1:ec:be:1d:6c:
                    62:5a:a3:9b:6f:2a:bf:44:d6:b1:da:ca:19:35:cd:
                    e7:23:be:8b:b3:fb:98:dd:b0:c3:f3:61:1f:28:af:
                    dc:1d:5f:73:56:05:18:c8:67:0c:08:d9:ac:e7:81:
                    7d:f8:69:60:6b:53:98:40:ac:28:5b:2e:70:d9:22:
                    5f:19:ef:aa:52:e1:3e:a7:c8:af:6b:63:6d:db:a2:
                    7a:d4:7f:17:e9:55:3b:eb:dd:c4:2f:ff:01:08:91:
                    8e:61:23:c1:a8:16:9b:37:1b:e1:5f:d5:56:37:b6:
                    6c:11:7e:7f:df:92:85:8b:e7:0d:f0:01:bc:2b:06:
                    e0:05
                Exponent: 65537 (0x10001)
    Signature Algorithm: sha256WithRSAEncryption
    Signature Value:
        7c:2c:65:ac:09:95:50:a5:bc:95:29:74:8f:67:9a:4c:f1:b6:
        ec:95:4f:e0:d2:5a:44:23:5a:7a:08:cc:a4:3e:92:8a:3d:c9:
        1a:ad:9c:15:bf:db:77:a3:08:c5:05:40:36:c3:80:53:d7:75:
        ff:4d:c0:39:f6:16:67:3d:d3:c7:38:e4:82:60:32:bf:c7:39:
        73:d7:75:6b:99:9f:6e:ad:95:c7:fc:db:e6:04:95:2c:ee:db:
        22:ce:80:1f:fb:ee:85:39:36:e5:81:01:6f:0a:c5:4d:b6:67:
        46:61:d3:4b:55:2e:a3:66:63:d8:eb:f9:1a:d7:4e:62:aa:59:
        f3:c4:04:4d:94:2c:34:06:1f:f7:04:22:bc:5e:95:25:7f:97:
        88:21:96:38:2a:d1:a3:ae:bc:4b:8f:5f:4c:c0:89:a5:07:6a:
        e8:ef:86:8a:ac:05:5b:e7:70:53:36:d4:10:d5:e7:46:ab:64:
        03:11:d0:cf:13:09:f3:8b:15:d0:5c:5d:52:68:41:70:2d:c6:
        dd:15:37:70:20:2e:a9:5a:74:c1:44:ea:25:3a:69:13:ed:34:
        71:7f:9f:75:db:88:07:51:da:54:7c:07:38:87:95:67:c9:d1:
        db:ca:ae:1b:76:5f:a0:4a:1f:67:1d:11:85:77:5a:46:eb:3a:
        3f:4b:6b:e7

Where,

Certificate: indicates this is a certificate file compared to CSR (Certificate Request).
Issuer: CN = sub-ca.example.com shows who signed this certificate.
Validity specifies the certificate’s valid period.
Subject: CN = client.example.com indicates who you are.
Public Key is the identification signed by CA or Sub-CA.
Signature is used to validate that this certificate is signed by the Issuer.

4. Setup TLS on PostgreSQL Server Side

Now, let’s set up the PostgreSQL server side to enable TLS connections. Below are the relevant parameters supported by PostgreSQL up to version 16; however, TLS is disabled by default.

# - SSL -

#ssl = off
#ssl_ca_file = ''
#ssl_cert_file = 'server.crt'
#ssl_crl_file = ''
#ssl_crl_dir = ''
#ssl_key_file = 'server.key'
#ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL'       # allowed SSL ciphers
#ssl_prefer_server_ciphers = on
#ssl_ecdh_curve = 'prime256v1'
#ssl_min_protocol_version = 'TLSv1.2'
#ssl_max_protocol_version = ''
#ssl_dh_params_file = ''
#ssl_passphrase_command = ''
#ssl_passphrase_command_supports_reload = off

In this blog, we will use the following parameters in postgresql.conf:

ssl = on
ssl_ca_file = '/tmp/tls/ca.crt'
ssl_cert_file = '/tmp/tls/server-1.crt'
ssl_key_file = '/tmp/tls/server.key'

Add listen_addresses = '*' if you are setting up TLS connections across multiple machines. Since all tests are conducted on one machine, this setting remains unchanged.

After changing postgresql.conf, add the hostssl configuration to allow TLS connections in pg_hba.conf as shown below:

# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
hostssl all             all             0.0.0.0/0               cert

Finally, connect to the postgres server using psql and reload the configuration.

postgres=# SELECT pg_reload_conf();
...

postgres=# SELECT name, setting, short_desc FROM pg_settings WHERE name LIKE 'ssl%';
                  name                  |         setting          |                               short_desc                                
----------------------------------------+--------------------------+-------------------------------------------------------------------------
 ssl                                    | on                       | Enables SSL connections.
 ssl_ca_file                            | /tmp/tls/ca.crt          | Location of the SSL certificate authority file.
 ssl_cert_file                          | /tmp/tls/server-1.crt    | Location of the SSL server certificate file.
 ssl_key_file                           | /tmp/tls/server.key      | Location of the SSL server private key file.
... 

If you tail the logfile simultaneously, you should see messages like the ones below:

2024-01-05 15:16:41.395 PST [1226391] LOG:  received SIGHUP, reloading configuration files
2024-01-05 15:16:41.398 PST [1226391] LOG:  parameter "ssl" changed to "on"
2024-01-05 15:16:41.398 PST [1226391] LOG:  parameter "ssl_ca_file" changed to "/tmp/tls/sub-ca.crt"
2024-01-05 15:16:41.398 PST [1226391] LOG:  parameter "ssl_cert_file" changed to "/tmp/tls/server-1.crt"
2024-01-05 15:16:41.398 PST [1226391] LOG:  parameter "ssl_key_file" changed to "/tmp/tls/server.key"

5. Verify the TLS Connection Using psql as Client

PostgreSQL supports various TLS modes. In this blog, we will test sslmode=verify-ca and sslmode=verify-full. Additional tests can be covered in future blogs.

Open another terminal, set up the paths to point to the binaries and libraries, and run the following command:

$ psql "sslmode=verify-ca sslrootcert=ca.crt sslcert=client-1.crt sslkey=client.key hostaddr=127.0.0.1 user=david dbname=postgres"
psql (17devel)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

postgres=#

If you can connect to the PostgreSQL server with information like SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off), then your psql is connected to the PostgreSQL server using TLS, and the communication is encrypted. You can also use Wireshark to verify it.

You can also verify the TLS connection through the extension sslinfo. For example:

$ cd contrib/sslinfo/
$ make && make install

postgres=# CREATE EXTENSION sslinfo;
CREATE EXTENSION

postgres=# SELECT ssl_is_used();
 ssl_is_used 
-------------
 t
(1 row)

or use a query like below:

postgres=# SELECT datname, usename, ssl, client_addr, application_name FROM pg_stat_ssl JOIN pg_stat_activity ON pg_stat_ssl.pid = pg_stat_activity.pid;
 datname  | usename | ssl | client_addr | application_name 
----------+---------+-----+-------------+------------------
 postgres | david   | f   |             | psql
 postgres | david   | t   | 127.0.0.1   | psql
(2 rows)

postgres=# 

To verify sslmode=verify-full, you need to provide the host in the psql connection string. In my case, the PostgreSQL server has a certificate with the Common Name set to pg-server.example.com. To avoid the real DNS lookup, add the line 127.0.0.1 pg-server.example.com to /etc/hosts.

$ psql "sslmode=verify-full sslrootcert=ca.crt sslcert=client-1.crt sslkey=client.key hostaddr=127.0.0.1 user=david dbname=postgres host=pg-server.example.com"
psql (17devel)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

postgres=# 

The details about sslmode has been documented here.

6. Summary

In this blog post, I demonstrated a simple process to set up a TLS connection using the psql client with the PostgreSQL server, and I hope it can be helpful.