Multiple Mysql communication through TCP with TLS based on SNI

[Dreimus]

Hello there !

I'm trying to make multiple database connexion though traefik. I got that hostSNI for non TLS routing won't work if the host is specific. So I configured two databases for TLS support by generating a openssl certificate.

The problem is that, even with a mysql ssl connection, I got a timeout.

Can you tell me what I'm missing ?

# traefik/static.yml
entryPoints:
  web:
    address: ":80"
    http:
      redirections:
        entryPoint:
          to: websecure
          scheme: https
    transport:
      lifeCycle:
        requestAcceptGraceTimeout: "90"
        graceTimeOut: "90"
      respondingTimeouts:
        readTimeout: "90"
        writeTimeout: "90"
        idleTimeout: "90"

  websecure:
    address: ":443"
    transport:
      lifeCycle:
        requestAcceptGraceTimeout: "90"
        graceTimeOut: "90"
      respondingTimeouts:
        readTimeout: "90"
        writeTimeout: "90"
        idleTimeout: "90"

  mysql:
    address: ":3306"

# docker-compose.ymlversion: "3.9"

services:
  # SOURCE : https://hub.docker.com/_/mariadb
  db1:
    image: mariadb
    restart: unless-stopped
    volumes:
      - .docker/mariadb/my.cnf:/etc/mysql/my.cnf
      - .docker/mariadb/certs:/etc/mysql/certs
    environment:
      - MYSQL_DATABASE=sample1
      - MYSQL_ROOT_PASSWORD=root
    labels:
      - "traefik.enable=true"
      - "traefik.docker.network=gateway"
      - "traefik.tcp.routers.db1.entrypoints=mysql"
      - "traefik.tcp.routers.db1.rule=HostSNI(`db1.app.localhost`)"
      - "traefik.tcp.routers.db1.tls.passthrough=true"
      - "traefik.tcp.services.db1.loadbalancer.server.port=3306"
    networks:
      - default
      - sample

  db2:
    image: mariadb
    restart: unless-stopped
    volumes:
      - .docker/mariadb/my.cnf:/etc/mysql/my.cnf
      - .docker/mariadb/certs:/etc/mysql/certs
    environment:
      - MYSQL_DATABASE=sample2
      - MYSQL_ROOT_PASSWORD=root
    labels:
      - "traefik.enable=true"
      - "traefik.docker.network=gateway"
      - "traefik.tcp.routers.db2.entrypoints=mysql"
      - "traefik.tcp.routers.db2.rule=HostSNI(`db2.app.localhost`)"
      - "traefik.tcp.routers.db2.tls.passthrough=true"
      - "traefik.tcp.services.db2.loadbalancer.server.port=3306"
    networks:
      - default
      - sample

networks:
    # gateway is just a bridge for traefik to use multiple container stack communication
    default:
        external: true
        name: gateway

    sample:
        internal: true

# .docker/mariadb/my.cnf
[mysqld]
ssl-ca=/etc/mysql/certs/mysql.crt
ssl-cert=/etc/mysql/certs/mysql.crt
ssl-key=/etc/mysql/certs/mysql.key
bind-address            = 0.0.0.0
proxy-protocol-networks = *

Some screenshots :
Routing :

Detail :

Thanks !

[bluepuma77]

You need to load your custom TLS certs via dynamic config file in Traefik static config with provider.file, then enable TLS on the router.

[Dreimus]

Hello @bluepuma77,

The routing configuration is set on passthrough, why should I config my certs in the dynamic config file in traefik ?
I got another configuration file to set the certificates for my other routes.

[bluepuma77]

When you don’t have the TLS cert available in Traefik, then you can only use HostSNI(`*`).

This means you need a dedicated port for a single service because Traefik can’t read the HostSNI and differentiate between services.

[lucasctd]

@Dreimus Hello buddy.

Were you able to make it work? I am interested =)

Thx for sharing.

[bluepuma77]

@bluepuma77

When you don’t have the TLS cert available in Traefik, then you can only use HostSNI(*).

This means you need a dedicated port for a single service because Traefik can’t read the HostSNI and differentiate between services.

So use multiple entrypoints on different ports, each for a different target service.

[lucasctd]

That's what I am doing now actually, but I wanted to use a single port ^^
Support for SNI on Postgres seems to be available on v3

Support SNI routing with Postgres STARTTLS connections by rtribotte · Pull Request #9377 · traefik/traefik (github.com)

[bluepuma77]

To use HostSNI() you need to have a certificate for Traefik, either a custom purchased one or let LetsEncrypt create one for you.

Forwarding the connection to your DB, you need to share the same cert with the DB, or use a different cert for the DB (which is known to Traefik or use insecureSkipVerify) or potentially use no encryption at all.

And it needs to be TLS, no SSL or SSH certs.

[yassine]

Hi,

Did you find a solution ? I got +/- the same problem!
Do I miss something ?

Thanks

[bluepuma77]

So you use Traefik with LetsEncrypt with TLS passthrough.

That means you have a LE TLS cert for Traefik for the two domains and you pass the encrypted traffic on.

Did you import the LE cert in your DB to encrypt the forwarded traffic?

[bmeneg]

Same issue here. I have the certificates placed in the dynamic config file and it has worked for other services I have running on HTTP routes. But on TCP routes things are not working:

tls:
  certificates:
    - certfile: /traefik/letsencrypt/live/example.com/fullchain.pem
      keyfile: /traefik/letsencrypt/live/example.com/privkey.pem
...
tcp:
  routers:
    foo-db:
      entrypoints:
        - "mariadb"
      rule: "HostSNI(`foo-db.example.com`)"
      service: "foo-db"
      tls: {}
  services:
    foo-db:
      loadBalancer:
        servers:
          - address: "10.0.0.33:8086"

MariaDB container file is correctly mapping 3306 to 8086 and I have all ports open on firewall and whatnot, it works just fine with non-SSL connection. At the same time, I have the certs into my MariaDB's container and a configuration file pointing to them: when using the wrong permissions to the certs there was an error during container initialization, then fixing it there were no errors on the output, meaning mariadb was able to read the certs.

[bluepuma77]

You want Traefik and your DB to use the same TLS cert? Then pass the encrypted TLS traffic from Traefik with TLS passthrough to the target service. (Doc)

[bmeneg]

Well pointed. But still, the connection seems to hang. Are there any option I can activate to check where it's hanging? When I stop traefik container at server side, the client returns with the following error:

ERROR 2013 (HY000): Lost connection to MySQL server at 'handshake: reading initial communication packet', system error: 11

At the same time, I don't really need SNI if I just change to a different entrypoint (different port), and make the rule check for HostSNI(*), letting the database itself handle the SSL certificates verification, right?

[bluepuma77]

Yes. You can pass the encrypted TCP connection through Traefik with HostSNI(`*`) and use a different port/entrypoint per target service, not enabling any TLS in Traefik.

Check simple Traefik TCP example.

[Dreimus]

@bluepuma77

You can pass the encrypted TCP connection through Traefik with HostSNI(*) and use a different port/entrypoint per target service, not enabling any TLS in Traefik.

Exactly what we are trying to avoid. Our goal is to target multiple database host with the same port.

Personnaly I surrended, too much wasting time for a poor ROI. I now have configured ssh over docker to use the web url using traefik, then using ssh tunneling for reaching the database.

[cotjoey]

Hello @Dreimus ,

I ran into a similar issue, but in my case with Postgres. I was able to get it to work, but with those things in place:

1. I am using Traefik v3 (in my case v3.0.0-beta3); The STARTTLS feature for Postgres is only in that version onward (I know you are using MariaDB, but just FYI);

2. I had to set the database port in my container to be the same as exposed on the host (i.e. 5432 on host and 5432 in container); This was the cause of my connection timeout;

3. I don't use TLS for the database in the container, only at the Traefik entrypoint with the labels;

4. I need to set at least one TCP entrypoint port for all the DB connections on Traefik (i.e. 5432). If your Traefik runs on the same host as your DB, you will need to change this so you don't have a port conflict.

5. I set the following Traefik labels:

   "traefik.enable=true",
   "traefik.tcp.routers.postgres-db.rule=HostSNI(`postgres-db.${domain}`)",
   "traefik.tcp.routers.postgres-db.tls=true"
   "traefik.tcp.routers.postgres-db.entrypoints=postgres"
   

6. Make sure your have a default cert on Traefik for ${domain} or you will have to specify it. I don't use passthrough as I don't have TLS enabled on the DB.

7. I think in your case you will have to specify a service backend port and address (in the Traefik labels) if you don't have service discovery capabilities in your system.

With all of that in place, I am able to have one port and use HostSNI to send the traffic to my different databases. i.e.:

my-db-1.example.com:5432 (goes to db1 backend container)
my-db-2.example.com:5432 (goes to db2 backend container)
my-db-3.example.com:5432 (goes to db3 backend container)

Hope this was useful to you.
Joey

[vladoportos]

@Dreimus did you manage to get it to work ? I'm stuck at the same issue. The best I could do is get the response from mysql container via openssl s_client -connect demo.domain.com:3306 that gave me in the end:

---
read R BLOCK
I
8.2.0   m@=FrNXk�����eLQHYNvjA"caching_sha2_password2��#08S01Got timeout reading communication packetsclosed

Which mean it got to the container... but no client I could find can actually connect to the mysql at all

[bluepuma77]

Do you know if 8.2.0 is a database transport version?

Share your full Traefik static and dynamic config, and docker-compose.yml if used.

[vladoportos]

8.2.0 is the DB version installed in the pod.

I use this deployment:

apiVersion: apps/v1
kind: Deployment
metadata:
  name: mysql-test
  namespace: demo-dev
  labels:
    app: mysql-test
spec:
  replicas: 1
  selector:
    matchLabels:
      app: mysql-test
  template:
    metadata:
      labels:
        app: mysql-test
    spec:
      containers:
      - name: mysql
        image: mysql:8.2.0
        env:
        - name: MYSQL_ROOT_PASSWORD
          value: "rootpassword"
        - name: MYSQL_DATABASE
          value: "testdb"
        ports:
        - containerPort: 3306
          name: mysql
---
apiVersion: v1
kind: Service
metadata:
  name: mysql-test-service
  namespace: demo-dev
spec:
  selector:
    app: mysql-test
  ports:
    - protocol: TCP
      port: 3306
      targetPort: 3306

IngressTCP

apiVersion: traefik.containo.us/v1alpha1
kind: IngressRouteTCP
metadata:
  name: demo-helm-route
  namespace: demo-dev
spec:
  entryPoints:
    - mysql
  routes:
  - match: HostSNI(`demo.domain.com`)
    services:
    - name: mysql-test-service
      port: 3306
  tls:
#    passthrough: true
    secretName: tls-demo-dev-helm <- this is valid certificate for domain, cert-manager got it

Traefik have:
--entrypoints.mysql.address=:3306/tcp

But from what I have been reading today, it seems its not possible to work. More of MySQL shitty protocol implementation then anything else

[NoOverflow]

Hey, did you have anymore luck with this ? I can get routing working properly with SNI, and I can see I'm getting routed to the right container using ncat --ssl:

/tmp$ ncat --ssl test.redacted 3306 -v
Ncat: Version 7.80 ( https://nmap.org/ncat )
Ncat: Subject: CN=TRAEFIK DEFAULT CERT
Ncat: Issuer: CN=TRAEFIK DEFAULT CERT
Ncat: SHA-1 fingerprint: 540A B291 45F9 59D0 FA2C 713B 8CBB 61CE D8E7 3D5E
Ncat: Certificate verification failed (self-signed certificate).
Ncat: SSL connection to 82.65.150.23:3306.
Ncat: SHA-1 fingerprint: 540A B291 45F9 59D0 FA2C 713B 8CBB 61CE D8E7 3D5E
I
8.3.0  G-LdPh-��!��qP;j>Ef\/"(mysql_native_password2��#08S01Got timeout reading communication packets

But no matter how hard I try I cannot get the mysql client to connect, and I just end up with the same error message as you:

:/tmp$ docker run mysql:8.3.0 mysql -u root -h redacted --port 3306 --verbose 4 --tls-sni-servername redacted
ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0

I'm honestly starting to think that something is wrong with the way the mysql client opens a session and that it may be incompatible with traefik, since it works fine with postgresql.