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

6 minute read
1

I can't drop a user or group in Amazon Redshift.

Short description

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

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

To resolve these error messages, first remove any 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

Drop 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 used 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. If there are any column changes in your view definition, drop your view before you create a new view and definition. If a view already exists and you attempt to create a new one before you drop the old view, you receive a "not valid table" error.

  2. Find all granted user permissions by user that must be dropped. Then, regrant those 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: The user that regrants the permissions must be a user with permissions on the object. This user must have grant permissions rights 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 v_generate_user_grant_revoke_ddl where ddltype='revoke' and (grantee='<username>' or grantor='<username>') order by objseq, grantseq desc;

    Note: Replace grantor and grantee with the usernames who can grant and receive permissions, respectively.
    If your query doesn't return any records or the DROP USER command fails, then run this 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 query lists the permissions that must be revoked from the user before the user can be dropped. Revoke these permissions before you move to the next step.

  4. Run these 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>;
    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 table.
    Note: You can't drop a user if there are any user entries in the PG_DEFAULT_ACL table.

  5. If the user still has permissions on some objects, then check if the user was granted assume-role permissions. To check this, run this query:

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

    If you see assumerole permissions for the user to be dropped, then revoke them with this command:

    revoke assumerole on all from <user-name> for all;
  6. (Optional) If the user to be dropped still has permissions on some objects, then check whether 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 or all users.
    To confirm the permissions that are still granted to the user, run these 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. Verify whether there are any entries that list the user to be dropped. If the user to be dropped belongs to another group, then the user ID is 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.

  7. Find all objects owned by the user, and then transfer the ownership from the user that you want to drop 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.

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

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

    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.

  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, then 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 this 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. Confirm that the group's permissions are revoked in all databases.

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

Related information

DROP USER usage notes

DROP GROUP example

AWS OFFICIAL
AWS OFFICIALUpdated 2 months ago