Why can't I drop a user or role in my RDS for PostgreSQL DB instance?

Short description

When a user or role in RDS for PostgreSQL creates an object, such as a table or schema, the user or role is the owner of the object created. If you try to drop a user or role that owns one or more objects in any database or has privileges on these objects, then you receive an error indicating that there are objects that depend on the user or role along with granted permissions, if there are any.

To drop a user or role that has dependent objects, you must do the following:

1. Reassign the ownership of these objects to another user.
2. Revoke any permissions that were granted to the user or role.

Note: If these objects are no longer needed, consider dropping these objects and then deleting the role. You can drop all objects that are owned by a role in a database using the DROP OWNED command. You can also revoke any privileges granted to the role on objects in that database or shared objects. After the DROP OWNED command runs successfully, you can drop the role.

Resolution

In the following example, three different database roles are used:

• test_user: This is the user or role that must be dropped.
• admin_user: This is the role that's used to drop the required user or role. This user is the highest privileged user in RDS with the rds_superuser role attached to it.
• another_user: This is the user or role that's assigned ownership of objects owned by test_user.

Run the following command to see the role with which you logged in:

pg_example=> SELECT current_user;

The output looks similar to the following:

current_user
--------------
 admin_user
(1 row)

When you try to drop a user or role with dependent objects, you get an error similar to the following:

pg_example=> DROP ROLE test_user;
ERROR:  role "test_user" cannot be dropped because some objects depend on it
DETAIL:  privileges for database pg_example
owner of table test_table
owner of schema test_schema
owner of sequence test_schema.test_seq
privileges for table test_t2

In this example, the role being dropped is test_user. Note that the role that's currently logged in is admin_user, which is the master user of the database.

From the error message, you get the following information:

• The role test_user has privileges granted on the database pg_example and table test_t2.
• The role test_user owns the table test_table, schema test_schema, and a sequence object test_seq in test_schema.

Note: if you drop a user or role when you're connected to a different database, then you get an output similar to the following:

pg_another_db=> DROP ROLE test_user;
ERROR:  role "test_user" cannot be dropped because some objects depend on it
DETAIL:  privileges for database pg_example
4 objects in database pg_example

To see objects that are owned by a user or role, be sure to connect to the database where the owned objects are located.

To drop the user or role, you must reassign the ownership of the owned objects to another user or role and revoke associated permissions. You can use the PostgreSQL REASSIGN OWNED command to reassign the ownership of these objects to another user. When running this command, you might get an error similar to the following:

pg_example=> select current_user;
 current_user
--------------
 test_user
pg_example=> REASSIGN OWNED BY test_user TO another_user;
ERROR:  permission denied to reassign objects

To resolve this issue, you must grant the user or role to the user that's reassigning ownership. You can't use test_user to do so because test_user isn't the owner of another_user. Therefore, you might see an error similar to the following:

pg_example=> select current_user;
 current_user
--------------
 test_user
pg_example=> grant another_user to test_user;
ERROR:  must have admin option on role "another_user"

You can do either of the following to grant the user or role to the user that's reassigning ownership:

• Sign in to your master user and run the GRANT command:

pg_example=> select current_user;
 current_user
--------------
 admin_user
pg_example=> GRANT another_user TO test_user;
GRANT ROLE

• Sign in to the user that will reassign ownership and run the GRANT command:

pg_example=> select current_user;
 current_user
--------------
 another_user
pg_example=> GRANT another_user TO test_user;
GRANT ROLE

After choosing one of the preceding options, reassign the ownership of objects owned by test_user to another_user after logging in to test_user:

pg_example=> select current_user;
 current_user
--------------
 test_user
pg_example=> reassign owned by test_user to another_user;
REASSIGN OWNED

If you sign in to your master user and attempt to drop test_user that still has existing privileges, then you might error similar to the following:

pg_example=> select current_user;
 current_user
--------------
 admin_user
pg_example=> DROP ROLE test_user;
ERROR:  role "test_user" cannot be dropped because some objects depend on it
DETAIL:  privileges for database pg_example
privileges for table test_t2

In this case, you get an error even though the REASSIGN command is successful. This is because, the privileges of test_user must be revoked. Run the REVOKE command to revoke all usage permissions from any object on which test_user has privileges. In this example, revoke the permissions on the database pg_example and table test_t2 for test_user.

pg_example=> REVOKE ALL ON TABLE test_t2 FROM test_user;
REVOKE
pg_example=> REVOKE ALL ON DATABASE pg_example FROM test_user;
REVOKE

Then, drop the user test_user:

pg_example=> DROP ROLE test_user;
DROP ROLE

After revoking the privileges, you can successfully drop the role.

---

Related information

PostgreSQL documentation for DROP ROLE