Error connecting to postgresql foreign server using SSL-CodePudding

I'm having trouble using SSL to connect to a foreign server using postgres_fdw. I had this working on a different server, but seemingly the same process is not working on my new machine.

I mostly follow the same steps as this Percona blog and create my server with the following command:

create
    server my_fdw
    foreign data wrapper postgres_fdw
    options (
        host 'localhost',
        port '5432',
        dbname 'my_foreign_db',
        use_remote_estimate 'on',
        sslrootcert '/home/myhome/.postgresql/root.crt',
        sslcert '/home/myhome/.postgresql/postgresql.crt',
        sslkey '/home/myhome/.postgresql/postgresql.key',
        sslmode 'verify-full'
    )
;

grant usage on foreign server my_fdw to myuser;

create user mapping
    for myuser
    server my_fdw
    options (password_required 'false')
;

I have no problem connecting to the primary database using SSL. However, when I try to access the foreign server, e.g.:

import 
    foreign schema foreign_schema
    from server my_fdw
    into local_schema
;

I get the following error:

ERROR:  could not connect to server "my_fdw"
DETAIL:  connection to server at "localhost" (::1), port 5432 failed: could not read root certificate file "/home/myhome/.postgresql/root.crt": Permission denied

The root.crt file is owned by the myhome user and permissions are set to 0600. I can (temporarily) change the permissions of the root.crt file to be more permissive, which will bypass this specific error, but then I will get the same error for the other files. However, changing the permissions of these files will cause postgres to fail because they must be 0600 or lower.

It seems that postgres is trying to access the SSL files as a different OS user when executing a command related to the foreign server. Any ideas on what is going on or how to solve the problem?

CodePudding user response：

Sure, it is using a different user--the user who owns the database server processes. This would usually be 'postgres'. You might need to make a copy of those files, and do a chown on the copy.