11

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 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
  • 26,339
  • 60
  • 204
  • 378
  • Does this answer your question? [PostgreSQL - how to quickly drop a user with existing privileges](https://stackoverflow.com/questions/3023583/postgresql-how-to-quickly-drop-a-user-with-existing-privileges) – Eugen Konkov Sep 20 '20 at 12:32

2 Answers2

5

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;
aleroot
  • 71,077
  • 30
  • 176
  • 213
  • 12
    I 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 Oct 07 '15 at 11:15
  • 2
    I also tried `reassign owned by vantaa to idd_owner` but got same error. – Andrus Oct 07 '15 at 11:22
  • 1
    I ran reassign command under user postgres. After that drop user still causes the same error. – Andrus Oct 07 '15 at 12:18
4

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;
Olivier D
  • 423
  • 3
  • 10