Stunneling (SSL Tunnels) MySQL on Ubuntu

We have really restrictive IT policies at my current employer which dictates that no client/server communication can happen in the clear. This has some interesting implications as most software servers do not support encryption or encrypted tunnels in any way.

What are the options?

• Write our own software that supports client/server encryption
• Only use existing applications that support client/server encryption
• Treat the encryption layer as an abstraction that the client/server is not aware of

The first two options have obvious drawbacks in terms of time and choice. The third seems to be the best way to transparently insert encryption into an existing architecture without requiring massive changes.

This approach can be done by:

• SSH tunnels
• OpenVPN tunnels
• Stunnel

There may be other options but these are the main ones I have had experience with. SSH tunnels are very convenient but tend to be unstable requiring restarts every now and again.

OpenVPN has also provided very little stability requiring frequent restarts.

Lets explore the setup of the third option then, stunnel.

Stunneling
The stunnel home page describes stunnel as:

Stunnel is a program that allows you to encrypt arbitrary TCP connections inside
SSL (Secure Sockets Layer) available on both Unix and Windows. Stunnel can allow
you to secure non-SSL aware daemons and protocols (like POP, IMAP, LDAP, etc) by
having Stunnel provide the encryption, requiring no changes to the daemon's code.

Sounds promising. They have a really comprehensive examples section on their site which I highly recommend you have a look at.

In my case I needed to get a MySQL master/slave pair to replicate via a secure channel. MySQL seems to support SSL but the package requires a rebuild with the required options which would mean I need a non-standard MySQL package which makes me itchy. I also needed a similar solution for several other applications that need secure communications channels so I've decided on this solution throughout my architecture.

See this Ubuntu thread for more info on rebuilding the MySQL package with SSL support if you're so inclined.

Our installation targets are an Ubuntu servers and will therefore be flavored accordingly but you should be able to easily adapt anything mentioned here for your platform of choice.

Preamble
If you are going to be using this for the same purposes I am ensure that you have a working MySQL master/slave setup. You don't want to troubleshoot multiple applications if something is not playing nice.

Installation
A simple aptitude install stunnel4 on the master and slave should do the trick.

Now, keep in mind that if one of the nodes you have stunnel installed on is acting as both a stunnel client and stunnel server you need to have at least one stunnel server running for the client entries in a config file and one for the server entries in a config file.

Not heading this warning will result in you seeing errors like this on the client:

2009.01.28 07:02:16 LOG7[11508:3082910608]: SSL state (connect): before/connect initialization
2009.01.28 07:02:16 LOG7[11508:3082910608]: SSL state (connect): SSLv3 write client hello A
2009.01.28 07:02:16 LOG7[11508:3082910608]: SSL alert (write): fatal: handshake failure
2009.01.28 07:02:16 LOG3[11508:3082910608]: SSL_connect: 1408F10B: error:1408F10B:SSL routines:SSL3_GET_RECORD:wrong version number
2009.01.28 07:02:16 LOG5[11508:3082910608]: Connection reset: 0 bytes sent to SSL, 0 bytes sent to socket

And this on the server:

2009.01.28 07:10:06 LOG7[13426:3082861456]: SSL state (connect): before/connect initialization
2009.01.28 07:10:06 LOG7[13426:3082861456]: SSL state (connect): SSLv3 write client hello A
2009.01.28 07:10:06 LOG7[13426:3082861456]: SSL alert (write): fatal: handshake failure
2009.01.28 07:10:06 LOG3[13426:3082861456]: SSL_connect: 1408F10B: error:1408F10B:SSL routines:SSL3_GET_RECORD:wrong version number
2009.01.28 07:10:06 LOG5[13426:3082861456]: Connection reset: 0 bytes sent to SSL, 0 bytes sent to socket

My convention is to use the default config file (/etc/stunnel/stunnel.conf) that ships with the stunnel4 package for the client configuration and a modified copy of that as a config (/etc/stunnel/mysql-master.conf, in this specific case) for the server.

If you will not be using the MySQL master server as a stunnel client then you can simply ensure that your /etc/stunnel/stunnel.conf is the same as my /etc/stunnel/mysql-master.conf.

Keep in mind that multiple config files in /etc/stunnel/ will cause multiple stunnel servers to be run with each of the files found there.

Server
First off, let's generate our own key (/etc/ssl/master.pem) that we'll be using for encryption. This key must be treated as a secure file that should have the relevant access controls placed on it.

root@master:~# cd /etc/ssl/
root@master:~# openssl req -new -x509 -nodes -days 730 -out master.pem -keyout master.pem
root@master:~# chmod 600 master.pem
root@master:~# dd if=/dev/urandom of=temp_file count=2
root@master:~# openssl dhparam -rand temp_file 512 >> master.pem
root@master:~# ln -sf master.pem `openssl x509 -noout -hash < coretools01.pem`.0

Now, change the following lines in /etc/stunnel/mysql-master.conf:

cert = /etc/ssl/master.pem
;client = yes
debug = 7
output = /var/log/stunnel4/stunnel.log

[mysql-master]
accept  = 443
connect = 127.0.0.1:3306
TIMEOUTclose = 0

This config will ensure we're running as a stunnel server, listening for incoming SSL connections on 0.0.0.0:443 and shunting the unencrypted connection to 127.0.0.1:3306 (ensure your MySQl master is listening for connections here).

Fire stunnel up on the master with /etc/init.d/stunnel4 start && tail -f /var/log/stunnel4/stunnel.log.

Client
Just modify the default /etc/stunnel/stunnel.conf that was installed via the package by changing the lines below:

;cert = /etc/stunnel/mail.pem
;key = /etc/stunnel/mail.pem
debug = 7
output = /var/log/stunnel4/stunnel.log
client = yes

[mysql-master]
accept  = 127.0.0.1:3307
connect = master.example.com:443
TIMEOUTclose = 0

This sets up the MySQL slave machine as a stunnel client, forwarding all unencrypted connections on 127.0.0.1:3307 via SSL to master.example.com:443. Just replace master.example.com with the correct FQDN for your master server. You also need to ensure that your MySQL slave's config points to 127.0.0.1:3307 for its replication requirements.

Fire stunnel up on the slave with /etc/init.d/stunnel4 start && tail -f /var/log/stunnel4/stunnel.log.

Testing
An initial test can be done from the slave with the mysql command line utility in a separate terminal session by connecting to a suitable user/db configured on the master with access from 'localhost' or '127.0.0.1':

root@slave:~# mysql -u replication -h 127.0.0.1 -P 3307 -p test
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 21
Server version: 5.0.51a-3ubuntu5.4-log (Ubuntu)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

Yay!

Now, apply your general method for determining if your master and slave is in sync. You can use the logs at /var/log/stunnel4/stunnel.log if you hit any snags. Once you're happy with everything ensure you disable the debugging in the stunnel config files.