Guide to Setup Rails with MySQL SSL

UPDATE 5/24/08: There was a recent APB security bulletin for all those running Debian-based OSes (including Debian) with OpenSSL 0.9.8c (released 2006) onward. You can read about it here. Long story short: if you’re running a flavor of Debian, you should run “sudo apt-get update” and “sudo apt-get upgrade openssl” before you start these instructions, to ensure that you’re using the patched version of SSL. We now resume our regularly scheduled programming…

If you have a database in one place and some Rails stuff in another place (be it your Rails app, or an asynchronous module that interacts with the DB), and if you’re running on a hosted server (i.e., you can’t just setup a hardware firewall for your entire server network), chances are you have thought or should be thinking about setting up your database to accept SSL connections. This ensures that malicious third parties can’t read the network packets being transmitted between your remote server and your database. Here are some of the sites and notes that I used to get us setup doing this:

For detailed but not too-detailed instructions on generating the SSL keys with MySQL, the MySQL documentation on SSL is great. This documentation describes not only how to generate your SSL keys, but also how to tell your DB who to accept connections from, and whether SSL is required when interacting with those remote IPs.

Important Note 1: to get the shell script on the MySql page working, you’ll need to change the directory in the script so that your openssl.cnf file is at /etc/ssl/openssl.cnf (if you’re running Gutsy).

Important Note 2: The “common name” field in your client and server keys must be different, or your key generation will fail. You’ll know you got it wrong if you run the shell script and it doesn’t ask you if you want to sign the certificate.

A couple other points not specifically called out in the MySQL documentation:

1) to tell MySQL to load with your server certificates, “sudo vi /etc/mysql/my.cnf”. There are a couple lines near the bottom of the file (under the mysqld section) that you can uncomment and change to point at location where your certificates reside.

2) To stop and restart your MySQL server on Ubuntu (necessary so the my.cnf file is reloaded), run “/etc/init.d/mysql stop” or “/etc/init.d/mysql start”. Yes, it won’t work unless you include the path (at least, it wouldn’t for me, and the other Google results I found).

After you think your SSL certificates are legit, you can double check your work by following the instructions here. The link has the commands for verifying that your server and client certificates are setup correctly.

Next, when you have the server certificates setup, you’ve granted access to your remote box (as specified in the MySQL instructions) and you’ve copied the client certificates to your remote box, I recommend testing your mysql connection from the command line on the remote box to verify that everything is kosher. Something like this should do the trick:

mysql -u [username] -p[password] -h [mysql box address] --ssl-capath=[path to client certificates] --ssl-ca=[path to ca-cert]/ca-cert.pem --ssl-cert=[path to client cert]/client-cert.pem --ssl-key=[path to client-key.pem]/client-key.pem

This assumes that you’ve retained the default filenames (ca-cert.pem, client-cert.pem, client-key.pem) mentioned in the MySQL documentation.

If all is well, you should get connected to your MySQL server.

Next up is to setup your database.yml so that it can do this stuff for you. I was somewhat surprised to find that database.yml actually already has (almost completely undocumented) options for SSL security. Do a find all in the Rails source for “:sslkey” and you’ll find all of the options that you can pass to database.yml. Make the database.yml options point to the correct address/filesnames, and you have yourself a more secure connection between remote boxes and your DB.

If you don’t feel like doing the find all, here are the relevant SSL options to put in your database.yml:

sslkey: /path/to/client-key.pem
sslcert: /path/to/client-cert.pem
sslca: /path/to/ca-cert.pem
sslcapath: /path/to/certificates