WL#7706: SSL cert and key generation for MySQL Community

Affects: Server-5.7   —   Status: Complete   —   Priority: Medium

WL#7699 seeks to automatically enable SSL (and RSA) for MySQL commercial-license
servers, leveraging OpenSSL APIs.  Due to license incompatibilities between
OpenSSL and GPL, a tightly-bound solution like this cannot apply to MySQL
Community.  Instead, we should provide a "best effort" SSL set up process during
installation.  This process will be loosely-bound to OpenSSL, expecting the
necessary OpenSSL libraries to already be deployed on the target server (largely
true for Unix-like platforms, not so for Windows), and will interact with
OpenSSL via command-line interfaces.

Our documentation defines a script for generation of SSL certs and keys:

http://dev.mysql.com/doc/refman/5.6/en/creating-ssl-certs.html

This script (or a variant of it) should be called from installation scripts such
as mysql_install_db on a "best effort" basis, and if successful, the MySQL
server configuration file modified to reference the generated SSL certs & keys.
FR1 : A new binary : mysql_ssl_rsa_setup shall be introduced to setup SSL
certificates and RSA key pair at the time of server installation through MySQL
community binaries.

FR2 : mysql_ssl_rsa_setup shall check for availability of openssl binary at
locations specified by $PATH. If openssl is not found, it shall exit with
appropriate message.

FR3.1 : mysql_ssl_rsa_setup shall be checked for the presence of following files
at location specified by --datadir
 1> ca.pem
 2> server-cert.pem
 3> server-key.pem.
If any of these files are present, generation shall be skipped and appropriate
message will be displayed.

FR3.2 : mysql_ssl_rsa_setup shall be checked for the presence of following files
at location specified by --datadir
 1> private_key.pem
 2> public_key.pem.
If any of these files are present, generation shall be skipped and appropriate
message will be displayed.

FR4 : Following files shall be created with below mentioned permission mask:
1> Self Signed CA Certificate : ca.pem : -rw-r--r--
2> CA Private Key : ca-key.pem : -rw-------
3> Server Certificate signed by CA cert/key generated above : server-cert.pem :
-rw-r--r--
Signature Algorithm : sha256WithRSAEncryption
4> Server Private Key : server-key.pem : -rw-------
5> Client Certificate signed by CA cert/key generated above : client-cert.pem :
-rw-r--r--
Signature Algorithm : sha256WithRSAEncryption
6> Client Private Key : client-key.pem : -rw-------
7> RSA Private Key : private_key.pem : -rw-------
8> RSA Public Key :public_key.pem : -rw-r--r--

FR 4.1 : Generated SSL artifacts shall use 2048 bit keys.

FR 4.2 : Generated SSL certificates shall use CN =
MySQL_Server_<suffix>_Auto_Generated_[CA|Server|Client]_Certificate. suffix
shall be supplied by --suffix option.

FR 4.3 : Generated SSL artifacts shall have blank values for Country (C), State
or Province (ST), Organization (O), Organization Unit Name (OU).

FR 4.4 : Generated SSL artifacts shall be valid for ten years from generation.
Note that for enterprise edition (WL#7699), validity is one year.

FR 4.5 : Generated SSL artifacts shall have different Serial Numbers for each
cert/key pair (1 for CA, 2 for Server, 3 for Client)

FR 5 : --datadir option shall have compile time default for data directory
(/var/lib/mysql).

FR 6 : Server shall try to enable SSL support if
a> No SSL parameters are used at the time of start-up AND
b> ca.pem, server-cert.pem and server-key.pem are found in data directory.
Server log will contain appropriate message when SSL artifacts will be picked
up automatically.

FR 7 : If a self-signed CA certificate is used, server shall log a warning in
server log.

FR 8 : mysql_install_db shall call mysql_ssl_rsa_setup utility with --datadir
set to data directory.

NFR 1 : Documentation should be updated to mention that for cases where SSL
artifacts need to recreated (e.g. after certificate expiration), user can
following below mentioned steps:

a> STOP the server
b> Remove/Backup and Remove existing SSL artifacts
c> Run mysql_ssl_rsa_setup with --datadir=<location_of_mysql_data_dir>
e> Restart the server
I-1 : New file mysql_ssl_rsa_setup.cc : This file will be added under client/
folder.

I-2 : Corresponding binary : mysql_ssl_rsa_setup will be placed in bin directory.

I-3 : New binary will take 3 arguments:
1. --datadir : Path to the destination directory where certificates will be placed.
2. --suffix : Version number suffix provided by user. This will be used for CN
in X509 Certificates.
3. --help : Display help and exit.

I-3.1 : Defaults:
1. --datadir : Compile time default for data directory (e.g. /var/lib/mysql)
2. --suffix : current MySQL version (e.g. 5.7.5-r15 etc)

I-4 : Certificate and key files will be generated if following conditions are
satisified:

1. --datadir points to a valid/writable directory

AND

2a. ca.pem, server-cert.pem and server-key.pem files are not present in
destination directory.
2b. private_key.pem and public_key.pem files are not present in destination
directory.

I-5 : Following OpenSSL commands are used to generate certificates and key files:

1. For key:

openssl req -newkey rsa:2048 -day 365 -nodes -keyout <KEY_FILE_NAME> >
<REQ_FILE_NAME>

2. For Self Signed X509 Certificate :

openssl x509 -sha256 -days 365 -set_serial <SERIAL_NUM> -req -in <REQ_FILE_NAME>
-signkey <KEY_FILE_NAME> > <CERT_FILE_NAME>

3. For CA signed X509 Certificate :

openssl x509 -sha256 -days 365 -set_serial <SERIAL_NUM> -req -in <REQ_FILE_NAME>
-CA <CA_CERT_FILE> -CAkey <CA_KEY_FILE> > <CERT_FILE_NAME>

4. For RSA Private key
openssl genrsa 2048 > <PRIVATE_KEY_NAME>

5. For RSA Public key
openssl rsa -in <PRIVATE_KEY_NAME> -pubout -out <PUBLIC_KEY_NAME>

I-6 : List of generated files and their permission :

ca.pem : -rw-r--r--
ca-key.pem : -rw-------
server.pem : -rw-r--r--
server-key.pem : -rw-------
client.pem : -rw-r--r--
client-key.pem : -rw-------
private_key.pem : -rw-------
public_key.pem : -rw-r--r--

I-7 : mysql_install_db will call this binary unless --insecure mode is used.
mysql_install_db will pass following parameters:
1> Location of data directory