Different SSL setups for MySQL

In this blog post I will describe different ways of using SSL with the MySQL database server.

What does SSL give you?

You might use MySQL replication over the internet or connect to MySQL over the internet.

Another posibility is that you connect over an enterprise network to which just too many people have access. This is especially an issue if you use an BYOD network.

SSL helps here by encrypting the network traffic to prevent against evesdropping. It also validates that you're talking to the correct server to prevent man-in-the-middle attacks.

And you can also use SSL client certificates together with an password as two factor authentication.

SSL is not the only option, you could use SSH and many MySQL GUI clients like MySQL Workbench support this. But you can't easily use SSH with a python script or mysqldump.

Things that could go wrong

Using SSL is almost always better than not using SSL at all. So there is not much you could do wrong.

But there are a few things that could go wrong:

A false sense of security

You think you are protected by using SSL, but you forgot some option somewhere and the connection falls back to a non-SSL connection. Make sure you set SSL as required so this can't happen and use Wireshark or a similar tool to inspect the traffic to see if it is really encrypted.

Not renewing certificates in time

You should have some mechanism in place to notify you that your certificate is about to expire. This could be a nagios check, a note in your calendar or an email alert from your CA. If you let your certificates expire then your clients should stop with an error.

Performance

If performance is important then you should benchmark with and without SSL to see what the impact is. You could try a different cipher and try both OpenSSL and YaSSL to see what performs best.

Tools which use traffic inspection

If you are using a tool like VividCortex, pt-query-digest based on a pcap file then you should make sure these continue to work after you've deployed SSL. This can be done by giving them your key and use a cipher which doesn't use Diffie-Hellman (DH) or let them use a different source like performance_schema and/or your slow query log. But this depends on what is supported with that application. This might also includes some load balancer setups.

How SSL with MySQL is different to SSL in your browser

The browser by default has a big list of trusted CA's. MySQL doesn't trust anything by default. So that a big difference. This is more similar to how OpenVPN uses SSL.

For both a MySQL server and a Web server you have to enable SSL. And with both it's possible to require client certificates, but that's much more common with MySQL.

There are some other minor differences like protocol support: MySQL only uses TLS v1.0. And MySQL doesn't use hostname validation by default, so your certificate might be for db1.example.com and might be used on the host db2.example.com. Your browser might use OCSP, CRL's or CRLsets to verify if a certificate is still valid. MySQL is only capable of using a CRL sice MySQL 5.6.

Setup 1: Server/Client with Internal CA

This is the most basic setup and where I created mysslgen for.

You should create a key for the server in PKCS#1 format as PKCS#8 won't work. And then create a CSR (Certificate Signing Request) which can be signed by your CA. The end result is a CA certificate a server certificate and a server key. I always use PEM formatted certificates.

What you need to configure on the server:

• SSL server certificate and key (ssl-cert and ssl-key)
• SSL CA certificate (ssl-ca)

What you need to configure on the client:

• SSL CA certificate (ssl-ca)

To require the use of SSL for an account you should use REQUIRE SSL with your GRANT statement.

Setup 2: Server/Client with Internal CA and client certificate

For this you need to enable SSL on the server and create a certificate for the client.

On the client you need to specify the client certificate and key with ssl-cert and ssl-key.

Now you must use REQUIRE X509 to require a valid certificate. This won't work if you use the CA for more than this one MySQL server as other people might be able to get a valid certificate.

You might use REQUIRE SUBJECT '/CN=myclient.example.com' to limit the certificates which can be used.

In this setup each certificate is issued by your CA this gives you a lot of control about which certificates are issued. The result is that all certificates from your CA are trusted.

Setup 3: Server/Client with Public CA

This is similar to setup 2. But a public CA will issue much more certificates and will probably also revoke some of them.

Here you should use --ssl-verify-server-cert to make sure the server has a certificate which matches the hostname. Otherwise someone might use a certificate which is valid, but just not for that hostname to create a man in the middle attack.

Also you should use REQUIRE X509 and require a SUBJECT and optionally require a ISSUER for each user.

Setup 4: Server/Client with multiple public CA's

This looks almost like what webbrowsers use.

You could either use a certificate bundle (one file with multiple certificates) and specify this file with ssl-ca.

On Ubuntu this is made available by the ca-certificates package on /etc/ssl/certs/ca-certificates.crt

Another option is to specify a ssl-capath instead of using ssl-ca. Then you could set it to /etc/ssl/certs . If you want to use a custom dir with CA's then you need to run the c_rehash utility from OpenSSL on it to generate the correct symlinks. This only works if you use OpenSSL.

Setup 5: Replication

This works by setting MASTER_SSL_* parameters in the CHANGE MASTER TO command.

Make sure to use MASTER_SSL_VERIFY_SERVER_CERT if this is possible to enable hostname verification.

The only way to be really sure that the connection is using SSL is to set SSL as required for the replication user. The output of SHOW SLAVE STATUS output will only show if SSL is allowed, not that it's in active use.

One CA per server or a centralized CA?

You could use a CA per server and then issue a client certificate for each client. But then you will need a different client certificate for each server you want to connect to. Also you would have much more CA certificates to distribute.

So you might want to use one CA instead of a per-server CA.

What to do if things go wrong

If one of the private keys (client, server or CA) get's compromised there are a few things you can do:

1. Nothing, just wait till the certificate expires. This only works if you are using short lived certificates (e.g. certificates which are valid for 7 days). This will still leave a window in which you are vulnerable. You might have another control in place which mitigates this risk. The problem with this is that you need to restart MySQL every 7 days and you need some mechanism to distribute the certificates in an automated fashion.

2. Re-create all certificates and keys (including the CA). This might only be feasable if you have a very small CA with just a few users and one or more servers.

3. Use a CRL This is a Certificate Revocation list. This only works if your certificates have proper serial numbers. Also if you use multiple CA's then you must have a CRL for each and then bundle them in one file and specify that with ssl-crl or in a directory with symlinks created by c_rehash and specified with ssl-crlpath (OpenSSL only). If you use CRL's then you need to use MySQL 5.6 or newer and have some method for updating the CRL, also for all clients.

Note: You must always re-create the private key if leaked, using the old CSR (Certificate Signing Request) is not enough.

Note on client configuration

The ssl-ca parameter might be set in the [client] section of your my.cnf. However mysqlbinlog then stops working. So use loose-ssl-ca in the client section instead.

The ssl-key and ssl-cert should be in the [client] section of your ~/.my.cnf and those file should be properly protected.

It is currently not possible to add SSL settings the a 'login-path' as created by my_config_editor.

Note on ciphers

With ssl-cipher you can set a certain cipher, but with YaSSL this is quite limited. When you use OpenSSL you could use a cipher suite, which is a certain set of ciphers with some filters and an order.

Note on YaSSL, OpenSSL and status variables

It's hard to determine if your MySQL server uses OpenSSL or YaSSL. There are some methods like using ldd /path/to/mysqld. By default MySQL Community Edition uses YaSSL and MySQL Enterprise Edition uses OpenSSL.

Depending on the SSL implementation some status variables might not get updated correctly.

To make sure you're fully protected against any SSL vulnerabilities you should follow the Oracle Critical Patch Update and keep your MySQL and OpenSSL packages up to date.

Note on running a CA

This is not as easy as it seams. It is easy to get started, but doing it right is hard. Make sure you understand how to issue a CRL, what X509 extensions are and which of them you must use.

One of the many websites which tells you how to setup your own CA: How to act as your own certificate authority (CA) - jamielinux

Also many of the public CA's offer to host your private CA for you.

Books on SSL/TLS

There is a book about SSL I can recommend: Bulletproof SSL and TLS

And there is a very entertaining book about the history of cryptography: The Code Book