Thread: Foreign tables, user mappings and privilege setup

Foreign tables, user mappings and privilege setup

From
Niels Jespersen
Date:

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

 

 

Re: Foreign tables, user mappings and privilege setup

From
Laurenz Albe
Date:
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




SV: Foreign tables, user mappings and privilege setup

From
Niels Jespersen
Date:
>>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



Re: SV: Foreign tables, user mappings and privilege setup

From
Tom Lane
Date:
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