Sometimes you need to connect to a restricted server, by tunneling through a server that does have access — from your local machine. SSH tunneling and port forwarding are common ways to accomplish this.

Most people encounter the need to worry about port forwarding when attempting to connect to a database on a restricted host. This could be from a cloud provider, such as Amazon RDS or Microsoft Azure. Normally you’re web server will be granted access to connect to the database, but all other internet traffic will be restricted. To connect to the database, you need to seem as if you’re on the approved web server, so we’re going to “tunnel” through.

In this post, we’ll take a quick look at how you would accomplish this at the command line, and also entirely in ruby (and rails).

SSH Port Forwarding at the Terminal

Port forwarding can be accomplished at the terminal with a few additional options to the “ssh” command. You’ll need the following information to connect:

• username and password to the database instance (target)
• port for the database instance (such as 3306 for MySQL or 5432 for PG)
• username and either a password, or SSH key to the web server (tunnel)
• a port you’d like exposed on your local machine (convention is 3307 or 5433 for instance)

First you’ll want to just ensure that you can connect to the tunnel computer directly with a simple SSH command, such as:

ssh -l username hostname -i certificate.pem

Assuming that your connection credentials are correct, we can worry about setting up a tunnel to your local machine now.

ssh -L [local port]:[db hostname]:[db port] [tunnel username]@[tunnel hostname] -i certificate.pem

For a concrete example connecting to a MySQL instance:

ssh -L [local port]:[db hostname]:[db port] [tunnel username]@[tunnel hostname] -i certificate.pem

If you see a SSH prompt, you should now be able to connect to your db instance via ‘localhost’ with your db user/pass, but use port 3307 instead of 3306 (or whatever you chose).

ssl -L 3307:aws-rds.com:3306 matt@my-vps.com -i certificate.pem

That’s all great, but for a ruby project I might not want to have to remember to run a command before anything else, to forward a port locally. I’d like it to just work - hence we'll use port forwarding in ruby itself.

To achieve that goal, we’ll make use of the net-ssh-gateway gem. You’ll need all the same information as above, but we’ll actually open our port and forward inside the ruby process itself.

require 'net/ssh/gateway'
Net::SSH::Gateway.new('my-vps.com', 'matt', keys: ['certificate.pem']).open('aws-rds.com', 3306, 3307)

With that snippet in place, we’ve opened port 3307 locally and can now connect to the database, without running any other commands on our machine. If we stop this ruby process, then the tunnel is terminated as well.

Port Forwarding with Rails

I’m going to answer the question most will ask — can this be used with Rails. While I haven’t tested, this should work wonderfully with rails.

I know you’re using Bundler instead of installing gems globally, so there’s no need to require anything. I’d place the snippet to open the connection in an initializer, and probably wrap in an environment check like so:

# config/initializers/production_ssh_tunnel.rb
Net::SSH::Gateway.new('my-vps.com', 'matt', keys: ['certificate.pem']).open('aws-rds.com', 3306, 3307)

Setting up “database.yml” connection credentials becomes trivial:

production:
  adapter: mysql2
  username: db_user
  password: db_password
  database: db_name
  host: 3307

That’s all there is to it — ActiveRecord will connect to localhost port 3307, which is forwarded to your restricted server on the internet somewhere, and you’re in business.

I’d never recommend actually using this in a codebase as there is simply too high a chance for something to go wrong in a test, mistyped command, or at a console and poof you realize all too late you were operating against production data. Not to mention the version of your Rails app in memory (think a feature branch) could be vastly different than what is deployed on production server. You’ve been warned - it's dangerous to perform port forwarding in ruby in a production environment.

However, this could be a nifty way to code a migration script. Keep that thought, because in our next post we’ll explore how to write a ruby app with models and services that connects to 2 separate databases (PG and MySQL) to migrate data from one legacy system into another newer one.