SSH in Your Database

When I want to access and query a database sitting on a remote server, I generally open an SSH tunnel to the database. The tunnel lets other applications running on my machine talk to the database as if the database itself were also running on my machine. So, for instance, this lets me configure and use a database client (such as Postico for PostgreSQL) to access the database on the remote server.

Opening a Tunnel

The command I run in the terminal to create an SSH tunnel might look like:

ssh -i ~/.ssh/my-aws-server-key -N -L 5433:localhost:5432 johnnyrocket:00.000.00.000

Where:

1. ~/.ssh/my-aws-server-key is the path to the key I use to connect to the server
2. johnnyrocket is my username on the server (not my database)
3. 00.000.00.000 is the IP address of my server
4. 5433 is the port I’ll use to access the server locally
5. localhost is (perhaps confusingly) the hostname of the database that’s running on my server. If you used something like Amazon RDS instead of managing the database yourself on a server, you’d have a longer, unique hostname
6. 5432 is the port used by my database on my server

Effectively, this command connects to my server via my SSH key, then relays traffic between localhost:5432 on my server and localhost:5433 on my machine.

Using the Tunnel

I can then create a connection with Postico to run SQL queries and haphazardly delete a bunch of data or otherwise explore the remote database. My connection configuration might look like the image below. I use localhost and 5433 as the host and port, because that’s the local location that traffic will be forwarded to. The user, password, and database name are for the database on my remote server (the one running on localhost:5432 at 00.000.00.000 from my tunnel above).