Foreign tables, user mappings and privilege setup

[Niels Jespersen <NJN@xxxxxx>]

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