2

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

3
  • If exactly at the same time, with an ssh instance redirecting localhost port 5432 to some outside server, psql -h localhost -p 5432 works but pgAdmin configured the same way does not, you then have a strange problem... You should really make sure pgAdmin uses this configuration. For low level diagnostics you could use strace and tcpdump to discover what it does exactly. Make sure to use same username/password in both cases. Also look at your remote postgresql instance log files. Feb 8 '18 at 16:32
  • Also, unrelated, but if you want to avoid ssh, PostgreSQL can be reached remotely over TLS and you can restrict the source IPs both at the OS firewall level (iptables) and in PostgreSQL pg_hba.conf settings. Feb 8 '18 at 16:33
  • Thanks, Patrick, I finally solved, and it was a stupid mistake. I posted my answer below. It was indeed a "strange" error and I was sure it was something quite simple that I was missing. Feb 9 '18 at 0:12
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.