AWS has become a fairly ubiquitous hosting option for small companies. But developers typically work on local dev setups outside the host and occasionally need access to the main dev and staging databases located on AWS. How do you do this and still run in a secure environment? One thing you don’t do is poke a hole in AWS’ firewall and run PostgreSQL’s port in the open. An interested hacker only needs to sniff the connection to get the login credentials, then he has complete access to the server as user postgres via psql’s shell-out command, \!, where he can do lots of damage, including planting malware. That’s assuming he doesn’t just try and succeed with a typically obvious database login like ‘www’ first.

A more secure option would be to remove PostgreSQL’s default port 5432 from AWS’s router exceptions and tunnel that connection through the SSH port. Tunneling is a common way to support lots of services on a Unix box without opening up a lot of vulnerable ports to the outside world.

The mechanics for tunneling (a/k/a port forwarding) seem complex but fortunately the implementation is not. So long as you can log into an interactive shell via SSH on the remote server hosting the database you can tunnel connections to it, including PostgreSQL’s.

THE DETAILS

Step 1 is making sure you can log into the server. Amazon maintains good instructions for doing so. Just know that tunneling will not work unless you can establish an SSH connection to the host because tunneling piggybacks on an open SSH session.

AWS servers we use two layers of credentials: AWS’ keypairs as well as credential-only SSH logins (no password access). That means two identity files. To start a tunneling session we use this command on a local server. This will initialize a tunnel for PostgreSQL on ecX-XX-XX-XX-X.compute-1.amazonaws.com:

ssh -L 2345:localhost:5432 -i ~/PATH_TO/AWS_KEYPAIR.pem -i ~/PATH_TO/id_rsa YOUR_LOGIN@ecX-XX-XX-XX-X.compute-1.amazonaws.com

This looks very similar to what you use to SSH into a shell on an AWS server, with the addition of the port forwarding instructions in the -L argument. What this does is park the remote PostgreSQL port (5432) on your local box on port 2345.

If all is well, you should be greeted by a normally functioning interactive shell on the AWS server. You can use this shell conventionally too. Just don’t log out of it or you’ll also snuff the connection to PostgreSQL.

Step 2: To access the database via the psql client, use this command on a local shell on your computer:

psql -h localhost -p 2345 -U USERNAME DATABASE

If you also have a password on your database you can set it in a PG_PASSWORD environment variable.

You should see the psql database prompt as usual:

psql (9.3.4, server 9.3.3)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.
database=>

Once this is confirmed as working, just modify your local connection string to use these arguments.