From 096db2eafc2452368d6a392d7d3772462e84070b Mon Sep 17 00:00:00 2001
From: Jakub Czajka <jakub@ekhem.eu.org>
Date: Sun, 29 Jan 2023 14:20:35 +0100
Subject: [PATCH] [postgres] Enable remote connections with certificates.

Client can authenticate with certificates generated by a new
certificate authority.
---
 postgres/README          | 68 ++++++++++++++++++++++++++++++++++++++++
 postgres/pg_hba.conf     |  1 +
 postgres/postgresql.conf |  8 ++---
 3 files changed, 73 insertions(+), 4 deletions(-)

diff --git a/postgres/README b/postgres/README
index 05b88ca..ada20c2 100644
--- a/postgres/README
+++ b/postgres/README
@@ -1,6 +1,8 @@
 postgres
 ========
 
+Relational database.
+
 Files
 -----
 
@@ -9,6 +11,72 @@ postgres
 |-> pg_hba.conf     -- /etc/postgresql/$(version)/main/
 `-> postgresql.conf -- /etc/postgresql/$(version)/main/
 
+DNS
+---
+
+Set up <domain> and psql.<domain>.
+
+<domain>      A     <IPv4 address>
+<domain>      AAAA  <IPv6 address>
+psql.<domain> CNAME <domain>
+
+SSL
+---
+
+Postgres uses both server- and client-side certificates. Whenever a client tries
+to connect, verifies server's certificate and then presents their certificate.
+
+SSL (server)
+------------
+
+Obtain a certificate with `certbot`.
+
+```
+$ certbot certonly --standalone -d psql.<domain>
+```
+
+Copy the certificate to a directory owned by `postgres`. Change its ownership and
+permissions.
+
+```
+$ mkdir /etc/postgresql/<version>/main/private
+$ chmod 700 /etc/postgresql/<version>/main/private
+$ cp /etc/letsencrypt/live/psql.<domain>/{fullchain.pem,privkey.pem} \
+  /etc/postgresql/<version>/main/private/
+$ chmod 600 /etc/postgresql/<version>/main/private/{fullchain.pem,privkey.pem}
+$ chown postgres:postgres -R /etc/postgresql/<version>/main/private
+```
+
+SSL (client)
+------------
+
+Obtain a certificate with `openssl`. <user> must contain the name of a postgres
+account for which the certificate is issued.
+
+```
+openssl req -newkey rsa:4096 -x509 -sha512 \
+  -days 365 -nodes -out <out>.crt -keyout <out>.key \
+  -subj "/C=<country>/ST=<state>/L=<location>/O=<organization>/CN=<user>"
+```
+
+Append the certificate to the list of valid certificates.
+
+```
+$ mkdir /etc/postgresql/<version>/main/private
+$ chmod 700 /etc/postgresql/<version>/main/private
+$ cp psql.crt /etc/postgresql/<version>/main/private/
+$ chmod 600 /etc/postgresql/<version>/main/private/psql.crt
+$ chown postgres:postgres /etc/postgresql/<version>/main/private/psql.crt
+```
+
+Present the certificate when connecting.
+
+```
+psql "host=psql.<domain> sslcert=<out>.crt sslkey=<out>.key user=<user> \
+  dbname=<db_name> sslrootcert=/etc/ssl/certs/ca-certificates.crt \
+  sslmode=verify-full"
+```
+
 Install
 -------
 
diff --git a/postgres/pg_hba.conf b/postgres/pg_hba.conf
index 81e5670..1b94e9c 100644
--- a/postgres/pg_hba.conf
+++ b/postgres/pg_hba.conf
@@ -105,3 +105,4 @@ host    all             all             ::1/128                 md5
 local   replication     all                                     peer
 host    replication     all             127.0.0.1/32            md5
 host    replication     all             ::1/128                 md5
+hostssl all             postgres        0.0.0.0/0               cert
diff --git a/postgres/postgresql.conf b/postgres/postgresql.conf
index e8adb82..bfe0d76 100644
--- a/postgres/postgresql.conf
+++ b/postgres/postgresql.conf
@@ -57,7 +57,7 @@ external_pid_file = '/var/run/postgresql/13-main.pid'			# write an extra PID fil
 
 # - Connection Settings -
 
-#listen_addresses = 'localhost'		# what IP address(es) to listen on;
+listen_addresses = '*'                  # what IP address(es) to listen on;
 					# comma-separated list of addresses;
 					# defaults to 'localhost'; use '*' for all
 					# (change requires restart)
@@ -99,10 +99,10 @@ unix_socket_directories = '/var/run/postgresql'	# comma-separated list of direct
 # - SSL -
 
 ssl = on
-#ssl_ca_file = ''
-ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem'
+ssl_ca_file = '/etc/postgresql/13/main/private/psql.crt'
+ssl_cert_file = '/etc/postgresql/13/main/private/fullchain.pem'
 #ssl_crl_file = ''
-ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key'
+ssl_key_file = '/etc/postgresql/13/main/private/privkey.pem'
 #ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL' # allowed SSL ciphers
 #ssl_prefer_server_ciphers = on
 #ssl_ecdh_curve = 'prime256v1'
-- 
2.39.5