Re: BUG #3319: Superuser can't revoke grants on a schema given by aother user

On 05/30/2007 07:55:58 PM, Tom Lane wrote:

> Pedro Gimeno Fortea <pgsql(at)personal(dot)formauri(dot)es> writes:
>
> > Still, is silently ignoring the command the proper action to take
> > when the REVOKE is executed by the superuser and not by the
> > grantor?
>
> You want a warning when REVOKE didn't do anything because there was
> no prior grant to be revoked?

No, I want a warning when REVOKE didn't do anything because there *was*
a grant to be revoked, but the user who wanted to revoke it was not the
grantor.

Actually I'd rather prefer the REVOKE to be effective when the user who
wants to do it is a superuser; otherwise at a minimum a NOTICE-level
message would be desirable. If that is "too noisy", then I guess that
other NOTICEs are too and the DBA should disable notices. I really
think that this kind of notification is more important than e.g. the
implicit creation of a primary-key index, because of the security
implications (the superuser may think that the permission is revoked
when it actually isn't, so the grantee can do Bad Things).

Note that this is not similar to the GRANT case. I'd say it's similar
to wanting to delete a table created by another user: if you're not the
owner, you can't, unless you're a superuser. The similarity becomes
obvious when replacing "delete a table created by" with "revoke a
privilege granted by" and "owner" by "grantor".

At the very least, if nothing is changed then this quirk should be
documented, perhaps in the REVOKE statement.

> According to the code comments, this was considered and rejected as
> "too noisy, as well as inconsistent with the GRANT case". I can't
> find the discussion right now, but it would have probably been in May
> 2004 or a bit before, because the comment seems to date from a commit
> on 1 June 2004.

In a situation as you state it (the destination user doesn't have that
privilege on the object at all), I would agree, but the scenario I'm
stating is different.