How to drop role from database

Question

I tried to drop role admin using

reassign owned by admin to aus_owner;
ALTER DEFAULT PRIVILEGES FOR ROLE admin  IN SCHEMA public,firma1
   REVOKE ALL ON TABLES FROM admin;
drop role admin

but got error

 ERROR:  role "admin" cannot be dropped because some objects depend on
 it 
DETAIL:  privileges for schema public privileges for database aus

 privileges for schema firma1 privileges for table firma1.summav

 privileges for sequence firma1.summav_recnr_seq ... 
and 2406 other
 objects (see server log for list)

Using PostgreSQL 12.2

You need to change the owner of all objects belonging to admin see https://stackoverflow.com/questions/59785733/how-to-alter-the-ownership-of-some-tables-inside-a-database-from-postgres-to-ano and https://stackoverflow.com/questions/23933327/best-way-to-change-the-owner-of-a-postgresql-database-and-their-tables — , Apr 13 '22 at 09:42

score 1 · Accepted Answer · answered Apr 13 '22 at 09:49

1

After you ran REASSIGN ALL, it is probably enough to run

DROP OWNED BY admin;

to get rid of the privileges granted to the role. Then you should be able to drop it.

Read this for more background information.

answered Apr 13 '22 at 09:49

Laurenz Albe

209,280
17
206
263

Is it sure that DROP OWNED BY after REASSIGN OWNED BY will not delete any tables in database? – Andrus Apr 13 '22 at 11:09
If the user does not own any tables (after `REASSIGN OWNED`), which tables would that be? – Laurenz Albe Apr 13 '22 at 11:22

How to drop role from database

1 Answers1