Create and Install Client and Server SSL Certificates for PostgreSQL Database

 

When a database client establishes a TCP/IP connection to a database server, it typically creates an unencrypted communication channel unless explicitly stated. Most database engines have native support for encryption and authentication using SSL/TLS.

In typical secure setups, the database server defines the minimum level of security required for TCP/IP connections. It is possible to force all TCP/IP connections to be encrypted – for example, PostgreSQL uses “hostssl” in the pg_hba.conf file, while MySQL uses “require_secure_transport=ON” in the my.cnf file. In addition to encryption, authentication can be enabled by installing client/server certificates on the respective component.

Depending on the environment (e.g. dev/staging/uat/production), databases can have varying security requirements. Development databases may allow unencrypted TCP/IP connections while production databases would be hardened to only allow encrypted ones.

However, it is possible for the application/client to be the one that enforces this requirement. For example, an application might append a “sslmode=verify-ca” to the JDBC connection string, forcing all outgoing connections to use SSL/TLS encryption and authentication. This might be fine in the production environment, but it would also require that a development database be able to respond to SSL/TLS encryption and authentication connection requests.

This post will look at how we can enable SSL/TLS encryption and authentication on a self-managed PostgreSQL database and client so as to establish encrypted and secure connections between them.

Understanding What SSL/TLS Protects against

Without going too deep down the rabbit hole, SSL/TLS provides protection against the following threats:

• Eavesdropping – malicious third party examines the network traffic between the client and the server
• Man in the middle (MITM) – malicious third party pretends to be your database server and accepts your traffic
• Impersonation – malicious third party pretends to be an authorized client and accesses the database server

Because security often has computational overheads that impact application performance, most database servers offer several levels of protection for their connections. PostgreSQL has several SSL Modes:

• disable
• allow
• prefer
• require
• verify-ca
• verify-full

Except for verify-ca and verify-full, the remaining modes are supported by PostgreSQL out of the box. A full description of the threats and the protection levels can be found at https://www.postgresql.org/docs/current/libpq-ssl.html#LIBPQ-SSL-PROTECTION

Setup

I’ll be using the following for this guide:

• Database: PostgreSQL 12.7 (running locally)
• Cryptography Library: OpenSSL 1.1.1f
• Host: Virtual Machine running Ubuntu 20.04.2 LTS (fresh install)
• Hypervisor: Oracle VM VirtualBox 6.1

Step 1: Verify/Install OpenSSL Cryptography Library

OpenSSL is a cryptography software library used by PostgreSQL to secure TCP/IP connections via SSL/TLS (docs). Verify that OpenSSL is installed:

$ openssl version
OpenSSL 1.1.1f  31 Mar 2020

Or install it if necessary:

$ sudo apt-get install openssl

Step 2: Install, Configure and Start PostgreSQL

Check the version of PostgreSQL that is in the standard Ubuntu package repository either via the PostgreSQL Apt Repository page, or using apt-cache madison:

$ apt-cache madison postgresql
postgresql | 12+214ubuntu0.1 | http://sg.archive.ubuntu.com/ubuntu focal-updates/main amd64 Packages
postgresql | 12+214ubuntu0.1 | http://sg.archive.ubuntu.com/ubuntu focal-security/main amd64 Packages
postgresql |     12+214 | http://sg.archive.ubuntu.com/ubuntu focal/main amd64 Packages

Install PostgreSQL database:

$ sudo apt-get install postgresql
... 
The following additional packages will be installed:
  libllvm10 libpq5 libsensors-config libsensors5 postgresql-12 postgresql-client-12 postgresql-client-common postgresql-common
 ssl-cert sysstat
Suggested packages:
  lm-sensors postgresql-doc postgresql-doc-12 libjson-perl openssl-blacklist isag
The following NEW packages will be installed:
  libllvm10 libpq5 libsensors-config libsensors5 postgresql postgresql-12 postgresql-client-12 postgresql-client-common postgresql-common ssl-cert sysstat
0 upgraded, 11 newly installed, 0 to remove and 66 not upgraded.
Need to get 30.6 MB of archives.
After this operation, 122 MB of additional disk space will be used.
Do you want to continue? [Y/n] y

Verify that PostgreSQL has been installed successfully:

$ sudo systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited) since Thu 2021-08-05 12:41:01 UTC; 8min ago
   Main PID: 3410 (code=exited, status=0/SUCCESS)
      Tasks: 0 (limit: 2279)
     Memory: 0B
     CGroup: /system.slice/postgresql.service

Aug 05 12:41:01 user systemd[1]: Starting PostgreSQL RDBMS...
Aug 05 12:41:01 user systemd[1]: Finished PostgreSQL RDBMS.


$ tail /var/log/postgresql/postgresql-12-main.log
...
2021-08-05 17:50:50.910 UTC [7919] LOG:  database system is ready to accept connections

Step 3: Establish Encrypted/Unencrypted Connection (Without Certificate Verification) via psql Client

Out of the box, PostgreSQL will accept both encrypted and unencrypted TCP/IP connections. Before testing, I will make a quick change to pg_hba.conf to allow passwordless logins via localhost. For a non-POC database server, you should use the ALTER USER command to set a password.

$ find / -name pg_hba.conf 2>/dev/null
/etc/postgresql/12/main/pg_hba.conf

$ sudo vi /etc/postgresql/12/main/pg_hba.conf
host    all             all             127.0.0.1/32            trust     # I modified this line

## the above modification to pg_hba.conf will allow passwordless logins for all users connecting via TCP/IP to 127.0.0.1 (localhost)

Reload the PostgreSQL process to pick up the new configs.

$ sudo systemctl restart postgresql

Now lets connect to PostgreSQL with and without SSL/TLS encryption (refer to absence/presence of line that begins with “SSL connection”):

$ psql postgresql://postgres@127.0.0.1:5432/postgres?sslmode=disable
psql (12.7 (Ubuntu 12.7-0ubuntu0.20.04.1))
Type "help" for help.

postgres=# exit

$ psql postgresql://postgres@127.0.0.1:5432/postgres?sslmode=require
psql (12.7 (Ubuntu 12.7-0ubuntu0.20.04.1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=# exit

However, when we try to use the verify-ca or verify-full modes, we get an error as we have not set up the server certificates yet.

$ psql postgresql://postgres@127.0.0.1:5432/postgres?sslmode=verify-ca
psql: error: root certificate file "/home/user/.postgresql/root.crt" does not exist
Either provide the file or change sslmode to disable server certificate verification.

$ psql postgresql://postgres@127.0.0.1:5432/postgres?sslmode=verify-full
psql: error: root certificate file "/home/user/.postgresql/root.crt" does not exist
Either provide the file or change sslmode to disable server certificate verification.

Step 4: SSL Certificate for PostgreSQL Server

There are two possible options when it comes to creating server certificates for PostgreSQL – self-signed and CA-signed.

Step 4a: Create and Install Self-Signed Server Certificate for PostgreSQL

For this section, I am following the commands from the PostgreSQL documentation at https://www.postgresql.org/docs/current/ssl-tcp.html#SSL-CERTIFICATE-CREATION.

Use OpenSSL to create a self-signed server certificate and private key for the PostgreSQL server:

$ openssl req -new -x509 -days 365 -nodes -text -out server.crt \
>  -keyout server.key -subj "/CN=127.0.0.1"
Generating a RSA private key
.......................+++++
...........................................................+++++
writing new private key to 'server.key'
-----

$ chmod og-rwx server.key server.crt

Note that the common name of the certificate is set to 127.0.0.1.

As this is a non-production environment, I decided to locate all my certificates within ~/.postgresql for convenience (recall the error message in Step 3). In a production environment, you would usually put it in /etc/ssl/certs/ and /etc/ssl/private/ (or similar).

$ mkdir ~/.postgresql
$ mv server.* ~/.postgresql/

Since we are self-signed, we will use the server certificate (server.crt) as the trusted root certificate (root.crt) for the client (source). As root.crt is the same as server.crt, this will tell the psql client to trust certificates that are signed by itself (i.e. self-signed). Note that the “~/.postgresql/root.crt” location used by psql is the default location and is configurable.

$ cp ~/.postgresql/server.crt ~/.postgresql/root.crt

As the postgres service account will be accessing the certificate and private key, change ownership of server.crt and server.key to the postgres user:

$ sudo chown postgres:postgres ~/.postgresql/server*

If you do not do this, the database will fail to start with the error:

FATAL:  could not load server certificate file "/home/user/.postgresql/server.crt": Permission denied

Right now, our ~/.postgresql folder should look like this:

$ ls -al ~/.postgresql/
-rw------- 1 user     user     4141 Aug  5 17:28 root.crt
-rw------- 1 postgres postgres 4141 Aug  5 17:02 server.crt
-rw------- 1 postgres postgres 1704 Aug  5 17:02 server.key

Modify ssl_cert_file and ssl_key_file settings in the postgresql.conf file to point the our ~/.postgresql folder:

$ find / -name postgresql.conf 2>/dev/null
/etc/postgresql/12/main/postgresql.conf

$ sudo vi /etc/postgresql/12/main/postgresql.conf
ssl = on                                              # verify this is set to 'on'
ssl_cert_file = '/home/user/.postgresql/server.crt'   # update this line
ssl_key_file = '/home/user/.postgresql/server.key'    # update this line

Restart PostgreSQL server and verify that it is ready:

$ sudo systemctl restart postgresql

$ tail /var/log/postgresql/postgresql-12-main.log
...
2021-08-05 17:50:50.910 UTC [7919] LOG:  database system is ready to accept connections

Verify that we are now able to connect to the PostgreSQL database server using the verify-ca and verify-full SSL modes:

$ psql postgresql://postgres@127.0.0.1:5432/postgres?sslmode=verify-ca
psql (12.7 (Ubuntu 12.7-0ubuntu0.20.04.1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=# exit 


$ psql postgresql://postgres@127.0.0.1:5432/postgres?sslmode=verify-full
psql (12.7 (Ubuntu 12.7-0ubuntu0.20.04.1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=# exit

Verify that without the right “CA” certificate (i.e. the server certificate), the psql client will not be able to verify that the server certificate is signed by a “trusted” entity:

# verify missing CA certificate
$ rm ~/.postgresql/root.crt
$ psql postgresql://postgres@127.0.0.1:5432/postgres?sslmode=verify-ca
psql: error: root certificate file "/home/user/.postgresql/root.crt" does not exist
Either provide the file or change sslmode to disable server certificate verification.

# verify different CA certificate does not work
$ openssl req -new -x509 -days 365 -nodes -text -out server1.crt \
>  -keyout server1.key -subj "/CN=127.0.0.1"
$ cp server1.crt ~/.postgresql/root.crt
$ psql postgresql://postgres@127.0.0.1:5432/postgres?sslmode=verify-ca
psql: error: SSL error: certificate verify failed

Step 4b: Create and Install a CA-Signed Server Certificate for PostgreSQL

This step continues from Step 3 and does not depend on Step 4a.

In this approach, the steps are:

1. For self-managed CA: create the private key and certificate
2. For PostgreSQL Server: create the private key and a Certificate Signing Request (CSR)
3. Create a signed certificate for PostgreSQL server by using CA key and certificate to sign CSR

For this step, I am following the blog post from https://blog.devolutions.net/2020/07/tutorial-how-to-generate-secure-self-signed-server-and-client-certificates-with-openssl as I found it easier to understand than PostgreSQL’s documentation.

First, create the private key and certificate belonging to our self-managed CA:

$ openssl ecparam -name prime256v1 -genkey -noout -out ca.key
$ openssl req -new -x509 -sha256 -key ca.key -out ca.crt -subj "/CN=ca.luppeng.com"

$ ls 
ca.crt  ca.key

Next, create the private key and a Certificate Signing Request (CSR) for our PostgreSQL server:

$ openssl ecparam -name prime256v1 -genkey -noout -out server.key
$ openssl req -new -sha256 -key server.key -out server.csr -subj "/CN=127.0.0.1"

$ ls
ca.crt  ca.key  server.csr  server.key

Now use the self-managed CA private key and certificate to sign the CSR to generate the server certificate:

$ openssl x509 -req -in server.csr -CA ca.crt -CAkey ca.key -CAcreateserial -out server.crt -days 365 -sha256
Signature ok
subject=CN = 127.0.0.1
Getting CA Private Key

$ ls 
ca.crt  ca.key  ca.srl  server.crt  server.csr  server.key

With the signed server certificate, we can install the certificates on the client and server: 1) server.crt and server.key will be used by the PostgreSQL database server, 2) while the CA’s certificate (ca.crt) will be used by the psql client to validate that the server certificate returned by the server was signed by it. The private key of the CA will not be required for this step (and should be kept securely by the CA)

As this is a non-production environment, I decided to locate all my certificates within ~/.postgresql for convenience (recall the error message in Step 3). In a production environment, you would usually put it in /etc/ssl/certs/ and /etc/ssl/private/ (or similar).

$ mkdir ~/.postgresql
$ mv server.crt ~/.postgresql/
$ mv server.key ~/.postgresql/
$ mv ca.crt ~/.postgresql/

As the postgres service account will be accessing the certificate and key, change ownership of server.crt and server.key to the postgres user.

$ chmod og-rwx ~/.postgresql/server.*
$ sudo chown postgres:postgres ~/.postgresql/server.crt ~/.postgresql/server.key

If you do not do this, the database will fail to start with the error:

FATAL:  could not load server certificate file "/home/user/.postgresql/server.crt": Permission denied

Right now, our ~/.postgresql folder should look like this:

$ ls -al ~/.postgresql/
-rw-rw-r-- 1 user     user      591 Aug  6 04:32 ca.crt
-rw------- 1 postgres postgres  461 Aug  6 04:41 server.crt
-rw------- 1 postgres postgres  227 Aug  6 04:36 server.key

Modify ssl_cert_file and ssl_key_file settings in the postgresql.conf file to point the our ~/.postgresql folder:

$ find / -name postgresql.conf 2>/dev/null
/etc/postgresql/12/main/postgresql.conf

$ sudo vi /etc/postgresql/12/main/postgresql.conf
ssl = on                                              # verify this is set to 'on'
ssl_cert_file = '/home/user/.postgresql/server.crt'   # update this line
ssl_key_file = '/home/user/.postgresql/server.key'    # update this line

Restart PostgreSQL server and verify that it is ready:

$ sudo systemctl restart postgresql

$ tail /var/log/postgresql/postgresql-12-main.log
...
2021-08-06 06:14:36.231 UTC [9105] LOG:  database system is ready to accept connections

As per this PostgreSQL documentation, the ~/.postgresql/root.crt file stores a list of trusted certificate authorities that is used to verify that the server certificate is signed by an entity on the list. We have set up the CA certificate as ca.crt, so we’ll copy and rename it to root.crt. (note: it is possible to change the default location for this file)

$ cp ~/.postgresql/ca.crt ~/.postgresql/root.crt

Verify that we are able to connect to the PostgreSQL database server using the verify-ca and verify-full SSL modes:

$ psql postgresql://postgres@127.0.0.1:5432/postgres?sslmode=verify-ca
psql (12.7 (Ubuntu 12.7-0ubuntu0.20.04.1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=# exit


$ psql postgresql://postgres@127.0.0.1:5432/postgres?sslmode=verify-full
psql (12.7 (Ubuntu 12.7-0ubuntu0.20.04.1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=# exit

Verify that without the right CA certificate (which we generated in at the beginning of this step), the psql client will not be able to verify that the server certificate is signed by a trusted entity:

# verify missing CA certificate
$ mv ~/.postgresql/root.crt ~/.postgresql/root.crt.bk
$ psql postgresql://postgres@127.0.0.1:5432/postgres?sslmode=verify-ca
psql: error: root certificate file "/home/user/.postgresql/root.crt" does not exist
Either provide the file or change sslmode to disable server certificate verification.

# verify different CA certificate does not work
$ openssl ecparam -name prime256v1 -genkey -noout -out ca1.key
$ openssl req -new -x509 -sha256 -key ca1.key -out ca1.crt -subj "/CN=ca1.luppeng.com"
$ cp ca1.crt ~/.postgresql/root.crt
$ psql postgresql://postgres@127.0.0.1:5432/postgres?sslmode=verify-ca
psql: error: SSL error: certificate verify failed

To summarize, in this approach we are saying that we trust the CA, and are using its certificate to verify that PostgreSQL server’s certificate was indeed signed by it. If we trust the CA, and the CA trusts the PostgreSQL server, then by associative property we can trust the PostgreSQL server.

Step 5: SSL Certificate for PostgreSQL Client

What we have done so far is to provide a way for the client to validate the PostgreSQL database server’s identity. However, in some scenarios, the PostgreSQL database server should only allow connections from trusted clients. In this section, we see how we can use client certificates to enable the database server to validate the client’s identity.

To understand how this works, we can reference the following PostgreSQL documentations:

This is the requirement for the database server configuration (i.e. postgresql.conf):

To require the client to supply a trusted certificate, place certificates of the root certificate authorities (CAs) you trust in a file in the data directory, set the parameter ssl_ca_file in postgresql.conf to the new file name, and add the authentication option clientcert=verify-ca or clientcert=verify-full to the appropriate hostssl line(s) in pg_hba.conf. A certificate will then be requested from the client during SSL connection startup.

https://www.postgresql.org/docs/current/ssl-tcp.html#SSL-CLIENT-CERTIFICATES

This is the requirement of the pg_hba.conf:

… there is one method-independent authentication option clientcert, which can be specified in any hostssl record. This option can be set to verify-ca or verify-full. 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 or an applicable mapping.

https://www.postgresql.org/docs/current/auth-pg-hba-conf.html

This is the requirement for the psql client:

If the server attempts to verify the identity of the client by requesting the client’s leaf certificate, libpq will send the certificates stored in file ~/.postgresql/postgresql.crt in the user’s home directory. The certificates must chain to the root certificate trusted by the server. A matching private key file ~/.postgresql/postgresql.key must also be present. The private key file must not allow any access to world or group; achieve this by the command chmod 0600 ~/.postgresql/postgresql.key. 

https://www.postgresql.org/docs/current/libpq-ssl.html#LIBPQ-SSL-CLIENTCERT

Let’s begin. If you have not already generated the private key and certificate for the self-managed CA (previously done in Step 4b), you can do so by:

$ openssl ecparam -name prime256v1 -genkey -noout -out ca.key
$ openssl req -new -x509 -sha256 -key ca.key -out ca.crt -subj "/CN=ca.luppeng.com"

Next, create the private key and a Certificate Signing Request (CSR) for our PostgreSQL client:

$ openssl ecparam -name prime256v1 -genkey -noout -out client.key
$ openssl req -new -sha256 -key client.key -out client.csr -subj "/CN=postgres"

## note that the CN in the client cert must be the is the name of your postgresql user (e.g. alice, app_db_user) when using "verify-full" mode. Here, I am using the default postgresql user called "postgres"

Now, use the self-managed CA private key and certificate to sign the client’s CSR and generate the client certificate:

$ openssl x509 -req -in client.csr -CA ca.crt -CAkey ca.key -CAcreateserial -out client.crt -days 365 -sha256
Signature ok
subject=CN = postgres
Getting CA Private Key

$ ls 
ca.crt  ca.key  ca.srl  client.crt  client.csr  client.key

As per PostgreSQL documentation above, the client certificates should be stored at:

• Client certificate at ~/.postgresql/postgresql.crt
• Client private key at ~/.postgresql/postgresql.key

So let’s do that:

$ mkdir ~/.postgresql
$ cp client.crt ~/.postgresql/postgresql.crt
$ cp client.key ~/.postgresql/postgresql.key
$ chmod 0600 ~/.postgresql/postgresql.crt ~/.postgresql/postgresql.key

The PostgreSQL server would need the CA’s certificate to validate that it was indeed signed by the CA (and can be trusted). As the postgres service account will be accessing the certificate and key, change ownership of ca.crt to the postgres user:

$ cp ca.crt ~/.postgresql/ca.crt
$ sudo chown postgres:postgres ~/.postgresql/ca.crt

$ ls -al ~/.postgresql
-rw-rw-r-- 1 postgres postgres  591 Aug  6 19:15 ca.crt
-rw------- 1 user     user     4135 Aug  6 19:15 postgresql.crt
-rw------- 1 user     user     1704 Aug  6 19:15 postgresql.key

Modify ssl_ca_file setting in the postgresql.conf file to point to ~/.postgresql/ca.crt folder:

$ find / -name postgresql.conf 2>/dev/null
/etc/postgresql/12/main/postgresql.conf

$ sudo vi /etc/postgresql/12/main/postgresql.conf
ssl = on                                         # verify this is set to 'on'
ssl_ca_file = '/home/user/.postgresql/ca.crt'    # update this line

Modify pg_hba.conf to use clientcert as the auth-option:

$ sudo vi /etc/postgresql/12/main/pg_hba.conf
hostssl    all    all    127.0.0.1/32    cert clientcert=verify-full    # I modified this line 

Restart PostgreSQL server and verify that it is ready:

$ sudo systemctl restart postgresql

$ tail /var/log/postgresql/postgresql-12-main.log
...
2021-08-06 15:07:35.469 UTC [11227] LOG:  database system is ready to accept connections

Verify that we are able to connect to the Postgresql server using the client certificate and private key. Here, I do not assume that the server has certificates and that the CA used to sign them is the same as the client, so the highest SSL mode we will go is “require” to establish encrypted communication channel only. If you have set things up correctly from previous steps, you can try the SSL modes that perform authentication.

# Verify that encrypted connection with client certificate works 
# (PostgreSQL server could use CA certificate to verify that we are a trusted client)
$ psql postgresql://postgres@127.0.0.1:5432/postgres?sslmode=require
psql (12.7 (Ubuntu 12.7-0ubuntu0.20.04.1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=# exit

Verify that missing/invalid client certificates will result in failure to connect:

# Verify that connection fails when client certificate is missing 
# (enforced by "cert clientcert=verify-full" part of pg_hba.conf entry)
$ mv ~/.postgresql/postgresql.crt ~/.postgresql/postgresql.crt.bk
$ mv ~/.postgresql/postgresql.key ~/.postgresql/postgresql.key.bk
$ psql postgresql://postgres@127.0.0.1:5432/postgres?sslmode=require
psql: error: FATAL:  connection requires a valid client certificate


# Verify that unencrypted connections are not allowed 
#(enforced by "hostssl" part of pg_hba.conf entry) 
$ psql postgresql://postgres@127.0.0.1:5432/postgres?sslmode=disable
psql: error: FATAL:  no pg_hba.conf entry for host "127.0.0.1", user "postgres", database "postgres", SSL off


# Verify that connection fails when we use an untrusted certificate (i.e. not signed by CA)
# reused command from self-signed certificate to generate a certificate not signed by CA
$ openssl req -new -x509 -days 365 -nodes -text -out client-untrusted.crt \
>  -keyout client-untrusted.key -subj "/CN=postgres"
$ cp client-untrusted.crt ~/.postgresql/postgresql.crt
$ cp client-untrusted.key ~/.postgresql/postgresql.key
$ psql postgresql://postgres@127.0.0.1:5432/postgres?sslmode=require
psql: error: SSL error: tlsv1 alert unknown ca

Summary

To secure your database connection, PostgreSQL has six different SSL modes – disable, allow, prefer, require, verify-ca, and verify-full. The first four modes are for encryption and supported out of the box (pre-requisite: OpenSSL library installed) by PostgreSQL, while the last two modes need some setting up and configuration of certificates and private keys.

To verify a PostgreSQL server’s identity, we can install either a self-signed or CA-signed certificate and private key on the server. The client would then use a copy of the CA certificate (or the self-signed cert itself) to verify that the server can be trusted.

To verify a PostgreSQL client’s identity, we can use a CA-signed certificate and private key when establishing the connection. The server would then use a copy of the CA certificate to verify that the client can be trusted.

It is possible to perform both client and server certificate validation for the highest level of encryption and authentication. However, there will be performance overheads from the encryption and certificate validation process – one needs to carefully analyze the tradeoff before selecting that route.

Hope the above guide is helpful to understand how the PostgreSQL server and client can improve their security via SSL encryption and authentication. Thanks for reading and appreciate any support you can give! 🙂