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

Stephen Frost wrote:
> * Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
>
>> Stephen Frost <sfrost(at)snowman(dot)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