PostgreSQL中授予pg_read_all_data后如何撤销指定schema的权限

原有方案不生效的原因 #

PostgreSQL的权限逻辑为允许权限优先，不存在显式拒绝权限的机制。你所使用的pg_read_all_data是PostgreSQL 14及以上版本提供的内置角色，它会隐式授予用户所有schema的USAGE权限、所有表/视图/序列的只读权限，该全局权限优先级高于你对单个schema执行的REVOKE操作，因此单独收回指定schema权限的操作不会生效。

正确实现步骤 #

该方案可以满足后续新增schema自动授权的需求，无需每次手动操作：

1. 首先收回之前授予的内置角色权限

REVOKE pg_read_all_data FROM <your_username>;

2. 批量授予所有现有非系统schema、非禁止schema的访问权限
   将下方语句中的<your_username>替换为实际用户名，<exclude_schema>替换为你要禁止访问的schema名称后执行：

-- 授予schema USAGE权限
SELECT format('GRANT USAGE ON SCHEMA %I TO <your_username>;', nspname)
FROM pg_namespace
WHERE nspname NOT LIKE 'pg_%'
AND nspname NOT IN ('information_schema', '<exclude_schema>')
\gexec

-- 授予现有表/视图的SELECT权限
SELECT format('GRANT SELECT ON ALL TABLES IN SCHEMA %I TO <your_username>;', nspname)
FROM pg_namespace
WHERE nspname NOT LIKE 'pg_%'
AND nspname NOT IN ('information_schema', '<exclude_schema>')
\gexec

3. 配置默认权限，自动对后续新增的schema和表授予只读权限
   如果所有schema均由同一个角色创建，建议指定创建角色避免权限遗漏，将<creator_role>替换为实际创建schema的用户名：

-- 自动授予新建schema的USAGE权限
ALTER DEFAULT PRIVILEGES FOR ROLE <creator_role> GRANT USAGE ON SCHEMAS TO <your_username>;
-- 自动授予新建表的SELECT权限
ALTER DEFAULT PRIVILEGES FOR ROLE <creator_role> GRANT SELECT ON TABLES TO <your_username>;

如果schema由多个不同角色创建，可以省略FOR ROLE <creator_role>，由超级用户执行即可覆盖所有角色创建的对象。
4. 单独禁止指定schema的所有访问权限

-- 收回指定schema的访问权限
REVOKE ALL ON SCHEMA <exclude_schema> FROM <your_username>;
-- 收回指定schema下所有现有表的权限
REVOKE ALL ON ALL TABLES IN SCHEMA <exclude_schema> FROM <your_username>;
-- 配置默认权限，禁止后续在该schema下新建的表被该用户访问
ALTER DEFAULT PRIVILEGES IN SCHEMA <exclude_schema> REVOKE ALL ON TABLES FROM <your_username>;

注意事项 #

• 执行上述所有操作需要使用超级用户或者数据库OWNER角色执行
• 可通过\du <your_username>命令确认该用户没有从其他继承的角色中获取到禁止schema的访问权限
• 后续如果需要新增其他禁止访问的schema，仅需重复执行第4步的语句即可，无需修改全局配置

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