Re: Ynt: How to drop user if objects depend on it

Neslisah Demirci <neslisah(dot)demirci(at)markafoni(dot)com> writes:

> Hi ,
>
> REASSIGN OWNED -- change the ownership of database objects owned by a database role.
>
> REASSIGN OWNED BY old_role [, ...] TO new_role
>
> You can create a new role then you just assign database objects depend on old role.
> REASSIGN owned by old_role to new_role;
>
> Then
>
> DROP old_role;
>
> Is this helpful?

It might be if were accurate :-)

Permissions are not reassignable.

drop owned by foo_role;

Sometimes to be on the safe side, just in case foo_role did own objects
that you'd rather not drop...

create role foo_orphaned_objects_role;
reassign owned by foo_role to foo_orphaned_objects_role;
drop owned by foo_role
drop role foo_role;

Note that you may have to repeat this for each DB in a given cluster if
foo_role owns things or is direct grant recipient.

>
> Neslisah.
>
> ------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> G?nderen: pgsql-general-owner(at)postgresql(dot)org <pgsql-general-owner(at)postgresql(dot)org> ad?na Andrus <kobruleht2(at)hot(dot)ee>
> G?nderildi: 07 Ekim 2015 ?ar?amba 13:42
> Kime: pgsql-general
> Konu: [GENERAL] How to drop user if objects depend on it
>
> 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 ?
>
> Using Postgres 9.1+
> Posted also in
>
> http://stackoverflow.com/questions/32988702/how-to-drop-user-in-all-cases-in-postgres
>
> [apple-touch-icon] sql - How to drop user in postgres if it has depending objects - Stack Overflow
> 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 ob...
> Devam?n? okuyun...
>
> Andrus.
>

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres(dot)consulting(at)comcast(dot)net
p: 312.241.7800