Self-Hosted PostgreSQL with SSL in Docker

Earlier this year I decided to self-host a PostgreSQL database that had previously been running as a Heroku add-on. I wanted to support secure connections to the database, so that also meant configuring it to work with SSL. And finally, I wanted to run everything in Docker containers.

I definitely can’t claim to be an expert on any of this, but I did get it working. So, I thought I’d share what I put together in case it might be helpful to someone else.

Docker Compose

I decided to use Docker Compose to manage the multiple containers I would need. This decision was based mostly on my having used Docker Compose to manage containers needed for automated test suites on work projects. But there shouldn’t be anything about what I’m doing here that couldn’t be done with something like Rancher.

Let’s Encrypt

In order to support secure connections to the database, I wanted to use a real certificate (as opposed to a self-signed one). I wasn’t particularly interested in paying for it though. Fortunately Let’s Encrypt is a perfectly viable option for getting free certificates these days.

Traefik

In researching how to use Let’s Encrypt to get certificates, I came across the Traefik Proxy and its built-in support for automatically managing Let’s Encrypt certificates.

Here’s the Traefik section of my docker-compose.yml:

traefik:
  image: "traefik:v2.4.9"
  restart: unless-stopped
  networks:
    - traefik
  command:
    - "--api"
    - "--providers.docker=true"
    - "--providers.docker.exposedbydefault=false"
    - "--providers.docker.network=traefik"
    - "--entrypoints.web.address=:80"
    - "--entrypoints.websecure.address=:443"
    - "--entrypoints.dashboard.address=:8080"
    - "--accesslog=true"
    - "[email protected]"
    - "--certificatesresolvers.le.acme.storage=/letsencrypt/acme.json"
    - "--certificatesresolvers.le.acme.tlschallenge=true"
    # - "--log.level=DEBUG"
    # - "--certificatesresolvers.le.acme.caserver=https://acme-staging-v02.api.letsencrypt.org/directory"
  environment:
    - "TZ=America/Detroit"
  ports:
    - "80:80"
    - "443:443"
    - "8080:8080"
  volumes:  
    - "/etc/localtime:/etc/localtime:ro"
    - "/var/run/docker.sock:/var/run/docker.sock:ro"
    - "/home/patrick/traefik/mounted/letsencrypt:/letsencrypt"

Let’s Encrypt has a staging server you can use. Some documentation suggests starting with the staging server and then only commenting it out once things are working.

The labels section is setting up the Traefik dashboard so I can access it inside my home network at http://traefik.home. You’ll have to configure your internal DNS or hosts file to point to the server for this to work.

I’m using a traefik Docker Compose network to put multiple containers on the same network as the Traefik proxy. Here’s the docker-compose.yaml configuration for that:

networks:
  traefik:
    name: traefik

A Web Server

For a Let’s Encrypt certificate, you need to host a website at the domain name for which you want the certificate generated. In my case, I want the certificate for PostgreSQL, not a website. However, for this to work, I need to host something so Traefik and Let’s Encrypt can do their thing.

The domain name I want the certificate for is side-project.mydomain.org. And the Docker image I’m using (crccheck/hello-world) is just a tiny image that runs a web server hosting a “Hello World” page.

side-project-web:
  image: "crccheck/hello-world"
  restart: unless-stopped
  networks:
    - traefik
  labels:
    - "traefik.enable=true"
    - "traefik.http.routers.side-project.rule=Host(`side-project.mydomain.org`)"
    - "traefik.http.routers.side-project.entrypoints=websecure"
    - "traefik.http.routers.side-project.tls=true"
    - "traefik.http.routers.side-project.tls.certresolver=le"

The labels in this section is the important part. It tells Traefik to use Let’s Encrypt to generate a secure certificate for side-project.mydomain.org.

Traefik Certs Dumper

With the setup so far, I’ll have Traefik automatically generating, and updating, a secure certificate for my domain name. But I need to do something so that my PostgreSQL instance will have access to that certificate. That’s where traefik-certs-dumper comes in. It will automatically export the certificates from the acme.json file that Traefik stores the certificates in.

traefik-certs-dumper:
  image: ldez/traefik-certs-dumper:v2.7.4
  restart: unless-stopped
  entrypoint: sh -c '
    apk add jq
    ; while ! [ -e /data/acme.json ]
    || ! [ `jq ".[] | .Certificates | length" /data/acme.json` != 0 ]; do
    sleep 1
    ; done
    && traefik-certs-dumper file --version v2 --watch --domain-subdir=true --source /data/acme.json --dest /data/certs --post-hook "sh /post-hook.sh"'
  volumes:
    - "/home/patrick/traefik/mounted/letsencrypt:/data"
    - "/home/patrick/side-project-db/mounted/certs:/side-project-db-certs"
    - "/home/patrick/traefik-certs-dumper/mounted/post-hook.sh:/post-hook.sh"

This was taken pretty straight from the example docker-compose.yml file. The only difference is that I tacked on a --post-hook to invoke a shell script that will be used to copy the extracted certificates to a directory that the PostgreSQL server has access to (and fix permissions as well).

Here’s the post-hook.sh script:

#!/bin/sh

# These are the paths inside the traefik-certs-dumper container
SOURCE_PATH=/data/certs/side-project.mydomain.org
DEST_PATH=/side-project-db-certs

if cmp -s "${SOURCE_PATH}/certificate.crt" "${DEST_PATH}/certificate.crt" ; then
    echo "Nothing changed (certificate)"
else
    echo "Something changed (certificate)"
    cp "${SOURCE_PATH}/certificate.crt" "${DEST_PATH}/"
    chown 999:999 "${DEST_PATH}/certificate.crt"
fi

if cmp -s "${SOURCE_PATH}/privatekey.key" "${DEST_PATH}/privatekey.key" ; then
    echo "Nothing changed (private key)"
else
    echo "Something changed (private key)"
    cp "${SOURCE_PATH}/privatekey.key" "${DEST_PATH}/"
    chown 999:999 "${DEST_PATH}/privatekey.key"
fi

PostgreSQL in Docker

PostgreSQL puts out their own official Docker containers, so I found the version that corresponded to what had previously been running in Heroku.

side-project-db:
  image: postgres:11.12
  restart: unless-stopped
  ports:
    - "5432:5432"
  environment:
    - POSTGRES_USER=postgres
    - POSTGRES_PASSWORD=${SIDE_PROJECT_DB_ROOT_PASSWORD}
  command: postgres -c ssl=on -c ssl_cert_file=/var/lib/postgresql/server.crt -c ssl_key_file=/var/lib/postgresql/server.key
  volumes:
    - "/home/patrick/side-project-db/mounted/postgres-data:/var/lib/postgresql/data"
    - "/home/patrick/side-project-db/mounted/certs/certificate.crt:/var/lib/postgresql/server.crt:ro"
    - "/home/patrick/side-project-db/mounted/certs/privatekey.key:/var/lib/postgresql/server.key:ro"

You’ll notice that I’m mounting the certificate.crt and privatekey.key from the same directory (side-project-db/mounted/certs) that was mounted in the traefik-certs-dumper container. That’s how the PostgreSQL server gets access to the certificates that were extracted.

DNS

In order for Let’s Encrypt to generate a certificate, it needs to be able to resolve your domain name and then hit the webserver running there. That meant updating the DNS configuration for my domain name (side-project.mydomain.org) to point to my IP address. The interface you use to do this will differ based on where the domain name is registered.

Port Forwarding

The ports that are mapped in the docker-compose.yml file need to be exposed to the outside world. Exactly how this is done will vary depending on your home network/router. The ports that need to be forward to the machine hosting the Docker contains are 5432, 443, 80. I don’t think 80 is actually necessary, but since that’s what I’ve currently got configured I’m including here.

Run It! Self-Hosted PostgreSQL with SSL in Docker

At this point everything is ready to go. You need to run the traefik, side-project-web, and traefik-certs-dumper containers first so the secure certificate can be generated. Once that’s done, the side-project-db container can be started, and it should pick up the certificates when it runs.

NOTE: One issue that I have yet to figure out is how to get the PostgreSQL database to automatically pick up the new certificate. Restarting the service (stopping/starting the Docker container) definitely works. But I imagine there’s a way to tell a running PostgreSQL server to reload the secure certificates without having to reboot it. I normally try to update the host OS once a month, so when that reboots, it’s been picking up the latest certificate. That means this hasn’t really been a problem I’ve needed to solve yet.