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

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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Pedro Gimeno" <pgsql(at)personal(dot)formauri(dot)es>
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-29 13:35:00
Message-ID: 3198.1180445700@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
"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.  For example:

test1=# \dn+ public
                                           List of schemas
  Name  |  Owner   |                   Access privileges                    |      Description       
--------+----------+--------------------------------------------------------+------------------------
 public | postgres | {postgres=UC/postgres,user1=U*/postgres,user2=U/user1} | Standard public schema
(1 row)

test1=# revoke grant option for usage on schema public from user1;
ERROR:  dependent privileges exist
HINT:  Use CASCADE to revoke them too.
test1=# revoke grant option for usage on schema public from user1 cascade;
REVOKE
test1=# \dn+ public
                                   List of schemas
  Name  |  Owner   |            Access privileges            |      Description       
--------+----------+-----------------------------------------+------------------------
 public | postgres | {postgres=UC/postgres,user1=U/postgres} | Standard public schema
(1 row)


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

			regards, tom lane

In response to

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2007-05-29 13:41:38
Subject: Re: BUG #3320: Error when using INSERT...RETURNING as a subquery
Previous:From: Richard HuxtonDate: 2007-05-29 11:50:48
Subject: Re: [HACKERS] exit

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