How to drop user in postgres if it has depending objects
Solution 1
Before dropping the user you can run :
REASSIGN OWNED BY vantaa TO <newuser>
you could just reassign to postgres if you don't know who to reassign that to ...
REASSIGN OWNED BY vantaa TO postgres;
Solution 2
Your reassignment can fail. When you reassign to a new user/role - you do it on the currently selected database - the current user must belong to the 'from' role and 'to' (grant them and revoke them after)
Excerpt of a shell script of mine (a few things omitted)
# The DROP USER operation will fail if this user is the owner of an existing schema, table, index...
# The user creating a resource owns it. It should transfer the ownership to the role
# When reassigning, the current user needs to have the <<roles>> associated to BY and TO to execute the command.
GRANT $_roleservice TO $_superuser;
GRANT $_account TO $_superuser;
REASSIGN OWNED BY $_account TO $_roleservice;
# dropping the user removes it from the current user list of roles/users
DROP USER $_account;
REVOKE $_roleservice FROM $_superuser;
Related videos on Youtube
Andrus
Updated on October 24, 2022Comments
-
Andrus over 1 year
Database idd owner is role
idd_owner
.Database has 2 data schemas:
public
andfirma1
. User may have directly or indirectly assigned rights in this database and objects. User is not owner of any object. It has only granted rights.How to drops 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 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 ?
Using Postgres 9.1+
-
Andrus over 8 yearsI tried but got error
permission denied to reassign objects
. User which runs this command is not superuser. I can add some rights to user who invokes this command if this helps. How to fix ? -
Andrus over 8 yearsI also tried
reassign owned by vantaa to idd_owner
but got same error. -
Andrus over 8 yearsI ran reassign command under user postgres. After that drop user still causes the same error.