Midnight Oil

Recently for Obsidian Portal, we decided that we wanted to move some particularly intensive graphic processing offline from the main server. It was simply consuming too much CPU time and making the entire site sluggish. Luckily for us, it was already set up as a background process that only needed to connect to the database and Amazon S3. So, to do it on a whole new server wouldn’t require changing any of the algorithms.

However, since the remote server would be connecting to the main database over the Internet, we decided that encrypting the communication was probably a good idea. None of the actual data is all that sensitive, but the database username/password definitely is. Plus, in general, our policy is that any communication to and from our server should be encrypted.

It turns out that enabling SSL in MySQL is not too hard, but there are a lot of steps to follow. Further, to Rails docs on using database.yml to set up the connection aren’t that great (big surprise there). So, to help out those who are in the same boat, here’s what I did.

Setting up the MySQL server

The first thing to do is read through the official MySQL docs on SSL connections. These provide a good overview on how to configure the server to allow for (or force) encrypted connections. However, they assume a little knowledge of SSL and CA’s and keys and whatnot. So, if you’re stumped, you can read the following steps on how to proceed.

Verify that SSL is supported

We’re running Ubuntu on our server, and the MySQL that comes with it has SSL support already compiled in, so that’s a big relief on our part. I hate compiling from source, especially something big and important like MySQL.

To verify that support is already compiled in, log in with the mysql client and try the following:

show variables like ‘have_ssl’;

If it says DISABLED, then you’re in the right place. If it says YES, then you’ve already set the server up and can skip to the client or Rails sections below. If it says anything else (like no variables are returned) then it’s time to recompile MySQL. That’s beyond the scope here, but I wish you the best of luck.

Create the required SSL keys and certificates and whatnot

I’m no security expert, so all the SSL / CA / certificate / key advice is at your own risk. I’m still learning a lot of this stuff.

In order to get your server set up, you will need 3 files: A certification authority (CA), a certificate, and a key. Like I said, I really don’t know what all these things are. I just have a vague understanding.

To create my necessary files, I used TinyCA2, which I heavily recommend. It provides a GUI for using OpenSSL. Otherwise, get ready for lots of arcane command lines. If you’re on debian/ubuntu, all you need to do is run the following:

sudo apt-get install tinyca
tinyca2

Using TinyCA2, the process is a snap. It will walk you through creating a CA, then generating a certificate and key from that CA. I don’t know what options are required, but I got away with specifying only a common name for the CA and the certificate. Plus, I also used 1024 bit encryption since I’m not sure what level MySQL supports. I also heard somewhere that the common name for your CA and the certificate should be different, so watch out for that.

Once you’ve created your CA, certificate, and key, then you need to export them as pem files. In order to get MySQL to read the key, I had to export the key without a password. This is generally very bad advice, since if anyone gets the key they can pose as you. However, if the key is password locked, then MySQL would have to get the password from you somehow (Apache does this on startup), and maybe that’s just not supported. Please correct me if I’m wrong.

Configure MySQL to use the generated files

Copy the 3 files into /etc/mysql and then edit /etc/mysql/my.cnf Add the following lines:

ssl-ca=/etc/mysql/cacert.pem
ssl-cert=/etc/mysql/my-new-server-cert.pem
ssl-key=/etc/mysql/my-new-server-key.pem

Note! Make sure you check the file permissions to ensure that the mysql user can read the files. If you followed my previous bad advice and exported your key without a password, then it’s extremely important to strictly control the read permissions on these files. Chown them to the mysql user or use groups. Do not just chmod 777 and blissfully continue.

Restart your MySQL server and make sure there are no startup errors.

Test that everything works

The first thing to do is log in on the mysql client and check the have_ssl variable. Just run the following from the mysql client.

show variables like ‘have_ssl’;

If it says YES, then you’re good to go. If not, something went wrong and you need to retrace your steps to find out what’s up.

Client setup and testing

Now that the server is setup, let’s verify that we can connect with a client. The first thing to do is create a new user that only has SSL connection availability. Connect as root (or someone with grant privileges) and run the following:

GRANT ALL on somedatabase.* TO ’ssluser’@'localhost’ IDENTIFIED BY ’some_password’ REQUIRE SSL;

This will create a user that can only connect from localhost and must use SSL. Now, to make sure that everything is working, try this:

mysql -ussluser -p –ssl-ca=/etc/mysql/cacert.pem

If you are able to log in and don’t get the dreaded SSL ERROR message, then everything is great! If you do get an error, the first thing to check is the read permissions on the cacert.pem file. It must be readable by the current user. If your read permissions are set correctly and you’re still getting errors…sorry I can’t help.

Before we move on, it’s important to note that we’re using the same CA pem file as we did to create the server’s certificate and key. I really don’t understand why MySQL clients are required to specify a CA certificate, and I don’t know which are allowed. Presumably, you can set the server to allow clients to specify one of the major CAs (like Verisign or GoDaddy). Still, that’s beyond my knowledge. I tried creating a second CA and specifying that on the client side, but the server refused the connection. For now, it seems that you will simply have to copy the CA certificate to every remote client that wishes to connect to this server. That seems strange to me, and I’m probably wrong here. Please correct me in the comments.

Connecting from Rails

A small bump in the road…

The official Rails MysqlAdapter documentation lists the SSL parameters to use in database.yml. Unfortunately, the docs seem to be out of date and missing a very important parameter, sslca, the certificate authority file we need for every SSL client connection.

Further, the actual adapter code will not set any of the ssl parameters unless the sslkey option is set. This parameter is unnecessary in many cases, such as where you only want to require SSL communication and not X509 authentication of the client.

I have created a ticket and patch at lighthouse to cover this issue, but for now, you’re going to have to take a few extra steps to get things working.

Create a client key and certificate

While not strictly necessary according to MySQL, due to the coding of the Rails MySQL Adapter, you will need a client key and certificate. Like I said, I’ve submitted a patch, but no sense waiting on that.

Fire up TinyCA2 again and create a new certificate (select new client certificate and key) using the same CA that you used to create the server’s certificate and key. Again, export both the certificate (call it something like mysql-client-cert.pem) and the key (mysql-client-key.pem). Also, remember to export the key without a password!

Put these somewhere accessible to your Rails app. I will assume that you put them in the db directory. Make sure they are readable by your Rails app’s web server user.

Finally, place a copy of your cacert.pem in the db directory as well. Using TinyCA2, just go to the CA tab and click the export button. Drop in the db directory and check the file permissions.

Update your database user permissions

Update your Rails app’s user in mysql to give them remote access permissions. Assuming they are currently set to access only from localhost, the following line will extend access to your remote client.

GRANT ALL on my_rails_app_db.* TO ‘my_rails_app_db_user’@'my.remote.client.com’ IDENTIFIED BY ’somepassword’ REQUIRE SSL;

Check your MySQL options and firewall

Make sure your firewall is set to allow incoming connections on the MySQL port (defaul 3306) and that your MySQL server is set to allow connections from more than just localhost.

Test using the bare mysql client

At this point, I would test that MySQL is accepting outside SSL connections by trying to connect using the mysql client from the remote machine. Something like:

mysql -umy_rails_app_db_user -psomepassword -hmy.mysql.server –ssl-ca=/path/to/rails/app/db/cacert.pem

If you cannot successfully connect using this, then you’ll need to troubleshoot your remote connection before trying to do anything with the Rails connection.

Update your database.yml

Add the following lines to your database.yml

sslca: /path/to/rails/app/db/cacert.pem
sslkey: /path/to/rails/app/db/mysql-client-key.pem
sslcert: /path/to/rails/app/db/mysql-client-cert.pem

Fire it up!

Fire up a Rails console on your remote client. If all goes well, you will be presented with the standard console prompt. You can verify that everything is working by executing the following

ActiveRecord::Base.connection.execute(”show status like ‘Ssl_cipher’;”).fetch_row

If you see something like DHE-RSA-AES256-SHA, then you’re set!

Congratulations! Your communications are now encrypted!

File summary

Since we’re dealing with so darn many pem files, I thought it might be nice to have an index of exactly which files you need and where you need them.

On the MySQL server

/etc/mysql/cacert.pem
/etc/mysql/mysql-server-cert.pem
/etc/mysql/mysql-server-key.pem
All files must be readable by the MySQL user

On the remote client

/path/to/rails/app/db/cacert.pem
/path/to/rails/app/db/mysql-client-key.pem
/path/to/rails/app/db/mysql-client-cert.pem
All files must be readable by the Rails webserver user

The cacert.pem files must be the same, and all the certificates and keys must be generated using the cacert (CA). In addition, the keys must be exported without passwords. Divert from this at your own risk!

Correct me please!

As I have said, I am not a security expert. If there is anything in here that is bad practice or just blatantly wrong, please correct me! The official documentation isn’t great, so it’s up to us (the community) to help each other out.

Help us climb

If you like this article, please link to it with the text AisleTen’s guide to connecting to MySQL using SSL encryption in Ruby on Rails. A few incoming links like that will help this article be near the top when people google for MySQL and SSL and Rails.