The internet has changed to a place where most protocols like HTTP etc now use secure connections with TLS by default.
While both HTTP and the MySQL Protocol use TLS for secure connections there are still many differences which make it difficult for MySQL to benefit from the same advancements as HTTP has seen in the last so many years.
What is TLS?
TLS stands for Transport Layer Security and is the successor of SSL (Socket Layer Security). SSL and TLS are often used interchangably, while this isn’t correct strictly speaking. What TLS provides is a standardized way to encrypt in transit traffic and authenticate the other end of the connection. TLS when used together with HTTP is known as HTTPS, for secure HTTP.
How TLS works in HTTPS
The client (webbrowser) connects to a server on port 443. Then negitiation is done to agree on what encryption method is to be used. The server presents the client with a certificate, which the client then verifies against the system trust store.
For this to work the server needs to have a certificate that is signed by a cerfificate authority (CA) that is known to the client.
The CA will sign the certificate after doing some checks. Nowadays this is often a fully automated process. There are multiple ways to prove ownership of the domain to the CA. You can put a special file on the webserver or add a DNS record. See ACME for details.
What this provides is that the server authenticates itself to the client and that the connection is secure.
How TLS works with MySQL
The client (MySQL Client, etc) connects to the server. When both the
client and server set the CLIENT_SSL flag then the
connection switches to TLS. So the TLS part doesn’t start at the
beginning of the conversation as is the case with HTTPS. This allows the
MySQL protocol to do both TLS and non-TLS connections on the same port,
where HTTP and HTTPS use two ports (80 and 443).
Then the usual negitioation etc happens, but the server certificate is likely not signed by one of the CA’s in the system truststore, so the user might have to specify the certificate of the CA when setting up the connection. And when the user doesn’t specify a CA certificate the client might not verify the certificate of the server against a CA.
So for the server part there are multiple options: - MySQL can now generate a certificate on it’s own. But it won’t be signed by a proper CA, instead it is self signed. - You could setup your own CA and have that sign the certificate. For example with Lemur (From Netflix) or with a more bare bones OpenSSL setup as described here. - Get certificates from a proper CA, just like you would with HTTPS.
Both HTTPS and the MySQL protocol allow mutual-TLS or mTLS where not only the server authenticates to the client, but also the clients presents a certificate to the server. However this is much more common with MySQL.
Getting certificates for internal hostnames from a proper CA is hard
The common challenge types for Let’s Encrypt are all dependent on having a server that has a public DNS record. And while Let’s Encrypt supports wildcard certifictes, these are usually less secure as they are (obviously) less restrictive on hostname and are more valuable when they end up in the wrong hands. So the best practice is to not use wildcard certificates. Other CA often have similar issues.
You could have a public DNS entry for your database server, but not allow any direct access from the internet, but this does expose the hostname of your server to the internet, which might be a problem for some.
And even if you’re ok with a public DNS entry, this means that
automating this is more difficult than setting up certbot
with Nginx, Apache, etc. as those often rely on publishing a challenge
token on a HTTP endpoint. One option is to use
certbot --standalone, which provides it’s own builtin
webserver for the challenge token, but this requires the server to have
port 80 open to the internet.
It is common to have database servers with only internal IP’s
(192.168.x.x, etc), which also makes this harder. And the
limited supply of IPv4 addresses globally also doesn’t help, however the
“easy” solution for that is IPv6.
My opinion on this: - Small organizations should rely on Let’s Encrypt or something similar and have database servers with a public DNS entry and a public IP and then rely on strict firewall rules for security. - Bigger organizations should consider to setup their own CA, including automated certificate renewal.
This resuls in insecure defaults
The MySQL client, MySQL Shell and most connecteors have a default ssl-mode
that is set to PREFERRED. This means it will use TLS if
available, but it won’t validate the certificate or the hostname.
| SSL Mode | Encrypted communication | Prevents against downgrade | Prevents against MitM |
|---|---|---|---|
| DISABLED | ❌ | N/A | ❌ |
| PREFERRED | If available | ❌ | ❌ |
| REQUIRED | ✅ | ✅ | ❌ |
| VERIFY_CA | ✅ | ✅ | Mostly |
| VERIFY_IDENTITY | ✅ | ✅ | ✅ |
With VERIFY_CA the server needs a valid certificate, but
the hostname of the server and that of the certificate doesn’t have to
match. This means that a stolen certificate and key for
dev.example.com could be used to do a MitM attack on
proddb1.example.com.
Note that with curl comes with a more secrue default,
which is similar to VERIFY_IDENTITY. It allows you to go
connect to an insecure server with --insecure. This is only
possible because webservers are expected to have CA signed
certificates.
Note that with the caching_sha2_password authentiction,
which is now the default the password is send in plain text over TLS. So
a MitM attack could reveal this password to an attacker.
As the MySQL connections are less likely to go over the public internet and as servers are less likely to have a certificate that can be easily verified without additional configuration this behavor seems reasonable to me. However I think making this more secure by default would be a good step to take.
The handshake makes it difficult to use some TLS offloading products
As the MySQL Protocol first needs the exchange of some packets before the TLS connection is setup.
This makes it more difficult for proxies/routers/loadbalancers/etc to support MySQL as they also need to implement a small subset of the MySQL protocol.
Note that OpenSSL can do this with
openssl s_client --starttls mysql ....
Long lived and stateful connections
With HTTPS you may have a persistent connection, but if it fails you can just retry the connection and things are usually fine as the session is not directly linked to the connection. And connections usually don’t stay open for long.
With MySQL the connection is linked to the session. And there may be
local variables, ongoing transactions etc that are linked to this
session. So if the connection is gone, these are all gone and you have
to re-authenticate and retry your transaction and set any variables that
you modified before. Also it isn’t uncommon to have long running
sessions for things like ALTER TABLE or heavy reporting
etc.
This makes it more difficult to change the X.509 certificate and key that the server uses as just restarting would distrupt users.
How MySQL made TLS easier to use
There used to be many more issues with TLS but Oracle MySQL has been working hard over the last few years to improve things.
So what things improved? I’ll list a few:
- Clients and connectors where changed to use TLS by default. This isn’t a perfect situation yet, but it is better for security than not using TLS at all.
- MySQL Community Edition was changed to use OpenSSL instead of YaSSL. This is mostly a licensing thing. This helps a lot for performance and it also brought support for TLSv1.2 and TLSv1.3.
- First
mysql_ssl_rsa_setupwas introduced to generate TLS certificates and a RSA keypair. And eventually this was integrated into the server. Especially for development setups etc this makes this a lot easier. - Support for reloading certificates, keys, etc without server restart.
