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@personal.formauri.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.
>=20
> 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=20=
=20
part of a database administration task. During development some of the=20=
=20
privileges were incorrectly set and I wanted to adjust them. The grant=20=
=20
option was correct; the privilege wasn't, thus I issued a REVOKE and no=20=
=20
error was printed, so I thought everything was correct again. Only=20=20
later, when I doublechecked the ACLs, I realized that the REVOKE had=20=20
not been effective.

 From the docs, chapter 18.2:

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

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

But even in that case, at the very least some kind of notification=20=20
should be issued so that the superuser knows that the permission has=20=20
NOT been revoked. Failing silently is not the proper action in this=20=20
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=20=20
previously used and that seemed to work actually didn't work. That can=20=
=20
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=20=20
schemas, and the situation is the same.