Обсуждение: privilege inheritance to a login role through a group role

Hello Everyone.
I am having issues with privilege inheritance to a login role through a group role.

These are the steps I am performing:
1.  data: stcities belongs to user gdb, it resides in the gdb schema2.  map user is a login role:
CREATE ROLE map LOGIN ENCRYPTED PASSWORD 'md59ec9dda576db2a36c42c1c3af155d07c' NOSUPERUSER NOINHERIT CREATEDB
NOCREATEROLE;
1.  editor role is created, and privileges to the data are granted to it:
CREATE ROLE editor NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE;
GRANT select on gdb.stcities TO editor;
1.  user map is made a member of the editor role:
GRANT editor TO map;

Now, theoretically, I am expecting the map role to be able to inherit the select privileges via the editor group role
tothe gdb.stcities data. 

But that is not the case, when I make a connection to pgAdminIII as the map user, I receive the following message:

An error has occurred:
Error: permission denied for relation stcities

So, map is not inheriting the privs from the role.

-editor group has privs on the data: gdb=arwdxt/gdb,editor=r/gdb
-Selecting from pg_auth_members tells me that role map is part of group editor

So, why is map not inheriting the privs granted to the editor role? Am I missing a step?

Thanks in advance,
Sincerely,
Kasia

Kasia Tuszynska <ktuszynska@esri.com> writes:
> I am having issues with privilege inheritance to a login role through a group role.

> These are the steps I am performing:

>  1.  data: stcities belongs to user gdb, it resides in the gdb schema
>  2.  map user is a login role:
> CREATE ROLE map LOGIN
>   ENCRYPTED PASSWORD 'md59ec9dda576db2a36c42c1c3af155d07c'
>   NOSUPERUSER NOINHERIT CREATEDB NOCREATEROLE;

>  1.  editor role is created, and privileges to the data are granted to it:
> CREATE ROLE editor NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE;
> GRANT select on gdb.stcities TO editor;

>  1.  user map is made a member of the editor role:
> GRANT editor TO map;

> Now, theoretically, I am expecting the map role to be able to inherit the select privileges via the editor group role
tothe gdb.stcities data. 

You've got the INHERIT bit backwards: you'd have to apply INHERIT to
map, not editor, to have editor's privileges automatically work for map.

If there's some identifiable bit of the documentation that confused you
about this, please point it out so we can improve it.

            regards, tom lane

Tom Lane wrote:

> If there's some identifiable bit of the documentation that confused you
> about this, please point it out so we can improve it.

... because, apparently, lots of people are bitten by the same
misunderstanding.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Hello,

Alvaro Herrera wrote:

> Tom Lane wrote:

>> If there's some identifiable bit of the documentation that confused you
>> about this, please point it out so we can improve it.
>
> ... because, apparently, lots of people are bitten by the same
> misunderstanding.

Maybe it's not the documentation but the translation? I don't have
pgAdmin III at hand now. But if I remember correctly it says "Vererbt
Rechte von Vaterrollen" as translation for INHERIT in German. This
implies that it's the other way around (vererben = to pass sth. on to
so.). Less confusing would be "Erbt Rechte von anderen Rollen (INHERIT)".

Peter