Setting up TLS connection for containerized PostgreSQL database

When you need to allow external connections to your database while keeping costs low, encrypting the connection is essential. This guide shows you the simplest way to set up encrypted connections for your development environment.

Why Do We Need Encryption?

When connecting your database to external services, especially SaaS platforms, you often can't restrict access by IP address or domain. In these cases, enabling TLS (Transport Layer Security) encryption helps keep your data safe.

PostgreSQL version: 16

Understanding PostgreSQL Client Connection Types

PostgreSQL clients can connect in six different ways

1. disabled: No encryption at all. Only safe for local networks.
2. allow: Prefers unencrypted connections but will use encryption if the server requires it.
3. prefer: (Default for most clients) Tries to use encryption first but accepts unencrypted connections if necessary.
4. require: Must use encryption. Won't connect without it but doesn't verify certificates.
5. verify_ca: Uses encryption and checks if the server's certificate is signed by a trusted authority.
6. verify_full: The most secure option. Checks encryption, certificates, and ensures the server name matches the certificate.

The most secure options are verify_ca and verify_full, but they require more setup in development:

• one-way verification: You need the root CA certificate
• two-way(mTLS) verification: You need both the client certificate and key in your connection string, the certificate/key MUST signed by root CA and key.

This guide focuses on setting up one-way verification for development environments since it's simpler while still providing good security.

Setting Up the Server(One-Way Verification)

1. Create Certificate and Key

# Set to 100 years for development use
openssl req -x509 -newkey rsa:4096 -sha256 -nodes -keyout key.pem -out cert.pem -days 36500

2. Configure PostgreSQL

Add these lines to postgresql.conf

ssl = on
ssl_cert_file = '/var/lib/postgresql/cert.pem'
ssl_key_file = '/var/lib/postgresql/key.pem'

3. Update Access Rules

Add these lines to pg_hba.conf

hostssl all all all scram-sha-256
hostnossl all all all reject

4. Set Up Docker Compose

services:
  postgres:
    image: postgres:16.3
    ports:
      - '5432:5432'
    environment:
      - POSTGRES_PASSWORD=postgres
      - POSTGRES_USER=postgres
      - POSTGRES_DB=postgres
    volumes:
      - ./.docker/postgres/config/pg_hba.conf:/etc/postgresql/pg_hba.conf
      - ./.docker/postgres/config/postgresql.conf:/etc/postgresql/postgresql.conf
      - ./.docker/postgres/certs/cert.pem:/var/lib/postgresql/cert.pem:ro
      - ./.docker/postgres/certs/key.pem:/var/lib/postgresql/key.pem:ro
      - postgres:/var/lib/postgresql/data
    command:
        - postgres
        - -c
        - config_file=/etc/postgresql/postgresql.conf
        - -c
        - hba_file=/etc/postgresql/pg_hba.conf

Connecting to Your Database

After setting everything up, just add sslmode=require to your connection string:

DATABASE_URL="postgresql://postgres:postgres@postgres:5432/database?sslmode=require"

That's all you need to establish an encrypted connection to your PostgreSQL database.

Setting Up the Server(mTLS)

For mTLS (mutual TLS), both the server and client need valid certificates signed by a trusted Certificate Authority (CA). Here's how to set it up

1. Create certifications for server and client

Your server.csr CN MUST be your postgreSQL hostname

# 1. Create Root CA
openssl genrsa -out root.key 4096
openssl req -x509 -new -nodes -key root.key -sha256 -days 36500 -out root.crt \
  -subj "/C=US/ST=California/L=San Francisco/O=MyCompany/OU=IT/CN=PostgreSQL Root CA"

# 2. create server cert
openssl genrsa -out server.key 4096
openssl req -new -key server.key -out server.csr \
  -subj "/C=US/ST=California/L=San Francisco/O=MyCompany/OU=Database/CN=postgres"

# This is important that SAN MUST contains all your possible DNS/IP 
openssl x509 -req -in server.csr -CA root.crt -CAkey root.key -CAcreateserial \
  -out server.crt -days 36500 -sha256 \
  -extfile <(printf "subjectAltName=DNS:localhost,DNS:postgres,IP:127.0.0.1,IP:192.168.0.23")

# 3. create client cert - CN here MUST same as the postgres user you want
openssl genrsa -out client.key 4096
openssl req -new -key client.key -out client.csr \
  -subj "/C=US/ST=California/L=San Francisco/O=MyCompany/OU=Developers/CN=postgres"

openssl x509 -req -in client.csr -CA root.crt -CAkey root.key -CAcreateserial \
  -out client.crt -days 36500 -sha256

If you want your client CN using custom map, you should use pg_ident.conf and adjust setting in pg_hba.conf to archive that

# pg_hba.conf
hostssl all all 0.0.0.0/0 cert map=my_map
# pg_ident.conf
my_map /CN=client postgres

2. Configure PostgreSQL

Add these lines to postgresql.conf

ssl = on
ssl_cert_file = '/var/lib/postgresql/server.crt'
ssl_key_file = '/var/lib/postgresql/server.key'
ssl_ca_file = '/var/lib/postgresql/root.crt'

3. Update Access Rules

Add these lines to pg_hba.conf

hostssl all all all cert clientcert=verify-full
hostnossl all all all reject

4. Update docker-compose.yml

Update volume bindings

...
  volumes:
      - ./.docker/postgres/config/pg_hba.conf:/etc/postgresql/pg_hba.conf
      - ./.docker/postgres/config/postgresql.conf:/etc/postgresql/postgresql.conf
      - ./.docker/postgres/certs/server.crt:/var/lib/postgresql/server.crt:ro
      - ./.docker/postgres/certs/server.key:/var/lib/postgresql/server.key:ro
      - ./.docker/postgres/certs/root.crt:/var/lib/postgresql/root.crt:ro
...

Connecting to Your Database

After setting everything up, add the following options to your connection string

• sslmode=verify-full
• sslcert=client.crt
• sslkey=client.key
• sslrootcert=root.crt

DATABASE_URL="postgresql://postgres:postgres@postgres:5432/database?sslmode=verify-full&sslcert=client.crt&sslkey=client.key&sslrootcert=root.crt"