Stephen Frost wrote:
* Tom Lane ([EMAIL PROTECTED]) wrote:
Stephen Frost <[EMAIL PROTECTED]> 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.


Russell Smith

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Reply via email to