Easily connect to postgres via proxy with service

A client has their RDS postgresql databases locked down to only allow connection from their EC2 instances. I found an easy way to connect with my local clients.

Add a local forward to ssh config

In ~/.ssh/config add something like

Host prod.client
  User ubuntu
  Hostname prod-ec2-instance.example.com
  LocalForward localhost:5433 rds-gibberish.us-west-1.rds.amazonaws.com:5432
  IdentityFile ~/production-ec2-key.pem

Add the credentials to pg service

In ~/.pg_service.conf save the user, database name, and password

[client-prod]
host=localhost
port=5433
user=rds-user
dbname=client-production
password=blablabla

Now you can start your ssh tunnel in 1 terminal:

ssh prod.client

And connect with any postgres client tool (pg_dump, psql, etc.) in another:

psql service=client-prod

warning

Well you just saved a production password to a plain text file, and now you can easily connect and muck things up in production. Make sure your machine is secure and be careful and stuff.

Tweet