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

Short description

When you try to drop a user in Amazon Redshift, you might see 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:

• An owner or target user who has default permissions.
• An owner of any object, such as databases, schemas, tables, views, procedures, and libraries.
• A user who has permissions on objects.

To resolve these error messages, first remove the user permissions. 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

Install scripts to create views

Download and install the v_generate_user_grant_revoke_ddl.sql and v_find_dropuser_objs.sql scripts from the AWS Labs GitHub repository. 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 on the Amazon Redshift cluster, then modify the definition in an existing schema. Or, create an admin schema.

If there are any column changes in your view definition, then drop your view before you create a new view and definition. If you try to create a new view before you drop the old view, then you receive a not valid table error.

Drop a user

Complete the following steps:

1. Search by user to find all granted user permissions that you want to drop. Then, regrant the permissions as another user. It's a best practice that this user is a superuser:

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

   Note: To regrant the permissions, you must be a user with permissions on the object. You must also have grant permissions rights to another user. If you don't have grant permissions, then you can regrant the permissions as a superuser.

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

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

   Note: Replace grantor with the name of the user who grants the permissions. Replace grantee with the name of the user who receives the permissions.
   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;

   The preceding query lists the permissions that you must revoke from the user before you can drop the user. Revoke these permissions before you move to the next step.

3. To check for empty access control lists (ACLs), run the following queries:

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

   To retrieve the username and user ID, find the usename and usesysid column entries in the PG_USER_INFO table.
   Note: If there are user entries in the PG_DEFAULT_ACL table, then you can't drop the user.

4. If the user still has permissions on some objects, then check if the user has assumerole permissions:

   select pg_get_iam_role_by_user('<user-name>');

   If the user has assumerole permissions, then first revoke assumerole from the public. Then, run the following command to revoke username:

   revoke assumerole on all from public for all;
   revoke assumerole on all from <user-name> for all;

5. (Optional) If the user 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 were granted to the PUBLIC group.
   To check 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 pg_group query, check if there are entries that list the user in the grolist column. If the user belongs to another group, then their user ID is listed. 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';

   Note: Replace objname and schemaname with your table and schema. By default, permissions granted to the PUBLIC group are granted to all users.

6. Find all objects that the user owns, and then transfer the ownership to a different user or admin:

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

   The output from the preceding command lists the commands that you must use to transfer ownership to a new user.

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

8. To remove the user from the database, run the following command:

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

Drop a group

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

Complete the following steps:

1. Find all permissions granted to the group, and then revoke them:

   select ddl from admin.v_generate_user_grant_revoke_ddl where ddltype='revoke' and grantee= 'group <group-name>';

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

3. To remove the user group, run the DROP GROUP command.