Re: How to drop user if objects depend on it

On 7 October 2015 at 11:42, Andrus <kobruleht2(at)hot(dot)ee> wrote:
> Hi!
>
> Database idd owner is role idd_owner
> Database has 2 data schemas: public and firma1.
> User may have directly or indirectly assigned rights in this database and
> objects.
> User is not owner of any object. It has only rights assigned to objects.
>
> How to drop such user ?
>
> I tried
>
> revoke all on all tables in schema public,firma1 from "vantaa" cascade;
> revoke all on all sequences in schema public,firma1 from "vantaa"
> cascade;
> revoke all on database idd from "vantaa" cascade;
> revoke all on all functions in schema public,firma1 from "vantaa"
> cascade;
> revoke all on schema public,firma1 from "vantaa" cascade;
> revoke idd_owner from "vantaa" cascade;
> ALTER DEFAULT PRIVILEGES IN SCHEMA public,firma1 revoke all ON TABLES
> from "vantaa";
> DROP ROLE if exists "vantaa"
>
> but got error
>
> role "vantaa" cannot be dropped because some objects depend on it
> DETAIL: privileges for schema public
>
> in statement
>
> DROP ROLE if exists "vantaa"
>
> How to fix this so that user can dropped ?
>
> How to create sql or plpgsql method which takes user name as parameter and
> drops this user in all cases without dropping data ?
> Or maybe there is some command or simpler commands in postgres ?

The objects can't be owned by nothing, so you will need to reassign ownership:

REASSIGN OWNED BY old_role TO new_role;

e.g.

REASSIGN OWNED BY vantaa TO postgres;

Then you can drop the role.

Regards

Thom