Redshift无法撤销用户默认权限导致无法删除用户相关问题咨询

故障复现流程 #

删除用户时因该用户持有默认权限无法删除，同时无法直接撤销对应默认权限，复现步骤如下：

1. 由主用户redshift_master执行创建用户命令：

-- 由主用户redshift_master执行
CREATE USER anton_test_user PASSWORD '***' IN GROUP redshift_dev;

2. 使用anton_test_user账号执行建schema、建表、配置默认权限操作：

CREATE SCHEMA anton_test_schema;
CREATE TABLE anton_test_schema.anton_test_table AS SELECT 1 AS anton;
ALTER DEFAULT PRIVILEGES IN SCHEMA anton_test_schema 
GRANT SELECT ON TABLES TO GROUP redshift_readonly;

3. 切换回redshift_master执行对象所有权转移操作：

ALTER SCHEMA anton_test_schema OWNER TO redshift_master;
ALTER TABLE anton_test_schema.anton_test_table OWNER TO redshift_master;

4. 尝试删除用户时触发默认权限相关报错：
   执行DROP USER anton_test_user; 时返回如下提示：

owner of default privileges on new relations belonging to user 
anton_test_user in schema anton_test_schema;

5. 直接撤销默认权限时触发schema权限报错：
   • 用redshift_master执行如下命令：

   ALTER DEFAULT PRIVILEGES FOR USER anton_test_user IN SCHEMA anton_test_schema
   REVOKE ALL ON TABLES FROM redshift_readonly;
   

   返回报错：Invalid operation: permission denied for schema anton_test_schema
   • 切换为anton_test_user执行如下命令：

   ALTER DEFAULT PRIVILEGES IN SCHEMA anton_test_schema
   REVOKE ALL ON TABLES FROM redshift_readonly;
   

   同样返回报错：Invalid operation: permission denied for schema anton_test_schema

核心问题解答 #

1. 无需删除schema和表即可删除用户的解决方案 #

操作思路为补全撤销默认权限所需的schema访问权限，完成默认权限清理后再删除用户，具体步骤如下：

1. 用redshift_master给anton_test_user临时授予对应schema的USAGE权限：

GRANT USAGE ON SCHEMA anton_test_schema TO anton_test_user;

2. 执行默认权限撤销操作，以下两种方式二选一即可：
   • 方式一：用redshift_master直接执行指定用户的默认权限撤销命令

   ALTER DEFAULT PRIVILEGES FOR USER anton_test_user IN SCHEMA anton_test_schema
   REVOKE ALL ON TABLES FROM redshift_readonly;
   

   • 方式二：切换为anton_test_user执行默认权限撤销命令

   ALTER DEFAULT PRIVILEGES IN SCHEMA anton_test_schema
   REVOKE ALL ON TABLES FROM redshift_readonly;
   

3. 清理完默认权限后即可正常删除用户，无需删除schema和表：

DROP USER anton_test_user;

如果有需要，删除用户后可以回收anton_test_user之前的USAGE授权（用户删除后权限也会自动清理，此步骤可省略）。

2. 该运行逻辑符合Redshift的设计预期 #

Redshift的权限校验机制要求，执行ALTER DEFAULT PRIVILEGES指定schema范围时，执行命令的用户必须持有对应schema的USAGE权限，即便是超级用户也需要满足该校验规则。
你在转移schema所有权后，anton_test_user默认失去了该schema的USAGE权限，而redshift_master如果没有显式持有该schema的USAGE权限，也会触发权限报错，属于正常的权限校验逻辑。默认权限的归属绑定在创建权限的用户身上，删除用户前必须清理该用户关联的所有默认权限，这个规则也符合Redshift的设计规范。

3. PostgreSQL也存在完全一致的行为 #

Redshift的权限体系继承自PostgreSQL，上述默认权限校验、用户删除前置检查的逻辑和PostgreSQL原生逻辑完全一致。PostgreSQL中修改指定schema下的默认权限同样要求执行用户持有该schema的USAGE权限，删除用户时如果存在关联的默认权限归属也会触发报错，清理逻辑也完全相同。

内容的提问来源于stack exchange，提问作者Karl Anka