Re: [BUGS] Removing pg_auth_members.grantor (was Grantor name gets lost when grantor role dropped) - Mailing list pgsql-hackers

Stephen Frost wrote:
> * Tom Lane (tgl@sss.pgh.pa.us) wrote:
>   
>> Stephen Frost <sfrost@snowman.net> writes:
>>     
>>> If you're saying we don't currently warn if a revoke leaves the
>>> priviledges in-tact for the right and target, I'm not sure you can
>>> currently get in a state where it'd be possible to run into that.
>>>       
>> I'm thinking of the case that comes up periodically where newbies think
>> that revoking a right from a particular user overrides a grant to PUBLIC
>> of the same right.
>>     
>
> Technically, the grant to public is a different target from the target
> of the revoke in such a case.  Following the spec would mean that even
> when the grant and the revoke target is the same (unless you're the
> original grantor) the right won't be removed.  I'm not against adding a
> warning in the case you describe though, but I don't see it being as
> necessary for that case.  What the spec describes is, at least in my
> view, much more counter-intuitive than how PG currently works.
>
>
>   
If we were to follow the spec, I would expect that it would be possible 
for the object owner to revoke privileges no matter what role granted 
them.  It need not be the default, but as an object owner, I'd expect to 
be able to say that I want all privileges for a role revoked, no matter 
who granted them.

8.2 docs state this on the revoke page:
--

REVOKE can also be done by a role that is not the owner of the affected 
object, but is a member of the role that owns the object, or is a member 
of a role that holds privileges WITH GRANT OPTION on the object. In this 
case the command is performed as though it were issued by the containing 
role that actually owns the object or holds the privileges WITH GRANT 
OPTION. For example, if table t1 is owned by role g1, of which role u1 
is a member, then u1 can revoke privileges on t1 that are recorded as 
being granted by g1. This would include grants made by u1 as well as by 
other members of role g1.

If the role executing REVOKE holds privileges indirectly via more than 
one role membership path, it is unspecified which containing role will 
be used to perform the command. In such cases it is best practice to use 
SET ROLE to become the specific role you want to do the REVOKE as. 
Failure to do so may lead to revoking privileges other than the ones you 
intended, or not
revoking anything at all.

--

Paragraph 1 implies that we are meeting the standard now.  I think 
paragraph two is stating that if you are a member of multiple roles 
which could have granted privileges, then you don't know which one you 
are revoking.  Makes sense if we are implementing the SQL standard.  
Does this mean we were intending to be SQL compliant when we wrote the 
documentation?
I also note that 8.1 says the same thing in its documentation.

My possible suggestion is;
1. Implement the standard for revoking only your privileges by default.
2. Allow the object owner to revoke privileges assigned by any role, as 
if you drop and recreate the object you can achieve this anyway.

Regards

Russell Smith