Re: Role Inheritance Without Explicit Naming?

On 03/04/2014 06:00 AM, François Beausoleil wrote:
>
> Le 2014-03-03 à 10:53, Adrian Klaver a écrit :
>
>> On 03/02/2014 08:48 PM, François Beausoleil wrote:
>>> Hi all,
>>>
>>> I have four roles involved:
>>>
>>> meetphil - the database owner, should not login
>>> mpwebui - the role the web application logs in as, should have very limited privileges, but should be able to SET
ROLEto a user that has the correct privileges, should login 
>>> mpusers - the main group for regular users, the group on which I'll grant default privileges, should not login
>>> francois - one of the roles that has the right to do stuff, should login
>>>
>>> I've gist'd everything here: https://gist.github.com/francois/9318054 (also appended at the end of this email).
>>>
>>> In a fresh cluster, I create my users:
>>>
>>> $ psql -U meetphil -d meetphil
>>> psql (9.1.5)
>>> Type "help" for help.
>>>
>>> meetphil=> \du
>>>                               List of roles
>>>   Role name |                   Attributes                   | Member of
>>> -----------+------------------------------------------------+-----------
>>>   colette   |                                                | {mpusers}
>>>   francois  |                                                | {mpusers}
>>>   meetphil  |                                                | {}
>>>   mpusers   | Cannot login                                   | {}
>>>   mpwebui   | No inheritance                                 | {mpusers}
>>>   postgres  | Superuser, Create role, Create DB, Replication | {}
>>>   rene      |                                                | {mpusers}
>>>
>>
>>
>> If I am following correctly what you want is something like this:
>>
>>
>>            ------   mpusers  < ----
>>            |                       |
>>       \|/                      |
>>         francois                 mpwebui
>>
>>
>> In other words access sibling roles through a parent role. Is this correct?
>
> Yes, when you put it that way, it looks like it. I'm just exploring ideas on how to secure access to the database.
I'mexploring alternatives. 


Well my experience is that Postgres will not automatically do the above.
As you have found, you have to explicitly grant from one sibling to
another. There are others on this list that deal with more complicated
set ups then me and might have better ideas. In which case both of us
will learn something:)


>
> Bye,
> François
>


--
Adrian Klaver
adrian.klaver@aklaver.com