Connect with SSL to MySQL in Docker container

By: (plus.google.com) +David Herron; 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: (gitlab.com) 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 ( (www.npmjs.com) 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 (dev.mysql.com) https://dev.mysql.com/doc/refman/5.7/en/creating-ssl-files-using-openssl.html

Using encrypted connections with MySQL (dev.mysql.com) https://dev.mysql.com/doc/refman/5.7/en/encrypted-connections.html

Shows encrypted connections using PHP and Python drivers - (www.percona.com) https://www.percona.com/blog/2013/06/22/setting-up-mysql-ssl-and-secure-connections/

« The modular DIY cell phone you can upgrade yourself YouTube video recommendation algorithm reportedly preferences conspiracy videos »
2016 Election Acer C720 Ad block AkashaCMS Amazon Amazon Kindle Amazon Web Services America Amiga and Jon Pertwee Android Anti-Fascism AntiVirus Software Apple Apple Hardware History Apple iPhone Apple iPhone Hardware April 1st Arduino ARM Compilation Artificial Intelligence Astronomy Astrophotography Asynchronous Programming Authoritarianism Automated Social Posting AWS DynamoDB AWS Lambda Ayo.JS Bells Law Big Brother Big Finish Bitcoin Mining Black Holes Blade Runner Blockchain Blogger Blogging Books Botnets Cassette Tapes Cellphones China China Manufacturing Christopher Eccleston Chrome Chrome Apps Chromebook Chromebox ChromeOS CIA CitiCards Citizen Journalism Civil Liberties Clinton Cluster Computing Command Line Tools Comment Systems Computer Accessories Computer Hardware Computer Repair Computers Cross Compilation Crouton Cryptocurrency Curiosity Rover Currencies Cyber Security Cybermen Daleks Darth Vader Data backup Data Storage Database Database Backup Databases David Tenant DDoS Botnet Detect Adblocker Developers Editors Digital Photography Diskless Booting Disqus DIY DIY Repair DNP3 Do it yourself Docker Docker MAMP Docker Swarm Doctor Who Doctor Who Paradox Doctor Who Review Drobo Drupal Drupal Themes DVD E-Books E-Readers Early Computers Election Hacks Electric Bicycles Electric Vehicles Electron Emdebian Encabulators Energy Efficiency Enterprise Node EPUB ESP8266 Ethical Curation Eurovision Event Driven Asynchronous Express Face Recognition Facebook Fake News Fedora VirtualBox File transfer without iTunes FireFly Flickr Fraud Freedom of Speech Front-end Development Gallifrey git Github GitKraken Gitlab GMAIL Google Google Chrome Google Gnome Google+ Government Spying Great Britain Heat Loss Hibernate Hoax Science Home Automation HTTP Security HTTPS Human ID I2C Protocol Image Analysis Image Conversion Image Processing ImageMagick In-memory Computing InfluxDB Infrared Thermometers Insulation Internet Internet Advertising Internet Law Internet of Things Internet Policy Internet Privacy iOS Devices iPad iPhone iPhone hacking Iron Man iTunes Java JavaScript JavaScript Injection JDBC John Simms Journalism Joyent Kaspersky Labs Kindle Kindle Marketplace Lets Encrypt LibreOffice Linux Linux Hints Linux Single Board Computers Logging Mac Mini Mac OS Mac OS X Machine Learning Machine Readable ID macOS MacOS X setup Make Money Online March For Our Lives MariaDB Mars Mass Violence Matt Lucas MEADS Anti-Missile Mercurial MERN Stack Michele Gomez Micro Apartments Microsoft Military AI Military Hardware Minification Minimized CSS Minimized HTML Minimized JavaScript Missy Mobile Applications Mobile Computers MODBUS Mondas Monetary System MongoDB Mongoose Monty Python MQTT Music Player Music Streaming MySQL NanoPi Nardole NASA Net Neutrality Network Attached Storage Node Web Development Node.js Node.js Database Node.js Testing Node.JS Web Development Node.x North Korea npm NVIDIA NY Times Online advertising Online Community Online Fraud Online Journalism Online Photography Online Video Open Media Vault Open Source Open Source Governance Open Source Licenses Open Source Software OpenAPI OpenVPN Palmtop PDA Patrick Troughton Paywalls Personal Flight Peter Capaldi Phishing Photography PHP Plex Plex Media Server Political Protest Postal Service Power Control Privacy Production use Public Violence Raspberry Pi Raspberry Pi 3 Raspberry Pi Zero ReactJS Recaptcha Recycling Refurbished Computers Remote Desktop Removable Storage Republicans Retro Computing Retro-Technology Reviews RFID Right to Repair River Song Robotics Rocket Ships RSS News Readers rsync Russia Russia Troll Factory Russian Hacking Rust SCADA Scheme Science Fiction SD Cards Search Engine Ranking Season 1 Season 10 Season 11 Security Security Cameras Server-side JavaScript Serverless Framework Servers Shell Scripts Silence Simsimi Skype SmugMug Social Media Social Media Warfare Social Network Management Social Networks Software Development Space Flight Space Ship Reuse Space Ships SpaceX Spear Phishing Spring Spring Boot Spy Satellites SQLite3 SSD Drives SSD upgrade SSH SSH Key SSL Stand For Truth Strange Parts Swagger Synchronizing Files Telescopes Terrorism The Cybermen The Daleks The Master Time-Series Database Tom Baker Torchwood Total Information Awareness Trump Trump Administration Trump Campaign Twitter Ubuntu Udemy UDOO US Department of Defense Virtual Private Networks VirtualBox VLC VNC VOIP Vue.js Web Applications Web Developer Resources Web Development Web Development Tools Web Marketing Webpack Website Advertising Weeping Angels WhatsApp William Hartnell Window Insulation Windows Windows Alternatives Wordpress World Wide Web Yahoo YouTube YouTube Monetization