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 we believe is the most technically elegant way.
In this article, we will discuss how to use pgAdmin to access the databases with SSH tunnels because it is the most common choice for beginners to begin working with databases. We also recommend reading this page, which may help you better understand the mechanism of SSH tunneling, and how to use SSH tunnels for PostgreSQL in the command line.
Here's how SSH Tunneling works. 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 a 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 request form here and you are approved, we give you access to a Linux server that hosts the dataset. We only use key pair authentications for these servers. You will need to email us at email@example.com and send your public key or request us to help you generate a key pair if this is your first time doing this. You need to name your private key properly and keep it in a place that you will not forget or lose.
Step 2: Connect to Campus Network or VPN
Your computer has to be on the campus network or VPN to access our servers. When you are physically off campus, you will need to connect to Stevens VPN before accessing them. You can follow this page from our IT department to request access to the VPN.
Step 3: Set up pgAdmin
For any questions or concerns, please contact us at firstname.lastname@example.org.