Thread: Foreign tables, user mappings and privilege setup
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
On Mon, 2020-09-21 at 08:21 +0000, Niels Jespersen wrote: > 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 haveselect privilege on the table. They will get a "user mapping not found > for...". You have to create a user mapping for every user that is to access the foreign table. If several users should have the same credentials, use a group. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
>>On Mon, 2020-09-21 at 08:21 +0000, Niels Jespersen wrote: >> 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 foundfor...". > >You have to create a user mapping for every user that is to access the foreign table. > >If several users should have the same credentials, use a group. Thank you for replying. I can create a user mapping for a group (role). This works, but it is not enough to grant the role to the end user (thathas inherit defined). The end user must explicitly use "set role" before accessing the foreign table. Is this what Oracle Corporation would call "Intended Behavior" ;=) ? Regards Niels
Niels Jespersen <NJN@dst.dk> writes:
> I can create a user mapping for a group (role). This works, but it is not enough to grant the role to the end user
(thathas inherit defined). The end user must explicitly use "set role" before accessing the foreign table.
Seems right to me. If the user is a member of two groups, each of which
has a mapping for that server, how could the server choose one?
regards, tom lane