Connect to PostgreSQL over SSL/TLS on Debian/Ubuntu

Published on Jun. 7, 2018 by Gabriel Bordeaux

Introduction

This is a simple introduction on how-to connect to PostgreSQL from a remote server using a self signed SSL certificate.

Prerequisite

PostgreSQL is installed with the following commands:

echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list
apt-get --yes install wget ca-certificates
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -
apt-get update
apt-get --yes install postgresql-10 openssl

Creating the certificates

Let's create the server certificate first:

mkdir ~/cert && cd ~/cert

openssl req -new -nodes -text -out ca.csr -keyout ca-key.pem -subj "/CN=certificate-authority"

openssl x509 -req -in ca.csr -text -extfile /etc/ssl/openssl.cnf -extensions v3_ca -signkey ca-key.pem -out ca-cert.pem

openssl req -new -nodes -text -out server.csr -keyout server-key.pem -subj "/CN=pg-server"

openssl x509 -req -in server.csr -text -CA ca-cert.pem -CAkey ca-key.pem -CAcreateserial -out server-cert.pem

Now, let's create the client key and certificate:

openssl req -new -nodes -text -out client.csr -keyout client-key.pem -subj "/CN=pg-client"

openssl x509 -req -in client.csr -text -CA ca-cert.pem -CAkey ca-key.pem -CAcreateserial -out client-cert.pem

At this point you should have the following files in the directory:

$ ls ~/cert
ca-cert.pem  ca-cert.srl  ca.csr  ca-key.pem  client-cert.pem  client.csr  client-key.pem  server-cert.pem  server.csr	server-key.pem

Let's move the files to their final destination:

mkdir -p /etc/ssl/postgresql/
cp ca-cert.pem server-cert.pem server-key.pem /etc/ssl/postgresql/
chmod -R 700 /etc/ssl/postgresql
chown -R postgres.postgres /etc/ssl/postgresql

Edit PostgreSQL configuration

Open PostgreSQL's configuration file:

nano /etc/postgresql/10/main/postgresql.conf

Search and replace the following:

# Make sure the server listen to it's own IP to allow remote connections
# Replace `SERVER_PUBLIC_IP` with the server public IP address
listen_addresses = 'localhost,SERVER_PUBLIC_IP'

# SSL should already be `on`, switch it to `on` if its not the case
ssl = on # <-- This should be "on" by default

# Set SSL certificate
ssl_cert_file = '/etc/ssl/postgresql/server-cert.pem'
ssl_key_file = '/etc/ssl/postgresql/server-key.pem'
ssl_ca_file = '/etc/ssl/postgresql/ca-cert.pem'

Let's now edit pg_hba.conf to force SSL for our remote user:

nano /etc/postgresql/10/main/pg_hba.conf

Locate the IPv4 connections and add a new one:

# IPv4 local connections:
host    all             all             127.0.0.1/32            md5                # <-- existing line
hostssl all             remote_user     [CLIENT_IP_ADDRESS]/32  md5 clientcert=1   # <-- new line

Restart PostgreSQL:

/etc/init.d/postgresql restart

You can now check PostgreSQL's logs:

$ tail /var/log/postgresql/postgresql-10-main.log 
# 2018-05-23 16:08:14.081 UTC [8689] LOG:  listening on IPv4 address "127.0.0.1", port 5432
# 2018-05-23 16:08:14.082 UTC [8689] LOG:  listening on IPv4 address "123.456.78.9", port 5432
# 2018-05-23 16:08:14.083 UTC [8689] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
# 2018-05-23 16:08:14.099 UTC [8690] LOG:  database system was shut down at 2018-05-23 16:08:13 UTC
# 2018-05-23 16:08:14.106 UTC [8689] LOG:  database system is ready to accept connections
# 2018-05-23 16:08:14.664 UTC [8697] [unknown]@[unknown] LOG:  incomplete startup packet

Creating the remote user

If you don't have one already, you can now create the user remote_user:

su postgres
psql

-- Connect to your database
\c my_database
-- You are now connected to database "my_database" as user "postgres".

-- Creating user `remote_user`. If you want to choose a different name, make sure it matches the name in `pg_hba.conf`
CREATE USER remote_user PASSWORD 'my_password' LOGIN;
GRANT ALL PRIVILEGES ON ALL TABLES IN schema public to remote_user;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN schema public to remote_user

Configure the client

Let's install PostgreSQL's client if it's not already there:

echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list
apt-get --yes install wget ca-certificates
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -
apt-get update
apt-get --yes install postgresql-client-10 openssl

Let's move the certificates:

mkdir ~/.postgresql/ && cd ~/.postgresql/

# Copy the following files: client-cert.pem, client-key.pem and ca-cert.pem
scp root@REMOTE_SERVER_IP:~/cert/{client-cert.pem,client-key.pem,ca-cert.pem} ./

# To simplify the connection, let's use the default names
cp ca-cert.pem root.crt
cp client-cert.pem postgresql.crt
cp client-key.pem postgresql.key

# Set permissions
chmod -R 600 ./

# Test the certificate
openssl verify -CAfile root.crt postgresql.crt

You can now connect to the remote server:

PGPASSWORD="my_password" psql -h [SERVER_PUBLIC_IP] -U remote_user my_database

psql (10.4 (Debian 10.4-2.pgdg90+1))
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

my_database=> \d
Did not find any relations.
my_database=>