To: Kasia Tuszynska <ktuszynska@esri.com>
cc: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Subject: Re: privilege inheritance to a login role through a group role 
In-reply-to: <232B5217AD58584C87019E8933556D11011F00862C@redmx2.esri.com> 
References: <232B5217AD58584C87019E8933556D11011F00862C@redmx2.esri.com>
Comments: In-reply-to Kasia Tuszynska <ktuszynska@esri.com>
	message dated "Tue, 31 Mar 2009 16:33:53 -0700"
Date: Tue, 31 Mar 2009 19:39:18 -0400
Message-ID: <20824.1238542758@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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 to the 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