Error connecting to postgresql foreign server using SSL

Question

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?

jjanes · Accepted Answer · 2023-01-02 14:46:27Z

0

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.

answered Jan 2, 2023 at 14:46

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Reid Swanson Over a year ago

I don't think that's the way it is supposed to work. As far as I understand it should be reading the key files as the user who is executing the client process (e.g., psql). There is nothing about separate copies of the key files in the Percona blog and I didn't have to make copies for the previous server I had working. Regardless, I copied the certificates to /tmp and change the owner to postgres and changed the create server code to look for them there. That gives a new error: ERROR: could not connect to server "worthy_fdw" ... fe_sendauth: no password supplied.

jjanes Over a year ago

There is no code in libpq to proxy certs stored on the client through to the FDW. Maybe in both the tutorial and your previous set up, the OS user running the client and OS user owning the server were the same. Regarding you new error, when you updated the mapping, it looks like you left off the password_required 'false'

Reid Swanson Over a year ago

I didn't leave off the password_required 'false' options, but I think I had a problem with my pg_hba.conf file. I had an entry for host using scram-sha-256 and also for hostssl with cert clientcert and map. I think this was confusing the connection. Removing the host entry and keeping only the hostssl entry fixed the problem. Thanks for the help.

jjanes Over a year ago

Ah, right. Leaving off password_required 'false' would have generated a different error message. Glad you figured it out despite the red herring.

Collectives™ on Stack Overflow

Error connecting to postgresql foreign server using SSL

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related