In this article, we’ll discuss how to configure and setup Postgresql database server and psql client to use SSL X.509 server and client certificates so that the communication between them is end-to-end encrypted and secured using Mutual TLS(mTLS) authentication.
Also, we’ll show you how to turn ON both server certificate identity verification and client certificate identity verification in the Postgresql configuration, so that you could enforce Zero Trust Security over all DB connection requests originating from end users.
You can use the same procedure to create SSL TLS X.509 server certificates and client certificates to setting up Mutual TLS authentication for any database (MySQL, MariaDB, MongoDB etc.) and even webservers(Nginx, Apache), workloads(Docker Containers, Service Mesh), web apps etc.
We will use BastionXP CA to issue TLS X.509 server and client certificates to the Postgresql server and Postgresql client, respectively. BastionXP CA is very simple and easy to setup. BastionXP is a open source and open standards based CA which is free to download.
Prerequisites:
You’ll need the following artifacts for this tutorial
- Postgresql server
- Postgresql client
- BastionXP CA
Postgresql Database Server and Client Setup:
Let’s assume you already have a Postgresql database server installed and running in a server with a domain name db.example.com.
Note: You could use any domain name for your db, including internal domains such as psqldb.internal.example.com or even localhost for this tutorial.
If you don’t have a Postgresql installed already, visit the Postgresql official website and follow the instructions to download, install and setup a Postgresql database in your server.
We are using Postgresql version 16.0 for this tutorial.
Now let’s try connecting to this Postgresql database server using a local psql client on the server.
$ psql -U postgres -h 127.0.0.1 postgres psql (16.0 (Debian 16.0-1.pgdg120+1)) Type "help" for help. postgres=#
We have successfully connected to the Postgresql DB.
Next, let’s create SSL certificates and configure the server and client to use SSL certificates and operate in SSL mode.
SSL Mode
There are quite a few SSL modes available for configuration in Postgres such as disable, allow, prefer, require, verify-ca, verify-full.
In this tutorial, we’ll use the strictest mode of SSL connection, which is verify-full. To enable the strictest mode of SSL configuration, you need to have a PKI/CA that can generate more sophisticated server and client certificates with server and user identify built into them, respectively.
If you want to use a generic SSL server and client certificate, you can set the verify-ca SSL mode.
Create SSL TLS X.509 Certificates for the Postgresql Server and Client
Before you can turn ON the SSL mode or Mutual TLS authentication for your Postgresql server and client, you need to create self-signed SSL TLS X.509 certificates for the Postgresql server and client.
For this, please refer to the following tutorial:
BastionXP is a free open-source based SSL TLS X.509 certificate management software to automatically generate, renew and manage SSL X.509 certificates for various applications(web server, web clients, database, web apps, workloads, devices) in your orgnaization.
Once you have created the server and client SSL certificates and the keys, you can move on to the next section.
Configure SSL Certificate for Postgresql Server
To configure the Postgresql server to start accepting only SSL connections and discard all non-SSL connections, we need to turn ON the ssl option in the postgresql.conf file.
We also need to provide the location of the SSL server certificate, SSL private key and the SSL CA certificate files in the server.
The default directory where the Postgresql server looks for the certificates and keys is the data directory at: /var/lib/postgres/data or /var/lib/psql/data or /usr/local/postgresql/data based on your OS and distribution type. The default names for the certificates are: server.crt, server.key and root.crt.
Note: You can also provide a custom location for the SSL certificate and key files using a fullpath for these files in the config file.
First, copy the SSL TLS certificates generated by BastionXP CA to the default directory and change their ownership to postgresql.
$ sudo cp ~/.bsh/tls_server.crt /var/lib/postgres/data/server.crt
$ sudo cp ~/.bsh/tls_server.key /var/lib/postgres/data/server.key
$ sudo cp ~/.bsh/tls_root_ca.crt /var/lib/postgres/data/root.crt
$ sudo chown postgresql:postgresql /var/lib/postgres/data/server.crt /var/lib/postgres/data/server.key /var/lib/postgres/data/root.crt
Next, edit and update the following configuration in the Postgresql server’s configuration file: /var/lib/postgres/data/postgresql.conf
... # - SSL - ssl = on ssl_ca_file = 'root.crt' ssl_cert_file = 'server.crt' ssl_key_file = 'server.key' ...
Now, restart the Postgresql service to pick up the configuration changes.
$ sudo systemctl restart postgresql
$ sudo systemctl status postgresql
Now if we try connecting to the DB using a local psql client, we’ll see that the client is connecting to the server using an SSL connection as shown in the message below:
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
$ psql -U postgres -h 127.0.0.1 postgres psql (16.0 (Debian 16.0-1.pgdg120+1)) SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off) Type "help" for help. postgres=#
The client authentication controls are in the pg_hba.conf file located in the data directory. You need to add the following line to the config file:
# TYPE DATABASE USER ADDRESS METHOD ... hostssl all postgres 0.0.0.0/0 cert clientcert=verify-full
The above configuration will allow the user postgres to connect from any host or IP address to the DB server in SSL mode.
The METHOD "cert" in the configuration tells the DB server to change the authentication method from md5 password based authenticationto client certificate based authentication. So, password based authentication will not work any more.
The configuration "clientcert=verify-full" will force the server to check if the client certificate was issued by a known CA (root CA file at /var/lib/postgresql/data/root.crt).
The server will also check the client identity by verifying if the client certificate contains "CN=postgres" - meaning the client is using a certificate issued exclusively to the login user postgres.
Now, restart the Postgresql service to pick up the above configuration changes.
$ sudo systemctl restart postgresql
$ sudo systemctl status postgresql
Configure SSL Certificate for Postgresql Client
You need to install the Postgresql client on your laptop or local machine from which you want to connect to the Postgresql database server.
Note: BastionXP CA issues short-lived SSL TLS X.509 client certificates [valid for 8 hours only] by default to the end users or psql clients. Users must renew their client certificates after the expiry period. BastionXP CA provides granular control over who can access what resource and for how long.
Copy the SSL client certificates to the ~/.postgresql directory with the following names:
$ mkdir -p ~/.postgresql
$ cp ~/.bsh/tls_client.crt ~/postgres/postgresql.crt
$ cp ~/.bsh/tls_client.key ~/postgres/postgresql.key
$ cp ~/.bsh/tls_root_ca.crt ~/postgres/root.crt
$ ls ~/.postgresql/
postgresql.crt postgresql.key root.crt
When the sslmode is set to verify-full, the client will verify if the server certificate was issued by the trusted CA at ~/.postgresql/root.crt. The client will also verify the identify of the server certificate by checking if the Subject Alternative Name or CN in the certificate is set to the DB server name db.example.com.
This is to ensure the client is indeed connecting to the right Postgresql server it intends to connect to.
[Note: If you want the client to only verify the server certificate and not the server identify you can set the to sslmode=verify-ca, which will only verify if the known CA (~/.postgresql/root.crt) issued the server certificate.]
Now open a new terminal window in your laptop.
Using the psql client, you can connect to your Postgresql database server as shown below:
$ psql "dbname=postgres host=db.example.com port=5432 user=postgres sslmode=verify-full" psql (16.0 (Debian 16.0-1.pgdg120+1)) SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off) Type "help" for help. postgres=#
We are able to successfully connect to our Postgresql database using a client SSL TLS certificate issued by our BastionXP Private CA.
Now, if we try connecting to the DB using the hostname 127.0.0.1, instead of db.example.com we’ll get the below error:
$ psql "dbname=postgres host=127.0.0.1 port=5432 user=postgres sslmode=verify-full" psql: error: connection to server at "127.0.0.1", port 5432 failed: server certificate for "db.example.com" (and 1 other name) does not match host name "127.0.0.1"
Add the following configuration to the Postgresql client service file in the client machine: ~/.pg_service.conf. So that you can skip specifying these arguments as part of the psql client command.
dbname=postgres host=db.example.com port=5432 user=postgres sslmode=verify-full
Conclusion:
Securely connecting to a database is crucial. SSL TLS certificate based Mutual TLS(mTLS) authentication provides two-way authentication between the database server and the client.
In this article, you have learnt how to setup and use BastionXP PKI/CA to issue SSL TLS X.509 CA, server and client certificates.
You also learnt how to securely connect to a Postgresql database over an SSL connection by creating and installing SSL server certificate and SSL client certificate in the Postgresql server and client.
Finally, you learnt how to configure the Postgresql to verify the identity of the server and client specified in the certificate and also the identity of the certificate issuer(CA).
You can use the same procedure to create and configure SSL certificate based mTLS authentication for any database - MySQL, Mariadb, Postgresql, MongoDB etc.
If you have any questions, please write to us at: [email protected]