Postgres SSL Termination with Traefik

[gdgellatly]

Preamble:

• This is provided as advisory, we neither use Doodba or the doodba suggested Traefik config, however they are quite similar.
• The setup this was tested on is Traefik v3.6.1 which we consider the minimum version for production due to hard coded docker client api issues in all prior versions, using DNS challenge and letsencrypt wildcard certs.

Objective:

• Terminate connections to postgres databases behind traefik by hostname to enable port 5432 to be shared.
• Minimal changes to postgres configuration.
• Primary driver was to enable synchronous replication without having to expose mapped ports, but using SNI's also has other benefits such as being able to use .pgpass in an environment where IP's change.

Traefik Setup

• requires 3 things, tls postgresql options, port 5432 exposed and a db entrypoint.

In a dynamic config file add the following (and ensure it gets loaded).

tls:
  options:
    pg-alpn:
      alpnProtocols: 
        - postgresql
        - h2
        - http/1.1
        - acme-tls/1

Define your entrypoint and expose the port in your docker-compose.yml (only showing needed config, obviously there is more)

services:
  traefik:
    image: traefik:3.6.1
    ports:
      - "80:80"
      - "443:443"
      - "5432:5432"
    command:
      - '--entrypoints.postgres.address=:5432'
      - '--entrypoints.postgres.http.tls.certresolver=le'

Database Compose file

• This is a minimum working example and no other changes needed to default image (e.g. default pg_hba is fine), change HostSNI to suit.

services:
  postgres:
    image: postgres:18
    container_name: postgres
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
      POSTGRES_DB: postgres
      POSTGRES_LISTEN_ADDRESSES: '*'
    volumes:
      - postgres-data:/var/lib/postgresql
    labels:
      - "traefik.enable=true"
      - "traefik.docker.network=traefik_shared"
      - "traefik.tcp.routers.postgres.rule=HostSNI(`pg.example.com`)"
      - "traefik.tcp.routers.postgres.service=postgres"
      - "traefik.tcp.routers.postgres.entrypoints=postgres"
      - "traefik.tcp.routers.postgres.tls=true"
      - "traefik.tcp.routers.postgres.tls.certresolver=le"
      - "traefik.tcp.routers.postgres.tls.options=pg-alpn@file"
      - "traefik.tcp.services.postgres.loadbalancer.server.port=5432"
    networks:
      - traefik_shared

networks:
  traefik_shared:
    external: true

volumes:
  postgres-data:

Connection

When we connect, Traefik will detect the Postgres STARTTLS prototol, terminate the connection and forward it the HostSNI contained in the header via regular TCP. You may need to set sslmode=require in your connection string and should be considered best practice.

psql "host=pg.example.com dbname=postgres user=postgres sslmode=require"

If successful you should see something like

psql (18.1 (Ubuntu 18.1-1.pgdg24.04+2), server 18.0)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_128_GCM_SHA256, compression: off, ALPN: postgresql)
Type "help" for help.

postgres=#

With VSCode Postgres Explorer

{
  "label": "postgres",
  "host": "postgres.example.com",
  "user": "postgres",
  "port": 5432,
  "ssl": true,
  "database": "postgres",
  "password": "postgres"
}

From Windows Machine on Excel using ODBC Data Source

This is almost correct but with v17 of the pgodbc driver {PostgreSQL Unicode} is the driver. Once you click OK, the next screen asks for user and password plus connection information. In connection information you add sslmode=require

[pedrobaeza]

Thanks for this, Graeme. @josep-tecnativa can you please check this and comment which were the problems you faced when doing something similar?

[josep-tecnativa]

Hi @gdgellatly ,

Just for context: in the doodba-copier-template (our project scaffold), when you choose to expose PostgreSQL externally, the template automatically generates Traefik rules that are very similar to the ones you described.

For example:

labels:
  traefik.enable: "true"
  traefik.docker.network: "inverseproxy_shared"
  traefik.tcp.routers.db.entrypoints: postgres-entrypoint
  traefik.tcp.routers.db.tls: "true"
  traefik.tcp.routers.db.tls.certResolver: letsencrypt
  traefik.tcp.routers.db.rule: HostSNI(`example.com`)
  traefik.tcp.services.db.loadbalancer.server.port: 5432

This part works fine, and the logic is basically the same: TCP router, HostSNI, TLS termination, and forwarding to the internal Postgres port.

The issue we’ve found, however, is that while this setup works well with some clients (notably ODBC), several other PostgreSQL drivers fail to connect properly through Traefik’s TCP+SNI routing.
They simply don’t handle the connection negotiation as Traefik expects, so the behaviour becomes inconsistent across clients.

That’s essentially the main limitation we’ve hit when trying to rely on this configuration.

[gdgellatly]

Hi @josep-tecnativa Similar but not the same :} the missing magic in the labels above is the postgresql specific tls termination - "traefik.tcp.routers.postgres.tls.options=pg-alpn@file"