MySQL TLS is broken at 8.0.23-0ubuntu0.20.04.1

[David Ames]

WARNING: For production deployments on Focal with mysql-innodb-cluster related to vault's certificates do NOT upgrade mysql-router to 8.0.23-0ubuntu0.20.04.1 [0] until we have a charm fix in place.

It seems the most recent version of mysql-router 8.0.23-0ubuntu0.20.04.1 [0] has broken our implementation of TLS. Six days ago this [0] landed in Focal.

At first glance, it appears the previous behavior of TCP proxying the mysql connections to the mysql-innodb-cluster nodes and therefore allowing TLS to be terminated at the cluster node not at the router has changed. It now appears TLS termination is occurring at the mysql-router.

We cannot simply point python clients at the auto-generated CA for the router as the python clients do hostname validation.

We will need to implement the certificates relation for mysql-router and use a Vault CA and certificates that include 127.0.0.1.

[0] https://launchpad.net/ubuntu/+source/mysql-8.0/8.0.23-0ubuntu0.20.04.1

[David Ames (thedac)]

WARNING: For production deployments on Focal with mysql-innodb-cluster related to vault's certificates do NOT upgrade mysql-router to 8.0.23-0ubuntu0.20.04.1 [0] until we have a charm fix in place.

OK, this is a bad news good news situation.

The good news is that mysql-router now does what every other mysql client (mysql, mysqlsh) does and TLS encrypts communication to mysql by default. So once we are finished we will have a simpler more complete TLS story for mysql.

The bad news is this breaks our current topology for deployments. By simply not relating mysql-innodb-cluster to vault we get TLS for free. The difficulty is for upgrades. The charms will need to learn to unset ssl_ca on their respective relations.

The really bad news is that any production deployments on Focal with mysql-router and TLS enabled (mysql-innodb-cluster related to vault's certificates relation) will break if mysql-router is updated to 8.0.23-0ubuntu0.20.04.1 [0]. Please avoid doing this if at all possible.

If the worst case scenario has occurred, and mysql-router has been upgraded to 8.0.23-0ubuntu0.20.04.1 communication to the database from python clients will fail.

The tedious work around is as follows:

juju remove-relation mysql-innodb-cluster:certificates vault:certificates

# We need to force mysql-innodb-cluster to re-write its config so change any value in the mysqld.conf. i.e max-connections ++
juju config mysql-innnodb-cluster max-connections=601

# For every db-router relation ID we need to unset ssl_ca
for relid in $(juju run --unit mysql-innodb-cluster/leader -- "relation-ids db-router"); do
  juju run --unit mysql-innodb-cluster/leader -- "relation-set $relid ssl_ca="
done

# For every instance of mysql-router we need to unset ssl_ca. i.e. keystone-mysql-router, cinder-mysql-router etc
for relid in $(juju run --unit mysql-router/0 -- "relation-ids shared-db"); do
  juju run --unit mysql-router/0 -- "relation-set $relid ssl_ca="
done

We will begin working on an upgrade path for the mysql-router and mysql-innodb-cluster charms

[0] https://launchpad.net/ubuntu/+source/mysql-8.0/8.0.23-0ubuntu0.20.04.1

[David Ames (thedac)]

The upstream link [0] documents the change in behavior.

I will explore the PASSTHROUGH option to maintain current behavior and continue using CA signed certificates.

client_ssl_mode = PASSTHROUGH
server_ssl_mode = AS_CLIENT

[0] https://dev.mysql.com/doc/mysql-router/8.0/en/mysql-router-configuration-tls.html

[David Ames (thedac)]

UPDATED WORKAROUND

Slightly less tedious workaround in case the mysql-router package is upgraded, for each mysql instance (each instantiation and unit):

sed -i 's/client_ssl_mode=PREFERRED/client_ssl_mode=PASSTHROUGH/' mysqlrouter.conf
systemctl restart <app>-mysql-router

[Aurelien Lourot (aurelien-lourot)]

https://review.opendev.org/c/openstack/charm-mysql-router/+/774008
https://review.opendev.org/c/openstack/charm-mysql-innodb-cluster/+/774033

[David Ames (thedac)]

The mysql-router change [0] has landed. The mysql-innodb-cluster change [1] has passed tests and is ready to land.

[0] https://review.opendev.org/c/openstack/charm-mysql-router/+/774008
[1] https://review.opendev.org/c/openstack/charm-mysql-innodb-cluster/+/774033