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.
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.
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.
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.