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

Last updated: 2021-11-12

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 in Amazon Redshift, you might encounter one of the following error messages:

ERROR: user "username" cannot be dropped because some objects depend on it
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

These errors can occur when you try to drop the following types of users:

  • Owner or target user of default privileges that were previously granted.
  • Owner of any object (such as databases, schemas, tables, views, procedures, and libraries).
  • User has privileges on the previously mentioned objects.

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

Important: 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 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 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, then create these views in any other existing schema. To create these views in another schema, modify the definition or create an admin schema. Be aware that if there are any column changes in your view definition, you must drop your view before creating a new view and definition. If a view already exists and you try to create a new one without dropping the old view, then you receive an invalid table definition error.

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

select ddl, grantor, grantee from admin.v_generate_user_grant_revoke_ddl where grantee='<username-to-be-dropped>' 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 the permissions 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='<username-to-be-dropped>' order by objseq, grantseq desc;

Note: Make sure to replace "grantor" and "grantee" with the user names who can grant and receive permissions, respectively.

If your query doesn't return any records or the drop user command fails, then run the following query:

select ddl from admin.v_generate_user_grant_revoke_ddl where ddltype='revoke' and ddl ilike '%<user-to-be-dropped>%' order by objseq, grantseq desc;

This query lists the permissions that must be revoked from the user before the user can be dropped. Make sure to revoke these permissions before proceeding to the next step.

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

select * from pg_user where usename = '<username-to-be-dropped>'; 
select * from pg_default_acl where defacluser= <user-id>;

To retrieve the use name and user ID, find the usename and usesysid column entries in the PG_USER table.

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

5.    (Optional) If the user to be dropped still has permissions on some objects, then check if the user is part of another group. The user might have permissions that are granted from that group. Or, the user might have permissions that have been granted to the PUBLIC group (or all users).

To confirm the permissions that are still granted to the user, run the following queries:

select * from pg_user where usename = '<username-to-be-dropped>';
select * from pg_group;

In the output of the second query, check the grolist column and verify whether there are any entries listing the user to be dropped. If the user to be dropped belongs to another group, then the user ID will be listed.

If your grolist column indicates that the user is part of a group, then check the permissions that are granted to that group:

select ddl from admin.v_generate_user_grant_revoke_ddl where ddltype='revoke' and grantee= 'group <group-name>' ;
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.

6.    Find all objects owned by the user, and then transfer the ownership from the user being dropped to a different user or admin:

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

This output lists the commands that you can use to transfer ownership to a new user. Make sure to run the commands that are listed.

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

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

drop user <username-to-be-dropped>;

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 grantee= 'group <group-name>';

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?