Postgresql – Why a PGSQL graphical client can connect through SSH Tunnel and not me

postgresqlssh-tunnel

I have a PostgreSQL server which allow only local connexion.

I'm using "Navicat for PostgreSQL Lite" to do some admin operations. In this client, I configured a SSH Tunnel to my server. Everything works fine.

Today, I wanted to use another client which doesn't let me configure the SSH Tunnel inside. So, I tried to open a SSH Tunnel manually:

ssh -L 15021:myserver.com:5432 me@myserver.com

But when I tried to use it with the client, it says the connection is refused. In the SSH prompt, I've got this message.

channel 3: open failed: connect failed: Connection refused

I tried

psql -h localhost -p 15021 db_name

Same error …

I don't understand what magical stuff Navicat does that I don't do with my manual SSH Tunneling. I'm pretty certain that PostgreSQL listen on port 5432.

Thanks for any pointer or answer.

EDIT:

This is an attempt log with LogLevel DEBUG. I anonymized the hostname.

Sep 13 14:57:23 myserver sshd[27793]: debug1: server_input_channel_open: ctype direct-tcpip rchan 3 win 2097152 max 32768
Sep 13 14:57:23 myserver sshd[27793]: debug1: server_request_direct_tcpip: originator ::1 port 64027, target myserver.com port 5432
Sep 13 14:57:23 myserver sshd[27793]: debug1: connect_next: host myserver.com ([xxx.xx.xx.xxx]:5432) in progress, fd=9
Sep 13 14:57:23 myserver sshd[27793]: debug1: channel 1: new [direct-tcpip]
Sep 13 14:57:23 myserver sshd[27793]: debug1: server_input_channel_open: confirm direct-tcpip
Sep 13 14:57:23 myserver sshd[27793]: debug1: channel 1: connection failed: Connection refused
Sep 13 14:57:23 myserver sshd[27793]: error: connect_to myserver.com port 5432: failed.
Sep 13 14:57:23 myserver sshd[27793]: debug1: channel 1: free: direct-tcpip, nchannels 2

Best Answer

ssh -L 15021:myserver.com:5432 me@myserver.com

That's not the typical way of setting up a tunnel, because it's asking the remote SSH server to connect to PostgreSQL through its public IP address (myserver.com)

This leads to Connection refused because postgres doesn't listen on its public address. That's the usual and default case.

You probably meant to do:

 ssh -L 15021:localhost:5432 me@myserver.com

in this case SSH will route the db connections from your localhost:15021 to the localhost:5432 of the remote host, which is presumably where the db is expecting connections.

Related Topic