Connecting to a protected RDS (MySQL) instance through another box (via ssh?) from rails

Question

We have a read only MySQL database (prod mirror) hosted on Amazon AWS as an RDS instance. I'd like to access it remotely, but for security it can only be reached from the office network via a whitelist.

When I'm in the office, I just set the config/database.yml to point to the prod mirror and everything is fine.

However, I need to access it even when not in the office, without seriously compromising security.

There is a physical server in the office which I have ssh (key) access to, and I can access the database directly via that, but I'm not sure how to get rails to connect through that so that I can actually load a page on my laptop with rails using the prod data (I can't run rails on the office server).

I've tried a basic ssh tunnel over port 3306, and general proxying (running mac os x), but as expected neither worked. We don't have a vpn and we can't set one up at the moment. We are open to changing our AWS configuration as long as security won't be compromised, but an ideal solution would only involve some form of tunneling and futzing with database.yml.

(The current inconvenient workaround is to manually add an ip to the whitelist whenever I need to do remote work and then remove it afterword!)

Answer 1

Tunnelling the DB connection over ssh should work, I've used it many times. In your Rails config be sure to set the host to 127.0.0.1 and not localhost which the mysql driver will treat differently.

An example setup:

In config/database.yml:

development:
  adapter: mysql2
  database: yourdb
  username: user
  password: pass
  host: 127.0.0.1
  port: 8888

Your ssh command:

ssh -v -L 8888:your-db-endpoint.rds.amazonaws.com:3306 [email protected]

Specifying -v to ssh will show you debug messages when the tunnel gets used, which is useful to see if it's working. For example:

debug1: Connection to port 8888 forwarding to XXX.rds.amazonaws.com port 3306 requested.