PostgreSQL无法撤销schema默认权限如何解决？

撤销不生效的核心原因 #

ALTER DEFAULT PRIVILEGES 默认仅匹配执行授权操作的用户创建的新对象，如果你执行撤销时的账号和此前执行授权的账号不一致，或者没有显式指定授权时的发起角色，就会匹配不到已有的授权规则，导致撤销操作不生效。
另外默认权限规则仅对规则生效后新建的对象有效，已经存在于a_schema中的表，权限不会随默认权限的撤销自动回收，需要单独处理。

正确操作步骤 #

1. 先确认已配置的默认权限规则 #

执行如下查询，定位到你此前创建的授权规则，获取发起授权的角色名：

SELECT r.rolname AS grantor, 
       n.nspname AS schema_name, 
       pg_get_default_acl(d.defaclobjtype, d.defaclacl) AS acl_rule
FROM pg_default_acl d
LEFT JOIN pg_namespace n ON d.defaclnamespace = n.oid
LEFT JOIN pg_roles r ON d.defaclrole = r.oid
WHERE n.nspname = 'a_schema';

返回结果中的grantor字段就是之前执行授权语句的用户。

2. 显式指定授权角色执行撤销 #

假设查询得到的grantor是admin，使用如下语句即可成功撤销默认权限：

ALTER DEFAULT PRIVILEGES FOR ROLE admin IN SCHEMA a_schema
REVOKE ALL ON TABLES FROM a_user;

如果你是用此前执行授权的同一账号操作，也可以省略FOR ROLE子句，但显式指定可以避免账号切换、角色继承等场景导致的不生效问题。

3. 回收存量表的权限（可选） #

如果需要同时回收a_user对a_schema中已经存在的表的权限，额外执行以下语句：

REVOKE ALL ON ALL TABLES IN SCHEMA a_schema FROM a_user;

如果还要回收序列、函数等其他类型对象的权限，对应调整语句中的对象类型即可。

内容的提问来源于stack exchange，提问作者CJ Chang