Dropping default privileges.

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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?

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general