Re: BUG #6116: Not able to drop user if S/he has permission on tablespace

On Wed, Jul 13, 2011 at 8:57 AM, tushar <tushar(dot)qa(at)gmail(dot)com> wrote:
>
> The following bug has been logged online:
>
> Bug reference:      6116
> Logged by:          tushar
> Email address:      tushar(dot)qa(at)gmail(dot)com
> PostgreSQL version: 9.0
> Operating system:   Fedora 14
> Description:        Not able to drop user if S/he has permission on
> tablespace
> Details:
>
> Steps to reproduce
> ================
>
> \\create a directory
>
> postgres=# \! mkdir /tmp/g100
>
> \\create a tablespace
> postgres=#  CREATE TABLESPACE f location '/tmp/g100';
> CREATE TABLESPACE
>
> \\create a User
> postgres=# CREATE user abc;
> CREATE ROLE
>
> \\grant all on tablespace to user
>
> postgres=# GRANT all on TABLESPACE f to abc;
> GRANT
>
> \\drop permission from user
>
> postgres=# drop owned by abc;
> DROP OWNED
>
> \\ Try to drop User
> postgres=# drop user abc ;
> ERROR:  role "abc" cannot be dropped because some objects depend on it
> DETAIL:  privileges for tablespace f
> postgres=#

The "DROP OWNED BY" command only drops objects that are owned by a
user. It doesn't revoke privileges that user has granted: those
aren't considered dropable objects. So technically speaking all of
those commands are working just as expected.

Nevertheless, I agree with you that the behavior here leaves a lot to
be desired. Hunting down the privilege grant that is stopping you
from dropping a user is pretty darn annoying. I am not sure what to
do about that, though.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company