How to Connect to PostgreSQL Servers through SSH Tunnels

The data sets that are provided to the Stevens community by the Hanlon Financial Systems Laboratories are often quite valuable and, in some cases, security-sensitive. Among the ways of securing communication between the labs' servers and users' computers is SSH tunneling, which is technically quite elegant but not very friendly to beginners.

In this article, we will discuss how to use pgAdmin to access the databases with SSH tunnels. pgAdmin is a very common choice for beginners to work with PostgreSQL databases. We also recommend reading the page linked below, which may help you better understand the mechanism of SSH tunneling, and how to use SSH tunnels for PostgreSQL in the command line.

Secure TCP/IP Connections with SSH Tunnels

By default, a PostgreSQL database server only listens to local connection requests. It does not accept any connection requests except the ones coming from the “localhost” address. By launching a SSH tunnel between your computer and the Linux server, you are binding the two computers into one “localhost” with one specific port number on each side. After launching the tunnel on your computer, pgAdmin will try to connect to a PostgreSQL server on the “localhost” using the port number you specified while setting up the SSH tunnel. On the remote server, the PostgreSQL server will receive a connection request coming from a “localhost” address. In this tunnel, all the data traveling between your computer and the remote server will be encrypted by the SSH protocol.

Step 1: Request access to the PostgreSQL Server

When you request access to a dataset from Hanlon Labs using the Resource Access Request Form. You will need to email us, at fscadmin@stevens.edu, your public key. If you are not family with how to generate a public-private key pair, you can read the following page from AWS or you can send your questions to us by email. Please remember to name your private key properly and keep it in a safe place that you will not forget or lose. If you private key is leaked to someone else, please email us at fscadmin@stevens.edu.

Create a key pair for your Amazon EC2 instance

Step 2: Connect to Campus Network or VPN

Your computer has to be on the campus network or Stevens VPN to access our servers. You can follow this page from our IT department to request access to the VPN.

Step 3: Set up pgAdmin

Right click on "Servers" to create a new server connection.

In the "Name" field, enter a name for the server.

Type "localhost" in the "Host name/address". Enter your PostgreSQL username and password provided by Hanlon Lab and save your password if you would like.

Switch on the "Use SSH tunneling" tab. Enter the hostname provided by the lab in "Tunnel host." Enter your Linux username provided by Hanlon Lab. Switch the authentication method to "Identify file". Specify the path to your private key file using the three dots. Click "Save" and you will be able to access the server.

For any questions or concerns, please contact us at fscadmin@stevens.edu.