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.qa@gmail.com> wrote:
>
> The following bug has been logged online:
>
> Bug reference: =A0 =A0 =A06116
> Logged by: =A0 =A0 =A0 =A0 =A0tushar
> Email address: =A0 =A0 =A0tushar.qa@gmail.com
> PostgreSQL version: 9.0
> Operating system: =A0 Fedora 14
> Description: =A0 =A0 =A0 =A0Not able to drop user if S/he has permission =
on
> tablespace
> Details:
>
> Steps to reproduce
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
>
> \\create a directory
>
> postgres=3D# \! mkdir /tmp/g100
>
> \\create a tablespace
> postgres=3D# =A0CREATE TABLESPACE f location '/tmp/g100';
> CREATE TABLESPACE
>
> \\create a User
> postgres=3D# CREATE user abc;
> CREATE ROLE
>
> \\grant all on tablespace to user
>
> postgres=3D# GRANT all on TABLESPACE f to abc;
> GRANT
>
> \\drop permission from user
>
> postgres=3D# drop owned by abc;
> DROP OWNED
>
> \\ Try to drop User
> postgres=3D# drop user abc ;
> ERROR: =A0role "abc" cannot be dropped because some objects depend on it
> DETAIL: =A0privileges for tablespace f
> postgres=3D#

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.

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