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. To view the list of databases in the cluster, run the SHOW DATABASES command. To view the name of the database that you're logged into, run the CURRENT_DATABASE command.

Resolution

Install scripts to create views

As a superuser, download and install the v_generate_user_grant_revoke_ddl.sql and v_find_dropuser_objs.sql scripts from the AWS Labs repository on the GitHub website. 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 you change any columns in your view definition, then drop your existing view before you create a new view and definition. When you create a new view before you drop the old view, you receive a not valid table error.

Drop a user

Complete the following steps:

1. Open the query editor v2. Then, search for the user that you want to drop to view the granted user permissions.

2. To regrant the permissions to another user, run the following command:

   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.

3. After you regrant the permissions to another user, run the following command to revoke the permissions from the user:

   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 command:

   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 any additional permissions that you must revoke from the user. Revoke these permissions.

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

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

   Note: Replace username-to-be-dropped and user-name with your values. To retrieve the username and user ID, find the username and usesysid column entries in the PG_USER_INFO table. If there are user entries in the PG_DEFAULT_ACL table, then you can't drop the user.

5. If the user still has permissions on 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;

6. (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 are granted to the PUBLIC group.
   To check the permissions that the user still has, run the following queries:

   select * from pg_user where username = '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 the grolist column shows the user ID. To check a group's permissions, run the following command:

   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, Amazon Redshift grants permissions given to the PUBLIC group to all users.

7. Find all objects that the user owns, and then transfer 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.

8. Repeat steps 2-7 in each database on the Amazon Redshift cluster.
   Note: To list your databases, run the SHOW DATABASES command.

9. 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 the permissions:

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

2. Repeat step 1 in each database on the Amazon Redshift cluster to revoke the group's permissions in all databases.

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