pgAdmin 4: How to establish a connection through an SSH tunnel/interface in Windows

Question

I installed the latest pgAdmin 4 (v2.1) but I found out that (related thread), for the moment, the only way to establish a connection through an SSH tunneling is using another outside process, an example in the pgAdmin's forum says:

I setup a tunnel as follows:

ssh -f dpage(at)hostname(dot)enterprisedb(dot)com -L 5999: hostname.enterprisedb.com:5432 -N

(...) I was running on Mac, but the same procedure should work on Linux. On Windows, there's a tunnelling app in Putty iirc, but I don't know how to use it offhand.

I have found a simple PuTTY tutorial to establish a connection using an SSH Tunnel.

The connection is established but it seems, when I try to connect through pgAdmin it fails or is refused (using 127.0.0.1:3306 or 127.0.0.1:5432 as the tunnel's destination). Why?

Answer 1

So in this step of that PuTTY SSH tunnel tutorial:

I used instead 5432 as the Source port and my.tunnel.domain:5432 as the Destination.

Then in the connection details:

In the Host Name I placed the server to connect to (the one with the DB: my.db.domain), but the login credentials relate to the tunnel's my.tunnel.domain (e.g. Connection > Data has the tunnel's domain user and Connection > SSH > Auth has the tunnel's domain private key).

I opened this connection (it opens up a window with a terminal, if successful) and finally in the pgAdmin 4 (v2.1) Create Server... window, I just added localhost as the Host name/address and 5432 as the Port, with the credentials relating to the my.db.domain:

The DB connection is established and works normally if the PuTTY window with the terminal is active.

Another and more verbose tutorial on how to set a Putty SSH tunnel can be found here.

Answer 2

In newer version of PGAdmin4 you can configure SSH-tunnel (there is a tab for this there, but you need to use key in PEM format, not in RFC4716 that is now generated by default with ssh-keygen.

Use

ssh-keygen -m PEM -t rsa -b 4096

More information in my article.

Answer 3

I just wanted to add one tip to CPHPython's excellent answer because I've just wasted several days trying to find my mistake. My postgresql server running on my remote server runs on the standard port 5432 and my local postgresql server running on my Windows 10 laptop also uses port 5432. So, I needed to assign a free local port to this tunnel. I chose 8002. Here is my mistake when configuring the tunnel: I entered 8002 for the Source port (correct), but I entered localhost:8002 for the Destination (WRONG!)

The following screenshot shows the correct settings. The Source port is the port you want to use on your local machine, but the Destination needs to use the port assigned to Postgresql on the remote server (it's obvious now with hindsight), the standard 5432.