SSL/TLS in MySQL 5.7

MySQL 5.7 aims to be the most secure MySQL Server release ever, and that means some significant changes in SSL/TLS.  This post aims to tie together individual enhancements introduced over the span of several Development Milestone Releases (DMRs) into the larger initiative.  In the simplest terms, we hope to have a minimal TLS configuration enabled by default, and for connections to prefer TLS by default.  Let’s dig into the various aspects of this:

Generation of TLS key material

MySQL Server has long supported TLS connections, yet very few deployments are actually configured to leverage this.  This is partly because creation of key material – the certificates and keys needed to establish TLS connections – is a multi-step, extra, manual process.  Basic TLS concepts have to be understood, third-party software installed, series of commands executed, files distributed and MySQL Server configured.  We wanted to simplify that process, and have done so significantly.

MySQL Server 5.7.6 and later ship with a new program, mysql_ssl_rsa_setup.  On systems where OpenSSL is installed and in the path (openssl is understood as executable binary), mysql_ssl_rsa_setup will produce excellent-strength TLS keys and certificates.  These are placed in the –datadir (required argument).  The files produced are:

ca.pem               Self-signed CA certificate
ca-key.pem           CA private key
server-cert.pem      Server certificate
server-key.pem       Server private key
client-cert.pem      Client certificate
client-key.pem       Client private key
private_key.pem      Private member of RSA private/public key pair
public_key.pem       Public member of RSA private/public key pair

To prevent overwriting existing key material, no TLS key material will be generated if any of the first six files exist, and no RSA key material will be generated if any of the last two files exist.  The RSA key material is an alternative to connection TLS during the credential exchange process by the sha256_password authentication plugin for OpenSSL-linked client and server binaries.

In an effort to make this process even easier, most standard MySQL Server packages will automatically call mysql_ssl_rsa_setup after initializing the server.  Generic tarball and .ZIP packages require users to explicitly call mysql_ssl_rsa_setup, but other packages will do this automatically.

The documentation page describes the characteristics of the generated files – it’s worth reading.  Most importantly, the generated key material has appropriate file permissions, the certificate authority (CA) keys are self-signed, and used to sign the server and client keys – and expiration dates are set to 10 years in the future (for TLS key material) or to never expire (RSA key material).

Note also that the mysql_ssl_rsa_setup program works on Windows, provided OpenSSL is installed and the openssl.exe binary is found in the path).

Auto-discovery of key material

While mysql_ssl_rsa_setup makes key material generation a breeze, we also simplified how MySQL Server finds key material.  As you might expect, we’re now looking for key material in the server’s –datadir at startup.  If it exists, and no explicit configuration has been given to use alternate key material, MySQL Server will use it to configure TLS.  In other words, provided the TLS key material was generated into the correct –datadir and uses the file names specified above, there’s no more required configuration to enable TLS.  This is true whether using mysql_ssl_rsa_setup or another process to generate key material, and simplifies scripting setup of MySQL Server instances with TLS.

Preference for TLS

This is where default experiences will really change for many users, and this is intentional, and not without significant consideration.  Starting with clients based on libmysqlclient in 5.7.7, and eventually encompassing all Oracle-produced clients and drivers, connections over TLS will be preferred when connecting to a MySQL Server instance which supports TLS.  Typical MySQL installations will begin to use TLS by default.  This is what it looks like today:

D:\mysql-5.7.7-rc-winx64>bin\mysql --no-defaults -uroot -P3309 -e"STATUS;"
--------------
bin\mysql  Ver 14.14 Distrib 5.7.7-rc, for Win64 (x86_64)

Connection id:          3
Current database:
Current user:           root@localhost
SSL:                    Cipher in use is DHE-RSA-AES256-SHA
...

In a secure environment where the network is trusted, this security can be optimized away by explicitly disabling TLS, which will surely provide performance benefits.  Given the choices, though, we prefer to default to secure connections and let users make an explicit decision when to trade security for performance.  All clients and drivers will have a simple, single configuration option to disable TLS.  For the standard command-line clients, that option is –skip-ssl:

D:\mysql-5.7.7-rc-winx64>bin\mysql --skip-ssl -uroot -P3309 -e"STATUS;"
--------------
bin\mysql  Ver 14.14 Distrib 5.7.7-rc, for Win64 (x86_64)

Connection id:          4
Current database:
Current user:           root@localhost
SSL:                    Not in use
...

Performance

As you might expect, the added security of TLS connections has a performance cost.  This is particularly noticeable during connection set-up phases, and – while still present – lower for normal, post-handshake operations.  This means that applications which leverage short-lived connections, rather than persistent connections (like a JDBC connection pool) will see a greater impact on performance.  The solution to performance concerns is to ensure you can trust the network(s) your connections cross, and disable TLS for that connection.  With the –skip-ssl option (and equivalent in other drivers and clients), we’ll make it easy to disable TLS when you deem appropriate, but it will still be an explicit decision rather than MySQL preferring insecure connections.

Clients can demand TLS

I’ve noted this in an earlier post, but starting with MySQL Server 5.7.3, clients can demand TLS for their connections.  This works entirely independent of server-side account requirements (REQUIRE SSL/X509), and causes connections to fail when TLS cannot be negotiated.

Further hardening

I noted that the result of this work is a “minimal” TLS configuration – more work must be done by end users to truly harden the deployment.  This deployment will protect connections against passive network snooping, but won’t guard against more sophisticated active man-in-the-middle/proxy-based attacks.  I’ll explain in a later post how to harden the installation by distributing key material to clients, and how to produce and use better key material with proper CA signing.

Third party products

Some third party products may not play well with secure connections – I’ve written a separate blog post on this topic.  Users and developers of products which depend on intercepting network traffic between MySQL clients and servers may want to consider the issues raised in that post.

5 thoughts on “SSL/TLS in MySQL 5.7

    1. Hi Daniël,

      Thanks for the feedback! We’ll give that feature request some thought – I’m not entirely sure how we might (safely) persist and associate/resume TLS session information server-side, given that sessions in MySQL are equivalent to connections.

  1. Hi Todd,

    Do you know of any MySQL initiative to replace supplying user/passwd information to client with public/private key pairs much as is done with authentication for remote ssh connections?

    [My Google’s on that topic brought me here; this kind of authentication/credentialing is often bound up with SSL encryption]

    Kind regards,
    Rob

    1. Hi Rob,

      If you’re looking for the ability to simply validate client user identity via TLS keys, you can do that today by setting up an account with REQUIRE X509. An anonymous account would accept any supplied user id, and simply use the provided keys to authenticate. It gets tricky when you want to have multiple users, none of which supply a user name, who are independently authenticated based on the provided TLS keys. That likely won’t work – one matching anonymous account will be selected, and the corresponding TLS key content evaluated. If it doesn’t match, it will fail, rather than try a second anonymous account. At least, that’s my expectation.

      Sometimes when people talk about SSH connections, they mean the TOFU (Trust On First Use) approach where a client asks whether to add the supplied server certificate to a trust list (rejecting subsequent connections to the same host if the certificate changes). If your question is whether MySQL plans to implement something similar, I can’t really say – but I hope so. It can be a lot easier to manage than running (or leveraging) a CA.

      I hope that helps!

      1. Thank you kindly Todd.

        Yes I was wondering if there was or you knew of an initiative that would allow all clients to have their DB catalogue credentials updated with a client public key. Then the server would mediate connections with a client much as the sshd daemon does for ssh logins/remote-commands.

        This would avoid storing passwords in config files or supplying to clients & servers much as already has been solved for remote credentialing with ssh.

        Your second paragraph is very close to my intent i.e. we can have the same level of trust and smart credentialing as ssh/sshd provides.

        Kind regards,
        Rob

Leave a Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload CAPTCHA.