How do I resolve the "user cannot be dropped" error in Amazon Redshift?

Last updated: 2020-09-01

I'm unable to drop a user or group in Amazon Redshift. How do I resolve this?

Short description

When you try to drop a user or group in Amazon Redshift, you might encounter one of the following error messages:

ERROR: user 'username' cannot be dropped because the user has a privilege on some object
ERROR: user 'username' cannot be dropped because the user owns some object

To resolve the error message, you must first remove any user permissions and then transfer object ownership, or remove group ownership of objects.

Note: You must revoke user and group permissions from all databases in the Amazon Redshift cluster.

Resolution

Dropping a user

Important: Before you drop a user, revoke any user permissions. Then, transfer ownership of any database objects owned by the user.

1.    Download and install the v_generate_user_grant_revoke_ddl.sql script from the AWS Labs GitHub repository. The v_generate_user_grant_revoke_ddl.sql script creates a view in Amazon Redshift that is useful for the next two steps.

Note: The views for the v_generate_user_grant_revoke_ddl.sql and v_find_dropuser_objs.sql scripts use the admin schema in their definition. If you don't have an admin schema created on the Amazon Redshift cluster, you can create these views in any other existing schema. To create these views in another schema, modify the definition or create an admin schema.

2.    Find all granted user permissions that are to be dropped, and then regrant those permissions to another user:

select regexp_replace(ddl,grantor,'aws') from admin.v_generate_user_grant_revoke_ddl where grantor='aws' and ddltype='grant' and objtype <>'default acl' order by objseq,grantseq;

Note: The user that regrants the permissions must be a user that has permissions on the object. This user must also be able to grant permissions to another user. If there are no other users with regrant permissions, then you can regrant them as a superuser.

3.    Find all permissions granted to the user, and then revoke them:

select ddl from admin.v_generate_user_grant_revoke_ddl where ddltype='revoke' and (grantee='testuser1' or grantor='aws') order by objseq, grantseq desc;

4.    Run the following queries to check for any empty access control lists (ACLs):

select * from pg_user where usename = ‘testuser1’;
select * from pg_default_acl where defacluser= 108;

To drop a user, there can't be any user entries in the pg_default_acl table.

If the query returns an entry for the user, run the revoke command again:

select ddl from v_generate_user_grant_revoke_ddl where ddltype='revoke' and (grantee=’testuser1’ or grantor='aws') order by objseq, grantseq desc;

5.    Find all objects owned by the user, and then transfer the ownership from the user being dropped to a different user or admin. In the following example, username is the current owner and newuser is the new owner:

select ddl||'<newuser>;' as ddl from v_find_dropuser_objs where objowner = 'testuser1';

6.    Repeat steps 2-5 in each database on the Amazon Redshift cluster.

7.    Use the DROP USER command to remove the user from the database:

drop user testuser1;

Dropping a group

Before you drop a group, you must revoke any permissions that the group has for objects.

1.    Download and install the v_generate_user_grant_revoke_ddl.sql and v_find_dropuser_objs.sql scripts from the AWS Labs GitHub repository. These scripts create views in Amazon Redshift that are useful for the next step.

Note: The views for the v_generate_user_grant_revoke_ddl.sql and v_find_dropuser_objs.sql scripts use the admin schema in their definition. If you don't have an admin schema created on the Amazon Redshift cluster, you can create these views in any other existing schema. To create these views in another schema, modify the definition or create an admin schema.

2.    Find all permissions granted to the group, and then revoke them, as shown in the following example:

select ddl from admin.v_generate_user_grant_revoke_ddl where ddltype='revoke' and username='<groupname>' order by sequence;

3.    Repeat step 2 in each database on the Amazon Redshift cluster, confirming that the group's permissions are revoked in all databases.

4.    Use the DROP GROUP command to remove the user group.


Did this article help?


Do you need billing or technical support?