Accessing to a remote PostgreSQL server using port forwarding to another machine

Question

I try to access to a Server_A on which PostgreSQL is running. This server is on a local network, that's why I have to forward port from Server_B which is accessible and in the same network.

To access to postgreSQl on Server_B, I can easily forward ports like this :

ssh -L 54320:Server_A:5432 user@Server_B

And in another terminal :

psql -p 54320 -d db_name -U user

My problem is that I want to reproduce the connection done from a machine present in the network of Server_B. This machine can connect itself to the database using this command :

psql -h Server_A -p 5432 -d db_name -U user

I have in fact to problems :

I can't forward the 5432 port using the ssh command :

By inserting 5432:Server_A:5432 in the ssh command to forward the 5432 port, I have this error :

psql: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

I can't resolve the hostname :

Instead of directly forward the port from server_A, I try to forward the port from Server_B to have the -h argument in the psql command.

ssh -L 5432:127.0.0.1:5432 user@Server_B

And :

psql -h Server_A -p 5432 -d db_name -U user

But here, the hostname can't be resolved...

Is there any solution?

Solution

This command:

ssh -L 54320:Server_A:5432 user@Server_B

looks good, but then when doing this:

psql -p 54320 -d db_name -U user

the ssh tunnel is not used, because by default on Unix, psql connects to a Unix domain socket, like suggested by the error message you mention ("...accepting connections on Unix domain socket...")

You're just missing a -h localhost option. Try:

   psql -p 54320 -d db_name -U user -h localhost

Having previously established an ssh tunnel between localhost:54320 and Server_A:5432 through Server_B, this will reach postgres on Server_A:5432.

Comments

[Thibaut Guirimand]

I haven't any problem with psql -p 54320 -d db_name -U user for the 54320 port but your solution is working for the 5432 port. Have you a solution to the second part of my question ? I try to connect psql with -h Server_A after forwarding my 5432 port from the intermediary server, Server_B. Is it something like connecting to a unix socket threw an ssh tunnel?

[Daniel Vérité]

psql -h Server_A doesn't use your ssh tunnel at all so it's unclear what you're trying to achieve at that point. When doing ssh -Lport1:host:port2, the entry of the tunnel is at localhost:port1. Also, instead of saying inserting 5432:Server_A:5432 in the ssh command, please give the full resulting command.

[bonhoffer]

super helpful, I was struggling