PostgreSQL多库多schema权限配置及内置角色不生效问题咨询

1. 核心认知验证 #

你的ALTER DEFAULT执行规则的认知是正确的。默认情况下ALTER DEFAULT PRIVILEGES仅对执行该语句的用户后续创建的对象生效，如果要覆盖所有用户创建的新对象，可直接加上FOR ALL ROLES子句，不需要每个建表用户单独执行语句。

你认为角色无法适配跨库不同权限的认知是错误的，PostgreSQL权限为数据库级隔离，同一个角色在不同库授予权限完全不冲突，刚好可以适配你的业务场景。

2. 业务场景配置示例 #

2.1 全局基础角色创建（在postgres库执行1次即可） #

-- 读写基础角色，禁止直接登录
CREATE ROLE base_rw NOLOGIN NOINHERIT;
-- 只读基础角色，禁止直接登录
CREATE ROLE base_ro NOLOGIN NOINHERIT;

2.2 单库权限初始化（以exchange库为例，其他库逻辑一致） #

登录对应数据库后执行以下语句，覆盖现有对象权限+新对象默认权限：

-- 1. 授予所有schema使用权限给基础角色
GRANT USAGE ON ALL SCHEMAS IN DATABASE CURRENT TO base_ro, base_rw;
-- 新增schema自动授予权限
ALTER DEFAULT PRIVILEGES GRANT USAGE ON SCHEMAS TO base_ro, base_rw;

-- 2. 只读角色权限配置
-- 现有表/视图只读权限
GRANT SELECT ON ALL TABLES IN SCHEMA public, <你的自定义schema列表> TO base_ro;
-- 现有序列使用权限
GRANT SELECT, USAGE ON ALL SEQUENCES IN SCHEMA public, <你的自定义schema列表> TO base_ro;
-- 新对象默认权限，覆盖所有建表用户
ALTER DEFAULT PRIVILEGES FOR ALL ROLES GRANT SELECT ON TABLES TO base_ro;
ALTER DEFAULT PRIVILEGES FOR ALL ROLES GRANT SELECT, USAGE ON SEQUENCES TO base_ro;

-- 3. 读写角色权限配置
-- 现有表读写权限
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public, <你的自定义schema列表> TO base_rw;
-- 序列仅授予使用权限，禁止修改
GRANT SELECT, USAGE ON ALL SEQUENCES IN SCHEMA public, <你的自定义schema列表> TO base_rw;
-- 现有函数执行权限
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public, <你的自定义schema列表> TO base_rw;
-- 授予schema创建权限，支持用户创建自定义函数
GRANT CREATE ON SCHEMA public, <你的自定义schema列表> TO base_rw;
-- 新对象默认权限，覆盖所有建表用户
ALTER DEFAULT PRIVILEGES FOR ALL ROLES GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO base_rw;
ALTER DEFAULT PRIVILEGES FOR ALL ROLES GRANT SELECT, USAGE ON SEQUENCES TO base_rw;
ALTER DEFAULT PRIVILEGES FOR ALL ROLES GRANT EXECUTE ON FUNCTIONS TO base_rw;

2.3 跨库用户权限分配 #

完美适配用户跨库不同权限的需求，示例如下：

-- 给用户sunny配置：exchange库读写、analysis库只读、accounts库全权限
GRANT CONNECT ON DATABASE exchange, analysis, accounts TO sunny;
\c exchange
GRANT base_rw TO sunny;
\c analysis
GRANT base_ro TO sunny;
\c accounts
-- 直接授予库所有者权限即可
GRANT pg_database_owner TO sunny;

-- 给用户viewer配置：仅exchange库只读
GRANT CONNECT ON DATABASE exchange TO viewer;
\c exchange
GRANT base_ro TO viewer;

3. 专项问题解答 #

3.1 序列权限处理 #

正常使用序列（调用nextval、currval）仅需要USAGE和SELECT权限，不需要授予UPDATE、ALTER权限，上述配置已经覆盖该需求，RW用户无法修改序列起始值、步长等属性，仅能正常使用序列生成自增ID。

3.2 函数权限处理 #

上述配置已经给RW角色授予了schema的CREATE权限，RW用户可以自行创建、更新、删除自己的函数，默认情况下用户创建的函数自己拥有所有权限，不需要额外授权。

4. pg_write_all_data不生效问题排查 #

你遇到的内置角色失效问题，最常见的原因是pg_write_all_data、pg_read_all_data是PostgreSQL 14版本才新增的内置角色，如果你使用的是14以下的版本，授权语句不会报错但实际没有任何权限效果。
如果你的版本为14及以上，可排查以下两个点：

• 检查capture用户是否开启了角色继承：执行\du capture查看属性，如果有NOINHERIT标记，需要重新授权：GRANT pg_write_all_data TO capture WITH INHERIT;
• 检查instruments表所在schema的权限：执行\dn+ <表所在schema名>，确认capture用户有该schema的USAGE权限，如果没有需要单独授予。

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