How can I resolve root certificate file errors when using foreign data wrappers and SSL verify-full on Amazon RDS/Aurora PostgreSQL?
Last updated: 2022-05-26
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". How can I resolve this error?
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 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');
To confirm that the connection is working, create a user mapping and foreign table:
Note: PostgreSQL logs passwords in cleartext in the log files. To prevent this, review How can I stop Amazon RDS for PostgreSQL from logging my passwords in clear-text in the log files?
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.