Midnight Oil

One of the most irritating things about CloudFront is the lack of SSL support. It’s incredibly frustrating to install an SSL certificate, get all your routing set up, then watch the browser freak out because one teeny-tiny image comes through without encryption. A major pain in the ass.

Anyways, it’s possible to sidestep the issue by requesting the image directly from S3 instead of CloudFront. You are no longer leveraging the CDN, but in my case I’d rather have the page load slightly slower than have the browser complain about security flaws.

CloudFront Helper

I wrote the following helper to make it all easy:

module CloudfrontHelper
  # Will return a URL to an S3/Cloudfront image. If the current request is HTTPS, then it will return
  # an HTTPS URL (ie. S3) and if it is HTTP then it will return a Cloudfront URL.
  def cf_img_url(s3_image, *params)
    if request.ssl?
      s3_image.s3_url(*params)
    else
      s3_image.public_filename(*params)
    end
  end
end

SSL Config in amazon_s3.yml

The final step is to turn on SSL support for attachment_fu

production:
  bucket_name: my-bucket
  access_key_id: asdf
  secret_access_key: xxxx
  distribution_domain: [my-cloud-distribution]
  use_ssl: true

Example Usage

Now, anywhere you need to display an image that’s hosted on S3/CloudFront, just use the cf_image_url helper and it will automatically route to either the S3/https version or the CloudFront/http one depending on the protocol for the request. Simple!

< %= image_tag(cf_img_url(@user.profile_pic)) %>

Hosting a Rails app has always been a bit of a pain. Standard shared hosting was always pretty much out of the question, so most people naturally migrated to some sort of VPS. The obvious upside of being able to do whatever you want is a big draw. However, there is a dark side to every VPS: sysadmin work. Updating with patches, installing software, setting file permissions, and all that plumbing takes a lot of time. Then, there’s the big-daddy of them all: managing your own SMTP server. Sure, turning on Postfix or Exim is pretty easy. I just hope you don’t mind it when all your emails disappear into spam boxes.

For Obsidian Portal, we have been happily hosting with Slicehost for a while. Their uptime has been great, and we appreciate their services like automated backup and DNS management. However, we have been handling all our sysadmin tasks ourselves. So, when Ryan recently met one of the representatives from Blue Box Group, and he said that they’re happy to handle all that for us (at no additional charge!), we were quite interested.

Since we’re currently spinning up DoLeaf, we decided it would be a good time to see what we can get with a more hands-on hosting provider. And, after just a few days, we’re very impressed.

Choice of Distro

Blue Box’s basic installation is some flavor of Red Hat. Ryan and I are Ubuntu guys, so this was a problem. A single email to the support staff: “Hey, can you rebuild our server with Ubuntu server edition?” Answer: “Yep, it’s done.” Awesome.

Firewall

Anyone else think iptables is a bit of a pain? I can use Firestarter or Webmin, but when I try to manage it manually, forget it. Luckily it only has to be done once. Or, in the case of BB, never. Just send an email with the ports you want open. They set it up and replied, plus added a few standard ports that we forgot.

Rails Stack

BB supports several different Rails stacks. We’re used to Apache2 + Ruby Enterprise Edition + Passenger + Rails 2.3.2 + MySQL 5. Even though this kind of setup is pretty straightforward, it always seems to take me at least 1-2 hours just to get things set up in the most basic configuration. Now? You guessed it. Send an email and I’m done.

Functioning SMTP – The Great White Whale

We’ve always had problems with emails disappearing en-route. Getting Postfix set up is a 5 minute job, but your emails randomly get flagged as spam, especially by the big boys like Google and Yahoo. There are many different ways to counter this, like SPF and DKIM, but they are a pain to set up, and it’s always tough to know if you got it correct. This was the tipping point for me with BB. They have an SMTP server preconfigured with SPF and DKIM and you can use it for sending emails from your app. They limit you to 750 emails / hour, which is way more than most apps need, especially in the beginning. Considering that we had evaluated similar solutions that wanted to charge $0.01 / email, having this included for free in our hosting plan was mind blowing. Never again will I host anywhere that doesn’t do this.

And the cost?

The most surprising thing is that I haven’t been charged $0.01 for additional support. I’m still testing the waters to see exactly where that line is, but so far, I haven’t hit it. Plus, their prices are quite competitive. Slicehost is a little bit cheaper, but when you factor in the time saved on setup, I’d say we’re already way ahead.

We’ve only just started with Blue Box, and things may go south at some point, but for now we’re ecstatic with what we’ve received. If you want to spend more time coding and less time on irritating sysadmin tasks, then I highly recommend taking a look!

I’ve recently added a patch to attachment_fu that allows for serving out your S3-stored files via Amazon CloudFront. So, if you want an instant CDN, it doesn’t get much easier.

(Note: For Paperclip users, check out this writeup on intridea)

Get the plugin

Get the latest version of attachment_fu from github.

script/plugin install git://github.com/technoweenie/attachment_fu.git

Setup CloudFront

You’ll need to sign up for CloudFront. It’s as simple to join as any of the other AWS’s.

Once you’re signed up, you’ll need to create what are called distributions. These are essentially mappings from S3 buckets to CloudFront domains. You can use the command-line API if you want, but I found it easiest to just use S3Fox.

In S3Fox, find the S3 bucket that you want to serve out, right click, and choose Manage Distributions. You will see a screen like the one below:

Give the distribution a comment, make sure enable is checked, and click on Create Distribution. It will be InProgress for a few minutes, so just keep refreshing. Once it transitions to Deployed, you’re done!

Configuration

We’ve added one new option to the amazon_s3.yml file that comes with attachment_fu, distribution_domain. Copy the Resource Url (something like http://XXXXX.cloudfront.net) from S3Fox and enter it in your amazon_s3.yml file as the distribution_domain. Make sure to only get the domain and strip off the ‘http://’

Next, you’ll need to update all your has_attachment models that you wish to serve out. For each of these, add a cloudfront option like so:

class MyClass
has_attachment(
:content_type => :image,
:storage => :s3,
:cloudfront => true,
:max_size => 1.megabytes,
:processor => "Rmagick"
)
end

You’re Done!

That’s it! Now, everywhere you use myobj.public_filename, it will use the CloudFront domain and serve it through the CDN.

Bonus Points: CNAME

If you think the XXXX.cloudfront.net domain is ugly, you can set a CNAME on the distribution and do some DNS monkey-business to choose your own CNAME. I’ll do this at some point (and update this blog post), but so far it hasn’t been important to me.

You’re here because you want to be fully RESTful with Flex, found as3httpclientlib, and are now having some troubles. It could be because of the wonky Flash TCP security policies, problems with maintaining Rails sessions, or any one of a handful of other problems. Well, let me take away your burden, friend: Give up now and descend back into the darkness that is GET and POST.

Adobe, in their infinite wisdom, saw fit to only cover the basics with their built-in HTTP services. GET, POST, 200, 404, 500. Maybe there’s more, but not much. Stray into such strange territory as 201 (Created) at your own risk. Luckily, an enterprising 3rd party came along and figured out a way to get around this, creating the as3httpclientlib. Cleverly using XML sockets and connecting to port 80 allows for building your own web service controls inside of Flash. Brilliant! Unfortunately, it’s useless (or nearly so) for use in a real Flash app with a Rails backend. Give up!

I wrestled with as3httpclientlib for a few days, hoping that I could pair up Rails’s RESTful view of the HTTP with Flex. The first hurdle (as you know doubt know by now) was dealing with the Flash TCP security policy files. This stymied me for a few hours, as I had trouble serving the file from localhost to localhost. Eventually though, I figured it out. Still, requiring access to port 843 is a big red flag, as it immediately knocks out shared hosting and many other situations. So, unless you have control of every single server your Flex app will ever need to connect to, you’re screwed. One red flag…

Following my limited success with the policy files, I decided to push ahead. Immediately, I ran into problem two: session management. Without going into too much detail (since I don’t really understand it myself), the session information is not preserved across subsequent calls using the as3httpclientlib. URLLoader uses the browser’s connection to make the requests, and the browser handles transferring the session information back and forth. So, except in certain specific cases, using the URLLoader means that your Rails sessions just work. Not so with the as3httpclientlib. I didn’t test in detail, but it looks like a total game ender. I don’t think it’s possible (or at least easy) to read the cookies from Flash, and you’re going to have to manage sending that information yourself anyway. Red flag number two goes up…

Take my advice: Give up now and go back to URLRequest and URLLoader. The RESTafarian in me hates to say that, but you necessarily have to give up a lot of ivory-tower purity when you descend into Flex development.

Note: This is not a knock against the as3httpclientlib. From what I can tell, it’s a great library. I lay the blame for my problems at Adobe’s doorstep.

I’ve been playing around with Engines a bit at work, and I ran into an issue where I had to restart the server over and over due to class caching issues.

To make a long story short, I was able to force Rails to reload the particular engine I was using by adding the following to the engine’s init.rb:


%w(controllers helpers models views).each {|path| Dependencies.load_once_paths.delete File.join(File.dirname(__FILE__), 'app', path) }
Dependencies.load_once_paths.delete File.join(File.dirname(__FILE__), 'lib')


This worked with Rails 2.1 and an unknown version of the Engines plugin.

If you’re using the Facebooker SSH tunnels along with Phusion Passenger, you may run into an issue where you have multiple Rails apps running on your machine at different subdomains. Unfortunately, when facebook makes a request to your tunnel, it’s passed through on port 80 to your local machine and Apache chooses the first defined virtual host to serve the request, which may or may not be the one you want.

An easy solution is to use a ServerAlias directive in your vhost file that matches up with your tunnel domain.


<VirtualHost *:80>
ServerName my.local.rails.app.localhost
ServerAlias my.tunnel.domain
DocumentRoot /path/to/rails/app/public
</VirtualHost>


This works because the tunnel will pass the request through to your machine without changing any of the request headers. So, they come through with the Host header set to the tunnel domain, and Apache will match it to your server alias.

Update: (later that day…) A full stomach changes everything. Getting some breakfast lessened my git hatred quite a bit. Still, it manages to irk me on a daily basis, and I hate people who make their ill-thought-out blog posts disappear, so I will leave my hate-filled rant for posterity and a warning to myself not to post before I eat breakfast.

Update 2: (Feb 2010) I’ve been meaning to update this for a long time. I’m a 100% convert to git now. I’ve had my religious epiphany. I couldn’t understand how great it was at first, but over the past year I’ve finally wrapped my head around it. So, to all you haterz out there like me, just keep persevering. The light is there at the end of the tunnel. Now, back to the original rant…

Ok, I’m angry, so I’ll go ahead and make a ranty, heretical statement:

But wait! Micah, aren’t you the ultimate Rails fanboy? Don’t you jump on the bandwagon for everything the rest of the Rails lemmings do? RESTful routing? .html.erb instead of .rhtml?

Yes, in general, I do follow all the other Rails conventions and try to stay on the bleeding edge. I hate to feel like I’m falling behind, and that’s an easy feeling to get in the Rails community. So, I try to follow all the trends and at least educate myself on what it means and how it works. In most cases, like being RESTful, I eventually come to understand the underlying concept and appreciate the change. Learning new things often involves suppressing your own skepticism and the urge to stick to the known path.

So how is git different? Well, I’ve been using it for a few months, and the list of cons just keeps growing, while the list of pros stays nice and small. Sure, it’s fast, merging is dead simple, and I can scoff at all my friends still using subversion. But, every single time I want to do something, I have to look up a whole slew of commands. Why can’t I just memorize them? Because they’re horribly arcane and obtuse.

Want an example? Try deleting a remote branch. This seems like something one would do often, after merging a feature branch into the master. However, rather than using something simple, like “delete the remote branch”, you have to cast some arcane spell. For me, deleting a remote branch involves Google, every goddamn time.

The spark that set me off today was my attempt to connect to multiple repos on Unfuddle. For organizational reasons, I need to have multiple user accounts. Juggling usernames and passwords is bad enough. Luckily, they’ll allow me to use the same OpenID for both accounts. Still, when I tried to add my public ssh key, it complained that you cannot use the same public key for two accounts. Apparently, Github has the same policy.

Well, that was two hours ago, and I’m still trying to figure out how to get git to use my shiny new ssh key that is not named id_rsa. A little stumbling around got me some instructions on Github, but right at the top it says that it’s not an ideal solution and should only be attempted by advanced users. Hmm, using multiple different keypairs for different source control repositories does not exactly sound like an advanced feature. Do they recommend we use the same password for all our websites as well? Seems like a pretty basic concept to me.

Update: I was called “borderline retarded” in the comments for blaming git for the policies of github and unfuddle. Harsh, but true. I conflated git with hosts who provide git, and that’s not fair. So, github and unfuddle, my anger shifts to you!

Further, the recommended solution requires editing configuration files for ssh. So, I have to be an experienced ssh user to use git effectively? That’s ridiculous. Seriously, how many developers ever modify their ssh settings? I’ll muck around in git config files without worrying too much. Change settings in ssh without extensive research? Forget about it. Mess that up and you’ll lock yourself out of your machine, or worse, open it up wide to script kiddies who know more about ssh than you.

From a usability perspective, git sucks, and that’s that. If anyone feels compelled to defend git, please do so in the comments. However, don’t tell me how to do the things I’ve bitched about here. Tell me why it has to be so goddamn hard. To make an analogy, it reminds me of the Rails vs Java mentality. Java web apps are horribly convoluted. Sure, you can write tons of arcane XML and eventually maybe get your WAR deployed into Tomcat correctly. But why? Why waste time on this crap when it can be so much easier? Why spend minutes and hours trying to understand the obscurities of git when subversion, the ugly stepchild that it is, is still easier to use?

Subversion has its problems, but dear God why did the Rails fanboys have to jump to a worse solution? Now, in order to keep my certified fanboy card, I need to spend my precious development time tinkering with git, and then writing blog posts about how much I hate it.

If you allow rich text from your users in a Rails app, then you’re probably familiar with Textile. Either that or you’ve chosen the dark side, aka. Markdown. If that’s the case, I hate everything you stand for and I’ll bet you put your curly braces on the wrong line!

Assuming you’re one of The Chosen who has selected Textile as your input syntax of choice, you’ve probably got plenty of users who are turned off by the syntax. It may be easy for your average web developer to pick up yet another new syntax, but there are lots of people on the web who have never clicked the edit tab on Wikipedia, or think that links are specified with [URL=http://bbcode.com].

For these people, some sort of help is necessary. Quick tip sidebars, tutorials, and help pages only go so far. To make it truly useful you’ll probably have to dip into your Javascript bag of tricks. Luckily for you, a few others have already blazed that trail and created a handful of Textile utilities.

Textile Editor Helper (TEH)

NOTE: We’ve forked the Textile Editor Helper plugin to have more features and to conform to Rails 2.1 conventions. Our fork is available on github here:
http://github.com/felttippin/textile-editor-helper/tree/master

The Textile Editor Helper is a very simple toolbar that sits at the top of your textareas, much like the Wikipedia toolbar. It provides many of the most basic Textile options, like bold and italics, plus some text alignment. Unfortunately, it doesn’t include linking and image includes, which are really easy to do with Textile.

Supposedly, you can add your own buttons, but I haven’t confirmed this. Plus, I think the whole plugin is based around the idea of prefixes/suffixes for text (like * for strong, or _ for em), so anything that requires more than that might not be possible. So, TEH is a good solution if all you need is some basic formatting help.

Live Textile Preview

The Live Textile Preview (aka SuperTextile) is simply a Javascript implementation of the Textile parser. So, you can convert Textile to HTML on the client side. This allows users to experiment with the syntax and quickly see what the changes will look like.

Unfortunately, from my cursory testing, it only implements most of the Textile syntax. Basic text changes, images, and links are covered. However, advanced text alignment and CSS styling elements seem to be beyond its capabilities. So, it’s not perfect. Still, if you only need the basics, SuperTextile can make life bearable for your users.

Sanskrit WYSIWYG editor

Sanskrit is a true WYSIWYG editor, and the only one I’ve seen for Textile. However, it only does the absolute basics, so I can’t recommend it. Plus, I don’t think it’s a great idea to hide the raw Textile from your users. I’m a fan of the Wikipedia / Mediawiki way of things, where you provide some editing help, but your users are always looking at the raw text. Eventually, they’ll get the hang of things and won’t need the crutch of the editor buttons anymore.

markItUp!

markItUp! could be the most promising of all the solutions. A general purpose syntax parser written on top of jQuery, markItUp! can translate any syntax into HTML, provided you write the correct parser. Further, someone has already gone through the trouble and created a Textile parser!

I haven’t installed or tested markItUp!, but I was very impressed after playing around with the Textile demo. It seemed capable of most of the Textile syntax (including links and images), and even included a handy-dandy preview feature (you must render on the server side and return the HTML, I believe). So, it includes the best of all worlds: users work on raw Textile, but can get a live preview at the click of a button.

Update I finally got a chance to play with markItUp and it is hands down the best editor out of the bunch. Unless you need a true wysiwyg (in which case Sanskrit is the only one, AFAIK), then skip all the others and go straight to markItUp.

More?

Well, that’s all the Textile help I’ve found so far. Each one has its plusses and minuses, but any of them is probably better than a bare textarea with little or no help.

If I’ve left anything out, please let me know and I’ll add it to the list. With a little help, we can put together a comprehensive list of all the Textile tools available for Rails programmers.

I’ve had my first brush with EC2 today, and I’m thoroughly confused and exhausted. It definitely has a much steeper learning curve than the other Web Services. However, since you’re dealing with booting and configuring fully functional virtual machines, I guess that’s to be expected.

Still, I managed to spawn an instance, set all the necessary permissions, and then connect to it via ssh. Not exactly something to brag about, but it is a milestone. I consider it $0.20 well spent. (I had to terminate and re-run the instance because I didn’t store the key-pair RSA key the first time.)

The goal

For Obsidian Portal, the map processing and tiling is very CPU and RAM intensive. If run on our Slicehost VPS, it totally bogs down the whole system, making the main website completely unresponsive.

So, we’ve offloaded the processing to a machine in Ryan’s attic. Although it’s worked so far, it’s not exactly a professional solution. Plus, Ryan’s getting ready to move and his machine will be off for at least a week. That means we need a new place to run our stuff. Since I’ve been meaning to dive into EC2 for a while now, this seems like the perfect opportunity to put in place a permanent and professional solution.

The plan

Whenever we need to tile a map, I plan to spin up an EC2 instance and run the tiler. I’ll keep the instance up for the rest of the hour, in case more maps come in. When the hour runs out, if there are no more maps to process, then we’ll spin down the instance. Computing power on demand…nice!

grempe-amazon-ec2

If you’re a Ruby hacker, skip the EC2 toolset altogether. Just go get the grempe-amazon-ec2 gem and run all your EC2 requests through an irb shell. This allows for opening up the EC2 developer reference and learning the API commands straight from the docs, rather than having to learn the EC2 toolset. You’re probably going to have to spawn/terminate instances dynamically at some point, so you may as well just learn how right from the start.

Besides, the EC2 toolset route involves setting up a JVM, setting JAVA_HOME, and all that Java mumbo-jumbo that always takes way longer than it should. Save yourself a headache and go straight to the source.

ec2onrails

I’m not looking to run a full Rails server (yet), but we do want to run some rake tasks. Rather than hand build an image, I’ve decided to start with the ec2onrails image and see if that gets me most of the way there.

The main downside is that this image will need to install RMagick, ImageMagick, and possibly other gems and packages each time it spins up. ec2onrails has built-in support for this, but it means that the map processing can’t start for several minutes after the image boots up, meaning our users have to wait to see their maps. For now, it should be acceptable, but it’s something to improve on.

Next Steps

I’ve had enough for today, but there’s still a ways to go. I need to complete the following:

1. Setup the cron job on our VPS to spawn the EC2 instance whenever a map needs processing (and there’s not already an instance running)
2. Setup the script (rake task?) for the EC2 image that will do the processing. It may as well just run forever. Maybe it can be responsible for terminating the image? Hmm…that sounds dangerous. If it should crash out, then the instance will keep running and billing us!
3. Surely there’s more…

Oh, and one other next step: I’d better go terminate the instance I left running!

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.