做 PostgreSQL 这些年我踩过的坑一半以上都跟权限有关。这玩意不像很多数据库那样“grant all privileges 一把梭”就能搞定PostgreSQL 的权限是严格按照“实例 → 数据库 → Schema → 对象”层层递进的角色继承、PUBLIC 默认权限、行级安全RLS这些东西叠加在一起常常让人在关键时刻收到一句冷冰冰的permission denied for relation xxx然后开始怀疑人生。这篇文章我打算把权限这块彻底揉碎了讲清楚从角色怎么建、权限怎么授到视图权限、Docker 文件权限、行级权限落地实践最后附一份可以直接对着排查的速查表。不管你是开发、运维还是 DBA只要正在被 PG 权限问题折磨这篇应该能帮你省下不少排查时间。1. 权限体系整体拆解先搞懂 PostgreSQL 的大件套1.1 角色Login、组角色与继承逻辑PostgreSQL 里其实没有用户和组的严格区分统一叫角色ROLE。一个角色既可以登录数据库也可以被其他角色继承两者靠属性区分LOGIN属性代表这个角色能连数据库没有LOGIN的角色通常当组用用来批量授权。我见过很多刚上手 PG 的同事习惯性地给每个应用账号单独授权结果表一多就乱了后面想统一加权限得逐个改。正确做法是用组角色做中间层先建一个只读组、一个读写组再把具体用户挂到组里。比如-- 创建只读组 CREATE ROLE app_readonly NOLOGIN; GRANT USAGE ON SCHEMA public TO app_readonly; GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_readonly; -- 创建读写组 CREATE ROLE app_writer NOLOGIN; GRANT app_readonly TO app_writer; -- 继承只读组的权限 GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_writer; -- 创建真正的登录用户并挂到读写组 CREATE ROLE alice LOGIN PASSWORD 强密码; GRANT app_writer TO alice;这里有个容易忽略的点角色继承INHERIT默认是开启的所以alice登录后自动拥有app_writer的所有权限顺带继承了app_readonly的 SELECT。这是组角色能层层嵌套的原因。但要留意继承只决定默认有效权限不决定能否切换身份。如果你希望某个成员能临时变成另一个角色比如切到管理员角色执行任务那得单独授权SET ROLEGRANT app_admin TO alice;然后alice会话里执行SET ROLE app_admin;才能切换。这招在普通用户临时提权场景里非常实用配合RESET ROLE切回来比直接给 SUPERUSER 安全得多。1.2 四级权限递进实例、库、Schema、对象PostgreSQL 的权限不是扁平的官方文档把权限分成四级日常报错绝大多数出在低层级权限缺失但表现却在高层级的操作上。实例级谁能连到 PostgreSQL 服务由pg_hba.conf控制属于连接准入权限谁能创建数据库、创建角色由CREATEDB、CREATEROLE、SUPERUSER属性控制。数据库级谁能连进某个数据库通过GRANT CONNECT ON DATABASE xxx TO role控制还有CREATE、TEMPORARY等权限。Schema 级谁能进入某个 Schema由USAGE控制谁能在这个 Schema 里创建对象由CREATE控制。注意普通用户对 schema 只有 USAGE 不一定能建表必须显式给 CREATE。对象级表、视图、函数、序列各自有 SELECT、INSERT、UPDATE、DELETE、EXECUTE 等权限。这四层是递进关系连不进数据库后面全白搭进得了库但USAGE没有 Schema照样找不到任何表有了 Schema 的使用权但没表权限查询依然报permission denied。所以排查权限问题时别一上来就盯着表看先顺着连接 → 库 → Schema → 表一路查下来绝大多数问题能快速定位。我自己的排查习惯是先用\dn看 Schema 权限再用\dp 表名看对象权限两头夹击很少落空。1.3 PUBLIC 这个隐形角色还有一个非常容易埋雷的东西叫PUBLIC。它不是某个具体角色而是所有角色的合集。PostgreSQL 默认安装后publicSchema 会赋予 PUBLIC 角色CREATE和USAGE权限这意味着只要你能连进数据库就能在这个 Schema 里建表。这对多租户系统或者多人共用的数据库来说很危险普通开发账号连进来可能不小心在public里留下一张测试表而其他人都能看到。我也遇到过因为public权限过大被扫描工具或者误操作搞出隐患的情况。安全基线做法是收紧它REVOKE CREATE ON SCHEMA public FROM PUBLIC; REVOKE ALL ON DATABASE your_db FROM PUBLIC;执行后再确认一下\dn public这样publicSchema 只有明确授权过的角色才能创建对象。别担心这只影响所有角色默认都有权限不会影响你后续对具体角色的授权。很多加固文档和合规检查都会要求这一步建议新库建完第一件事就把 PUBLIC 的权限收掉。2. 从建角色到授权一整套可抄的权限配置流程2.1 建角色的基本功与几个容易被忽略的参数建角色本身不难难的是参数选对。我常用的角色属性有这么几个整理成一张表方便对照属性作用典型使用场景LOGIN允许连接数据库应用账号、人用账号NOLOGIN禁止连接只当组角色只读组、读写组PASSWORD设置登录密码配合LOGIN使用CREATEDB允许创建数据库给少部分研发开CREATEROLE允许创建/管理角色DBA 或分组管理员SUPERUSER超级用户绕过所有权限检查仅限 DBA绝不乱给INHERIT是否继承所属组的权限默认开启一般不动REPLICATION允许创建流复制备库、同步工具用建角色的一个坏习惯是把所有属性堆给一个人。比如某次我接手一个新项目发现一个应用账号被赋予了 SUPERUSER原因仅仅是当时图省事觉得后面会用到。结果这个账号一旦泄露整个库的生死就交出去了。后来我养成的习惯是应用账号永远不给 SUPERUSER只给它需要的那部分权限真要干运维类操作单独开一个带 CREATEDB/CREATEROLE 的运维角色并做好密码管理。还要注意CREATE ROLE和CREATE USER几乎等价区别仅仅是后者默认带LOGIN。看别人脚本时不用惊讶两个都能用。2.2 GRANT 授权五类场景的写法授权语法核心就一句GRANT 权限 ON 对象 TO 角色但对象类型不同写法差异很大。我按高频场景整理成表格授权对象SQL 示例说明Schema 使用权GRANT USAGE ON SCHEMA sales TO app_readonly;没这个权限表连看都看不到Schema 建对象权限GRANT CREATE ON SCHEMA sales TO app_writer;建表、建视图都用它所有现存表GRANT SELECT ON ALL TABLES IN SCHEMA sales TO app_readonly;只覆盖已存在的表不覆盖未来表未来表预设权限ALTER DEFAULT PRIVILEGES IN SCHEMA sales GRANT SELECT ON TABLES TO app_readonly;这个是重点见 2.3 节序列/函数GRANT USAGE ON SEQUENCE seq_id TO app_writer; GRANT EXECUTE ON FUNCTION func() TO app_writer;很多业务写完才发现序列权限没给有一种非常常见的组合拳开发同学分两步走先给USAGE后给SELECT经常忘了 Schema 权限或序列权限结果应用在插入数据时报permission denied for sequence。所以完整授权一个读写账号至少要同时覆盖-- 假设 sales 库、sales schema 都建好了 GRANT CONNECT ON DATABASE sales_db TO app_writer; GRANT USAGE, CREATE ON SCHEMA sales TO app_writer; GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA sales TO app_writer; GRANT USAGE ON ALL SEQUENCES IN SCHEMA sales TO app_writer; ALTER DEFAULT PRIVILEGES IN SCHEMA sales GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_writer; ALTER DEFAULT PRIVILEGES IN SCHEMA sales GRANT USAGE ON SEQUENCES TO app_writer;这套写进团队的数据库初始化脚本里后续基本不会再为授权的事反复开会。2.3 未来表授权ALTER DEFAULT PRIVILEGES 不能忘这是我见过最容易踩的隐形坑。GRANT SELECT ON ALL TABLES IN SCHEMA sales TO app_readonly这条语句只能管住当下已经存在的表。第二天业务新建了一张订单表app_readonly去查询立刻报权限不足DBA 一脸懵我明明授过权了。原因就是 PG 的默认权限Default Privileges不会自动继承到未来对象上。解决办法是专门设置默认权限ALTER DEFAULT PRIVILEGES IN SCHEMA sales GRANT SELECT ON TABLES TO app_readonly; ALTER DEFAULT PRIVILEGES IN SCHEMA sales GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_writer; ALTER DEFAULT PRIVILEGES IN SCHEMA sales GRANT USAGE ON SEQUENCES TO app_writer;设置完成后再建表这些授权会自动生效。要注意ALTER DEFAULT PRIVILEGES有个容易被忽略的作用范围限制它默认只对发起这个命令的角色有效。如果建表的是migrator角色而默认权限是由 DBA 角色设置的那未来 migrator 建的表不一定覆盖到。稳妥做法是ALTER DEFAULT PRIVILEGES FOR ROLE migrator IN SCHEMA sales GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_writer;把FOR ROLE migrator加上明确指定migrator 未来建的表也要自动授权给 app_writer。多花几秒钟想清楚由谁建表就能少很多后续补授权的麻烦。2.4 REVOKE 回收权限时的常见误操作REVOKE是把权限收回但很多人对它的理解有偏差。REVOKE SELECT ON ALL TABLES IN SCHEMA sales FROM app_readonly同样只作用于当前存在的表未来表的默认权限如果已经设置了那新表依然会被自动授权——如果你想连默认权限一起收回得单独改ALTER DEFAULT PRIVILEGES。还有个更常见的误操作想收回某人的 Schema 权限只写了REVOKE ALL ON SCHEMA sales FROM user1结果发现用户还能正常操作。原因是ALL ON SCHEMA只覆盖 Schema 层级的权限USAGE、CREATE如果之前授权到了具体表上表级权限并不会因此消失。正确姿势是同时处理REVOKE ALL ON SCHEMA sales FROM user1; REVOKE ALL ON ALL TABLES IN SCHEMA sales FROM user1; REVOKE ALL ON ALL SEQUENCES IN SCHEMA sales FROM user1;另外不要忽视GRANT ... TO PUBLIC的存量影响。如果某个对象历史上有过GRANT SELECT ON some_table TO PUBLIC即使后续REVOKE ALL ON some_table FROM PUBLIC也可能只回收表级权限关联的 Schema 权限还得再检查一遍。权限回收这件事做完了务必用\dp复核一遍别指望一条命令搞定所有层级。3. 三大高频权限坑视图、容器、操作系统3.1 创建视图权限不足问题往往出在底层搜创建视图权限不足的人特别多我自己也被这个坑过。CREATE VIEW看起来只是一个建对象动作但 PostgreSQL 对视图的权限要求比想象中严格你要有目标 Schema 的CREATE权限还要有视图底层引用的所有对象的SELECT权限。因为视图本质是一条被“固化”的查询如果创建者对底层表没有读取权限PostgreSQL 会认为你连这个视图都没资格定义。典型报错长这样ERROR: permission denied for schema public或者ERROR: permission denied for table orders排查思路分两步走-- 1. 确认有没有 Schema 的 CREATE 权限 \dn public -- 2. 确认对底层表是否有 SELECT 权限 \dp orders如果缺 Schema 权限解决办法GRANT CREATE ON SCHEMA public TO app_writer;如果缺底层表权限需要把底层表的 SELECT 也给到当前建视图的角色GRANT SELECT ON orders TO app_writer;这里有个设计层面的建议如果你做的是宽表视图给报表看、明细表不给看的隔离场景别只依赖建视图的人有权限就完事——视图创建后其他用户能否查视图取决于有没有视图本身的授权而不是底层表的权限。所以可以收紧底层表授权只把视图授权给报表账号REVOKE SELECT ON orders FROM report_user; GRANT SELECT ON v_monthly_report TO report_user;这样报表账号能看视图产出却碰不到底层明细权限面小很多。不过要注意物化视图MATERIALIZED VIEW的刷新需要刷新者拥有底层表的 SELECT或者用SECURITY DEFINER函数封装刷新逻辑。3.2 Docker 容器里跑 PostgreSQL 的文件权限噩梦容器化的 PostgreSQL 权限问题跟数据库权限没关系但报错往往一字不差。最常见的场景用docker run或 Compose 挂载了一个主机目录作为数据目录容器启动时报chmod: changing permissions of /var/lib/postgresql/data: Operation not permitted或mkdir: cannot create directory /var/lib/postgresql/data/pgdata: Permission denied原因在于官方postgres镜像默认以postgres系统用户运行这个用户的主机 UID 不一定和宿主机目录属主匹配。我用一个稳妥的排查思路先确认容器内用户的 UID。官方镜像里 postgres 用户 UID 通常是 999。如果用的是 bind mount先让目录属主匹配sudo chown -R 999:999 /your/host/pgdata或者干脆用命名卷named volume让 Docker 替你管理目录所有权省去 UID 匹配问题。如果坚持用 Compose可以显式指定用户services: postgres: image: postgres:16 user: 999:999 volumes: - ./pgdata:/var/lib/postgresql/data宿主机上执行sudo chown -R 999:999 ./pgdata这样启动基本不会再碰文件系统权限问题。还有个隐蔽的坑是 WSL2 或某些虚拟化文件系统即使 chown 了跨文件系统的 metadata 权限也可能异常表现为容器起来后postgres服务一直 crash。遇到这种最省事的办法是直接在 Linux 原生环境用命名卷别和宿主机的 drvfs 目录硬刚。3.3 先分清是数据库权限还是系统权限很多运行 PostgreSQL 的机器是 Windows 或混合环境搜权限问题时会把注册表权限问题、应用程序-特定 权限设置并未向在应用程序容器 不可用 SID 中运行的地址、你需要来自 Administrators 的权限才能删除这类系统权限报错也带进来。这里我得提醒一句不是所有 permission denied 都是 PostgreSQL 的锅。遇到权限问题先按这个分层来定位操作系统层文件 ACL、注册表权限、SID 容器权限、目录删除权限——这些要靠 Windows 的icacls、资源管理器权限设置去处理和 PG 无关。连接层pg_hba.conf规则、密码认证失败、SSL 证书权限。数据库层角色属性、Grant 授权、Schema 权限、RLS 策略。我自己处理过一个案例Windows 服务器上 PG 数据目录所在盘符的Authenticated Users权限被误删导致备份脚本无法读取文件应用端报could not open file ... Permission denied。当时一开始我疯狂检查 PG 授权查半天才发现是文件系统 ACL 的问题用icacls补回权限后立刻正常。所以当你看到权限不足时先停下 SQL 层面的排查看一眼报错上下文到底来自 PG 日志、Windows 事件查看器还是应用异常往往能直接跳过半小时的无用功。4. 行级权限落地RLS 与视图隔离怎么选4.1 RLS 初体验建策略、开行安全行级权限Row-Level SecurityRLS是 PostgreSQL 在 9.5 版本引入的能力简单说就是让同一张表不同角色只能看到满足条件的行。这对多租户系统来说简直就是救命稻草——不需要为每个租户建一套表也不用写大量 WHERE 条件过滤数据库自身就能约束行可见性。开启方式分两步。第一步在表上开启行级安全CREATE TABLE orders ( id bigint PRIMARY KEY, region_id int, amount numeric(10,2), customer_name text ); ALTER TABLE orders ENABLE ROW LEVEL SECURITY;第二步创建策略。最简单的按区域隔离CREATE POLICY order_region_policy ON orders USING (region_id current_setting(app.region_id)::int);这条策略的意思是查询和修改时只有region_id等于当前app.region_id设置值的行才可见/可操作。没有设置app.region_id的会话则看不到任何行。这带来的安全效果非常直观即使你把整张表的 SELECT 授权给了某个角色RLS 仍然能限制它只看特定区域。如果表里已经有数据还想让某个管理员角色看全部需要额外授权CREATE POLICY order_admin_policy ON orders FOR ALL USING (true) TO app_admin;注意RLS 在 PostgreSQL 里默认对表属主和超级用户是不生效的除非用FORCE ROW LEVEL SECURITY这个特性日常够用但别把它当成对抗超级用户的工具。4.2 应用层Java 等如何配合 RLSRLS 落地到应用层关键是让每个请求都能带上自己的身份标识。常见做法是使用SET app.region_id xxx这个自定义 GUC 变量不需要额外配置就能使用。Java 应用配合连接池时要注意一个细节连接池中的连接是复用的如果不重置变量上一个请求的app.region_id可能被下一个请求读到造成越权数据泄漏。所以更安全的做法是在事务里用SET LOCALBEGIN; SET LOCAL app.region_id 1001; SELECT * FROM orders WHERE customer_name 张三; COMMIT;SET LOCAL在事务结束自动失效搭配连接池高并发场景非常合适。Java 代码里可以在同一个事务中执行try (Connection conn dataSource.getConnection()) { conn.setAutoCommit(false); try (Statement st conn.createStatement()) { st.execute(SET LOCAL app.region_id 1001); // 正常查询逻辑 try (ResultSet rs st.executeQuery(SELECT * FROM orders)) { // 处理结果 } } conn.commit(); }思路就是把设定身份、查询、事务提交绑在一个生命周期里避免变量串用。如果不想每个 DAO 方法都写一遍可以用 AOP 或 MyBatis 拦截器在事务开启前统一执行SET LOCAL业务代码只需要按平时的逻辑写 SQL。4.3 RLS 与视图隔离的取舍RLS 很酷但不是所有行级隔离场景都非它不可。我总结过一张对比表方案优点缺点适用场景视图隔离语义清晰、性能可控、老版本可用每类角色要建不同视图对象多租户数少、权限模型简单RLS一张表一套逻辑策略集中管理性能有额外开销、调试复杂租户数多、多租户共用一张表两者结合视图 RLS 形成纵深防御配置复杂大团队、高安全要求如果租户数量很少比如就三五个内部部门用按部门建视图完全够还省去了SET app.xxx这种额外代码。但如果是一个平台级 SaaS所有客户的数据都塞在同一张业务表里那 RLS 几乎是必须的否则 WHERE 条件漏写一个部门过滤就是一场安全事故。技术选型时还要考虑性能。RLS 本质上会在 SQL 解析时把策略条件合并进原查询对复杂查询有一定开销。实测经验是简单的等值策略比如region_id current_setting(...)在索引命中情况下影响不大但如果策略里写了LIKE、函数调用成本会明显上升。部署前最好用真实数据和真实查询计划做一次压测别只听概念好就上。5. 权限排查与速查看完能少踩一半的坑5.1 排查权限的三板斧遇到权限问题我习惯按以下顺序排查基本能覆盖九成场景第一板斧看角色。\du查看所有角色及其属性\du username查看具体角色。确认账号是否 LOGIN、是否继承、属于哪个组。第二板斧看授权。\dp或\dp 表名查看对象权限\dn查看 Schema 权限。注意区分显示为空和没授权\dp里如果某个角色没有出现在权限列表里基本就是没授权。第三板斧看当前设置。查询SELECT current_user, session_user, current_setting(search_path);确认你当前到底是以哪个角色在干活。我就经历过用管理员改了授权结果应用连接用的是另一个角色这种低级问题。如果还需要更细致的信息可以查系统视图SELECT grantee, privilege_type, table_name FROM information_schema.role_table_grants WHERE table_name orders ORDER BY grantee;有时候也可以用\set ECHO_HIDDEN打开 psql 隐藏查询观察权限检查的内部 SQL不过这适合进阶玩家新手还是把\du和\dp吃透就好。5.2 高频报错速查表我把这些年见过的高频权限报错整理成一张速查表直接对照报错信息大概率原因解决方案permission denied for relation xxx对象级权限缺失GRANT SELECT/INSERT/UPDATE/DELETE ON xxx TO role;permission denied for schema xxxSchema 的 USAGE 权限缺失GRANT USAGE ON SCHEMA xxx TO role;permission denied to create xxxSchema 的 CREATE 权限缺失GRANT CREATE ON SCHEMA xxx TO role;permission denied for sequence xxx没用序列权限GRANT USAGE ON SEQUENCE xxx TO role;must be owner of table xxx不是对象属主无法执行 ALTER/DROP用属主账号操作或ALTER TABLE xxx OWNER TO role;password authentication failed密码错误或pg_hba.conf认证方式不对核对密码、pg_hba.conf中md5/scram-sha-256配置no pg_hba.conf entry for host ...连接来源未被允许在pg_hba.conf增加对应网段规则并 reloadpermission denied for view xxx视图权限未授权GRANT SELECT ON view_name TO role;could not open file ... Permission denied文件系统/容器权限问题检查宿主机目录属主、ACL、容器 UID这张表我每次处理异地环境问题都会对照一遍比在搜索引擎里翻半天高效得多。特别是permission denied for relation这种报错先查 Schema 权限再查表权限不要把时间浪费在无意义的参数调整上。5.3 我的实操复盘每次改权前后的习惯动作最后分享几个我自己的实操习惯算是一路踩坑换来的教训。第一改权限前先做快照。用pg_dump只导权限定义也行或者直接记录关键角色和授权语句万一改坏了能快速回滚。权限变更虽然没有事务回滚那么方便但保存一份\dp输出和角色定义已经能应付大多数事故。第二授权跟着最小必要原则走。宁可多建几个组角色也别图省事给大权限。比如报表账号就只给 SELECT别顺手给 INSERT给到 Schema 的 CREATE 权限之前先想想这个账号未来是否真的要建表。第三每次改完 Re-load 配置。如果动了pg_hba.conf记得执行SELECT pg_reload_conf();不用重启数据库如果改了角色或授权在另一个新会话里立刻验证一遍别在已打开的 psql 里测试授权因为连接缓存和管理员身份可能影响判断。第四RLS 上线前先跑一遍模拟攻击。用两个不同app.region_id的会话分别尝试查询对方数据确认结果里没有泄漏行。这条我吃过亏上线后才发现某条策略用了OR条件把管理员也过滤了调整后完整测试一遍才放心。PostgreSQL 的权限体系在主流数据库里算是设计得比较严谨的但也正因为严谨上手门槛和排查成本都不低。希望这篇梳理能帮你少走点弯路。如果你按文中方法解决了实际问题或者踩到了我没提到的坑欢迎来交流下一位倒霉蛋可能就靠你的经验少熬一个通宵了。