Обсуждение: Dropping default privileges.

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

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

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

reassign owned by tim to postgres;

Nothing seems to work.

Tim Uckun <timuckun@gmail.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

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


Just to be clear.

I don't want to drop the schema. Will DROP OWNED BY only drop the
priviliege or the schema?

Tim Uckun wrote:
>>> 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.
> 
> 
> Just to be clear.
> 
> I don't want to drop the schema. Will DROP OWNED BY only drop the
> priviliege or the schema?

It will not drop the schema unless the schema is owned by
the role you are dropping.

Yours,
Laurenz Albe

Thanks.

Worked.

On Wed, Jan 30, 2013 at 9:12 PM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:
> Tim Uckun wrote:
>>>> 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.
>>
>>
>> Just to be clear.
>>
>> I don't want to drop the schema. Will DROP OWNED BY only drop the
>> priviliege or the schema?
>
> It will not drop the schema unless the schema is owned by
> the role you are dropping.
>
> Yours,
> Laurenz Albe