Re: Ynt: How to drop user if objects depend on it - Mailing list pgsql-general

Neslisah Demirci <neslisah.demirci@markafoni.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@postgresql.org <pgsql-general-owner@postgresql.org> ad?na Andrus <kobruleht2@hot.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
droppingdata ? 
> 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
directlyor 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.consulting@comcast.net
p: 312.241.7800