As I’m building out my homelab I want to make sure everything is running securely with HTTPS and ssl everywhere. If I can run everything production like it will make it much easier to graduate any services to the public later much easier. Although I have no plans on making my PostgreSQL instance public, it will be running on my local network which has a lot of different devices. It seems prudent to ensure all connections to the PostgreSQL service is encrypted.

PostgreSQL allows you configure SSL certificates for the service and that all connections need to be using SSL to be allowed to connect and authenticate. We could use a certificate generated from a certificate authority like Verisign and download that to our server. This is relatively simple, but(!) I know I will forget to renew this and things will fall appart a year later. So I’d prefer to have something in place that automatically rewnews and updated the server.

Luckily there is a service that allows you to generate short lived certificates that also has a lot of great support and tooling available, Let’s Encrypt.

Let’s Encrypt generate valid and trusted certificates for your domains, but you have to prove that you own the domain. This is called a challenge. There are two main types of challenges, HTTP-01 and DNS-01. HTTP-01 means the Let’s Encrypt service will connect to the domain on port 80 on a known URI and verify that the response matches the expected token. The DNS-01 challenge uses a TXT dns entry on the domain to prove ownership. More details on the challenge types can be found in the docs for Let’s Encrypt.

Since we are not generating a certificate for a web service, and we are not making our service public, we can’t (at least not easily) use the HTTP-01 challenge.

This means we need a tool that will request a certificate from Let’s Encrypt, create a temporary DNS entry, and download the certificate locally to our machine, and update our PostgreSQL service.

LEGO

Enter LEGO, a Let’s Encrypt client written in GO with built in support for DNS challenges with wide range of supported DNS providers. It’s a single binary with no dependencies, so installing it is as easy as grabbing the latest binary from the github release page and move it to a folder on the path, like /usr/local/bin.

I have a install script that will download the latest version matching your linux architecture and install it in a known location.

The rest of the provided scripts assumes the lego binary is stored in /opt/lego/

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

#!/bin/sh
set -e


# Downloads latest linux binary matching the system architecture and writes it
# to /opt/lego/lego then sets up a symlink to /usr/local/bin/lego for access
# on the path
mkdir -p /opt/lego

LATEST=$(curl -i https://github.com/go-acme/lego/releases/latest | grep location: | cut -d '/' -f 8 | tr -d '\r'); ARCH=$(uname -m); case $ARCH in armv5*) ARCH="armv5";; armv6*) ARCH="armv6";; armv7*) ARCH="arm";; aarch64) ARCH="arm64";; x86) ARCH="386";; x86_64) ARCH="amd64";;  i686) ARCH="386";; i386) ARCH="386";; esac; curl -L https://github.com/go-acme/lego/releases/download/${LATEST}/lego_${LATEST}_linux_${ARCH}.tar.gz | tar -xz && mv lego /opt/lego &&

ln /opt/lego/lego /usr/local/bin/lego

Once Lego is installed, validate by checking the version

1
2

/opt/lego# lego -v
lego version 4.19.2 linux/amd64

Getting the initial certificate

Lego has split the initial generation and renew operations in the cli, which works really well as you generally want to do a few setup steps as part of the first generation.

In my case I use AWS Route53 to to manage my dns, so I use the built in route53 provider for DNS-01 challenge.

The following script will run the initial generation of the certfificate, download it to the local cache folder and set up a symlink to a known location for PostgreSQL to load the certificates. Then use systemctl to tell PostgreSQL to reload the config and certificates.

Having hard coded credentials in the script file is not great, but for explaining purposes it makes it easy to understand what’s going on. Don’t do this in your environment.

Save this shell script as /opt/lego/lego-run.sh and update it with your details before you run it.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

#!/bin/sh
set -e

export AWS_ACCESS_KEY_ID=xxx
export AWS_SECRET_KEY=xxx
export AWS_REGION=us-east-1
export AWS_HOSTED_ZONE_ID=z123
DOMAIN=psql.example.com
EMAIL="tom@example.com"

/opt/lego/lego --path /opt/lego/.lego --domains ${DOMAIN} --dns route53 --email ${EMAIL} --accept-tos=true run

# setup certificate symlinks
mkdir -p /etc/postgresql/tls
ln /opt/lego/.lego/certificates/${DOMAIN}.crt /etc/postgresql/tls/server.crt
ln /opt/lego/.lego/certificates/${DOMAIN}.key /etc/postgresql/tls/server.key

# reload config
systemctl reload postgresql

Renewing the certificate

Renewing the certificate is similar to generating a new certificate, other than we don’t have to set up new symlinks, as it will overwrite the files in the local cache directory. We can skip that step and go stright to reloading the configurtion.

Save the following script as /opt/lego/lego-renew.sh and update it with your details.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

#!/bin/sh
set -e

export AWS_ACCESS_KEY_ID=xxx
export AWS_SECRET_KEY=xxx
export AWS_REGION=us-east-1
export AWS_HOSTED_ZONE_ID=Z123
DOMAIN=psql.example.com
EMAIL="tom@example.com"

/opt/lego/lego --path /opt/lego/.lego --domains ${DOMAIN} --dns route53 --email ${EMAIL} --accept-tos=true renew

# reload certs for postgres
systemctl reload postgresql

Configure PostgreSQL

At this stage you should have two scripts /opt/lego/lego-run.sh and /opt/lego/lego-renew.sh, and two certificate files /etc/postgresql/tls/server.crt and /etc/postgresql/tls/server.key

Last thing we need to do before we can test our setup, is to update the PostgreSQL configuration to use the newly generated certificates.

My configuration file can be found at /etc/postgresql/17/main/postgresql.conf as I’m running version 17 on debian. Open it in your favorite editor and then you need to update two keys with the following values:

1
2

ssl_cert_file = '/etc/postgresql/tls/server.crt'
ssl_key_file = '/etc/postgresql/tls/server.key'

Once this is done, we also need to update the host access file to only allow ssl traffic. My host access file can be found at /etc/postgresql/17/main/pg_hba.conf

In my file I had a line allowing authentication from all hosts on my network with a username / password combo:

1

host all             all             192.168.0.0/24          scram-sha-256

update the first value to hostssl and all connections from the network will be denied unless ssl is used.

1

hostssl all             all             192.168.0.0/24          scram-sha-256

Restart the PostgreSQL service to drop all active connections (incase someone is connected without SSL) and reload all config.

1

systemctl restart postgresql

To test, connect to the postgres service from a different machine on your network and verify that you are using ssl.

Automating the renewal using Cron

There are a few ways we can automate the renewal, but the easiest way is to set up a daily cronjob that will run the rewnew script daily.

Use the folling command to open the crontab editor and add the job

1

crontab -e

By adding the following line it will run the renew script every night at 2.30am

1

30 2 * * * /opt/lego/lego-renew.sh

Cron logs to syslog, so any errors in the process will be available there.

You should now have a PostgreSQL service running with a certificate that is authorized by a globally recognized CA that will automatically update as required.

I’ve been running this for a while and have yet to see any issues with the setup, and knowing that I have a securely configured database server helps me sleep better at night.

I haven’t covered monitoring of the server here, but this is already getting pretty long, so I’m going to leave that for another post.