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 .

1. Set up the server connection

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

2. Create the user mapping

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

3. 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');

4. 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?

Answer 1

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;