Neslisah Demirci <neslisah.demirci@xxxxxxxxxxxxx> 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@xxxxxxxxxxxxxx <pgsql-general-owner@xxxxxxxxxxxxxx> ad?na Andrus <kobruleht2@xxxxxx> > G?nderildi: 07 Ekim 2015 ?ar?amba 13:42 > Kime: pgsql-general > Konu: 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.consulting@xxxxxxxxxxx p: 312.241.7800 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
