Help in TCP configure of traefik mapped postgresql container - hostSNI

Traefik Traefik v2
1

Greetings.

I'm trying to connect from docker host, to a container with postgresql, mapped by traefik.

If anyone has any example of docker-compose of traefik and postgresql and which command line I can connect via psql , from the host console, with the container that runs postgresql - it would help me a lot.

Here's what I use today:

traefik docker-compose.yml

version: '3.2'

# rede criada para comportar Server & Client
networks:
  netdocker:
    external: 
      name: ntwkr_docker

# volume com os dados dos Certificados
volumes:
  traefik-certificates:
    external:
      name: vlm_traefik_certs

services:
  wstraefik:
    image: traefik:v2.6
    labels:
    - "traefik.enable=true"
    - "traefik.http.routers.http-catchall.rule=hostregexp(`{host:.+}`)"
    - "traefik.http.routers.http-catchall.entrypoints=web"
    - "traefik.http.routers.http-catchall.middlewares=redirect-to-https@docker"
    - "traefik.http.middlewares.redirect-to-https.redirectscheme.scheme=https"    
    command:
      - "--providers.docker.endpoint=unix:///var/run/docker.sock"
      - "--providers.docker.swarmMode=false"
      - "--providers.docker.exposedbydefault=true"
      - "--providers.docker.network=ntwrk_docker"
      - "--entrypoints.web.address=:80"
      - "--entrypoints.websecure.address=:443"
      - "--certificatesresolvers.letsencryptresolver.acme.httpchallenge=true"
      - "--certificatesresolvers.letsencryptresolver.acme.httpchallenge.entrypoint=web"
      - "--certificatesresolvers.letsencryptresolver.acme.email=email@server.com.br"
      - "--certificatesresolvers.letsencryptresolver.acme.storage=/letsencrypt/acme.json"
      - "--api=true"
      - "--api.dashboard=true"
      - "--api.insecure=true"
    container_name: wstraefik
    restart: unless-stopped
    networks:
      - netdocker
    ports:
      - 80:80
      - 443:443
      - 8080:8080
      - 5432:5432
      - 3306:3306
    volumes:
      - /etc/localtime:/etc/localtime:ro
      - /var/run/docker.sock:/var/run/docker.sock:ro
      - traefik-certificates:/letsencrypt
    labels:
      - "traefik.enable=true"
      - "traefik.http.routers.dashboard.service=api@internal"
      - "traefik.http.routers.dashboard.rule=Host(`trfk-dashboard.wslan`)"
      - "traefik.http.routers.dashboard.entrypoints=web"
      - "traefik.http.services.dashboard.loadbalancer.server.port=80"

postgresql docker-compose.yml

version: '3.3'
# rede criada para comportar Server & Client
networks:
  netdocker:
    external: 
      name: ntwkr_docker

# volume com os dados do PGv11
volumes:
  vlmpg11mdr:
    external:
      name: vlm_pg11_mdr

services:
  wspgsql11mdr:
    container_name: ws-pg11-mdr
    image: ws-kartoza-pgpgis:11.0-2.5
    deploy:
        resources:
            limits:
              cpus: 1.0
              memory: 3072M
#    ports:
#      - '5432:5432'
    networks:
      - netdocker
    volumes:
      - vlmpg11mdr:/var/lib/postgresql/
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASS=pg01
      - POSTGRES_DB=postgres
      - ALLOW_IP_RANGE=0.0.0.0/0
      - POSTGRES_MULTIPLE_EXTENSIONS=postgis,hstore,postgis_topology,postgis_raster,pgrouting,unaccent
      - DEFAULT_ENCODING="UTF8"
      - DEFAULT_COLLATION="pt_BR.UTF-8"
      - DEFAULT_CTYPE="pt_BR.UTF-8"
    labels:
# -------------------------------------------
# para invocar por TCP
      - "traefik.enable=true"
      - "traefik.tcp.routers.wspgsql11mdr.entrypoints=postgres"
      - "traefik.tcp.routers.wspgsql11mdr.rule=HostSNI(`*`)"
      - "traefic.tcp.routers.wspgsql11mdr.tls=false"
      - "traefik.tcp.services.wspgsql11mdr.loadBalancer.server.port=5432"
      - "traefik.tcp.routers.wspgsql11mdr.service: wspg11mdr"
# -------------------------------------------
    restart: always

Try to Connect pg from host

root@docker-host #> psql -h wspg11mdr.wslan -p 5432 -U postgres -d postgres

Do not connect!

2

Typing error ? traefic instead of traefik

3

Thanks Caklwf.

I fixed the syntax error and re-executed the whole stack: traefik + postgresql + pgadmin4 and the result was that:

  1. Traefik performed well.
  2. Postgresql ran fine.
  3. pgadmin4 ran fine, i was able to register postgresql and browse the default cluster, ie pgadmin4 looked up the container's and connected to it.

However, from the docker host console, I can't connect to the container cluster via:
#>psql -h -p 5432 -U postgres -d postgres

The error accused is that there is no postgresql service running on host / TCP on port 5432.

Any idea ?

Does the administrative tools or the PostgreSQL server itself not connect via the HOSTSNI protocol?

4

Took a closer look. You don't have an entrypoint defined for 5432. Just 80 and 443.
To match your tcp router rule it would need to be:

      - "--entrypoints.postgres.address=:5432"
5

Very well observed Caklwl.

I put the entry point, as you mentioned ( - "--entrypoints.postgres.address=:5432" ) in the traefik container configuration and the error " That there is no postgresql service running on host / TCP on port 5432." disappeared.

But now the scenario is as follows:

  1. Traefik performs well;
  2. PostgreSQL performs well;
  3. PgAdmin4 runs fine and connects to PostgreSQL via the service name wspgsql11detran

But the connection via the client tool on the docker host console, is eternally in a loop and does not connect. Something like :

#> psql -h wspgsql11detran.wslan -p 5432 -U postgres -d postgres
and ... and ... and ...nothing happens

Here's the new snippet of the traefik container configuration

...
services:
  wstraefik:
    image: traefik:v2.6
    labels:
    - "traefik.enable=true"
    - "traefik.http.routers.http-catchall.rule=hostregexp(`{host:.+}`)"
    - "traefik.http.routers.http-catchall.entrypoints=web"
    - "traefik.http.routers.http-catchall.middlewares=redirect-to-https@docker"
    - "traefik.http.middlewares.redirect-to-https.redirectscheme.scheme=https"    
    command:
      - "--providers.docker.endpoint=unix:///var/run/docker.sock"
      - "--providers.docker.swarmMode=false"
      - "--providers.docker.exposedbydefault=true"
      - "--providers.docker.network=ntwrk_docker"
      - "--entrypoints.web.address=:80"
      - "--entrypoints.websecure.address=:443"
      - "--entrypoints.postgres.address=:5432"
      - "--certificatesresolvers.letsencryptresolver.acme.httpchallenge=true"
      - "--certificatesresolvers.letsencryptresolver.acme.httpchallenge.entrypoint=web"
      - "--certificatesresolvers.letsencryptresolver.acme.email=marcos.nobre@websis.com.br"
      - "--certificatesresolvers.letsencryptresolver.acme.storage=/letsencrypt/acme.json"
      - "--api=true"
      - "--api.dashboard=true"
      - "--api.insecure=true"
    container_name: wstraefik
    restart: unless-stopped
    networks:
      - netdocker
    ports:
      - 80:80
      - 443:443
      - 8080:8080
      - 5432:5432
      - 3306:3306
    volumes:
      - /etc/localtime:/etc/localtime:ro
      - /var/run/docker.sock:/var/run/docker.sock:ro
      - traefik-certificates:/letsencrypt
    labels:
      - "traefik.enable=true"
      - "traefik.http.routers.dashboard.service=api@internal"
      - "traefik.http.routers.dashboard.rule=Host(`trfk-dashboard.wslan`)"
      - "traefik.http.routers.dashboard.entrypoints=web"
      - "traefik.http.services.dashboard.loadbalancer.server.port=80"

Here is the snippet of the postgresql container configuration

services:
  wspgsql11detran:
    container_name: ws-pg11-detran
    image: ws-kartoza-pgpgis:11.0-2.5
    deploy:
        resources:
            limits:
              cpus: 1.5
              memory: 4096M
    networks:
      - netdocker
    volumes:
      - vlmpg11detran:/var/lib/postgresql/
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASS=pg01
      - POSTGRES_DB=postgres
      - ALLOW_IP_RANGE=0.0.0.0/0
      - POSTGRES_MULTIPLE_EXTENSIONS=postgis,hstore,postgis_topology,postgis_raster,pgrouting,unaccent
      - DEFAULT_ENCODING="UTF8"
      - DEFAULT_COLLATION="pt_BR.UTF-8"
      - DEFAULT_CTYPE="pt_BR.UTF-8"
    labels:
      - "traefik.enable=true"
      - "traefik.tcp.routers.wspgsql11detran.entrypoints=postgres"
      - "traefik.tcp.routers.wspgsql11detran.rule=HostSNI(`*`)"
      - "traefik.tcp.routers.wspgsql11detran.tls=false"
#      - "traefik.tcp.routers.wspgsql11detran.tls.certresolver=lets"
      - "traefik.tcp.services.wspgsql11detran.loadBalancer.server.port=5432"
      - "traefik.tcp.routers.wspgsql11detran.service: wspgsql11detran"

Do you have any new reviews?

6

SOLVED !!!!

With the help of CAKLWL who pointed out syntax/typing errors, I was able to successfully access a container running PostgreSQL database from the linux console outside the docker host.

Next I will highlight points of my configuration of Traefik containers and also PostgreSQL that made it possible to access it with the database client tools.

Traefik docker-compose.yml

services:
  wstraefik:
    image: traefik:v2.6
    labels:
    - "traefik.enable=true"
    - "traefik.http.routers.http-catchall.rule=hostregexp(`{host:.+}`)"
    - "traefik.http.routers.http-catchall.entrypoints=web"
    - "traefik.http.routers.http-catchall.middlewares=redirect-to-https@docker"
    - "traefik.http.middlewares.redirect-to-https.redirectscheme.scheme=https"    
    command:
      - "--providers.docker.endpoint=unix:///var/run/docker.sock"
      - "--providers.docker.swarmMode=false"
      - "--providers.docker.exposedbydefault=true"
      - "--providers.docker.network=ntwrk_docker"
      - "--entrypoints.web.address=:80"
      - "--entrypoints.websecure.address=:443"
      - "--entrypoints.postgres.address=:5432"   <<<<<<<<<<<<<<<< to access PostgreSQL
      - "--entrypoints.mysql57.address=:3306"   <<<<<<<<<<<<<<<<< to access MySQL
      - "--certificatesresolvers.letsencryptresolver.acme.httpchallenge=true"
      - "--certificatesresolvers.letsencryptresolver.acme.httpchallenge.entrypoint=web"
      - "--certificatesresolvers.letsencryptresolver.acme.email=marcos.nobre@websis.com.br"
      - "--certificatesresolvers.letsencryptresolver.acme.storage=/letsencrypt/acme.json"
      - "--api=true"
      - "--api.dashboard=true"
      - "--api.insecure=true"
    container_name: wstraefik
    restart: unless-stopped
    networks:
      - netdocker
    ports:
      - 80:80
      - 443:443
      - 8080:8080
      - 5432:5432   <<<<<<<<<<<<<<<< to access PostgreSQL
      - 3306:3306  <<<<<<<<<<<<<<<<< to access MySQL
    volumes:
      - /etc/localtime:/etc/localtime:ro
      - /var/run/docker.sock:/var/run/docker.sock:ro
      - traefik-certificates:/letsencrypt
    labels:
      - "traefik.enable=true"
      - "traefik.http.routers.dashboard.service=api@internal"
      - "traefik.http.routers.dashboard.rule=Host(`trfk-dashboard.wslan`)"
      - "traefik.http.routers.dashboard.entrypoints=web"
      - "traefik.http.services.dashboard.loadbalancer.server.port=80"

and know the PostgreSQL docker-compose.yml

services:
  wspgsql11detran:
    container_name: ws-pg11-detran
    image: ws-kartoza-pgpgis:11.0-2.5
#    user: "1001:997"
    deploy:
        resources:
            limits:
              cpus: 1.5
              memory: 4096M
    networks:
      - netdocker
    volumes:
      - vlmpg11detran:/var/lib/postgresql/
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASS=pg01
      - POSTGRES_DB=postgres
      - ALLOW_IP_RANGE=0.0.0.0/0
      - POSTGRES_MULTIPLE_EXTENSIONS=postgis,hstore,postgis_topology,postgis_raster,pgrouting,unaccent
      - DEFAULT_ENCODING="UTF8"
      - DEFAULT_COLLATION="pt_BR.UTF-8"
      - DEFAULT_CTYPE="pt_BR.UTF-8"
    labels:
# -------------------------------------------
# para invocar por TCP
      - "traefik.enable=true"
      - "traefik.tcp.routers.wspgsql11detran.entrypoints=postgres"
      - "traefik.tcp.routers.wspgsql11detran.rule=HostSNI(`*`)"
      - "traefik.tcp.routers.wspgsql11detran.tls=false"
      - "traefik.tcp.services.wspgsql11detran.loadBalancer.server.port=5432"
      - "traefik.tcp.routers.wspgsql11detran.service=wspgsql11detran"

There was a syntax error on this line here
"traefik.tcp.routers.wspgsql11detran.service=wspgsql11detran"
I was using ".....service:wspgsql11detran" instead of ".......service=wspgsql11detran" - colon instead of equal sign

With this fixed and the containers running, I can access the postgresql server with :

[some-host]#> psql -h wspgsql11detran.wslan -p 5432 -U postgres -d postgres

and ...

Password for user postgres: 
psql (13.5 (Ubuntu 13.5-0ubuntu0.21.04.1), server 11.7 (Debian 11.7-2.pgdg100+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=# \l
                              List of databases
   Name    |  Owner   | Encoding | Collate |  Ctype  |   Access privileges   
-----------+----------+----------+---------+---------+-----------------------
 postgres  | postgres | UTF8     | C.UTF-8 | C.UTF-8 | 
 template0 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |          |          |         |         | postgres=CTc/postgres
 template1 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |          |          |         |         | postgres=CTc/postgres
(3 rows)

postgres=#

Total success !!!!!

1 Like
7

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.