What Should I Do If a Role Failed to Be Deleted from an RDS for PostgreSQL Instance?

Symptom

Role test failed to be deleted from an RDS for PostgreSQL instance by running the DROP ROLE test; command on the DAS console.

Possible Causes

Role test cannot be deleted probably because it is associated with objects. Before deleting this role, revoke the permissions of its associated objects.

Solution

1. Check the permissions of the role.

   select * from INFORMATION_SCHEMA.role_table_grants WHERE grantee='test';

   

2. If role test is associated with objects, revoke the permissions of the objects and then delete the role.

   REVOKE ALL PRIVILEGES ON mytable FROM test;

   DROP ROLE test;