; Date: March 5, 2018

MySQL throws an error if you connect without using SSL, so therefore the MySQL team is making it clear it's best to use SSL. Clearly a database connection has critical data that you don't want to leak to 3rd parties, and encrypting the database connection is preferred. What's even more preferred is tight control to limit visibility of the database connection. The official MySQL Docker container automatically generates a set of SSL certificates to use for connections, so let's see how to put those certificates to use.

As with any TLS/SSL-protected connection, this task requires

SSL certificates
A "root Certificate Authority"
SSL certificates for the server signed by the Root CA
SSL certificates for the client signed by the Root CA
An arrangement whereby the client certificates work to authenticate to the server certificate

Fortunately the required steps are known and fairly well documented. We just need to go through them. links will be shown below.

A git repository for everything shown in this article is: https://gitlab.com/damp-stack/mysql-ssl-docker

Docker container to generate certificates

Generating the certificates requires the OpenSSL tools be installed on your computer. Maybe you already have them installed and you don't need a Docker container. But this Docker container contains everything required to generate MySQL SSL certificates, and you don't have to setup OpenSSL anything on your laptop.

Create a directory named gencerts. In that directory create Dockerfile containing:

FROM debian:jessie

RUN  apt-get update && apt-get install -y openssl

ENV OPENSSL_SUBJ="/C=US/ST=California/L=Santa Clara"
ENV OPENSSL_CA="${OPENSSL_SUBJ}/CN=fake-CA"
ENV OPENSSL_SERVER="${OPENSSL_SUBJ}/CN=fake-server"
ENV OPENSSL_CLIENT="${OPENSSL_SUBJ}/CN=fake-client"

COPY gencerts.sh /
RUN chmod +x /gencerts.sh

VOLUME /certs

WORKDIR /certs

CMD /gencerts.sh

This is a Debian container, with the openssl package installed. Not complicated at all. We have a directory, /certs, that is exported as a VOLUME. The gencerts.sh script is run while in that directory, containing openssl commands to generate the certificates.

The environment variables are X.509 DN's which avoid the necessity that the tools will stop and prompt the user for the DN's (Distinguished Name).

The gencerts.sh script contains this:

# Generate new CA certificate ca.pem file.
openssl genrsa 2048 > ca-key.pem

# TODO This has interaction that must be automated
openssl req -new -x509 -nodes -days 3600 \
    -subj "${OPENSSL_CA}" \
    -key ca-key.pem -out ca.pem

# Create the server-side certificates
# This has more interaction that must be automated

openssl req -newkey rsa:2048 -days 3600 -nodes \
    -subj "${OPENSSL_SERVER}" \
    -keyout server-key.pem -out server-req.pem
openssl rsa -in server-key.pem -out server-key.pem
openssl x509 -req -in server-req.pem -days 3600 \
    -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

# Create the client-side certificates
openssl req -newkey rsa:2048 -days 3600 -nodes \
    -subj "${OPENSSL_CLIENT}" \
    -keyout client-key.pem -out client-req.pem
openssl rsa -in client-key.pem -out client-key.pem
openssl x509 -req -in client-req.pem -days 3600 \
    -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem

# Verify the certificates are correct
openssl verify -CAfile ca.pem server-cert.pem client-cert.pem

Between the -subj and -nodes options, the commands do not query for inputs.

What we've done is to concoct a fake Certificate Authority, and then with the power we've duly invested ourselves with, we signed some certificates. It works for this case because our universe of exposure is completely limited to our server and our client application, therefore it is not necessary to procure certificates from some blessed official certificate authority.

This package.json could be useful:

{
    "scripts": {
        "build-gencerts": "docker build -t damp/gencerts gencerts",
        "run-gencerts": "mkdir -p certs && docker run --rm -v `pwd`/certs:/certs damp/gencerts"
    }
}

Then these can be run:

$ npm run build-gencerts

> @ build-gencerts /home/david/damp-stack/mysql-ssl-docker
> docker build -t damp/gencerts gencerts
...
Successfully tagged damp/gencerts:latest
$ npm run run-gencerts

> @ run-gencerts /home/david/damp-stack/mysql-ssl-docker
> mkdir -p certs && docker run --rm -v `pwd`/certs:/certs damp/gencerts
...

The result:

$ ls -l certs
total 32
-rw-r--r-- 1 root root 1679 Mar  6 21:07 ca-key.pem
-rw-r--r-- 1 root root 1241 Mar  6 21:07 ca.pem
-rw-r--r-- 1 root root 1119 Mar  6 21:07 client-cert.pem
-rw-r--r-- 1 root root 1679 Mar  6 21:07 client-key.pem
-rw-r--r-- 1 root root  968 Mar  6 21:07 client-req.pem
-rw-r--r-- 1 root root 1119 Mar  6 21:07 server-cert.pem
-rw-r--r-- 1 root root 1675 Mar  6 21:07 server-key.pem
-rw-r--r-- 1 root root  968 Mar  6 21:07 server-req.pem

MySQL container using the generated server certificates

A simple MySQL container using the mysql/mysql-server image already contains a set of certificates:

bash-4.2# cd /var/lib/mysql
bash-4.2# ls
auto.cnf	 ib_buffer_pool  mysql		     private_key.pem
ca-key.pem	 ib_logfile0	 mysql.sock	     public_key.pem
ca.pem		 ib_logfile1	 mysql.sock.lock     server-cert.pem
client-cert.pem  ibdata1	 notes@002dtest      server-key.pem
client-key.pem	 ibtmp1		 performance_schema  sys

But - do we know the provenance of those certificates? I don't know. They are not generated by the Dockerfile associated with that image, and therefore these certificates seems likely to arrive from the Fedora package the mysql/mysql-server uses. If so, those certificates were generated by Redhat? And therefore there isn't much value in using those certificates? I don't know.

The process in the previous section is simple, and we can generate equivalent certificates as just shown. Therefore we'll ignore those certificates and use the ones generated above.

In /etc/my.cnf, in the [mysql] section, these configuration settings use the certificates for the SSL connection:

[mysql]
...
# Ensure the MySQL server is listening to a TCP socket
# socket=/var/lib/mysql/mysql.sock
bind-address = 0.0.0.0
require_secure_transport = ON
...
# Type your own certificates directory
ssl-ca=/etc/certs/ca.pem
ssl-cert=/etc/certs/server-cert.pem
ssl-key=/etc/certs/server-key.pem
...

These settings make the server listen on TCP connections from any IP address, and to require SSL, and to use the certificates we generated previously. Those certificates will be mounted into the /etc/certs directory.

How do we do so?

$ docker run \
    -v `pwd`/mysql-data:/var/lib/mysql \
    -v `pwd`/my.cnf:/etc/my.cnf \
    -v `pwd`/certs:/etc/certs \
    -e MYSQL_ROOT_PASSWORD=passw0rd \
    -e MYSQL_ROOT_HOST='172.%.%.%' \
    -p 3306:3306 \
    --name mysql-ssl \
    mysql/mysql-server

[Entrypoint] MySQL Docker Image 5.7.21-1.1.3
[Entrypoint] Initializing database
[Entrypoint] Database initialized
[Entrypoint] ignoring /docker-entrypoint-initdb.d/*
[Entrypoint] Server shut down
[Entrypoint] MySQL init process done. Ready for start up.
[Entrypoint] Starting MySQL 5.7.21-1.1.3

This is a minimal configuration using environment variables of this server container. We give a root password and allow root to connect from outside the container using the MYSQL_ROOT_HOST variable.

Injected into the container using volume mounts are the data directory, the configuration file (settings shown above), and the directory containing the certificates.

A Dockerfile that would contain most of that is:

FROM mysql/mysql-server:5.7

RUN mkdir -p /etc/certs
COPY certs/*.pem /etc/certs/
COPY my.cnf /etc/

ENV MYSQL_ROOT_HOST='172.%.%.%'
ENV MYSQL_ROOT_PASSWORD=passw0rd

VOLUME /var/lib/mysql

EXPOSE 3306 33060
CMD ["mysqld"]

With that the command-line would become:

$ docker run \
    -v `pwd`/mysql-data:/var/lib/mysql \
    -p 3306:3306 \
    --name mysql-ssl \
    mysql/mysql-server

That Dockerfile and this abbreviated command-line haven't been tested.

Using client certificates in a MySQL application

How do we use this, then?

$ mysql -v -h 127.0.0.1 --port=3306 -u root -p \
    --ssl-ca=certs/ca.pem \
    --ssl-cert=certs/client-cert.pem \
    --ssl-key=certs/client-key.pem 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.21 MySQL Community Server (GPL)

This is a fairly normal method to connect with a MySQL server on another host using the command-line tool. Except that we've added the SSL certificates to the command-line options.

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.7.21, for Linux (x86_64) using  EditLine wrapper

Connection id:		2
Current database:	
Current user:		root@172.17.0.1
SSL:			Cipher in use is DHE-RSA-AES256-SHA
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		5.7.21 MySQL Community Server (GPL)
Protocol version:	10
Connection:		127.0.0.1 via TCP/IP
Server characterset:	latin1
Db     characterset:	latin1
Client characterset:	utf8
Conn.  characterset:	utf8
TCP port:		3306
Uptime:			11 sec

Threads: 1  Questions: 5  Slow queries: 0  Opens: 105  Flush tables: 1  Open tables: 98  Queries per second avg: 0.454
--------------

mysql> show global variables like '%ssl%';
--------------
show global variables like '%ssl%'
--------------

+---------------+----------------------------+
| Variable_name | Value                      |
+---------------+----------------------------+
| have_openssl  | YES                        |
| have_ssl      | YES                        |
| ssl_ca        | /etc/certs/ca.pem          |
| ssl_capath    |                            |
| ssl_cert      | /etc/certs/server-cert.pem |
| ssl_cipher    |                            |
| ssl_crl       |                            |
| ssl_crlpath   |                            |
| ssl_key       | /etc/certs/server-key.pem  |
+---------------+----------------------------+
9 rows in set (0.00 sec)

These commands verify that SSL is actually in use, and gives some specifics.

$ mysql -v -h 127.0.0.1 --port=3306 -u root -p 
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'172.17.0.1' (using password: YES)

And if you do not supply the SSL certificates, you cannot authenticate with the server. That behavior is controlled by the require_secure_transport = ON option in /etc/my.cnf.

Using MySQL Workbench and the SSL certificates

Whether you can use an SSL connection with a given programming language depends on the database driver, it seems.

As an example of using this from software - a Node.js sample:

const fs = require('fs');
const mysql = require('mysql');

var connection = mysql.createConnection({
    host: '127.0.0.1',
    port: '3306',
    user: 'root',
    password: 'passw0rd',
    database: 'test',
    ssl: {
        ca: fs.readFileSync(__dirname + '/certs/ca.pem'),
        key: fs.readFileSync(__dirname + '/certs/client-key.pem'),
        cert: fs.readFileSync(__dirname + '/certs/client-cert.pem')
    }
});

connection.connect();

connection.query('SELECT 1 + 1 AS solution', function (error, results, fields) {
    if (error) throw error;
    console.log('The solution is: ', results[0].solution);
});
   
connection.end();

Unfortunately the online documentation for the driver ( https://www.npmjs.com/package/mysql) does not make it clear, but browsing the issue queue demonstrated this connection configuration works for the certificates we have on hand.