If you’re using Traefik to generate Let’s Encrypt certificates, you can easily reuse them to secure your PostgreSQL connections. Here’s how I set it up, step by step.

Dump the Certificate from Traefik

Traefik stores its certificates in a JSON file. To extract them in a usable format (PEM/KEY), you can use traefik-certs-dumper.

services:
  certdumper:
    image: ghcr.io/kereis/traefik-certs-dumper:1.8.18
    container_name: certdumper
    volumes:
      - ${HOME}/traefik:/traefik:ro # Traefik's cert storage
      - ${HOME}/certs:/output:rw # Output directory for dumped certs
    environment:
      - DOMAIN=example.com # Only dump certs for this domain
      - OVERRIDE_UID=1000 # Match PostgreSQL user UID/GID
      - OVERRIDE_GID=1000
      - PRIVATE_KEY_FILE_NAME=server # Must match PostgreSQL config
      - PRIVATE_KEY_FILE_EXT=.key
      - CERTIFICATE_FILE_NAME=server
      - CERTIFICATE_FILE_EXT=.crt

What this does:

  • Reads Traefik’s certificate store.
  • Outputs server.key and server.crt to ${HOME}/certs.
  • Ensures file permissions match your PostgreSQL user.

Configure PostgreSQL for SSL

Edit your postgresql.conf to enable SSL and point to the dumped certs:

# ...

ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'

Note: If you’re using a container, ensure the certs are accessible inside it (e.g., via a bind mount).

Copy Certificates to PostgreSQL’s Data Directory

If your PostgreSQL container uses a bind mount (e.g., /var/lib/postgresql/18/docker), copy the certs there:

cp ${HOME}/certs/server.key ${HOME}/certs/server.crt /path/to/postgres/data/

Set Up a TCP Route in Traefik

Add these labels to your PostgreSQL container in docker-compose.yml:

labels:
  - "traefik.enable=true"
  - "traefik.tcp.routers.postgres.entrypoints=postgres"
  - "traefik.tcp.routers.postgres.rule=HostSNI(`postgres.example.com`)"
  - "traefik.tcp.routers.postgres.tls.passthrough=true"
  - "traefik.tcp.routers.postgres.tls.certResolver=letsencrypt"
  - "traefik.tcp.routers.postgres.tls.domains[0].main=example.com"
  - "traefik.tcp.routers.postgres.tls.domains[0].sans=postgres.example.com"
  - "traefik.tcp.services.postgres.loadbalancer.server.port=5432"

Update DNS and Test the Connection

Add a DNS record (or /etc/hosts entry) for postgres.example.com:

# ...
192.168.0.10 postgres.example.com

Restart your containers, then verify SSL is active in PostgreSQL:

SELECT ssl, version(), inet_server_addr(), inet_server_port()
FROM pg_stat_ssl
WHERE pid = pg_backend_pid();
postgres ssl request

Automate Certificate Renewal

Let’s Encrypt certificates expire every 3 months. Automate the update with this Ansible playbook:

---
- hosts: all
  vars:
    cert_files:
      - server.key
      - server.crt
    traefik_dir: /home/docker/traefik
    certs_dir: /home/docker/certs
    postgres_dir: /home/docker/postgres/18/docker
  tasks:
    - name: Start certdumper container
      community.docker.docker_container:
        name: certdumper
        image: ghcr.io/kereis/traefik-certs-dumper
        env:
          DOMAIN: "example.com"
          OVERRIDE_UID: "1000"
          OVERRIDE_GID: "1000"
          PRIVATE_KEY_FILE_NAME: "server"
          PRIVATE_KEY_FILE_EXT: ".key"
          CERTIFICATE_FILE_NAME: "server"
          CERTIFICATE_FILE_EXT: ".crt"
        volumes:
          - "{{ traefik_dir }}:/traefik:ro"
          - "{{ certs_dir }}:/output:rw"
    - name: Pause for 10 seconds to generate generate certs
      ansible.builtin.pause:
        seconds: 10
    - name: Copy cert files
      ansible.builtin.copy:
        src: "{{ certs_dir }}/{{ item }}"
        dest: "{{ postgres_dir }}/{{ item }}"
        remote_src: true
      loop: "{{ cert_files }}"
    - name: Delete src files
      ansible.builtin.file:
        path: "{{ certs_dir }}/{{ item }}"
        state: absent
      loop: "{{cert_files }}"
    - name: Stop certdumper container
      community.docker.docker_container:
        name: certdumper
        state: stopped
...

Tip: Run this playbook as a cron job every 2 months to stay ahead of expirations with Semaphore UI.

Update Client Configurations

Finally, ensure your clients (e.g., Adminer, DBeaver, or psql) use SSL:

sslmode=require

Conclusion

I hope this guide helped you secure your PostgreSQL connections using Traefik-managed Let’s Encrypt certificates.