1.0 Introduction

TLS is one of the most commonly used security protocol in most applications but also least understood. In this blog, I will briefly explain the concept of TLS and how it can be configured to Postgres version 15 compiled with compatible OpenSSL library.

2.0 PostgreSQL Server Side Settings

These are the TLS settings available in postgresql.conf related to TLS. Note that these parameters start with prefix SSL, which is a term used interchangeably with TLS (which is a newer term). Both refer to the same thing.

#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

Most of the time, you just need to fill in ssl, ssl_ca_file, ssl_cert_file, ssl_key_file and possibly ssl_passphrase_command. For example:

ssl = on
ssl_ca_file = '/home/user/cert/cacert.pem'
ssl_cert_file = '/home/user/cert/server.pem'
ssl_key_file = '/home/user/cert/server.key'
ssl_passphrase_command = 'echo passphrase'

These basic TLS parameters tell Postgres that you would like to enable TLS on your server and provide paths to Certificate Authority (CA) certificate file, entity certificate file and a private key file. These 3 files are normally referred as X509 certificates and are mainly used to guarantee trust between a client and a server.

the CA certificate file (ssl_ca_file) is the root of trust and you can use openssl to generate one for your organization and use it to create and sign other entity certificates for your application to use.

the entity certificate (ssl_cert_file) and private key (ssl_key_file) form a pair, in a sense that the certificate itself contains a public key while the private key file contains the private key. These keys are used for asynchronous authentication during TLS handshake. The entity certificate is normally signed with the same CA certificate file configured in ssl_ca_file such that it can be trusted. Both the private key and entity certificate files can also be generated and signed using openssl

ssl_passphrase_command is used to get a passphrase to decrypt the ssl_key_file if it is encryped when generated.

Refer to this blog post here about how to use openssl to create and sign these certificate files and also a short explaination on the rest of TLS related parameters that are left default mostly.

3.0 PostgreSQL Server Side pg_hba Settings

In addition to the main settings above, you also need to configure pg_hba.conf to restrict which connection requires TLS and which does not. These settings are normally more complicated to setup for most people. With TLS enabled, there are potentially 2 authentications involved; one is involved within TLS handshake (to verify the trust between client and server), the other is involved within PG to check if a connectiing user is authrorized.

Consider this example:

hostssl   mydatabase myuser       192.168.1.0/24     trust
hostssl   mydatabase myuser2      192.168.1.0/24     trust clientcert=verify-ca
hostssl   mydatabase myuser3      192.168.1.0/24     password clientcert=verify-ca
hostssl   mydatabase myuser4      192.168.1.0/24     cert clientcert=verify-ca
hostnossl mydatabase myuser5      127.0.0.1/32       trust

hostssl mydatabase myuser 192.168.1.0/24 trust

means as long as myuser uses TLS to connect within 192.168.1.0 network, myuser will be given access to mydatabase right away

hostssl mydatabase myuser2 192.168.1.0/24 trust clientcert=verify-ca

means that if myuser2 uses TLS to connect within 192.168.1.0 network, myuser2 is required to provide its own X509 certificate and the server will verify client certificate using the CA certificate configured in ssl_ca_file. If the certificate can be trusted, then myuser2 will be given access to mydatabase

hostssl mydatabase myuser3 192.168.1.0/24 password clientcert=verify-ca

means that if myuser3 uses TLS to connect within 192.168.1.0 network, myuser3 is required to provide its own X509 certificate and the server will verify client certificate using the CA certificate configured in ssl_ca_file. If the certificate can be trusted, then myuser3 will be prompted for a password. If the password matches then myuser3 will be given access to mydatabase

hostssl mydatabase myuser4 192.168.1.0/24 cert clientcert=verify-ca

means that if myuser4 uses TLS to connect within 192.168.1.0 network, myuser4 is required to provide its own X509 certificate and the server will verify client certificate using the CA certificate configured in ssl_ca_file. If the certificate can be trusted, then PG will read the Common Name (CN) field in the client-provided certificate and check if it matches the connecting user’s username, if it matches, then user4 will be given access to mydatabase

hostnossl mydatabase myuser5 127.0.0.1/32 trust

means that if myuser5 does not use TLS connect within 127.0.0.1 localhost, myuser5 will simply be given access to mydatabase

4.0 PostgreSQL Client Side TLS Options

Most of the time, when a psql client connects to a Postgres server with TLS enabled, it is the psql client who will verify the entity certificate sent by Postgres server (configured in ssl_cert_file in postgresql.conf). In otherwords, the client verifies the server. Of course, the psql client also need to have a copy of the CA certificate (or a sub CA certificate signed by a common root CA) that the client can use to verify the server’s certificate during handshake.

This is similar to a user connecting to a https website on a browser. The web server, for example, google.ca will send its server certificate to your browser during handshake, the browser will verify this certificate using many of the built-in CA certificates that come with your browser. If the certificate can be trusted, it allows you to connect. If not, it will give you a warning page saying “Your connection is not private” and you have a choice to proceed or not proceed.

With psql client, you can specify a certificate to send to the server and a CA certificate to use to verify server’s certificate.

psql -U myuser2 -h 192.168.1.123 -d "sslmode=verify-ca dbname=mydatabase sslrootcert=/home/user/cert/cacert.pem sslcert=/home/user/cert/client.pem sslkey=/home/user/cert/client.key"

This means that psql client will verify server’s certificate using its CA certificate specified in sslrootcert. Since the server also requires the client to provide a certificate (because of clientcert=verify-ca in pg_hba.conf), the client will send the certificate specified in sslcert to the server for mutual authentication.

Cary Huang

Cary is a Senior Software Developer in HighGo Software Canada with 8 years of industrial experience developing innovative software solutions in C/C++ in the field of smart grid & metering prior to joining HighGo. He holds a bachelor degree in Electrical Engineering from University of British Columnbia (UBC) in Vancouver in 2012 and has extensive hands-on experience in technologies such as: Advanced Networking, Network & Data security, Smart Metering Innovations, deployment management with Docker, Software Engineering Lifecycle, scalability, authentication, cryptography, PostgreSQL & non-relational database, web services, firewalls, embedded systems, RTOS, ARM, PKI, Cisco equipment, functional and Architecture Design.