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
