Using postgres_fdw via ssh tunel

Question

can be used postgres_fdw to connect via ssh tunnel?

The database is accessible only from the DB server, andI need to join from another remote server. The DB server log in with SSH keys.

If it's possible, how please?

Ostracism

Have you been able to solve this? – Any

Unfortunately no. – Ostracism

Answer 1

Yes It is possible. I solved it for mysql_fdw like that;

I use autossh for port forwarding. With autossh, you can keep connection up all time.

Run command on Postgres server:

autossh -L 127.0.0.1:3306:mysql_ip:3306 root@mysql_ip -N -i .ssh/id_rsa.mysql

Test autossh access from Postgres to Mysql.

Run command on Postgres server;

mysql --host=127.0.0.1 --port=3306 -u mysqldbuser -p

Last different part is;

CREATE SERVER mysql_server FOREIGN DATA WRAPPER mysql_fdw OPTIONS (host '127.0.0.1', port '3306'); Other things are same.

Pooh