Skip site navigation (1) Skip section navigation (2)

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

From: Pedro Gimeno Fortea <pgsql(at)personal(dot)formauri(dot)es>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #3319: Superuser can't revoke grants on a schema given by aother user
Date: 2007-05-30 16:50:19
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-bugs
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  

> 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.

In response to


pgsql-bugs by date

Next:From: Tom LaneDate: 2007-05-30 16:57:39
Subject: Re: BUG #3319: Superuser can't revoke grants on a schema given by aother user
Previous:From: Tom LaneDate: 2007-05-30 16:11:39
Subject: Re: backend crash with FATAL: BeginInternalSubTransaction: unexpected state END

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group