Simplified SSL/TLS Setup for MySQL Community

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:

D:\mysql-5.7.10-winx64>bin\mysql_ssl_rsa_setup --datadir=data
2016-01-13 16:17:10 [ERROR]   Could not find OpenSSL on the system

Adding OpenSSL to the PATH solves this problem:

D:\mysql-5.7.10-winx64>SET PATH=%PATH%;D:\OpenSSL

D:\mysql-5.7.10-winx64>bin\mysql_ssl_rsa_setup --datadir=data
Loading 'screen' into random state - done
Generating a 2048 bit RSA private key
....+++
.......+++
writing new private key to 'ca-key.pem'
-----
Loading 'screen' into random state - done
Generating a 2048 bit RSA private key
.........................................+++
.......+++
writing new private key to 'server-key.pem'
-----
Loading 'screen' into random state - done
Generating a 2048 bit RSA private key
....+++
..+++
writing new private key to 'client-key.pem'
-----

D:\mysql-5.7.10-winx64>

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

D:\mysql-5.7.10-winx64>dir data
 Volume in drive D is Data
 Volume Serial Number is A23D-1A21

 Directory of D:\mysql-5.7.10-winx64\data

01/13/2016  04:19 PM    <DIR>          .
01/13/2016  04:19 PM    <DIR>         ..
01/13/2016  04:16 PM                56 auto.cnf
01/13/2016  04:19 PM             1,679 ca-key.pem
01/13/2016  04:19 PM             1,074 ca.pem
01/13/2016  04:19 PM             1,078 client-cert.pem
01/13/2016  04:19 PM             1,675 client-key.pem
...
01/13/2016  04:19 PM             1,675 private_key.pem
01/13/2016  04:19 PM               451 public_key.pem
01/13/2016  04:19 PM             1,078 server-cert.pem
01/13/2016  04:19 PM             1,679 server-key.pem
...

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:

:\mysql-5.7.10-winx64\data>openssl x509 -in ca.pem -text
ertificate:
   Data:
       Version: 1 (0x0)
       Serial Number: 1 (0x1)
   Signature Algorithm: sha256WithRSAEncryption
       Issuer: CN=MySQL_Server_5.7.10_Auto_Generated_CA_Certificate
       Validity
           Not Before: Jan 13 23:19:04 2016 GMT
           Not After : Jan 10 23:19:04 2026 GMT
       Subject: CN=MySQL_Server_5.7.10_Auto_Generated_CA_Certificate
       Subject Public Key Info:
           Public Key Algorithm: rsaEncryption
               Public-Key: (2048 bit)
               Modulus:
                   00:ae:92:1e:fb:24:5f:0e:07:d2:a1:91:4b:e3:61:
...
               Exponent: 65537 (0x10001)
   Signature Algorithm: sha256WithRSAEncryption

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:

D:\mysql-5.7.10-winx64\data>openssl x509 -in server-cert.pem -text
Certificate:
    Data:
        Version: 1 (0x0)
        Serial Number: 2 (0x2)
    Signature Algorithm: sha256WithRSAEncryption
        Issuer: CN=MySQL_Server_5.7.10_Auto_Generated_CA_Certificate
        Validity
            Not Before: Jan 13 23:19:06 2016 GMT
            Not After : Jan 10 23:19:06 2026 GMT
        Subject: CN=MySQL_Server_5.7.10_Auto_Generated_Server_Certificate
        Subject Public Key Info:
            Public Key Algorithm: rsaEncryption
                Public-Key: (2048 bit)
                Modulus:
...
                Exponent: 65537 (0x10001)
    Signature Algorithm: sha256WithRSAEncryption

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:

D:\mysql-5.7.10-winx64\data>openssl x509 -in client-cert.pem -text
Certificate:
    Data:
        Version: 1 (0x0)
        Serial Number: 3 (0x3)
    Signature Algorithm: sha256WithRSAEncryption
        Issuer: CN=MySQL_Server_5.7.10_Auto_Generated_CA_Certificate
        Validity
            Not Before: Jan 13 23:19:09 2016 GMT
            Not After : Jan 10 23:19:09 2026 GMT
        Subject: CN=MySQL_Server_5.7.10_Auto_Generated_Client_Certificate
        Subject Public Key Info:
            Public Key Algorithm: rsaEncryption
                Public-Key: (2048 bit)
                Modulus:
                    00:bf:37:1b:55:f2:ae:3b:eb:c1:08:7b:62:b9:2b:
....
                Exponent: 65537 (0x10001)
    Signature Algorithm: sha256WithRSAEncryption
...

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:

D:\mysql-5.7.10-winx64>bin\mysql_ssl_rsa_setup --datadir=data

D:\mysql-5.7.10-winx64>bin\mysql_ssl_rsa_setup --datadir=data --verbose
2016-01-13 19:02:30 [NOTE]    Destination directory: D:\mysql-5.7.10-winx64\data
2016-01-13 19:02:30 [NOTE]    Executing : openssl version OpenSSL 1.0.1l 15 Jan 2015
2016-01-13 19:02:30 [NOTE]    Certificate files are present in given dir. Skipping generation.
2016-01-13 19:02:30 [NOTE]    RSA key files are present in given dir. Skipping generation.
2016-01-13 19:02:30 [NOTE]    Success!

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.

Leave a Reply

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

Time limit is exhausted. Please reload CAPTCHA.