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