PostgreSQL 14刚发布那会儿我最关注的不是性能提升了多少也不是什么新索引类型而是两个不起眼的预定义角色pg_read_all_data和pg_write_all_data。说实话做数据库运维这么多年被“只读账号怎么建”这个问题反复折腾的次数比被业务方催促的次数还多。这个新特性看着简单实际上把一类长期存在的权限管理痛点给根治了。这篇文章我就围绕这两个角色把背后的权限模型、实操姿势、以及我实际踩过的坑一次讲清楚。1. 为什么这个特性值得专门写一篇1.1 先回忆一下PG14之前建只读账号有多痛苦在PostgreSQL 14之前如果你想给一个数据分析师开个只读账号标准流程大概是这样的先创建登录角色然后进到目标数据库对每一张表执行GRANT SELECT。一个生产库动辄几百张表、几十个schema手工敲命令不现实所以常见的做法是拼一段动态SQL把所有的表遍历出来然后批量授权。SELECT GRANT SELECT ON TABLE || schemaname || . || tablename || TO readonly_user; FROM pg_tables WHERE schemaname NOT IN (pg_catalog, information_schema);然后把这堆生成的语句复制出来执行一遍。这还只是开始如果某天业务方新建了一张表你又得重新跑一遍这个脚本。没跑那新表对于只读账号来说就是不可见的查询直接报permission denied。如果用了分区表父表子表还得分别处理。如果再碰上视图、物化视图、序列那授权的对象类型就更多了漏掉一个是常有的事。可能有人会说可以用ALTER DEFAULT PRIVILEGES来设置默认权限让之后新建的表自动授权给某个角色。这确实是个办法但它的生效范围很讲究它只对“执行该语句的角色的当前数据库、当前schema中新建的对象”生效而且只对“由该角色自己创建的对象”生效。换句话说如果表是别的开发账号建的或者建在别的schema里默认权限根本管不到。数据库里的对象创建者五花八门指望这个去兜底很容易出纰漏。1.2 PG14给出的答案把“能不能读”变成一个角色属性pg_read_all_data和pg_write_all_data这两个预定义角色的核心思路是让权限检查从“对象有什么权限”变成“角色是什么身份”。翻译成人话就是以前我们关心的是“这张表授权给了谁”现在只要你是pg_read_all_data的成员你就能读取数据库里所有普通对象上的数据未来新建的表也自动能读。这一点非常关键。它从根本上解决了“新表漏授权”的问题因为权限检查发生在查询执行的那一刻而不是授权操作发生的那一刻。只要角色是pg_read_all_data的成员不管对象是昨天建的还是今天建的查询时都会被视为“拥有对该对象的SELECT权限”。它解决的第二个痛点是“跨schema授权”。以前如果你的表分散在public、ods、dw、app这几个schema下你得逐个schema去授权。而现在pg_read_all_data是数据库集群级别的角色定义成员身份一成立所有schema下的普通数据表都直接可读不需要再按schema区分。这在多schema架构中省掉的运维工作量是实打实的。2. 核心机制与原理拆解2.1 先理解PostgreSQL的权限体系要真正用好这两个角色得先搞清楚PostgreSQL底层的权限模型。PostgreSQL的权限控制是分层级的大致是这样的链条连接权限CONNECT到数据库进入数据库后需要schema上的USAGE权限才能访问里面的对象然后才能谈对象上的SELECT、INSERT、UPDATE、DELETE等权限。在这个基础上PostgreSQL还有一个“角色成员关系”的机制你可以把某个角色授予另一个角色被授予者就继承了该角色的权限。比如GRANT pg_read_all_data TO my_user实际上是把pg_read_all_data这个角色作为成员角色添加到了my_user的成员关系列表中my_user自动继承pg_read_all_data对应的权限。这里有个容易被忽略的点CREATE ROLE默认是INHERIT属性所以在绝大多数情况下授权之后用户直接就获得了能力不需要每次执行SET ROLE。如果你恰好创建角色时用了NOINHERIT那就需要手动SET ROLE pg_read_all_data才能生效这点后面讲坑的时候会再提。2.2 pg_read_all_data的边界到底在哪里pg_read_all_data从语义上看是“读取所有数据”但它的权限边界有几个非常具体的限制很多人理解得太宽。第一个限制是它不包括系统目录。也就是说你虽然能读业务表但pg_catalog下面的系统表比如pg_authid这种存密码哈希的敏感表是读不了的。这个设计很有必要否则只读角色反而成了敏感信息泄露的通道。第二个限制是它不绕过行级安全策略RLS。如果一张表启用了ROW LEVEL SECURITY并且策略里限制了行可见性那么pg_read_all_data的成员同样只能看到策略允许的行。换句话说这个角色绕过的是“表级权限检查”但不会把RLS给你关掉。第三个限制是它不绕过列级权限。如果某些列被单独做了REVOKE或GRANT授权那么列级权限仍然起作用。生产场景中列级授权用得不多但遇到时要注意。第四个限制是它只管对象访问权限不管连接权限和schema权限。用户依然需要数据库的CONNECT权限通常也需要schema上的USAGE权限。这是最容易踩的坑——角色授了连库都连不上或者表看得到但打开就报错原因多半就在这里。2.3 pg_write_all_data的语义与隐含陷阱pg_write_all_data对应的是“写入所有数据”按官方定义它相当于对所有普通表、视图、序列拥有INSERT、UPDATE、DELETE的权限。这里首先要强调一个很多人用错的地方pg_write_all_data不包含读取权限。它的定位是纯写入如果你给一个ETL账号只授了这个角色那它能往表里INSERT但是拿不到SELECT很多写入逻辑需要先查后写就会报错。实际使用中读写分离账号通常会把pg_read_all_data和pg_write_all_data一起授予形成“真正的读写账号”。其次pg_write_all_data对视图来说权限表达的是“可以在视图上INSERT/UPDATE/DELETE”但这些操作最终能不能成功还取决于视图本身是不是可更新视图、以及视图底层基表的权限。它并不会魔法般地让一个不可更新视图变成可更新。第三这个角色同样不覆盖系统表和系统视图同样受RLS约束同样不解决schema的USAGE问题。这几点和pg_read_all_data完全对称就不再重复了。2.4 和其他预定义角色的对比PostgreSQL的预定义角色不是14才有的比如pg_monitor、pg_signal_backend、pg_execute_server_program这些早就存在但14新增的这两个角色填补了一个巨大的空白以前没有一个预定义角色能表达“普通业务对象上的全局只读”。为了更好地理解它俩的定位我用表格对比一下几个高频角色角色名核心能力常见场景是否覆盖系统表pg_read_all_data读取所有普通表、视图、序列数据分析、只读账号、备份账号否pg_write_all_data写入所有普通表、视图、序列数据写入、ETL落地否pg_monitor读取系统视图、执行监控相关函数监控系统、巡检脚本是pg_signal_backend允许取消/终止其他会话运维工具、会话管理不适用注意pg_monitor和pg_read_all_data是不同的东西监控角色读的是系统视图和统计信息业务只读角色读的是业务数据两者不能互相替代。如果你要做一个“既能看业务数据又能看监控数据”的分析账号就得都授予。3. 实操过程与典型场景复现3.1 环境准备与版本确认准备工作很简单但在动手之前一定要先确认版本。这两个角色是PostgreSQL 14引入的13及以下的版本根本没有这个对象跑GRANT语句会直接报role pg_read_all_data does not exist。SELECT current_setting(server_version);拿到类似14.x的版本号就对了。另外最好确认一下当前连接的数据库因为后面的授权涉及CONNECT权限得在目标数据库的上下文里操作或者用\c切换。3.2 创建只读账号的标准三步假设我现在要给业务方的分析同学开一个只读账号目标是能读mydb库下所有业务schema的表。核心操作就三步CREATE ROLE readonly_user LOGIN PASSWORD 此处写一个强密码; GRANT pg_read_all_data TO readonly_user; GRANT CONNECT ON DATABASE mydb TO readonly_user;如果这个库的schema权限被收紧过比如REVOKE ALL ON SCHEMA public FROM PUBLIC这种加固操作那还需要把对应的schema加进来GRANT USAGE ON SCHEMA public, ods, dw TO readonly_user;这一步的原理是即使pg_read_all_data让用户“拥有所有对象的SELECT权限”PostgreSQL的权限检查链条里访问对象前要先过schema的USAGE这道关。pg_read_all_data不管schema这层所以要么schema对PUBLIC开放了USAGE默认情况要么显式授权。提示授权后可以让对方先用\dn看一下schema列表再执行\dt看表能正常看到表结构再去查数据这样排查起来更快。3.3 验证一下权限是否真正生效授权完不能光看命令执行成功一定要实际验证。我习惯的做法是以目标用户的身份执行查询分别测试成功的路径和失败的路径SET ROLE readonly_user; SELECT count(*) FROM public.orders;这里如果建表时对PUBLIC没有任何特殊授权普通用户默认是查不了orders表的但作为pg_read_all_data的成员这条查询会直接成功。反之如果你执行SELECT rolname FROM pg_authid;会正常返回结果或报错准确说是直接报permission denied因为系统表不受这个角色保护。这个对比测试能帮你直观理解角色的边界。3.4 创建写账号含读写账号的正确姿势创建一个纯写账号核心命令是CREATE ROLE write_user LOGIN PASSWORD 此处写一个强密码; GRANT pg_write_all_data TO write_user; GRANT CONNECT ON DATABASE mydb TO write_user; GRANT USAGE ON SCHEMA public, ods, dw TO write_user;但是就像前面说的纯写账号在实际业务中很少见。通常一个数据同步账号既要读源表又要写目标表最稳妥的做法是同时授予两个角色CREATE ROLE etl_user LOGIN PASSWORD 此处写一个强密码; GRANT pg_read_all_data, pg_write_all_data TO etl_user; GRANT CONNECT ON DATABASE mydb TO etl_user; GRANT USAGE ON SCHEMA public, ods, dw TO etl_user;写操作验证也比较直接SET ROLE etl_user; INSERT INTO public.audit_log(created_at, msg) VALUES (now(), test);如果插入成功说明写入权限正常。然后执行ROLLBACK回滚掉测试数据或者顺手清理掉。3.5 序列、视图和物化视图的补充说明业务中除了表还总会遇到序列、视图和物化视图。pg_read_all_data对序列的作用是允许执行nextval吗不官方定义中它对应的是序列的SELECT权限也就是说可以执行lastval或者读取序列当前值这类操作。但要注意nextval这个动作严格来说不算读而是“推进序列”它属于UPDATE类操作所以要让某个角色能执行nextval依赖的是pg_write_all_data而不是读取角色。物化视图的读取和普通表一致pg_read_all_data可以直接覆盖。但物化视图的刷新REFRESH MATERIALIZED VIEW需要的是写权限所以只读账号是不能刷新物化视图的这一点在给报表系统授权时经常被忽略。3.6 安全加固场景下的额外配置如果你的环境做得比较规范通常会把publicschema上的PUBLIC权限收掉也会把数据库的CONNECT权限按需下放。这种环境里哪怕你授了pg_read_all_data新用户依然可能连不上库或者看不到表。我遇到过不止一次业务方反馈“明明授权了Toad/Navicat里却一片空白”最后查出来都是schema USAGE缺失。一个完整的加固环境授权脚本长这样CREATE ROLE analyst LOGIN PASSWORD 此处写一个强密码; GRANT pg_read_all_data TO analyst; GRANT CONNECT ON DATABASE mydb TO analyst; GRANT USAGE ON SCHEMA public TO analyst; -- 如果使用了多个schema照葫芦画瓢继续加 GRANT USAGE ON SCHEMA sales, marketing TO analyst;还有一个经验尽量把角色授予做成一个可重复执行的脚本放到运维平台的自动化任务里。因为数据库会不断新增schema新schema不会自动带着USAGE授权定期把新schema纳入授权范围是个好习惯。4. 常见问题与排查技巧实录4.1 问题速查表我整理了实际运维中最高频的几个问题以及对应的解决方向现象可能原因处理方式数据库连接失败缺少CONNECT权限GRANT CONNECT ON DATABASE表列表为空或看不到表缺少schema USAGE权限GRANT USAGE ON SCHEMA能看表结构但查询无权限角色名拼错或版本低于14检查版本、检查GRANT语句查询结果行数比预期少表启用了RLS检查行级安全策略能SELECT但不能INSERT只授了pg_read_all_data补充pg_write_all_data执行nextval报权限不足序列需要UPDATE权限依赖pg_write_all_data子事务里SET ROLE后权限丢失NOINHERIT属性导致SET ROLE对应的角色4.2 典型问题明明授了pg_read_all_data怎么还查不了某个schema的表这个问题的根因几乎都是schema的USAGE权限缺失。有一个真实的例子某公司的数据仓库分了ods、dwd、ads三层schema运维给分析师开了pg_read_all_data但ods这个schema的USAGE权限是单独管理的分析师连上库以后能看到public下的表切到ods却什么都查不到。排查路径是这样的先用管理员账号查看schema权限SELECT nspname, nspowner, array_to_string(nspacl, ,) AS acl FROM pg_namespace WHERE nspname IN (public, ods, dwd, ads);然后直接补一条授权GRANT USAGE ON SCHEMA ods TO analyst;补完再让业务方刷新重试问题立刻消失。所以排查这类问题时不要一上来怀疑角色有问题先按“CONNECT - USAGE - 对象权限”这个顺序逐层检查基本几分钟就能定位。4.3 典型问题pg_write_all_data角色为何“只能写不能读”有一个真实案例是某团队给数据接入账号只授了pg_write_all_data结果同步任务老是失败。看日志才发现同步组件在写入之前要先查一下目标表有没有重复记录结果SELECT直接permission denied。这不是异常而是这个角色设计的边界。官方对pg_write_all_data的定位就是纯写入如果你需要先读后写那就把pg_read_all_data也一起授了。我在给别人做权限模板时默认都会把这两个角色捆绑成一组命名为“读写模板”避免后续反复踩这个坑。4.4 典型问题行级安全策略导致“只读账号看不到数据”这个问题比较隐蔽。pg_read_all_data不会绕过RLS所以如果一张业务表启用了行级安全并设置了tenant隔离策略那么即使角色拥有全局读权限实际返回的行也依然受策略过滤。举一个具体的排查例子某租户系统给运维开了只读账号用于排查线上数据但只读账号查询orders表时返回0行。一开始大家都以为是权限配错了最后发现orders表上启用了FORCE ROW LEVEL SECURITY并且只对应用角色开放了行访问策略。解决方式需要业务方配合在RLS策略里增加允许该只读角色访问的行规则或者临时创建专门的策略。遇到这种问题不要急着改角色权限先执行SELECT relname, relrowsecurity, relforcerowsecurity FROM pg_class WHERE relname orders;确认是否启用了RLS再决定怎么处理。这个排查顺序能帮你少走弯路。4.5 典型问题新用户看不到任何表结构客户端工具比如DBeaver、Navicat连上数据库后一片空白这类“看着像没权限其实权限有”的情况大概率是search_path和schema USAGE的组合问题。客户端默认找publicschema如果业务表都在salesschema里而sales的USAGE权限没有授予自然什么都看不到。还有一个我踩过的细节有些客户端工具在连接时自动执行了LISTEN或者查询某些扩展相关的表如果扩展装在独立schema里也需要给USAGE权限。所以在严格打权限的生产环境建议提前梳理所有schema并形成一个统一的授权清单。5. 经验总结与一些扩展建议5.1 个人实测后的几条体会把这两个角色用进生产环境之后最大的感受就是授权脚本瘦身了。以前上百行的动态授权SQL现在变成几条GRANT成员语句而且不再需要担心“新表漏授权”这类定时炸弹。不过有一条经验值得单独说pg_read_all_data和pg_write_all_data都是“集群级”的角色但授权动作是在具体数据库里执行的。也就是说如果你在db_a库里给用户授了这两个角色用户天然就可以读db_b里的所有业务数据前提是用户对db_b有CONNECT权限。这个权限范围比很多人预想的更大实施前一定要和业务方确认好信任边界。如果不想让某个数据库被这类全局角色覆盖能做的有限因为这个角色的设计取向就是“全库可读”。所以我的建议是把这类授权放进变更审批流程而不是让每个开发随手就能执行。5.2 什么场景够用什么场景还不够如果是常规的分析查询、报表导出、数据备份、数据接入这两个角色完全够用。特别是备份场景以前用普通用户备份需要挨个表授权现在只要给备份账号授pg_read_all_datapg_dump就能完整导出所有业务表省心太多。但如果你的需求更细比如只允许某用户读某几个特定schema的表或者不允许访问某些敏感列那这两个角色就不合适了。这种场景还是老老实实走传统的表级授权或者引入更细粒度的权限管理体系。另外如果要求“只读账号不能看某些字段”那么列级权限仍是唯一选择这类对象级控制不是pg_read_all_data能替代的。5.3 后面还可以怎么扩展使用除了直接给登录用户授权也可以把这两个角色授给自定义的组角色再让具体的人加入组角色。这样管理上是两层结构组角色定义“能干什么”成员关系定义“谁可以干”后续人员变动只需要调整组成员身份不用重做权限。跨部门场景下这种模式会非常清晰。另外如果你在操作逻辑复制比如用pgoutput插件做增量同步发布端需要读取数据的账号直接授pg_read_all_data会很方便订阅端需要写入的账号授pg_write_all_data也很顺手。这种组合算是14版本里最实惠的权限增强之一。最后再分享一个小细节这两个角色虽然叫“只读”和“只写”但它们不影响DDL类的权限。也就是说用户不能建表、不能删表、不能改表结构这层能力依然需要一个有CREATE权限的角色来提供。权限设计时把DML和DDL分开思考整体模型会更健全。