Thread: postgres_fdw user mapping and role inheritance

Hi all,

We're looking at using postgres_fdw, and while setting it up for a test using postgres 9.4.4, I noticed that it doesn't
seemto honor role inheritance.  

For example, having created foreign_server:

create role local_group with nologin;
create role individual_user;
grant local_group to individual_user;

create user mapping for local_group
   server foreign_server
   options (user 'foreign_user', password '');

create foreign table foreign_table (id serial, data text)
   server foreign_server
   options (schema_name 'some_schema', table_name 'some_table');

When I tried this, individual_user could not select from foreign_table, even though I expected individual_user to
inheritfrom local_group: 
select * from foreign_table;
ERROR:  user mapping not found for "individual_user"

It did work when I created a user mapping for individual_user (to verify that I had the other parts set up correctly):

create user mapping for individual_user
   server foreign_server
   options (user 'foreign_user', password '');


Am I missing something? Is there a way to tell postgres_fdw to allow individual_user to inherit user mappings from
groupsof which it is a part? Is there a reason that it doesn't allow it, or is it a bug or something that hasn't been
implementedyet? (Will it be fixed or implemented in postgres 9.5?)  

Many thanks,
Natalie

Natalie Wenz <nataliewenz@ebureau.com> writes:
> Is there a way to tell postgres_fdw to allow individual_user to inherit
> user mappings from groups of which it is a part?

No.  How would you disambiguate if the current role were a member of
multiple groups?

            regards, tom lane

> Hi all,
>
> We're looking at using postgres_fdw, and while setting it up for a test
> using postgres 9.4.4, I noticed that it doesn't seem to honor role
> inheritance.
>
> For example, having created foreign_server:
>
> create role local_group with nologin;
> create role individual_user;
> grant local_group to individual_user;
>
> create user mapping for local_group
>    server foreign_server
>    options (user 'foreign_user', password '');
>
> create foreign table foreign_table (id serial, data text)
>    server foreign_server
>    options (schema_name 'some_schema', table_name 'some_table');
>
> When I tried this, individual_user could not select from foreign_table,
> even though I expected individual_user to inherit from local_group:

Please,

Select  current_user;
???

Session authorization local_group;

psql> select * from foreign_table;

;-)

> ERROR:  user mapping not found for "individual_user"
>
> It did work when I created a user mapping for individual_user (to verify
> that I had the other parts set up correctly):
>
> create user mapping for individual_user
>    server foreign_server
>    options (user 'foreign_user', password '');
>
>
> Am I missing something? Is there a way to tell postgres_fdw to allow
> individual_user to inherit user mappings from groups of which it is a
> part? Is there a reason that it doesn't allow it, or is it a bug or
> something that hasn't been implemented yet? (Will it be fixed or
> implemented in postgres 9.5?)
>
> Many thanks,
> Natalie
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>


Saludos,
Gilberto Castillo
ETECSA, La Habana, Cuba

Would it be necessary to disambiguate? Wouldn't individual_user's privileges be a union of all of the privileges of all
thegroups of which it is a part? That seems to be how it works in the core postgres functionality: 

If local_group_1 is a role with privileges on table_a and table_b, and local_group_2 is a role with privileges on
table_c,then after 
   grant local_group_1 to individual_user;
   grant local_group_2 to individual_user;
individual_user now has privileges on table_a, table_b, and table_c.

But if local_group_2 also has a user mapping on foreign_table_x, individual_user inherits the privileges on table_c,
butnot foreign_table_x.  

Or am I misunderstanding you? Is there something about foreign tables that precludes the role inheritance from working
thesame way it does with native tables? 

Thanks,
Natalie

> On Jul 16, 2015, at 1:16 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Natalie Wenz <nataliewenz@ebureau.com> writes:
>> Is there a way to tell postgres_fdw to allow individual_user to inherit
>> user mappings from groups of which it is a part?
>
> No.  How would you disambiguate if the current role were a member of
> multiple groups?
>
>             regards, tom lane

Natalie Wenz <nataliewenz@ebureau.com> writes:
> Would it be necessary to disambiguate?

Of course.  If the mapping for group_x says to connect to the remote
server as user foo, while the mapping for group_y says to connect
as user bar, then it matters which one we use.  But there would be no
principled way to choose, if the current userid is a member of both
group_x and group_y.

            regards, tom lane

Oh! Of course! The local database doesn't know anything about the role privileges on the remote database, so the
informationisn't even available on the local database to somehow take the union of all the privileges of foo and bar
becausethey are defined on the remote database. Thanks for responding. I got so wrapped up in our simple use case
(whereany individual_user in the local_group would connect to foreign_server as the same foreign_user) and couldn't
comeup with a counter example.  

For our use case, can you think of any other way to do it besides creating a user mapping for every member of
local_group?Gilberto's suggestion of setting the session authorization (I think?) won't work because the
individual_usersdon't have privileges to set that. ("ERROR:  permission denied to set session authorization") 

Thanks again for your responses; I appreciate the help!

Natalie

> On Jul 16, 2015, at 3:12 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Natalie Wenz <nataliewenz@ebureau.com> writes:
>> Would it be necessary to disambiguate?
>
> Of course.  If the mapping for group_x says to connect to the remote
> server as user foo, while the mapping for group_y says to connect
> as user bar, then it matters which one we use.  But there would be no
> principled way to choose, if the current userid is a member of both
> group_x and group_y.
>
>             regards, tom lane
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin

Natalie Wenz <nataliewenz@ebureau.com> writes:
> Oh! Of course! The local database doesn't know anything about the role privileges on the remote database, so the
informationisn't even available on the local database to somehow take the union of all the privileges of foo and bar
becausethey are defined on the remote database. Thanks for responding. I got so wrapped up in our simple use case
(whereany individual_user in the local_group would connect to foreign_server as the same foreign_user) and couldn't
comeup with a counter example.  

> For our use case, can you think of any other way to do it besides creating a user mapping for every member of
local_group?Gilberto's suggestion of setting the session authorization (I think?) won't work because the
individual_usersdon't have privileges to set that. ("ERROR:  permission denied to set session authorization") 

Would it work for them to do SET ROLE to the group whose mapping they want
to use?  IIRC that's allowed for any member of the group.  The problem
with this is that their privileges for local operations are also affected,
so maybe that doesn't help you.

            regards, tom lane

Oh! I didn't know that was possible! And then I looked in the documentation and there it was, very clearly explained.
(Thatdoesn't surprise me at all; I've always appreciated how clear and thorough the Postgres documentation is.) That
willwork beautifully for us; thanks again for your help. I promise to scour the documentation more carefully before I
bugthe mailing list next time. :) 

Thanks,
Natalie

> On Jul 17, 2015, at 12:59 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Natalie Wenz <nataliewenz@ebureau.com> writes:
>> Oh! Of course! The local database doesn't know anything about the role privileges on the remote database, so the
informationisn't even available on the local database to somehow take the union of all the privileges of foo and bar
becausethey are defined on the remote database. Thanks for responding. I got so wrapped up in our simple use case
(whereany individual_user in the local_group would connect to foreign_server as the same foreign_user) and couldn't
comeup with a counter example.  
>
>> For our use case, can you think of any other way to do it besides creating a user mapping for every member of
local_group?Gilberto's suggestion of setting the session authorization (I think?) won't work because the
individual_usersdon't have privileges to set that. ("ERROR:  permission denied to set session authorization") 
>
> Would it work for them to do SET ROLE to the group whose mapping they want
> to use?  IIRC that's allowed for any member of the group.  The problem
> with this is that their privileges for local operations are also affected,
> so maybe that doesn't help you.
>
>             regards, tom lane