Re: [HACKERS] Removing pg_auth_members.grantor (was Grantor name gets lost when grantor role dropped)

Alvaro Herrera wrote:
> Russell Smith wrote:
>
>> Alvaro Herrera wrote:
>>
>>> Alvaro Herrera wrote:
>>>
>>>
>>>
>>>> 2. decide that the standard is braindead and just omit dumping the
>>>> grantor when it's no longer available, but don't remove
>>>> pg_auth_members.grantor
>>>>
>>>> Which do people feel should be implemented? I can do whatever we
>>>> decide; if no one has a strong opinion on the matter, my opinion is we
>>>> do (2) which is the easiest.
>>>>
>>> Here is a patch implementing this idea, vaguely based on Russell's.
>>>
>> I haven't had time to finalize my research about this, but the admin
>> option with revoke doesn't appear to work as expected.
>>
>> Here is my sample SQL for 8.2.4
>>
>> create table test (x integer);
>> \z
>> create role test1 noinherit;
>> create role test2 noinherit;
>> grant select on test to test1 with grant option;
>> grant select on test to test2;
>> \z test
>> set role test1;
>> revoke select on test from test2;
>> \z test
>> set role test2;
>> select * from test;
>> reset role;
>> revoke all on test from test2;
>> revoke all on test from test1;
>> drop role test2;
>> drop role test1;
>> drop table test;
>> \q
>>
>>
>> The privilege doesn't appear to be revoked by test1 from test2. I'm not
>> sure if this is related, but I wanted to bring it up in light of the
>> options we have for grantor.
>>
>
> Humm, but the privilege was not granted by test1, but by the user you
> were using initially. The docs state in a note that
>
> A user can only revoke privileges that were granted directly by
> that user.
>
> I understand that this would apply to the grantor stuff being discussed
> in this thread as well, but I haven't seen anyone arguing that we should
> implement that for GRANT ROLE (and I asked three times if people felt it
> was important and nobody answered).
>
>
Well, I would vote for implementing this in GRANT ROLE. I wish to use
it in my security model. I don't think the spec is brain dead when you
understand what it's trying to achieve.

Example:

2 Groups of administrators who are allowed to grant a role to users of
the system

App_Admin_G1
App_Admin_G2
App_User

SET ROLE App_Admin_G1
GRANT App_User TO "Fred";
SET ROLE App_Admin_G2
GRANT App_User TO "John";
SET ROLE App_Admin_G1
REVOKE App_User FROM "John";

As App_Admin_G1 did not grant App_User rights to John, he should not be
able to take them away.

I currently have a situation where I would like to be able to do the
above. I have two separate departments who might grant privileges for
the same application to the same user. One department administrator
should not be able to revoke the privileges set by the other one.

I would expect superusers to be able to revoke from anybody, or the
"owner". I'm not sure what the owner is when we talk about granting roles.

Regards

Russell Smith