Multiple Mysql communication through TCP with TLS based on SNI

Dreimus · June 12, 2023, 11:05am

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 · June 12, 2023, 3:26pm

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 · June 13, 2023, 7:47am

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 · June 13, 2023, 7:56am

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 · July 12, 2023, 3:58am

@Dreimus Hello buddy.

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

Thx for sharing.

bluepuma77 · July 12, 2023, 11:27am

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

lucasctd · July 12, 2023, 1:56pm

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 · July 12, 2023, 3:52pm

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 · August 2, 2023, 1:04pm

Hi,

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

Thanks

bluepuma77 · August 2, 2023, 1:43pm

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 · August 13, 2023, 12:37am

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 · August 13, 2023, 7:25am

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 · August 13, 2023, 2:51pm

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 · August 13, 2023, 4:08pm

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 · August 14, 2023, 6:58am

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 · August 17, 2023, 12:48pm

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:

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);
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;
I don't use TLS for the database in the container, only at the Traefik entrypoint with the labels;
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.

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"

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.
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 · October 29, 2023, 6:39pm

@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 · October 29, 2023, 7:16pm

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 · October 29, 2023, 7:27pm

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 · March 6, 2024, 4:47pm

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.

Topic		Replies	Views
Traefik v2.0: Help setting up TCP w/TLS for MySQL Traefik v2 (latest) docker , tcp	4	3335	August 12, 2021
Can't connect to MySQL server: Connection refused Traefik v2 (latest) docker	21	10914	October 13, 2020
Creating a TCP Router for Docker Swarm Traefik v2 (latest) docker-swarm , tcp	1	1243	June 20, 2023
Can't figure why Traefik serve default SSL cert for TCP TLS passthrough Traefik v2 (latest) docker , tcp , udp	7	2285	February 6, 2023
Configuration of non http port without TLS Traefik v2 (latest) docker , tcp	4	5613	May 14, 2020

Multiple Mysql communication through TCP with TLS based on SNI

Related Topics