DBLink to connect to remote RDS Postgresql

Question

I am trying connect to remote RDS PostgreSQL database from another RDS PostgreSQL [both SSH Enabled] and getting errors. Could anyone please help me to establish the connection in the correct way.

SELECT dblink_connect ('myconn', 'dbname=Test-Prod port=5432 host=10.60.0.10 user=postgres password=abcd');

ERROR: could not establish connection DETAIL: could not connect to server: Connection timed out Is the server running on host "10.60.0.10" and accepting TCP/IP connections on port 5432? SQL state: 08001

SELECT dblink_connect ('myconn', 'dbname=Test-Prod host=10.60.0.10 user=postgres password=abcd sslmode=require ss_host=10.20.2.12 ssh_port=22 ssh_user=ubuntu ssh_password=acbde');

ERROR: password is required DETAIL: Non-superusers must provide a password in the connection string. SQL state: 2F003

I'd be very grateful if you have any other option to help me on this. Thank you.

Answer 1

Establish an ssh tunnel on your own, and then use dblink to connect through it. dblink has no provision of establishing a tunnel for you.

Something like:

ssh -L 9999:10.60.0.10:5432 [email protected]

Then you would edit your dblink connection string to point to the local end of the tunnel, '... host=localhost port=9999 ...'

Your first attempt is blocked by a firewall. Which makes sense, as there is no point in having an ssh tunnel if you just let people bypass it.

Your second attempt fails because you specified made-up parameters in your connection string. dblink reports this as a password problem even though it is not. I think that has been reported as a bug before, but I can't find the bug report and evidently it has not been fixed.