Re: BUG #6116: Not able to drop user if S/he has permission on tablespace - Mailing list pgsql-bugs

Excerpts from Robert Haas's message of mié jul 20 15:11:47 -0400 2011:

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

The message detail indicates what's the grant that needs to be revoked.
IIRC we discussed whether DROP OWNED should revoke privileges on
tablespaces and databases just like it does for regular (non shared)
objects, but that went nowhere and nothing got done about it.

I think the expectation is that a combination of DROP OWNED and REASSIGN
OWNED, when applied to sufficient databases, should be enough to let you
drop a user.  With that in mind, fixing this bug should be
straightforward.

--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support