Ssh – pgadmin won’t connect to Postgresql via ssh on macOS

connection-refusedpostgresqlsshssh-tunnelterminal

I am trying, with no success, to connect pgAdmin 4 to my Postgresql installation on Linode using ssh tunnel as specified in https://linode.com/docs/databases/postgresql/securely-manage-remote-postgresql-servers-with-pgadmin-on-macos-x/

It did not work, with pgAdmin giving me:

Unable to connect to server:

could not connect to server: Connection refused
Is the server running on host "localhost" (::1) and accepting
TCP/IP connections on port 5432?
could not connect to server: Connection refused
Is the server running on host "localhost" (127.0.0.1) and accepting
TCP/IP connections on port 5432?

I spent hours searching for a solution and nothing seems to work. I am for sure missing something. Here is what else I tried:

  • I went through all the steps in https://blog.bigbinary.com/2016/01/23/configure-postgresql-to-allow-remote-connection.html
  • my netstat -nlt shows:

    tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN
    tcp 0 0 127.0.0.1:587 0.0.0.0:* LISTEN
    tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN
    tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN
    tcp 0 0 127.0.0.1:5433 0.0.0.0:* LISTEN
    tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN
    tcp6 0 0 :::80 :::* LISTEN
    tcp6 0 0 :::22 :::* LISTEN
    tcp6 0 0 :::5432 :::* LISTEN
    tcp6 0 0 ::1:5433 :::* LISTEN
    
  • my ufw status shows:

    Status: active
    To Action From
    22 ALLOW Anywhere
    5432/tcp ALLOW Anywhere
    22 (v6) ALLOW Anywhere (v6)
    5432/tcp (v6) ALLOW Anywhere (v6)
    
  • I certainly restarted Postgresql more often than not (for every single modification try)

  • if I run psql -h localhost -p 5433 -U postgres it works fine
  • also with psql -h localhost -p 5432 -U postgres all works fine

But nothing else I do works for pgAdmin.

Does anyone can shed a light on it? What am I missing? What else could I try?

Thank you
Greetings from Beijing!
Daniel

Best Answer

It was indeed a stupid error, certainly because I a total newbie on this:

As per instructions, I entered the command

ssh -f -L 5433:127.0.0.1:5432 username@remote-host -N

while already logged into my Linode server. This should have been done on my local machine, before logging in Linode.

Otherwise my Mac would have no way of knowing about the tunnel! So obvious, not that I look at it.