Why DROP USER still fails with "owns some object" in Amazon Redshift after standard cleanup

First, confirm that this article applies to your situation

This article does not cover the normal DROP USER procedure. It covers only the rare case where the drop still fails after you complete that procedure. It applies when all of the following are true:

• You have already completed the usual user-cleanup steps, such as transferring owned objects and revoking explicit privileges.
• The admin views from the Knowledge Center procedure (v_find_dropuser_objs, v_generate_user_grant_revoke_ddl) return zero rows.
• A pg_default_acl check shows no entries for the user.
• The target user owns no objects.

Yet the following error persists:

DROP USER "a_example_user";
[55006] ERROR: user "a_example_user" cannot be dropped because the user owns some object

Note: If you have not yet tried the standard procedure, first complete the ownership-transfer and privilege-revoke steps in the existing Knowledge Center article. This article focuses only on one cause that can remain afterward.

Root cause

The cause is default privileges set with ALTER DEFAULT PRIVILEGES that still remain. A default privilege predefines the permissions that automatically apply to objects the user creates in the future. Because the user is recorded as the owner of those entries, Amazon Redshift treats them as objects owned by the user and blocks the drop.

These entries are missed by the standard procedure for the following reasons:

• The existing Knowledge Center admin views and pg_default_acl do not surface default privileges granted to RBAC roles. Default privileges targeting a role must be checked in the SVV_DEFAULT_PRIVILEGES view.
• Default privileges are managed per database. Cleaning one database does not remove entries in another, and those remaining entries continue to block the drop, so you must check every database in the cluster.

Diagnosis

First, check the error message. Amazon Redshift raises two different messages from different check layers. Resolving one can surface the other, so handle them in order.

• has a privilege on some object → This is a regular privilege issue. Use SHOW GRANTS FOR <username> to find the privileges the user holds and revoke them.
• owns some object → This is an owned-object, owned-role, or default-privilege issue. If you have already cleaned up owned objects and roles with the standard procedure, the remaining cause is default privileges. Continue with the steps below.

1. List every database in the cluster.

   SHOW DATABASES;

2. Connect to each database as a superuser and check for default privileges that remain for the target user. A database that returns rows is a location that is blocking the drop.

   SELECT schema_name, object_type, grantee_name, grantee_type
   FROM svv_default_privileges
   WHERE owner_name = '<username>';

Resolution

1. Revoke the default privileges for the schema and grantee identified during diagnosis. Apply the following rules:

   • Use ON TABLES when object_type is RELATION, ON FUNCTIONS when it is FUNCTION, and ON PROCEDURES when it is PROCEDURE.
   • Specify the grantee as ROLE, a user, or PUBLIC according to grantee_type.
   • For entries where schema_name is empty (database-level defaults), revoke without IN SCHEMA.
   • Enclose names in double quotes if they contain uppercase letters.

   ALTER DEFAULT PRIVILEGES FOR USER <username> IN SCHEMA <schema> REVOKE ALL ON TABLES FROM ROLE "<role>";

2. The command above can fail with [42501] ERROR: permission denied for schema. This is because the permission check for ALTER DEFAULT PRIVILEGES ... IN SCHEMA looks at whether the target user (the one specified in FOR USER) has CREATE permission on the schema — not the superuser running the command. If the target user lacks CREATE, this occurs regardless of the schema type. To work around it, grant CREATE temporarily, wrapped in a single transaction so that no permanent privilege change remains.

   For a regular (internal) schema, grant schema-scoped CREATE.

   BEGIN;
   GRANT CREATE ON SCHEMA <schema> TO <username>;
   ALTER DEFAULT PRIVILEGES FOR USER <username> IN SCHEMA <schema> REVOKE ALL ON TABLES FROM ROLE "<role>";
   REVOKE CREATE ON SCHEMA <schema> FROM <username>;
   COMMIT;

3. For an external (Spectrum) schema, the GRANT CREATE ON SCHEMA above does not apply and is ignored with an INFO: No privileges were granted message. In this case, use database-scoped CREATE (a scoped permission) instead.

   BEGIN;
   GRANT CREATE FOR SCHEMAS IN DATABASE <database> TO <username>;
   ALTER DEFAULT PRIVILEGES FOR USER <username> IN SCHEMA <schema> REVOKE ALL ON TABLES FROM ROLE "<role>";
   REVOKE CREATE FOR SCHEMAS IN DATABASE <database> FROM <username>;
   COMMIT;

4. After you finish revoking in every database identified during diagnosis, drop the user.

   DROP USER <username>;

Note: Revoking default privileges affects only objects created in the future. Privileges already granted on existing tables and views are not changed.

Related information

• SVV_DEFAULT_PRIVILEGES
• ALTER DEFAULT PRIVILEGES
• Scoped permissions
• How do I resolve the "user cannot be dropped" error in Amazon Redshift?