PostgreSQL® Native Server SSL setup

This tutorial describes how to set up a PostgreSQL® native server on a Raspberry Pi. It is not exhaustive, for a production-ready setup follow the PostgreSQL Trust Authentication documentation.

Setup PostgreSQL® native server

sudo apt update
sudo apt install postgresql

Switch to the postgres user, enter the postgres CLI, and change the postgres DB default password:

sudo su postgres
psql
ALTER USER postgres WITH PASSWORD '<password>';

Setup SSL server certificates

For SSL to work with PostgreSQL we need to generate three certificate files:

• server.key: the private key file
• server.crt: the server certificate file
• root.crt: the trusted root certificate

Find the server's data directory with:

sudo su postgres
psql
SHOW data_directory;

From the data directory, we can now generate the key pair that will be used by the postgres server. Move to the data directory and create the request configuration file named server.cnf following this template:

[req]
default_bits = 4096
default_md = sha256
distinguished_name = req_distinguished_name
x509_extensions = v3_req
prompt = no
[req_distinguished_name]
C = IT
ST = FVG
L = Amaro
O = MyCompany
OU = MyDivision
CN = <ip-address>
[v3_req]
keyUsage = keyEncipherment, dataEncipherment
extendedKeyUsage = serverAuth
subjectAltName = @alt_names
[alt_names]
IP.1 = <ip-address>

The certificate and private key can be generated using the configuration file with the following command:

openssl req -new -nodes -x509 -days 30 -keyout server.key -out server.crt -config server.cnf

It is possible to inspect the generated certificate:

openssl x509 -in server.crt -noout -text

Allow read access and assing ownership of the private key to the postgres user:

chmod 400 server.key
chown postgres.postgres server.key

Since we are using a self-signed certificate, we will use it as the trusted root certificate by making a copy of the server certificate:

cp server.crt root.ca

The server.crt can be imported into Kura/ESF as a trusted certificate.

Setup PostgreSQL® server to use SSL

Edit the postgres configuration file. It can be retrieved with:

sudo su postgres
psql
SHOW config_file;

Edit the Connection Settings section as follows:

listen_addresses = '*'

Edit the SSL section as follows:

ssl = on
ssl_ca_file = 'root.ca'
ssl_cert_file = 'server.crt'
ssl_crl_file = ''
ssl_key_file = 'server.key'
ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL' # allowed SSL ciphers
ssl_prefer_server_ciphers = on

It is possible to configure the host based authentication by editing the pg_hba.conf file. It can be retrieved with:

sudo su postgres
psql
SHOW hba_file;

Edit such file to allow connections from all hosts under the IPv4 local connections section:

# IPv4 local connections:
host    all             all             0.0.0.0/0               md5
hostssl all             all             0.0.0.0/0               md5

Restart the database:

systemctl restart postgresql

Check that the server is accepting SSL connections:

psql -h localhost -U postgres