How to create SSL dblink connection from RDS to postgres on premise?

Question

We have an rds postgres instance and a remote postgres instance(on premise) we can only access via SSL connection.

To create an SSL dblink connection in postgres one has to define SSL cert file locations on on the client.

I haven't been able to find a way to upload SSL cert files to our RDS postgres instance in order to establish a dblink from our RDS postgres instance to remote postgres instance(on premise).

Would it be possible to upload SSL cert files from remote instance somehow so that rds instance could have access to them?

Please tell me if this is possible or if there are any alternative options. 
We would rather use a RDS instance than to manage our own on a EC2 instance.

Answer

Hi, you can use postgres_fdw extension to setup encrypted in transit connection to foreign PG based database on premise. In order to enforce ssl please use sslmode in the connection string. You can find an example here:

FDW uses CREATE SERVER to create foreign (remote) server mapping as the PostgreSQL-based server from which we pull the data. A foreign server typically encapsulates connection information that an FDW uses to access an external data resource. It uses the same connection options as libpq. SSLMODE ‘require’ makes sure that the data is encrypted in transit. See the following code:

> pgfdwsource=> create server my_fdw_target Foreign Data Wrapper postgres_fdw OPTIONS (DBNAME 'pgfdwtarget', HOST 'pgfdwtarget.xxxx.us-west-2.rds.amazonaws.com', SSLMODE 'require');

Following blog will also be helpful for more information on postgres_fdw: https://aws.amazon.com/blogs/database/federated-query-support-for-amazon-aurora-postgresql-and-amazon-rds-for-postgresql/

Unfortunately, as of now you cannot upload a custom certification to your RDS or Aurora environment.

How to create SSL dblink connection from RDS to postgres on premise?

Relevant content