Re: Dropping default privileges.

Tim Uckun <timuckun(at)gmail(dot)com> writes:
> I am unable to drop a user.
> drop role tim;
> ERROR: role "tim" cannot be dropped because some objects depend on it
> DETAIL: owner of default privileges on new relations belonging to
> role tim in schema strongmail

DROP OWNED BY ought to get rid of that.

> ALTER DEFAULT PRIVILEGES IN SCHEMA strongmail
> REVOKE INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES,
> TRIGGER ON TABLES
> FROM tim;

The "owner of" in the DETAIL really means "grantor of". What you would
need in order to take care of this manually is to become tim and then
revoke whatever default privileges he'd granted to other people.

But DROP OWNED BY is a bigger hammer.

> reassign owned by tim to postgres; [ doesn't help ]

IIRC, "reassign owned by" only reassigns ownership of actual objects,
it doesn't try to change mentions of the user in privilege lists.
(Replacing such references with "postgres" would typically be the wrong
thing anyway.) So after "reassign owned", you may still need "drop owned"
to give up any remaining privileges for the user.

regards, tom lane