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

On 05/29/2007 03:35:00 PM, Tom Lane wrote:

> "Pedro Gimeno" <pgsql(at)personal(dot)formauri(dot)es> writes:
> > When a USAGE grant on a SCHEMA is given by an user (non-superuser
> > in my case), the superuser can't revoke it; instead the REVOKE
> > statement is silently ignored.
>
> This is not a bug. If you want to revoke the privilege, revoke the
> GRANT OPTION you originally gave.

Why should I? I want to revoke the privilege, not the grant option, as
part of a database administration task. During development some of the
privileges were incorrectly set and I wanted to adjust them. The grant
option was correct; the privilege wasn't, thus I issued a REVOKE and no
error was printed, so I thought everything was correct again. Only
later, when I doublechecked the ACLs, I realized that the REVOKE had
not been effective.

From the docs, chapter 18.2:

"A database superuser bypasses all permission checks. This is a
dangerous privilege and should not be used carelessly; it is best to do
most of your work as a role that is not a superuser.[...]"

If this behaviour is really by design, the documentation should at
least state that a database superuser bypasses all permission checks
EXCEPT the permission to revoke roles granted by other users.

But even in that case, at the very least some kind of notification
should be issued so that the superuser knows that the permission has
NOT been revoked. Failing silently is not the proper action in this
case.

> Alternatively, since you are superuser, you can become user1 and
> revoke the privilege he gave ...

That's right, assuming that you noticed that the REVOKE statement you
previously used and that seemed to work actually didn't work. That can
be undetected for an undefined time and is a security risk IMO.

By the way, I have tried with permissions given on tables, not just
schemas, and the situation is the same.