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.
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
~/.ssh/my-aws-server-keyis the path to the key I use to connect to the server
johnnyrocketis my username on the server (not my database)
00.000.00.000is the IP address of my server
5433is the port I’ll use to access the server locally
localhostis (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
5432is 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.
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
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
00.000.00.000 from my tunnel above).