How can I resolve root certificate file errors when using foreign data wrappers and SSL verify-full on Amazon RDS running PostgreSQL?
Last updated: 2019-09-16
I'm using Foreign Data Wrappers (FDW) and sslmode that is set to verify-full on Amazon Relational Database Service (Amazon RDS) running PostgreSQL. When I try to create an FDW server for my DB instance, I receive the following error: "root certificate file "/home/rdsdb/.postgresql/root.crt" does not exist".
To enable certificate verification in PostgreSQL, sslmode must be set to verify-full. If the sslmode is set to verify-full when you create an FDW server from one Amazon RDS instance to another, you receive the root certificate file error. This error is generated on the DB instance where the CREATE SERVER command is run. You can't access the filesystem on an Amazon RDS instance directly or install the CA certificates, but the required root certificate is already installed on the RDS DB instance. To find the location of the certificate, run the following command:
postgres=> show ssl_cert_file; ssl_cert_file ----------------------------------------- /rdsdbdata/rds-metadata/server-cert.pem (1 row)
To resolve this error, point the FDW connection to the /rdsdbdata/rds-metadata/server-cert.pem file when creating the server.
To point the FDW connection to the root certificate file, run a command similar to the following:
CREATE SERVER my_foreign_db foreign data wrapper postgres_fdw options (host 'my_db.xyz.eu-west-1.rds.amazonaws.com', port '5432', dbname 'my_db', sslmode 'verify-full', sslrootcert '/rdsdbdata/rds-metadata/server-cert.pem');
Note: For Aurora PostgreSQL, use sslrootcert '/rdsdbdata/rds-metadata/root-cert.pem' instead of sslrootcert '/rdsdbdata/rds-metadata/server-cert.pem'.
To confirm that the connection is working, create a user mapping and foreign table:
CREATE USER MAPPING FOR dbuser SERVER my_foreign_db OPTIONS (user 'dbuser', password 'dbpasswd'); CREATE FOREIGN TABLE foreign_table ( id integer not null, name character(84)) SERVER my_foreign_db OPTIONS (schema_name 'public', table_name 'my_table');
A connection isn't made until the table is accessed. To confirm that the connection is working, query the table:
SELECT * from foreign_table ;
If the FDW connection is successful, the data from the foreign table is returned.