AWS Redshift无法删除用户：撤销权限后仍提示存在对象访问权限

Let's work through resolving this issue where you can't drop the etlglue user because Redshift insists they still hold object permissions. Here's a step-by-step, targeted approach:

1. First, Pinpoint Exactly What Permissions/Objects Are Tied to etlglue #

Before randomly revoking permissions, you need to see what's still linked to the user. Run these queries to get clear visibility:

Check Table/View Privileges #

SELECT table_schema, table_name, privilege_type
FROM information_schema.table_privileges
WHERE grantee = 'etlglue';

Check Schema-Level Privileges #

SELECT n.nspname AS schema_name, p.privilege_type
FROM pg_namespace n
JOIN pg_namespace_privilege p ON p.nspname = n.nspname
WHERE p.grantee = (SELECT usesysid FROM pg_user WHERE usename = 'etlglue');

Check if the User Owns Any Objects (Tables, Views, Sequences) #

SELECT 
  n.nspname AS schema_name, 
  c.relname AS object_name, 
  CASE c.relkind 
    WHEN 'r' THEN 'Table'
    WHEN 'v' THEN 'View'
    WHEN 's' THEN 'Sequence'
    ELSE 'Other'
  END AS object_type
FROM pg_class c
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE c.relowner = (SELECT usesysid FROM pg_user WHERE usename = 'etlglue');

Check Default Privileges (Often Overlooked!) #

If you ever granted default privileges to etlglue for future objects, those might be lingering in the background:

SELECT 
  n.nspname AS schema_name, 
  d.defaclobjtype AS object_type, 
  d.defaclprivilege AS default_privileges
FROM pg_default_acl d
JOIN pg_namespace n ON d.defaclnamespace = n.oid
WHERE d.defacluser = (SELECT usesysid FROM pg_user WHERE usename = 'etlglue');

2. Revoke All Identified Privileges & Clean Up Ownership #

Based on the results from the above queries, run the appropriate commands to clear out permissions:

Revoke Table/View Privileges #

Your initial schema revoke didn't touch table-level permissions—you need to handle those explicitly:

-- Revoke on existing tables in tbl schema
REVOKE SELECT ON ALL TABLES IN SCHEMA tbl FROM etlglue;
-- Repeat for pub schema if needed
REVOKE SELECT ON ALL TABLES IN SCHEMA pub FROM etlglue;

-- If views are present, revoke permissions on those too
REVOKE SELECT ON ALL VIEWS IN SCHEMA tbl FROM etlglue;
REVOKE SELECT ON ALL VIEWS IN SCHEMA pub FROM etlglue;

Revoke Schema-Level Privileges #

Double down on schema permissions to ensure nothing is left:

REVOKE ALL PRIVILEGES ON SCHEMA tbl FROM etlglue;
REVOKE ALL PRIVILEGES ON SCHEMA pub FROM etlglue;
-- Explicitly revoke USAGE (a common leftover schema privilege)
REVOKE USAGE ON SCHEMA tbl FROM etlglue;
REVOKE USAGE ON SCHEMA pub FROM etlglue;

Revoke Default Privileges #

If default privileges were found, revoke them to eliminate future access potential:

ALTER DEFAULT PRIVILEGES IN SCHEMA tbl REVOKE ALL ON TABLES FROM etlglue;
ALTER DEFAULT PRIVILEGES IN SCHEMA pub REVOKE ALL ON TABLES FROM etlglue;
ALTER DEFAULT PRIVILEGES IN SCHEMA tbl REVOKE ALL ON SEQUENCES FROM etlglue;
ALTER DEFAULT PRIVILEGES IN SCHEMA pub REVOKE ALL ON SEQUENCES FROM etlglue;

Transfer Ownership If the User Owns Objects #

If the ownership check showed etlglue owns tables/views/sequences, transfer ownership to another valid user (e.g., your superuser):

-- Example for a table
ALTER TABLE tbl.your_target_table OWNER TO your_superuser;
-- Example for a view
ALTER VIEW tbl.your_target_view OWNER TO your_superuser;

3. Drop the User #

Once all permissions are revoked and ownership is transferred, you can finally drop the user:

DROP USER etlglue;

Why Your Initial Revoke Failed #

Your original REVOKE ALL PRIVILEGES ON SCHEMA only affects schema-level permissions (like creating objects or accessing the schema itself), not individual table-level permissions. Table access rights, default privileges, and owned objects are the most common reasons Redstone blocks user deletion.

内容的提问来源于stack exchange，提问作者Andres Urrego Angel