Hello all
I am a bit confused about how to design privileges properly around foreign tables.
Here's the setup.
-- As superuser
create server s.... foreign data wrapper postgres_fdw options (host 'hhh.d', dbname 'db1', port '5432');
grant usage on foreign server s... to public; -- This does not give access to anything beyond allowing user created foreign tables and user mappings.
-- As user with create privileges in schema
create user mapping for current_user server s... (user 'remoteuser', password 'remotepassword');
create foreign table t.... ( a int) server s... options (table_name 't_remote');
This works fine. Except only the user who created the user mapping can select from foreign table, even if other users have select privilege on the table. They will get a "user mapping not found for...".
Now, I could, as superuser:
create user mapping for public server s.... -- But that would give anyone access the whatever thre remote user has access to. Not good.
I am unsure of the best solution to this. I can see a few, but I have not tested them. There may be other solutions that are much better.
One way is a public user mapping on top of a server, and only give a specific role usage privileges.
Another way is to create a view on top of the foreign table and give select privileges on that. I'm not sure that would work, actually.
Yet another is a set returning function on top of the foreign table defined as 'security definer'.
That's basically it. Shoot.
Regards Niels Jespersen
