Enable ssl connections on postgres 12 on ubuntu 18.04

postgres_ssl.md

Configure Server for SSL connections

Create certs

https://www.postgresql.org/docs/current/ssl-tcp.html

#!/bin/bash

mkdir ~/temp
cd ~/temp

openssl req -new -x509 -days 3650 -nodes -text -out server.crt \
  -keyout server.key -subj "/CN=dbhost.jacobochoa.me"
chmod og-rwx server.key

openssl req -new -nodes -text -out root.csr \
  -keyout root.key -subj "/CN=root.jacobochoa.me"
chmod og-rwx root.key

openssl x509 -req -in root.csr -text -days 3650 \
  -extfile /etc/ssl/openssl.cnf -extensions v3_ca \
  -signkey root.key -out root.crt

openssl req -new -nodes -text -out server.csr \
  -keyout server.key -subj "/CN=dbhost.jacobochoa.me"
chmod og-rwx server.key

openssl x509 -req -in server.csr -text -days 365 \
  -CA root.crt -CAkey root.key -CAcreateserial \
  -out server.crt

sudo mkdir /etc/postgresql/12/main/ssl
sudo cp ./* /etc/postgresql/12/main/ssl
sudo chown -R postgres.postgres /etc/postgresql/12/main/ssl

ls -la /etc/postgresql/12/main/ssl/

output should look like this

-rw-r--r-- 1 postgres postgres 2881 Feb 14 10:13 root.crt
-rw-r--r-- 1 postgres postgres 3343 Feb 14 10:13 root.csr
-rw------- 1 postgres postgres 1708 Feb 14 10:13 root.key
-rw-r--r-- 1 postgres postgres   41 Feb 14 10:13 root.srl
-rw-r--r-- 1 postgres postgres 2763 Feb 14 10:13 server.crt
-rw-r--r-- 1 postgres postgres 3349 Feb 14 10:13 server.csr
-rw------- 1 postgres postgres 1704 Feb 14 10:13 server.key

all we really care about imidiately are 3 files.

• root.crt: certificate CA
• server.crt: postgres connection public key
• server.key: postgres connection private key

add the files to the postgres config.

sudo nano /etc/postgresql/12/main/postgresql.conf

ensure the following parameters are is configured.

listen_addresses = '*'  
ssl = on
ssl_ca_file = '/etc/postgresql/12/main/ssl/root.crt'
ssl_cert_file = '/etc/postgresql/12/main/ssl/server.crt'
ssl_key_file = '/etc/postgresql/12/main/ssl/server.key'

sudo nano /etc/postgresql/12/main/pg_hba.conf

add the following line.

hostssl all             all             0.0.0.0/0               md5 clientcert=1

here is what mine looks like

# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
#lcaal   all             all                                     md5

# IPv4 local connections:
#host    all             all             127.0.0.1/32            md5
host    all             all             0.0.0.0/0               md5
hostssl all             all             0.0.0.0/0               md5 clientcert=1
# IPv6 local connections:
host    all             all             ::1/128                 md5
#host    all             all             ::/0                    md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5

restart the server and check the logs

sudo systemctl restart postgresql
tail /var/log/postgresql/postgresql-12-main.log

log output should look something like this.

2020-02-14 10:30:55.217 UTC [20049] LOG:  shutting down
2020-02-14 10:30:55.232 UTC [20047] LOG:  database system is shut down
2020-02-14 10:30:55.387 UTC [20259] LOG:  starting PostgreSQL 12.2 (Ubuntu 12.2-1.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.4.0-1ubuntu1~18.04.1) 7.4.0, 64-bit
2020-02-14 10:30:55.387 UTC [20259] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2020-02-14 10:30:55.387 UTC [20259] LOG:  listening on IPv6 address "::", port 5432
2020-02-14 10:30:55.390 UTC [20259] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2020-02-14 10:30:55.407 UTC [20260] LOG:  database system was shut down at 2020-02-14 10:30:55 UTC
2020-02-14 10:30:55.413 UTC [20259] LOG:  database system is ready to accept connections # <---- everyhting is working!

Create User

sudo -u postgres -s
psql

CREATE DATABASE <username>;
CREATE USER <username> WITH PASSWORD '<password>';
GRANT ALL PRIVILEGES ON <username> TO <username>;
\du
\q

test user login. you will be propted for password.

psql -U <username> -w

Connect

copy the root.crt file that was generated at the begining of this document. we will use it to verifiy our database server is who it says it is.

https://www.postgresql.org/docs/current/libpq-connect.html

in the connection string set the sslrootcert to the path of the root.crt and sslmode to verify-full.

certbot

sudo certbot renew --dry-run

# move certs
sudo cp -rL /etc/letsencrypt/live/api.jacobochoa.me/ /etc/postgresql/12/main/ssl/
sudo chown -R postgres:postgres /etc/postgresql/12/main/ssl/api.jacobochoa.me
sudo chmod -R 755 /etc/postgresql/12/main/ssl/api.jacobochoa.me
sudo chmod -R 600 /etc/postgresql/12/main/ssl/api.jacobochoa.me/privkey.pem 
sudo service postgresql restart

test

netstat -nlt
tail /var/log/postgresql/postgresql-12-main.log