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

Andrew Schulman
  • 8,811
  • 21
  • 32
  • 47
Daniel da Rocha
  • 131
  • 1
  • 4
  • 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. – Patrick Mevzek Feb 08 '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. – Patrick Mevzek Feb 08 '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. – Daniel da Rocha Feb 09 '18 at 00:12

1 Answers1

1

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.

Daniel da Rocha
  • 131
  • 1
  • 4