In my previous blogs I told you to enable SSL/TLS and configure it to check the CA. So I followed my advice and did all that. Great!
--ssl-mode setting was used a few times as a solution. And it has a setting we didn't use yet:
VERIFY_IDENTITY. In older MySQL versions you can use
--ssl-verify-server-cert. Both turn on hostname verification.
Get any certificate which is trusted by the configured CA, this can for example be a certificate from a development machine. And use that with a man-in-the-middle proxy.
Then the client:
- Checks if SSL is uses (
- Verify if the certificate is signed by a trusted CA (
Both checks succeed. But the certificate might be for testhost01.example.com and the database server might be prod-websitedb-123.example.com.
Browsers by default verify hostnames, MySQL does not.
Turning on hostname validation
--ssl-mode=VERIFY_IDENTITY and everything should be fine?
Well that might work for simple setups, but would probably fail for more complex setups.
This is because you might have a master-slave setup with loadbalancer in front of it. So your webapp connect to mydb-prod-lb.example.com which might be served by mydb1.example.com (master) or mydb2.example.com (slave). There might or might not be any automatic read/write splitting.
So then just configure the loadbalancer be the endpoint of the SSL connection? Well no, because most loadbalancers don't know how to speak the mysql protocol, which is needed to setup the SSL connection.
Ok, then just configure both servers with the certificate for mydb-prod-lb.example.com and everything should work. And it does!
But then you want to change the replication connection to also use SSL, but now the certificates and hostnames don't match anymore as they connect directly.
The same might be true for mysqldump or mysqlbinlog instances running on a separate backup server.
But there is a X.509 extension available which can be used: 'SubjectAlternativeName' a.k.a. SAN. (Not to be confused with Storage Area Networking). This allows you to have a certificate with multiple hostnames.
So for both hosts put their own hostname and the loadbalancer hostname in there.
But unfortunately that doesn't work yet. MySQL doesn't support this.
So yes, do enable hostname verification, but probably not everywhere yet.