Running multiple databases via Traefik, and accessing the databases from the host machine

Traefik Traefik v2 (latest)
,
1

The problem overview

At present, Traefik can handle a single project's database just fine, and the host machine can access the db via psql -h 127.0.0.1 -p 5432 -U postgres . My current config regarding my project databases was inspired by this setup here.

The problem comes when you have two projects running - aka when you spin up another project's container stack/docker-compose file). My configuration to get traefik to play nice with databases involves setting my projects' HostSNI to * instead of a domain name, so each project's docker-compose.yml database service labels section looks like this:

# COMPOSE_PROJECT_NAME = the container stack project name, e.g. neato
labels:
  - traefik.enable=true
  - traefik.tcp.routers.${COMPOSE_PROJECT_NAME}_postgres.rule=HostSNI(`*`)
  - traefik.tcp.routers.${COMPOSE_PROJECT_NAME}_postgres.service=${COMPOSE_PROJECT_NAME}_postgres
  - traefik.tcp.services.${COMPOSE_PROJECT_NAME}_postgres.loadbalancer.server.port=5432
  - traefik.tcp.routers.${COMPOSE_PROJECT_NAME}_postgres.entrypoints=postgres

And even though there's all that namespacing in there, that wildcard in the HostSNI causes the host machine to only see the db of the last container stack spun up. For an example of this problem:

# SUCCESS: when only project-a is running
➜ project-a (master) ✗ psql --host 127.0.0.1 -p 5432 -U postgres 
Password for user postgres: 
psql (13.2, server 12.4 (Debian 12.4-1.pgdg100+1))
Type "help" for help.

postgres=# \l
                                              List of databases
                 Name                 |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges   
--------------------------------------+----------+----------+------------+------------+-----------------------
 postgres                             | postgres | UTF8     | en_US.utf8 | en_US.utf8 | 
 template0                            | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
                                      |          |          |            |            | postgres=CTc/postgres
 template1                            | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
                                      |          |          |            |            | postgres=CTc/postgres
project-a_django | postgres | UTF8     | en_US.utf8 | en_US.utf8 | 

# BUG: if we start project-b and then re-access the host 127.0.0.1 from the host/local machine, we see this
➜ literally-anywhere (master) ✗ psql --host 127.0.0.1 -p 5432 -U postgres 
Password for user postgres: 
psql (13.2, server 12.4 (Debian 12.4-1.pgdg100+1))
Type "help" for help.

postgres=# \l
                                              List of databases
                 Name                 |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges   
--------------------------------------+----------+----------+------------+------------+-----------------------
 postgres                             | postgres | UTF8     | en_US.utf8 | en_US.utf8 | 
 template0                            | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
                                      |          |          |            |            | postgres=CTc/postgres
 template1                            | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
                                      |          |          |            |            | postgres=CTc/postgres
project-b_django | postgres | UTF8     | en_US.utf8 | en_US.utf8 | 

# WHAT I WANT TO HAPPEN: I'd expect to see this:
➜ literally-anywhere (master) ✗ psql --host 127.0.0.1 -p 5432 -U postgres 
Password for user postgres: 
psql (13.2, server 12.4 (Debian 12.4-1.pgdg100+1))
Type "help" for help.

postgres=# \l
                                              List of databases
                 Name                 |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges   
--------------------------------------+----------+----------+------------+------------+-----------------------
 postgres                             | postgres | UTF8     | en_US.utf8 | en_US.utf8 | 
 template0                            | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
                                      |          |          |            |            | postgres=CTc/postgres
 template1                            | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
                                      |          |          |            |            | postgres=CTc/postgres
project-a_django | postgres | UTF8     | en_US.utf8 | en_US.utf8 | 
project-b_django | postgres | UTF8     | en_US.utf8 | en_US.utf8 |

If you set the HostSNI to a domain name, it does not work. neato-db.localdev.fool.com with tls=true fails, as does something like neato.local
(with tls=true added, since it's required, tho in this latter case, the tls certs are nonexistent)

From an issue ticket in 2019, supposedly this was fixed, but for the life of me I cannot figure out what label or config setting I need to add in order to change this behavior.

The traefik container stack code. Only one of these is ran, though it is running at the same time as other project container stacks.

docker-compose.yml

version: "3.7"

services:
  traefik:
    image: traefik:v2.4
    restart: "unless-stopped"
    ports:
      # typical HTTP traffic ports
      - "80:80"
      # Port 443 is used for secure HTTPS traffic
      - "443:443"
      # Port 5432 is used for Postgres TCP traffic
      - "5432:5432"
    volumes:
      # Here is the mount of the Traefik config
      - ./traefik.yml:/etc/traefik/traefik.yml:ro
      # Here is the mount of the dynamic config
      - ./dynamic-conf.yml:/etc/traefik/dynamic-conf/conf.yml:ro
      # Here is the mount of the local directory that holds the certs
      - ./certs:/etc/traefik/ssl:ro
      # The docker socket is mounted for auto-discovery of new services
      - /var/run/docker.sock:/var/run/docker.sock:ro
    labels:
      # Expose Traefik dashboard through Traefik
      - traefik.enable=true
      # Setup the dashboard router
      - traefik.http.routers.proxy_dash.entrypoints=web
      - traefik.http.routers.proxy_dash.rule=Host(`proxy.localdev.fool.com`)
      - traefik.http.middlewares.force_https.redirectscheme.scheme=https
      - traefik.http.routers.proxy_dash.middlewares=force_https@docker
      # Serve it via HTTPS
      - traefik.http.routers.proxy_dash_https.entrypoints=websecure
      - traefik.http.routers.proxy_dash_https.rule=Host(`proxy.localdev.fool.com`)
      - traefik.http.routers.proxy_dash_https.tls=true
      - traefik.http.routers.proxy_dash_https.service=api@internal
    networks:
      # Attach the Traefik container to the default network (which is the global "proxy-network" network)
      - default

# Make the externally created network "proxy-network" available as network "default"
networks:
  default:
    external: true
    name: proxy-network

dynamic-conf.yml
These certs are created on my OSX machine with the mkcert utility, as well as added to the system trust store. They are tested and they work as intended when utilized by Traefik's http routers/middleware/services.

tls:
  certificates:
    - certFile: /etc/traefik/ssl/_wildcard.localdev.fool.com.pem
      keyFile: /etc/traefik/ssl/_wildcard.localdev.fool.com-key.pem

traefik.yml

entryPoints:
  web:
    address: ":80"
  websecure:
    address: ":443"
  postgres:
    address: ":5432"

api:
  dashboard: true
  insecure: true

log:
  level: DEBUG

providers: # You can add more than one provider if needed
  docker:
    endpoint: "unix:///var/run/docker.sock"
    network: "proxy-network" # Custom docker network
    exposedByDefault: false # If set to false, containers that don't have a
                            # traefik.enable=true label will be ignored from
                            # the resulting routing configuration.
  file:
    directory: /etc/traefik/dynamic-conf/
    watch: true

Code for each project container stack. Many of these can run concurrently.

docker-compose.yml

version: '3.8'

services:
    project:
        build:
            context: ../../
            dockerfile: ./cicd/docker/django/Dockerfile
            target: dev
        restart: unless-stopped
        command: >
            bash -c "python manage.py migrate &&
                     python manage.py runserver 0.0.0.0:8000"
        env_file:
            - ../../.env
        environment:
            - DATABASE_URL=postgres://postgres:password123@database/${COMPOSE_PROJECT_NAME}_django
        volumes:
            - ../..:/var/src/neato
        depends_on:
            - database
        labels:
            # This tells Traefik to listen for this container on the network
            - traefik.enable=true
            # Set the python container so port 8000 is picked up by Traefik's HTTP network
            - traefik.http.services.${COMPOSE_PROJECT_NAME}.loadbalancer.server.port=8000
            - traefik.http.routers.${COMPOSE_PROJECT_NAME}.entrypoints=web
            # Set the domain that gets picked up by Traefik's HTTP network
            - traefik.http.routers.${COMPOSE_PROJECT_NAME}.rule=Host(`${COMPOSE_PROJECT_NAME}.localdev.fool.com`)
            # Redirect the above via HTTPS
            - traefik.http.middlewares.force_https.redirectscheme.scheme=https
            - traefik.http.routers.${COMPOSE_PROJECT_NAME}.middlewares=force_https@docker
            - traefik.http.routers.${COMPOSE_PROJECT_NAME}_https.entrypoints=websecure
            # Set the domain that gets picked up by Traefik's HTTPS network
            - traefik.http.routers.${COMPOSE_PROJECT_NAME}_https.rule=Host(`${COMPOSE_PROJECT_NAME}.localdev.fool.com`)
            # Tell Traefik that the certs are on the local machine.
            # This works in tandem with the dynamic config present in the Traefik container.
            - traefik.http.routers.${COMPOSE_PROJECT_NAME}_https.tls=true
        networks:
            - default
            - proxy-network
    database:
        image: ${DATABASE_REPO_SOURCE:-postgres:12}
        volumes:
            - dbdata:/var/lib/postgresql/data # https://github.com/docker-library/docs/blob/master/postgres/README.md#pgdata
        restart: unless-stopped
        environment: # setting these here instead of .env so these do not get mistaken for app env vars
            - POSTGRES_PASSWORD=password123
            - POSTGRES_DB=${COMPOSE_PROJECT_NAME}_django
        labels:
            - traefik.enable=true
# NOTE: AFAIK, THIS IS THE SOURCE OF MY PROBLEM
# With this wildcard, the host machine can only find the database of the last container stack spun up.
# I think I need this to be a domain name, but no domain name works when I run `psql -h whatever.whatever-domain` from the host machine.
# I think this is failing since I need some additional label or config setting to get this to work
            - traefik.tcp.routers.${COMPOSE_PROJECT_NAME}_postgres.rule=HostSNI(`*`) 
            - traefik.tcp.routers.${COMPOSE_PROJECT_NAME}_postgres.service=${COMPOSE_PROJECT_NAME}_postgres
            - traefik.tcp.services.${COMPOSE_PROJECT_NAME}_postgres.loadbalancer.server.port=5432
            - traefik.tcp.routers.${COMPOSE_PROJECT_NAME}_postgres.entrypoints=postgres
            - traefik.tcp.routers.${COMPOSE_PROJECT_NAME}_postgres.tls=true
        networks:
            - default
            - proxy-network

volumes:
    dbdata:

networks:
  default:
  proxy-network:
    external: true
    name: proxy-network
2

You will only get to use one pg router per port.

Postgres does TLS after connecting plaintext then upgrades. As is does not connect TLS first no hostname is sent an HostSNI(hostname) cannot be used.

You can fudge this on the client side by using something like stunnel. If postgres is requireTLS this could end up being double encapsulated, something to be aware of.

3

SNI routing for postgres with STARTTLS has been added to Traefik in this PR. Now Treafik will listen to the initial bytes sent by postgres and if its going to initiate a TLS handshake (Note that postgres TLS requests are created as non-TLS first and then upgraded to TLS requests), Treafik will handle the handshake and then is able to receive the TLS headers from postgres, which contains the SNI information that it needs to route the request properly. This means that you can use HostSNI("example.com") along with tls to expose postgres databases under different subdomains.

As of writing this answer, I was able to get this working with the v3.0.0-beta2 image (Reference)

1 Like
4

Can you please add one docker-compose example of a dockerfile having multiple subdomains each pointing to a different postgres container?

5

Are your databases running in Docker? The you can use Configuration Discovery, see simple Traefik TCP example.

6

Yes multiple postgres on docker but I need each to have different subdomain.
But I need all of them to be available via port 5432 normally via the db uri string.

I don't see the tls enabled for the tcp container .
.which I thought it should be required if the host is subdomain.example.com

7

How do you want to handle TLS? Should Traefik create and terminate TLS, use unencrypted connections internally?

When using Traefik LE on non-standard ports, you might need to use dnsChallenge.