Port forward from external postgresql to localhost

Question

I have several databases hosted outside the application server, ex: DB instances:

192.168.178.21:5432
192.168.178.22:5432
192.168.178.23:5432

app-server: 192.168.178.11

now I want to forward the external dbs to ports 5431-5433 and be able to connect with psql to localhost:

psql -h 127.0.0.1 -p 5432 -U <user> -d <db>

I get:

psql: could not connect to server: Connection refused
        Is the server running on host "127.0.0.1" and accepting
        TCP/IP connections on port 5432?

services on this server are only allowed to connect to localhost, additionally these ports will be forwarded with ssh.

I tried firewalld masquerade:

firewall-cmd --zone=internal --add-masquerade --permanent
firewall-cmd --zone=internal --add-forward-port=port=5432:proto=tcp:toport=5432:toaddr=192.168.178.22 --permanent
firewall-cmd --reload

Any ideas? (I've cowered several manuals, including CentOS, Fedora and Redhat intros)

EDIT: Only Postgres Ports are open on the respective servers, nothing else. I will try using a postgresql aware gateway like crunchy-proxy

Answer 1

It depends how is the postgresql pg_hba.conf configured and whether the postgresql server listen on IP addresses you've listed.

Otherwise If You can ssh to those servers You can use ssh port forwarding -L option in ssh(1) example:

ssh [email protected] -L 5431:localhost:5432 -f
ssh [email protected] -L 5432:localhost:5432 -f
ssh [email protected] -L 5433:localhost:5432 -f

You can achieve the same with following ~/.ssh/config

Host db1
        Hostname 192.168.178.21
        LocalForward            localhost:5431 localhost:5432

Host db2
        Hostname 192.168.178.22
        LocalForward            localhost:5432 localhost:5432

Host db3
        Hostname 192.168.178.23
        LocalForward            localhost:5433 localhost:5432

and then just

ssh -f db1
ssh -f db2
ssh -f db3