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

Last updated: 2020-04-16

I get error messages similar to the following when I try to drop a user or group in Amazon Redshift. How do I resolve this?

"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"

Resolution

Before you can drop a user or group, you must first remove user privileges and transfer object ownership, or remove group ownership of objects.

Drop a user

Important: Before you drop a user, revoke any user privileges. 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 Amazon Web Services - 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.

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

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

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

3.    Find all privileges granted to the user, and then revoke those privileges:

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

4.    Download and install the v_find_dropuser_objs.sql script from the Amazon Web Services - Labs GitHub repository. This script creates a view in Amazon Redshift that is useful for the next step.

5.    Find all objects owned by the user, and then transfer ownership to a different user. In the following example, <username> is the current owner and <newuser> is the new owner:

select ddl||'<newuser>;' as ddl from admin.v_find_dropuser_objs where objowner = '<username>';

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

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

Drop a group

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

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 step.

2.    Find all privileges granted to the group, and then revoke those privileges, 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 cluster to confirm that the group's privileges are revoked in all databases.

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


Did this article help you?

Anything we could improve?


Need more help?