In a recent sequence of posts on deploying a Rails application to Elastic Beanstalk (Step1 is here) we set up a secure VPC with public and private subnets. Part of this secure setup is to place your PostgreSQL database server in the private subnets and not make it publicly accessible.
This is great from a security perspective, but raises the question - how do I run my rails console against the database? Typical reasons for doing this might be to bootstrap your users (e.g. create the first Admin user). Or for when you need to debug some data issue.
This post runs through how to tunnel through your Bastion server to your database.
To tunnel through to our database we use
ssh -i [IDENTITY_FILE] -L [LOCAL_PORT]:[RDS_HOST]:[RDS_PORT] ec2-user@[BASTION_HOST]
An example of what this command might look like is:
ssh -i DemoKeyPair.pem -L 6543:demo.c2syu2demiip.eu-west-1.rds.amazonaws.com:5432 email@example.com
This will create a port mapping through our bastion server from our local machine port 6543 to demo.c2syu2demiip.eu-west-1.rds.amazonaws.com port 5432.
Once this is successfully set up we can connect to our database as if it was running locally:
psql -h localhost -p 6543 -U db_user -d demo
And to run our rails console (assuming you have setup your production database yml to use
DATABASE_URL=postgres://db_user:Y509q2XJ8M@localhost:6543/demo DISABLE_SPRING=1 bundle rails console production
One important thing to note -
DISABLE_SPRING=1 - this is necessary as otherwise spring will bring up the console with the cached environment from previous runs. This can lead to you connecting to the develop database or to errors if you don’t have PostgreSQL running locally:
Is the server running locally and accepting connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
As always, once you are finished doing what needs to be done with the database, make sure you shutdown your Bastion server. Ideally it should only be run on an as needed basis.