How to create SSL dblink connection from RDS to postgres on premise?
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.
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.
Relevant questions
FATAL: password authentication failed for user "postgres
asked 4 years agoQuickSight Unable to Connect to RDS(postgres)
asked a month agoIs it possible to use AWS RDS SQL Server as an AAG target from on premise primary?
asked 7 months agoHow to create SSL dblink connection from RDS to postgres on premise?
asked 4 months agoHow to restrict database users for RDS Postgres using AWS Managed AD trusted with customer on-prem AD
Accepted Answerasked 2 years agoCan we create a Table in Aurora during the infra setup using terraform IaaC code ?
Accepted Answerasked 25 days agoRDS Postgres SSL/TLS certificate rotation via CloudFormation
asked 3 years agoUpgraded MySQL and accepted SSL/TLS Certs and now connection refused.
asked 2 years agoMigration from Aurora PostgreSQL back to regular RDS
Accepted Answerasked 3 years ago60K updates to RDS Postgres - performance challenge
Accepted Answerasked 2 years ago