Transport Layer Security (TLS, also often referred to as SSL) is an important component of a secure MySQL deployment, but the complexities of properly generating the necessary key material and configuring the server dissuaded many users from completing this task.  MySQL Server 5.7 simplifies this task for both Enterprise and Community users.  Previous blog posts have detailed the changes supporting Enterprise builds; this blog post will focus on parallel improvements made to MySQL Community builds.

Introducing mysql_ssl_rsa_setup

To enable TLS with MySQL Server, we first need to generate the required key material.  Key material must be unique and secret – it’s not something that MySQL can generate and pre-populate in packages.  If another organization has the server TLS keys for your organization, they can decrypt network traffic for your organization.  That means key material needs to be dynamically created on the deployment host.  In OpenSSL-linked MySQL Server binaries, this can be done directly by the mysqld server process at startup.  The yaSSL library used by GPL-licensed MySQL Community builds lacks the ability to generate the necessary key material, so another means to produce TLS certificates and keys was required.  This is the new mysql_ssl_rsa_setup utility.

For GPL compatibility, mysql_ssl_rsa_setup is also not linked to OpenSSL libraries – it uses OpenSSL by making calls to OpenSSL via the command-line.  For this to succeed, OpenSSL must be found in PATH.  For most Linux distributions, this requirement is satisfied by default.  On Windows, this requires OpenSSL be explicitly installed and configured.  Here is an example of the error resulting from calling mysql_ssl_rsa_setup on Windows without OpenSSL configured in PATH:

Adding OpenSSL to the PATH solves this problem:

The output above shows the key material being generated – here’s a listing of the resulting files:

We now have public certificate and private key pairs for a certificate authority (CA), the server and clients. Additionally, we have a RSA private/public key pair, which provides the means to securely exchange client password during authentication with the sha256_password plugin, when TLS for the duration of the connection is not required. This also requires OpenSSL-linked client and server binaries, meaning RSA key material is unused for GPL-licensed MySQL Community builds from Oracle.

Generated key material

Let’s take a closer look at the generated certificates, starting with the CA cert:

The issuer and the subject are identical: MySQL_Server_5.7.10_Auto_Generated_CA_Certificate. This is a self-signed CA certificate, and the corresponding key (ca-key.pem) is used to sign the server and client key certificates.  The certificate start date is the current date/time, and is valid for 3650 days (10 years, not accounting for leap days).  The sha256WithRSAEncryption signature algorithm shows that SHA-2 was used (not the less-secure SHA-1), and the public key field shows that 2048-bit encryption is used.

Be careful with the CA private key (ca-key.pem) – anybody with access to it can use it to generate additional client or server certificates that will be accepted as legitimate when CA verification is enabled. Compromised CA keys undermine the entire chain of trust on which TLS is based. Consider securely deleting the CA private key in situations where no further client or server certificates should be signed with this CA.

The public certificate (ca.pem) can be distributed as needed. It may be used client-side to validate the server identity, and the server can use it to validate client identity.

Here’s the server certificate:

The server key uses a canonical name in the subject (MySQL_Server_5.7.10_Auto_Generated_Server_Certificate), and is signed by the CA certificate examined earlier. It has the same duration and generation strength characteristics, but the Serial Number for generated server certificates is 2 (CA is always 1, client certificates are always 3).

The client certificate is similar to the server certificate, except it has a different subject:

Like all private keys, the client private key (client-key.pem) needs to be kept secret – but it also needs to be distributed to the clients who will use it to identify themselves during connections. There’s no reason to keep it on the server unless TLS connections are required for clients connecting from the same host.

Key Material Naming

The key material generated by mysql_ssl_rsa_setup uses a naming convention consistent with MySQL Server (mysqld). These filenames are the new default values for TLS configuration options for MySQL Server, allowing MySQL Server to discover the generated key material automatically, and not require additional configuration. After running mysql_ssl_rsa_setup to generate key material to the data directory, starting MySQL Server 5.7 with that data directory will automatically enable TLS.

The standard naming convention also allows mysql_ssl_rsa_setup to avoid overwriting any existing TLS material produced by earlier executions, or by OpenSSL-linked MySQL Server binaries. Running mysql_ssl_rsa_setup a second time against the same data directory results in no output, and adding a --verbose option shows that key generation is skipped because existing key material was found:

The checks for existing TLS and RSA key material are independent – if any of ca.pem or server-cert.pem are present, TLS key material generation is skipped, while the existance of either private_key.pem or public_key.pem prevent generation of new RSA key material. Note that existance of client-cert.pem alone does not prevent generation of new TLS key material, which will overwrite the client public key.

This also means that users wanting to replace existing key material will need to manually delete (or move) the existing key material before invoking mysql_ssl_rsa_setup to generate new keys and certificates.

Installation Scripting

Users of Oracle-produced .RPM and .DEB packages will find key generation has been further simplified.  Because OpenSSL is likely to be already installed on most Linux machines, post-installation scripts for these packages call mysql_ssl_rsa_setup during deployment.  As a result, users who deploy MySQL Server 5.7 using these packages will typically find key material automatically generated without any additional steps.

Conclusion

Introduction of mysql_ssl_rsa_setup supports users of GPL-licensed MySQL Community secure deployments by simplifying generation of TLS key material.  By integrating this utility in post-installation scripts for Linux packages, many users will find key material is produced automatically, allowing MySQL Server 5.7 to support TLS without any extra effort or configuration.