Enable SSL in Postgresql

Enabling SSL in PostgreSQL is a straightforward process that only requires three simple steps:

• Make sure we have the server certificate and key files available
• Enable the SSL configuration (ssl = on)
• Make sure the pg_hba.conf file rules are updated accordingly

Obtain server certificate and key files for Postgres

On PostgreSQL server, we need 3 certificates in data directory for SSL configuration. They are:

• root.crt (trusted root certificate)
• server.crt (server certificate)
• server.key (private key)

In this article, we will focus on one-way TLS setup. The root.crt is not needed here.

One-way TLS (Transport Layer Security) in PostgreSQL is a method of securing network communication between a client and a PostgreSQL server.

In one-way TLS, only the server is authenticated and its identity is verified by the client.

The server sends its SSL certificate to the client, which the client verifies using the trusted Certificate Authority (CA) certificate installed on its system.

In this setup, the client doesn’t send its own SSL certificate to the server for mutual authentication.

This means that the client’s identity is not verified by the server, and the server does not require the client to have an SSL certificate.

One-way TLS is commonly used in situations where the client is not required to have a trusted identity, such as public websites or non-sensitive internal applications.

Let’s take a closer look at SSL certificates.

SSL certificates are used to encrypt the communication between a client and a server. They also provide authentication and integrity for the data exchanged.

To obtain a server certificate and key files, you can either generate a self-signed certificate or obtain a trusted certificate from a certificate authority (CA).

A self-signed certificate is a certificate that is signed by the same entity that issued it, while a trusted certificate is issued by a third-party CA that is recognized by most web browsers and operating systems.

To generate SSL certificates for the Postgres database server, you need to follow these steps:

1. Create a private key and a certificate signing request (CSR) for the server using openssl command. For example: openssl req -new -nodes -keyout server.key -out server.csr

2. Send the CSR to a trusted certificate authority (CA) and obtain a signed certificate for the server. Alternatively, you can use a self-signed certificate, but this is not recommended for production environments.

3. Copy the server certificate, private key and the CA certificate (if any) to the Postgres data directory, usually /var/lib/postgresql/data.

Let’s get into details.

openssl req -new -newkey rsa:2048 -nodes -keyout server.key -out server.csr

This command generates a new SSL certificate signing request (CSR) and a private key for an SSL/TLS server.

Here’s what each option in the command does:

req: specifies that this command is for generating a certificate signing request

-new: tells OpenSSL to generate a new CSR

-nodes: specifies that the private key should not be encrypted with a passphrase

-keyout server.key: specifies the output file where the private key should be stored. In this case, it is named server.key

-out server.csr: specifies the output file where the certificate signing request should be stored. In this case, it is named server.csr

Our system should launch a text-based questionnaire for us to fill out. What you are about to enter is what is called a Distinguished Name or a DN.

There are quite a few fields but you can leave some blank.

Enter our information in the fields as follows:

We can also use one command to skip the interactive input. Here is one example:

openssl req -out server.csr -new -newkey rsa:2048 -nodes -keyout server.key -subj "/C=US/ST=Florida/L=Saint Petersburg/O=Your Company, Inc./OU=IT/CN=yourdomain.com"

CSR file is a common plain-text file. We can use more or cat commands in Linux to check the content of the CSR file.

After obtaining the CSR (Certificate Signing Request) file, the next step is to send it to a trusted Certificate Authority (CA) to obtain an SSL certificate.

The CA will validate the information in the CSR and issue an SSL certificate that is signed.

Once the SSL certificate is obtained, it needs to be installed on the server along with the private key and any intermediate certificates.

You might have concerns about intermediate certificates. In many cases, we need intermediate certificate.

root certificate authorities do not issue SSL/TLS certificates directly to owners. Instead, they delegate this responsibility to intermediate CAs that they have authorized to issue certificates on their behalf.

The intermediate CA’s SSL/TLS certificate is included in the chain of trust between the root certificate and the end-user certificate, allowing the client to verify the SSL/TLS certificate all the way back to the trusted root.

In our case, server.crt and intermediate.crt should be concatenated into a certificate file bundle and stored on the server.

we can create the correct file for the chain using the following command:

$ cat server.pem intermediate.pem > server.crt

It is required to put the server certificate file first, and then the intermediate certificate file(s).

Enable SSL configuration by setting ssl = on

Enabling SSL configuration in PostgreSQL is the next step after acquiring the server certificate and key files.

There are two methods to achieve this. One of the ways is to set ssl = on in the configuration file and restart PostgreSQL.

The PostgreSQL configuration file is typically named postgresql.conf and is located in the data directory of the PostgreSQL installation.

You can find the location of the data directory by running the following command as the PostgreSQL user:

SHOW data_directory;

Then you can access the postgresql.conf file using a text editor, such as nano or vim.

For example, if the data directory is located at /var/lib/postgresql/13/main, you can open the configuration file with the following command:

sudo nano /var/lib/postgresql/13/main/postgresql.conf

Note that the actual location of the configuration file may vary depending on your installation and operating system.

Locate the line that contains ssl = off and change it to ssl = on.

Save the changes to the file and close the text editor.

Restart the PostgreSQL server to apply the changes. This can usually be done using the command sudo service postgresql restart on Linux systems or by using the Services application on Windows systems.

The default names for these files are server.crt and server.key, but you can change them using the ssl_cert_file and ssl_key_file parameters in postgresql.conf.

Here is one example of this:

ssl = on
ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL'
ssl_ca_file = '/pgarchive/ssl/root.crt'
ssl_cert_file = '/pgarchive/ssl/server.crt'
ssl_key_file = '/pgarchive/ssl/server.key'
ssl_min_protocol_version = 'TLSv1.2'

Or you can enable this using alter command and reload the configuration in psql.

In PostgreSQL, ALTER SYSTEM is a command that modifies the configuration file of the database system. The SET option is used to modify the configuration parameter.

The pg_reload_conf function is used to reload the server configuration files without stopping and starting the PostgreSQL server process.

This can be useful when you need to make configuration changes on a running system without interrupting the service.

Here are the steps:

Open psql by typing psql in your terminal.

Connect to the database by typing \c <database_name> and then entering your credentials.

Enter the following command to enable SSL:

ALTER SYSTEM SET ssl = 'on';

Reload the configuration by entering the following command:

SELECT pg_reload_conf();

Verify that SSL is enabled by checking the output of the following command:

SHOW ssl;

If SSL is enabled, the output should be on.

However, it is important to note that existing client connections will not be affected by this change, and they will still be able to connect without SSL until their connection is terminated and they reconnect with SSL enabled.

Enforce SSL connections for PostgreSQL clients

To update the pg_hba.conf file to require SSL connections, follow these steps:

Open the pg_hba.conf file, which is typically located in the data directory of your PostgreSQL installation.

Locate the lines that specify the connection type and authentication method for each host or network. These lines will begin with host or hostssl for TCP/IP connections, or local for Unix domain socket connections.

For each line that specifies trust as the authentication method, change it to hostssl or host (if you want to allow non-SSL connections as well). For example, change:

host all all 127.0.0.1/32 md5

to:

hostssl all all 127.0.0.1/32 md5

Save the pg_hba.conf file.

Restart the PostgreSQL server or reload pg config with select pg_reload_conf(); to apply the changes.

Connect Postgresl server using SSL

Once SSL is enabled on the server, you can connect to it using SSL from psql by specifying the sslmode parameter.

For example, to connect to a database called “mydb” on a server with hostname “myserver.com” using SSL, you can run the following command:

psql "sslmode=require host=myserver.com dbname=mydb user=myuser"

When sslmode=require is used, it means that the client will require the server to have SSL encryption enabled. If the server does not support SSL, the client will not be able to connect.

This will require an SSL connection to be established before connecting to the server.

Here is one example.

psql (13.10)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.
postgres#

We can also use the following sql to get all the connection status.

# SELECT ssl.pid, usename, datname, ssl, ssl.version, ssl.cipher, ssl.bits, client_addr FROM pg_catalog.pg_stat_ssl ssl, pg_catalog.pg_stat_activity activity WHERE ssl.pid = activity.pid;
pid | usename | datname | ssl | version | cipher | bits | client_addr
---------+----------+---------+-----+---------+------------------------+------+-----------------
235628 | postgres | postgres | t | TLSv1.3 | TLS_AES_256_GCM_SHA384 | 256 | 127.0.0.1

sslmode=verify-ca is also a secure mode. With this mode, the client will still require the server to have SSL encryption enabled.
But it will also verify the server’s SSL certificate against a trusted Certificate Authority (CA). This helps ensure that the client is not connecting to an imposter server.

psql "port=5432 host=localhost user=postgres sslrootcert=/pgarchive/ssl/root.cer sslmode=verify-ca"
Password for user postgres:
psql (13.10)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=

When sslmode=verify-full is used, the client will perform the following steps to verify the SSL certificate:

• Verify that the certificate is signed by a trusted certificate authority (CA) by checking the certificate chain up to the root CA.
• Check that the certificate common name (CN) matches the server hostname.
• Verify that the certificate has not expired.

$ psql "port=5432 host=localhost user=postgres sslrootcert=/pgarchive/ssl/root.cer sslmode=verify-full"
psql: error: server certificate for "howtouselinux.com" does not match host name "localhost"

Here is a good example.

$ psql "port=5432 host=howtouselinux.com user=postgres sslrootcert=/pgarchive/ssl/root.cer sslmode=verify-full"
Password for user postgres:
psql (13.10)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=#