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

forwardingnetworkingport-forwardingpostgresqlssh

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?

Best Answer

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.

Related Topic