Connect a local Rails app to an RDS Instance via an SSH tunnel through a bastion server

Editor's note: this is supposed to be a bit tongue-in-cheek. We've all felt like this before!

Right, here's what we're doing; local Rails app connects to a local Postgres instance in the usual way. Fine. But because reasons it also connects to another database that is massive and a total Pain In The Ass (PITA).

Because "The Man" cares about security, the only way to access that second RDS instance is through a "bastion" server, which makes this a 10x PITA. Here's probably the worst guide in the world to setting up the sort of configuration we're working with.

I can't believe I have to do this. I hate computers.

Fine.

Connect to the bastion server

The first thing we have to be able to do is to access the bastion server from our local machine. Let's assume you know how to do this. You basically want to run something like ssh user@ip where user is the username of the account you'll be accessing the machine as and ip address is the IP address of the stupid computer getting in the way of you and your multi-database dreams. You'll want to add your ssh keys to the box and stuff. Figure it out.

Then we have to connect from the freakin' bastion server to the RDS instance living inside it's ivory tower with no bloody internet access. Urgh.

On the bastion server we should be able to run something like psql -h "<a.very.long.hostname.us-east-1.rds.amazonaws.com>" name_of_database username. Fill in the blanks, smash that enter key and probably enter a password. You know the password, right? Right.

Great. We can access the database. This is 10x engineering right here - accessing a sodding database. Welcome to the bleeding edge.

Now we have our computer talking to the bastion server and the bastion server talking to the RDS. Wonderful. I'm so pleased. Now all we gotta do is get this box on our desk to talk to the RDS instance through the bastion server. Joy.

Tunnel from the bastion server to the RDS instance

This is so annoying.

What we want is to forward a port on our local machine to a port on the bastion server, which in turn forwards traffic on that port through to the appropriate port on the RDS server, adding absolutely nothing of value on the way except "security".

Here's how we do that. ssh has a -L flag that does... something. Basically you give it the local port you want the connection for forward, the host you want it forwarded to, and the port on that host that the traffic should end up on. Or something like that. I don't know. I didn't really read the man page.

For us, that makes the whole thing look something like this:

ssh -L 5433:a.very.long.hostname.us-east-1.rds.amazonaws.com:5432 username@bastion-ip-address

Actually using it

That's a bit of a mouthful to type out every time, so here's what we're going to do; we'll use the ~/.ssh/config file to set configure an ssh connection and specify the tunnel in there so that every time we ssh into the box the tunnel will be set up and our connection to the remote database will Just Work™ (aye, right).

Here's what our ~/.ssh/config file is going to look like:

# ~/.ssh/config

Host stupid-computer
  HostName <bastion IP address goes here>
  User <bastion username goes here>
  IdentityFile ~/.ssh/id_rsa
  LocalForward 5433 <a.very.long.hostname.us-east-1.rds.amazonaws.com>:5432

(The LocalForward bit isn't exactly the same as when we used the -L flag. It's LocalForward <local port> <remote database uri>:<remote port> with a space between the <local port> and the rest of it.)

This tells ssh that when we run ssh -Nf stupid-computer, it should connect to the bastion server and forward any traffic on local port 5433 to remote port 5432 on the RDS instance.

-N tells ssh not to execute any remote commands (just forward ports) and -f tells ssh to fork into the background. This has the effect of you now not knowing what the h*ck ssh is doing anymore, but we'll do something about that later.

For now you can ps aux | grep ssh to find the process id and then kill <process id> when you want to stop the tunnel.

Configuring Rails

Now we get to hook our Rails app to the database. Again, this is forefront of technology right here. Connecting a CRUD app to a database. Doesn't get any more exciting than this.

# config/database.yml

remmote_db:
    database: name_of_the_actual_db
    username: db_username
    password: db_password
    host: localhost # may need to be 127.0.0.1 if using mysql
    port: 5433

Or if you're not an animal and prefer to connect to the database via a URL:

# config/database.yml

remote_db:
    url: <%= ENV['REMOTE_DB_URL'] %>

and in yr .env file (or however you go about managing that sort of thing):

# .env

REMOTE_DB_URL: postgres://<db_username>:<uri_encoded_db_password>@localhost:4533/<name_of_the_actual_database>

Hooking it all up

Run ssh stupid-computer and then boot yr Rails app. Assuming you have an Active Record model that looks a bit like this:

class Whatever < ApplicationRecord
  establish_connection :remote_db
  self.table_name = "whyyy"
end

You should be able to ./bin/rails c and Whatever.count should return the number of rows in the whyyy table in the public schema on remote_db.

And that's about it. You need to have the ssh connection to the bastion server up and running in order for your model to be able to access that database.

Wrangling that ssh process

This is all fine and it works but we have to have the ssh process running and we're definitely going to forget to do that and then it'll all break and we'll wonder why and why oh why do we have to do these things?

The main problem is that output of ssh stupid-computer doesn't really give us any feedback, so we don't know if that's up and running.

We can fix that with some more ~/.ssh/config and a couple shell aliases (shamelessly stolen from this post).

We're gonna tell ssh to connect and create a control socket (whatever that is) for our connection by passing it the -M flag and updating our config.

To make our lives easier, we'll create some aliases in .bashrc (note the addition of the M to the command we used above).

alias sc-up="ssh -NfM stupid-computer"
alias sc-status="ssh -TO check stupid-computer"
alias sc-down="ssh -TO exit stupid-computer"

And we'll specify where we want the control socket to live by adding a line to our ~/.ssh/config:

# ~/.ssh/config

Host stupid-computer
  HostName <bastion IP address goes here>
  User <bastion username goes here>
  IdentityFile ~/.ssh/id_rsa
  LocalForward 5433 <a.very.long.hostname.us-east-1.rds.amazonaws.com:5432
  ControlPath ~/.ssh/stupid-computer.ctl

Now when we want to start the ssh tunnel we can simply sc-up. When it blows up we can run sc-status, see we didn't start the tunnel yet and run sc-up, and we can kill it all off when we have given up on life with a simple sc-down.

Conclusion

Computers are rubbish. "The cloud" is just somebody else's computer, therefore is also rubbish. The struggle is real.

Now if you'll excuse me I have some actual work to do.

References:

https://myopswork.com/ssh-tunnel-for-rds-via-bastion-host-6659a48edc
https://stackoverflow.com/questions/26774264/how-do-i-configure-rails-for-password-less-access-to-remote-database
https://dev.to/thebenforce/connect-to-a-postgresql-server-via-ssh-tunnel-832
https://aws.amazon.com/premiumsupport/knowledge-center/rds-connect-using-bastion-host-linux/
https://codingsight.com/connecting-a-bastion-server-to-a-postgresql-server-via-secure-shell-tunnel/
https://mpharrigan.com/2016/05/17/background-ssh.html

Comments

[Andres Leon]

Nice article!
I gave it a go and it worked for me, although, I had some gotchas that I wanna share

• In config/database.yml host needs to be 127.0.0.1instead of localhost, otherwise, I get the following error Mysql2::Error::ConnectionError: Access denied for user 'dbusername'@'localhost' (using password: YES)
• In the command aliases you are missing the - in the TO options

[Adam Rogers]

Thanks! Updated on both counts.