How to set up permissions on foreign table using postgres_fdw?

Question

The source data is in a keys table in the public schema of database keys (reference pg docs: https://www.postgresql.org/docs/current/postgres-fdw.html) :

create table keys (
    id varchar not null,
    keyname varchar not null,
    created timestamp default current_timestamp not null,
    modified timestamp default current_timestamp not null
);

The referencing user/schema/database is vids/public/vids .

Set up the server connection

CREATE SERVER keys
        FOREIGN DATA WRAPPER postgres_fdw
        OPTIONS (host '1.2.3.4', port '5432', dbname 'keys');

Create the user mapping

CREATE USER MAPPING FOR vids
        SERVER keys
        OPTIONS (user 'keys', password 'keys');

Create the table mapping

create foreign table keys (
    id varchar not null,
    keyname varchar not null,
    created timestamp default current_timestamp not null,
    modified timestamp default current_timestamp not null
) server keys options (schema_name 'public', table_name 'keys');

Try to access the foreign table when connected as vids in the vids db:

vids=> select * from keys;
ERROR:  permission denied for foreign table keys

I do not understand given that the user keys is the owner of the keys table in the foreign database. What should be done here?

@jjanes I clarified in the question as such: user/schema/database is _vids_/_public_/_vids_ — WestCoastProjects, Sep 23 '20 at 20:17
It still isn't clear which user is logged into which database on which server at each step of this procedure. Also, you created a user mapping for `clip`, which not a user you appear to use anywhere else. — jjanes, Sep 23 '20 at 20:30
The error message suggest the problem is on the local side. The local representation of the foreign table is not owned by `vids`, and `vids` does not have permissions to it. So it never gets far enough to figure out if `keys` has access to `keys` on the foreign side. — jjanes, Sep 23 '20 at 20:36
@jjanes thx i will check the perms on that local representation. the `clip` was a typo it was corrected to `vids`. — WestCoastProjects, Sep 23 '20 at 20:45
@jjanes Yes grantint `all` on the `foreign table` to the local `user mapping` fixed the permissions problem. connectivity is still not working but that's on me. Feel free [/encouraged] to make an answer. — WestCoastProjects, Sep 23 '20 at 20:48

score 2 · Accepted Answer · answered Oct 03 '20 at 18:10

From a comment by @jjanes:

the error message suggest the problem is on the local side". The local repfresentation of the foreign table is nowned by vids and vids does not have permissions to it. So it never gets far enough to figure out if keys has access to keys on the foreign side

So the correction to my steps was:

 grant all on table keys to clips;

How to set up permissions on foreign table using postgres_fdw?

1 Answers1