Category: Privileges

Try to drop a role/user in PostgreSQL, and get below errors:

postgres=>drop role testuser_read;
ERROR: role "testuser_read" cannot be dropped because some objects depend on it
DETAIL: privileges for table user1.tab1
privileges for table table user1.tab1
privileges for sequence user1.seq1
...
..
.

SOLUTION

1)Revoke objects privileges from this role/user which is to be dropped:

postgres=> select grantee,table_schema,table_name,privilege_type
            from information_schema.role_table_grants
           where grantee='testuser_read' 
           order by table_name,privilege_type;
grantee    | table_schema | table_name | privilege_type
-----------+--------------+------------+----------------
testuser_read |    user1     | tab1       | SELECT
testuser_read |    user1     | tab2       | SELECT
...
..
.

postgres=> select 'revoke '||privilege_type ||' on '||
           table_schema||'.'||table_name||' from '||grantee||' ;'
from information_schema.role_table_grants
where grantee='testuser_read' ;

-----------------------------------------------------------------------------
 revoke SELECT on user1.tab1 from testuser_read ;
 revoke SELECT on user1.tab2 from testuser_read ;
...
..
.

OR just ...

postgres=>REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA user1 
          FROM testuser_read; 

postgres=>REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA user1
          FROM testuser_read; 

postgres=>alter default privileges in schema testuser revoke select  
          on tables from testuser_read;

postgres=>alter default privileges in schema testuser revoke select ,  
          usage on sequences from testuser_read;

2) Revoke depended roles.

\du to find dependent roles

postgres=>\du
postgres=>revoke other_role from testuser_read;

3) Drop the role.

postgres=> drop role testuser_read;
ERROR:  role "testuser_read" cannot be dropped because some objects depend on it
DETAIL:  privileges for default privileges on new sequences belonging to role testuser in schema testuser
privileges for default privileges on new relations belonging to role tetsuser in schema testuser

postgres=> ALTER DEFAULT PRIVILEGES FOR ROLE testuser IN SCHEMA testuser REVOKE ALL ON TABLES FROM testuser_read;
ALTER DEFAULT PRIVILEGES
postgres=> ALTER DEFAULT PRIVILEGES FOR ROLE testuser IN SCHEMA testuser REVOKE ALL ON SEQUENCES FROM testuser_read;
ALTER DEFAULT PRIVILEGES
postgres=> drop role testuser_read;
DROP ROLE
postgres=>