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

Last updated: 2020-12-23

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 AWSLabs 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.sqland 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, then 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 with 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;

Make sure to replace "grantor" and "grantee" with the usernames that can grant and receive permissions, respectively.

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;

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

5.    (Optional) If the query returns an entry for the user, run the revoke command again:

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

Verify whether all permissions granted to the user have been revoked:

select * from pg_user where usename = 'testuser1';
select * from pg_group;

In the output of the query, check the grolist column to verify whether the user (testuser1) belongs to any other group.

6.    (Optional) If your grolist column indicates that testuser1 is part of a group, check the permissions granted to that group:

select ddl from admin.v_generate_user_grant_revoke_ddl where ddltype='revoke' and (grantee='group-name') order by objseq, grantseq desc;

7.    (Optional) If the PG_DEFAULT_ACL table is still showing entries, check whether any permissions have been granted to the PUBLIC group:

select * from admin.v_generate_user_grant_revoke_ddl where objname='timecards' and schemaname='postgres' and grantee='PUBLIC' and ddltype='revoke';

Make sure to replace objname and schemaname with your respective table and schema.

Note: By default, permissions granted to the PUBLIC group are granted to all users.

8.    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 admin.v_find_dropuser_objs where objowner = 'testuser1';

9.    Repeat Steps 2-8 in each database on the Amazon Redshift cluster.

10.    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 AWSLabs 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?