Connect a local Rails app to an RDS Instance via an SSH tunnel through a bastion server
Tags: linux, aws, rails
Published: 2021-12-13 08:50:33

> 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](https://aws.amazon.com/premiumsupport/knowledge-center/rds-connect-using-bastion-host-linux/) 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](https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/AccessingInstancesLinux.html).

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:

```bash
# ~/.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.

```yaml
# 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:

```yaml
# 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):

```bash
# .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: 

```ruby
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](https://mpharrigan.com/2016/05/17/background-ssh.html)).

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).

```bash
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`:

```bash
# ~/.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