Setup SSL/TLS for PostgreSQL with Docker

The goal

If you want to run PostgreSQL in production, setting up Transport Layer Security (TLS) is a must in order to prevent man-in-the-middle attacks. In this step-by-step guide, I will show you how you can connect to your database securely using your own local Certificate Authority. If you need an in-depth explanation of all settings, you can check out the official documentation on how to set up TLS in PostgreSQL.

In this example, we will ONLY allow encrypted connections and we will set sslmode to verify-full, which is the strictest possible connection setting in PostgreSQL.

Prerequisites

You need Docker installed on your machine. If you are on a Mac or Windows, make sure to install Docker Desktop and have it running in the background. Verify Docker is running by typing docker ps in your terminal. You should see a list of running containers or at least no error if Docker is running.

Setting Up PostgreSQL with TLS Using OpenSSL and Docker

Step 1: Generate a Certificate Authority (CA)

Note: All certificate files that are generated in the following steps (CA, server, and client) should be treated as secrets. Do not add them to your git repository, do not log them, and do not bake them into your Docker image (use runtime envs only). If necessary, add them to your .gitignore file.

First, we create a root CA key and certificate that will sign our server and client certs. You can change the name if you want. Our CA will be valid for 365 days and needs to be renewed after that.

openssl genrsa -out rootCA.key 2048
openssl req -x509 -new -nodes -key rootCA.key -sha256 -days 365 -out rootCA.crt -subj "/CN=MyLocalCA"

Step 2: Create Server Certificate Files

Generate a key and CSR (Certificate Signing Request), then sign it using the CA:

Important: Here you need to provide the domain name your PostgreSQL instance will be running on. In the example, I use localhost, but make sure to swap out the hostname if you use another one.

openssl genrsa -out server.key 2048
openssl req -new -key server.key -out server.csr -subj "/CN=localhost"
openssl x509 -req -in server.csr -CA rootCA.crt -CAkey rootCA.key -CAcreateserial -out server.crt -days 365 -sha256

3. Create Client Certificate Files

This certificate will be used by the client to authenticate. Only certificates that have been signed by the same certificate authority as the server certificate will work.

To create the client certificate, we follow the same steps as for the server. As a common name, I chose the postgres user:

openssl genrsa -out client.key 2048
openssl req -new -key client.key -out client.csr -subj "/CN=postgres"
openssl x509 -req -in client.csr -CA rootCA.crt -CAkey rootCA.key -CAcreateserial -out client.crt -days 365 -sha256

4. Create an Init Script to Enable TLS in PostgreSQL

Create a new file in the root of the project and call it ssl-config.sh. The script is intended to be used inside a Docker container based on a PostgreSQL base image.

It will copy the server certificates to the Docker container and add all the necessary settings in order to enforce TLS with sslmode set to verify-full.

If you want to allow insecure connections, you can omit the "Force SSL" section of the script or change the sslmode to something more forgiving.

#!/bin/bash

# Add certificate files
echo "$SERVER_CRT" > /var/lib/postgresql/server.crt
echo "$SERVER_KEY" > /var/lib/postgresql/server.key
echo "$ROOT_CA_CRT" > /var/lib/postgresql/rootCA.crt

# Set permissions for the certificate files
chmod 600 /var/lib/postgresql/server.* /var/lib/postgresql/rootCA.crt
chown postgres:postgres /var/lib/postgresql/server.* /var/lib/postgresql/rootCA.crt

# Configure PostgreSQL to use SSL
echo "ssl = on" >> /var/lib/postgresql/data/postgresql.conf
echo "ssl_cert_file = '/var/lib/postgresql/server.crt'" >> /var/lib/postgresql/data/postgresql.conf
echo "ssl_key_file = '/var/lib/postgresql/server.key'" >> /var/lib/postgresql/data/postgresql.conf
echo "ssl_ca_file = '/var/lib/postgresql/rootCA.crt'" >> /var/lib/postgresql/data/postgresql.conf

# Force SSL
echo "hostssl all all all cert clientcert=verify-full" > /var/lib/postgresql/data/pg_hba.conf

The script requires 3 environment variables:

• SERVER_CRT
• SERVER_KEY
• ROOT_CA_CRT

The contents of these envs is the plaintext content of the 3 certificate files: server.crt, server.key, and rootCA.crt.

5. Build a Docker Image with TLS Enabled

We create a simple Dockerfile based on the postgres:17.5 image and COPY our init script to the docker-entrypoint-initdb.d directory:

FROM postgres:17.5

COPY ssl-config.sh /docker-entrypoint-initdb.d/ssl-config.sh

6. Connect to the instance

To see if everything is working, we will try to build and run our image locally and try to connect to our instance. We will use the psql client to check if we can connect to our instance. If you have not installed it on your machine, make sure to add it first using your package manager of choice, e.g. brew install libpq on macOS.

Note: This local test will only work if you previously set the common name of your server certificate to localhost. Otherwise, you need to create a new server certificate with localhost as a common name.

Build the docker image:

docker build -t postgres-tls .

Run the image:

docker run \
 -e POSTGRES_PASSWORD=secret \
 -e SERVER_CRT="$(cat server.crt)" \
 -e SERVER_KEY="$(cat server.key)" \
 -e ROOT_CA_CRT="$(cat rootCA.crt)" \
 -p 5432:5432 --name pgv pgv

Make sure to use the correct path to your server.crt, server.key, and rootCA.crt file if you moved these files around.

Test the connection:

# This command should get you into the PostgreSQL shell:
psql "host=localhost dbname=postgres user=postgres sslmode=verify-full sslrootcert=rootCA.crt sslcert=client.crt sslkey=client.key"

# This should fail, because we enforce tls:
psql "host=localhost dbname=postgres user=postgres sslmode=disable"

Important: Make sure to run this from the same directory where your certificates are, or update the file paths accordingly.

Deploy

In order to make the database available over the internet, we will deploy it on Sliplane.

1. Create a GitHub repository with the two files described above: ssl-config.sh from step 4 and Dockerfile from step 5. I created an example repo that you can fork here: PostgreSQL with TLS repository
2. Log in to Sliplane with your GitHub account
3. Create a new Project and click on "Deploy Service"
4. Create a new Server, where your PostgreSQL instance will be running - you can easily start with the base server and scale up later if you need to
5. Choose "Repository" as the deploy source
6. Choose your PostgreSQL repository from the dropdown.

If the PostgreSQL repository does not show up in the list, you need to hit "Configure Repository Access" first in order to grant Sliplane access to deploy the repo

7. Change the protocol to TCP and hit "Deploy". Here you can see an overview of how the settings should look:

Note: this deploy will fail, but intentionally! We did not add our environment variables yet. We need to deploy first in order to get issued a sliplane.app domain that we need to generate our certificate. Alternatively, you could already fill out the environment variables if you use a custom domain in your certificate and attach that domain to your service afterwards.

8. Create the certificates according to steps 1-3. Make sure to use the sliplane.app domain of your service when you create your server certificate. You can find it in the service settings of your newly created service under Public Domain.

Note: Your service needs to be public when you create it. Otherwise, it won't get a public domain (see screenshot above).

9. After you created the certificates that you want to use in your public postgres instance, add them as environment variables along with a POSTGRES_PASSWORD env that you can set to any value you want.

Your envs should contain:

• POSTGRES_PASSWORD
• SERVER_CRT
• SERVER_KEY
• ROOT_CA_CRT

After saving your envs, a new deploy will be triggered which now should be successful.

That's it! You now have access to a PostgreSQL instance via a secure connection. You can test it by running:

psql "host=YOUR_APP.sliplane.app dbname=postgres user=postgres sslmode=verify-full sslrootcert=rootCA.crt sslcert=client.crt sslkey=client.key"

Note: Replace the sliplane.app domain that you have been issued and that is used in your certificate.

If you liked this tutorial, feel free to comment, like, and share.

Thanks!

Lukas