On 7 October 2015 at 11:42, Andrus <kobruleht2@xxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
