; Date: March 5, 2018
Tags: Docker »»»» Docker MAMP
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
.

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.
For more discussion on doing this with Node.js Using SSL to connect to MySQL database in Node.js
Links
Generating the SSL certificates for MySQL
https://dev.mysql.com/doc/refman/5.7/en/creating-ssl-files-using-openssl.html
Using encrypted connections with MySQL
https://dev.mysql.com/doc/refman/5.7/en/encrypted-connections.html
Shows encrypted connections using PHP and Python drivers -
https://www.percona.com/blog/2013/06/22/setting-up-mysql-ssl-and-secure-connections/