PostgreSQL权限排查：角色、Schema、行级安全与权限审计SQL

上个月帮一个项目组排查问题同一套PostgreSQL实例测试环境的A账号能创建视图生产环境的B账号一执行就报“permission denied for schema”更诡异的是新创建的业务账号居然能直读好几个核心业务表。排查到最后发现大家对PostgreSQL权限的理解几乎都停留在“用户、密码能连上就行”这个层面。这篇文章的目的就是把我对PostgreSQL权限的一次系统梳理写成文字覆盖从安装环境、连接认证、对象权限到行级权限的完整链路也包含我实际踩过的坑和常用巡检SQL。适合接手PG的DBA、经常被权限报错打断的后端开发以及想给业务系统设计权限模型的技术负责人。1. 先厘清权限链路的四层连接层、数据库、模式、对象1.1 角色与登录PostgreSQL没有单独的“用户表”很多人刚接触PostgreSQL时会习惯性地找“用户管理”界面看到CREATE ROLE和CREATE USER两个命令就懵了。其实PG里面没有传统意义上的“用户表”所有能登录、能操作对象的实体都叫角色role角色既可以是一个登录账号也可以是一个权限分组。CREATE USER app_user WITH LOGIN PASSWORD App2024; CREATE ROLE readonly_group;这两条命令的区别只有一个CREATE USER默认带上LOGIN属性CREATE ROLE默认不带。一个角色如果需要登录必须显式拥有LOGIN属性或者通过角色成员关系借用登录能力当然实际登录还是得靠具体账号。这个设计我一开始觉得绕后来发现它非常灵活你可以把一堆权限授予一个分组角色再把具体登录账号加入这个分组而不是在每个账号上重复授权。在pod里管理权限时我一般会把“人”和“组”分开人就是能登录的LOGIN角色组就是不带LOGIN的纯权限容器。后面讲角色继承时还会用到这个思路。1.2 权限的作用域数据库、Schema、对象三种授权PostgreSQL的权限不是一把钥匙全打开。它至少分四层连接层能否连上某个数据库由数据库上的CONNECT权限控制默认所有角色都有PUBLIC CONNECT权限。数据库层能否在数据库里创建schema由数据库上的CREATE权限控制。Schema层能否进入schema、能否在schema里创建对象由SCHEMA上的USAGE与CREATE权限控制。对象层表、视图、序列、函数上的SELECT、INSERT、UPDATE、DELETE、TRUNCATE、REFERENCES、TRIGGER、EXECUTE等具体权限。很多“权限不足”报错其实不是表没授权而是schema的USAGE权限没给。打个比方你知道公司三楼有会议室表但门禁卡没刷开三楼走廊schema USAGE你根本走不到会议室门口。USAGE就是让你“能进到这个schema空间里”。下面这张表是我自己整理的对象级权限说明用来给团队培训特别直观对象类型权限项说明表SELECT, INSERT, UPDATE, DELETE常规CRUD表TRUNCATE, REFERENCES, TRIGGER高危操作建议收口视图SELECT视图只能读序列USAGE, SELECT, UPDATEUSAGE是nextval必需的函数EXECUTE调用函数必需SchemaUSAGE, CREATEUSAGE进得去CREATE建得了数据库CONNECT, CREATE, TEMPTEMP是创建临时表此外还有列级权限比如GRANT SELECT(col1, col2) ON TABLE t TO role这个后文讲行列权限时会用到。1.3 底层aclitemGRANT命令的本质是往对象上贴权限标签GRANT SELECT ON TABLE orders TO finance_role;这条命令看起来简单底层做的事情是把一个叫“aclitem”的权限项写入pg_class系统表里对应表的relacl列。权限查询无非就是把relacl这个数组解析成人能读懂的行。我见过不少同事遇到“有权限却还报错”时第一反应是反复GRANT。其实需要先确认授权到底落在哪个对象上了。比如你给角色授了表的SELECT但角色在schema上没有USAGE依然访问不了。可以先执行SELECT relname, relacl FROM pg_class WHERE relname orders;再用一个快速解析查询SELECT grantee, privilege_type FROM information_schema.role_table_grants WHERE table_name orders;如果grantee里确实有你的角色那问题就多半出在schema层而不是表层。这个排查思路在后面的“创建视图权限不足”章节还会详细展开。2. 环境层的权限安装和运行期常见的OS文件权限坑2.1 Windows安装目录与数据目录的ACL问题很多人第一次装PostgreSQL都遇到过服务启动失败日志里报“could not open directory ... Permission denied”。这类问题几乎都出在Windows的文件系统ACL上。PostgreSQL在Windows上安装后服务默认以独立账户运行不是以你当前登录的Administrator运行。如果你把data目录手动挪到D盘却没给服务账户授予相应权限启动时自然打不开目录。遇到这种问题不要一股脑给Everyone加“完全控制”权限正确做法是只给运行服务的那几个必要账户加读取和写入权限。具体步骤是打开服务管理器找到postgresql服务查看“登录”选项卡里用的是哪个账户。右键数据目录进入安全设置添加该账户。勾选“读取和执行”“列出文件夹内容”“读取”“写入”这几个基本权限不需要给“完全控制”。重启服务验证。这里有个很多人不知道的点PostgreSQL对data目录的权限校验非常严格如果你给了太宽泛的权限反而可能让它拒绝启动。在Linux上要求data目录owner必须是postgres用户权限通常为700。在Windows上同理所有者与服务账户不一致也可能导致校验失败。2.2 删除数据目录时的“你需要来自Administrators的权限才能删除”热搜词里有个很常见的Windows现象“你需要来自Administrators的权限才能删除”这跟数据库有关但又不是数据库报错而是文件系统ACL在作怪。我卸载PostgreSQL或者清理旧数据目录时经常踩这个坑明明我是管理员却删不掉data目录。原因往往是该目录的文件所有者是创建它的服务账户或者目录里某些文件被PG服务进程占用。Windows会弹出需要Administrators权限的提示实际上是告诉你“你没有这个目录的属主权限”。这时候不要硬抢所有者先确认服务是否已停止。如果服务停了还删不掉先通过文件属性把所有者改成当前管理员账户再删除。如果目录里有文件正在被某个进程占用即使改了所有者也会提示“操作无法完成”需要先找到占用进程或者重启一次机器再删。顺带一提“TrustedInstaller权限怎么转给Administrator”也是同一个知识点。系统关键目录的所有者是TrustedInstaller普通管理员没有完全控制权。数据库目录虽然一般不是这种情况但如果你把PG数据目录放在Program Files这类受保护位置下就很容易遇到类似规则。2.3 Linux下数据目录权限与服务启动在Linux服务器上权限问题更直接。很多人在CentOS或Ubuntu上用apt或yum安装PG后会顺手把数据目录chown成自己的用户结果服务起不来。PostgreSQL的安全设计里有一条硬性校验data目录的所有者和属主权限必须匹配。如果目录所有者是root或者权限变成755initdb/service启动就会提示“data directory has invalid permissions”。解决方式很简单保持目录所有者是postgres用户权限700即可。日志和数据文件目录都不应该交给应用账号去写入应用账号只负责通过数据库协议访问数据不直接碰数据文件。这个“数据库账号与操作系统账号分离”的原则同样适用于Windows环境。2.4 pg_hba.conf最容易忽略的入口闸门权限体系里还有一个特别容易忽略的入口层pg_hba.conf。这个文件控制“谁能从哪个IP、用什么方式连上数据库”。即使数据库内部授权全部正确如果pg_hba.conf里拒绝了你所在网段客户端依然报“no pg_hba.conf entry for host ...”。我维护的服务器上一般这样配置本地Unix socket连接使用peer认证local all postgres peer本机TCP连接使用scram密码认证host all all 127.0.0.1/32 scram-sha-256内网业务网段host all all 10.0.0.0/8 scram-sha-256注意pg_hba.conf是按顺序从上到下匹配的第一条命中的规则立即生效所以reject条目必须放在前面否则永远不会被匹配到。改完文件后一定要执行SELECT pg_reload_conf();不用重启服务。另外要区分“pg链接”这个词在PostgreSQL语境里的两种含义一个是客户端连接数据库时由pg_hba.conf控制的连接权限另一个是数据库内部对象级授权。排查问题前先问清楚是哪种能省掉大量瞎查的时间。3. “创建视图权限不足”的完整排查链路一次SQL执行需要哪些权限3.1 建视图失败的两种报错创建视图是个很典型的权限组合场景报错通常有两种第一种ERROR: permission denied for schema public意思是这个角色在public schema上没有建对象的权限。PG15之后对public schema的默认权限做了收紧已经不是所有用户都能在里面CREATE了。如果是从旧版本迁移过来的实例还要考虑public schema默认权限是否被之前版本放开过。第二种ERROR: permission denied for table orders这往往发生在视图定义包含查询表的时候。创建视图本身只是记录一段SQL逻辑但PostgreSQL会在创建时校验你是否有权限引用这些表。若没有权限连视图都建不出来。这两种报错经常连续出现先缺schema权限补上后建视图又缺表权限。很多人给用户建视图权限时只记得GRANT CREATE ON SCHEMA却忘了SELECT权限。3.2 排查步骤与SQL从角色到对象逐层验证我把排查过程固定成一套SQL遇到权限问题直接跑一遍基本能定位到具体层级。第一步确认当前会话角色SELECT current_user, session_user;第二步确认角色继承关系SELECT r.rolname, m.rolname AS member_of FROM pg_auth_members am JOIN pg_roles r ON r.oid am.roleid JOIN pg_roles m ON m.oid am.member;第三步确认schema权限SELECT grantee, privilege_type FROM information_schema.schema_privileges WHERE schema_name public;第四步确认表权限SELECT grantee, privilege_type, table_name FROM information_schema.role_table_grants WHERE table_schema public AND table_name orders;第五步确认表属主SELECT schemaname, tableowner FROM pg_tables WHERE tablename orders;我之前帮人排查时发现报错用户的角色是某分组角色的成员分组有表权限但schema USAGE权限却授给了另一个分组结果用户跑起来依然失败。权限继承不是“将分组的所有属性都复制到用户身上”而是“用户通过路径访问对象时权限校验过程中会向上检查组角色的acl”。理解这一点后排查思路就清晰多了。3.3 短期解法与长期解法短期解法就是补齐缺失权限GRANT USAGE ON SCHEMA public TO app_role; GRANT CREATE ON SCHEMA public TO app_role; GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_role;如果以后还要自动给新建的表授权用默认权限ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO app_role;这里有个重要规则ALTER DEFAULT PRIVILEGES只影响设置之后“新创建”的对象对已存在的表无效。所以正确做法是“现在把存量对象授权补上同时把默认权限设好避免未来新建表又踩坑”。3.4 SECURITY DEFINER的使用边界建视图权限不足的另一个“捷径”是给函数或视图加SECURITY DEFINER让它以定义者身份执行。这个方式在工作规模小的时候很管用但它存在提权风险。比如你让普通用户通过一个SECURITY DEFINER函数绕过表权限读取生产数据一旦函数逻辑写得不够严谨就可能让用户看到本不该看的数据。PostgreSQL也提供了更安全的SECURITY INVOKER选项让视图或函数以调用者身份校验权限。在PG15及以上版本里CREATE VIEW默认支持安全调用者视图这个概念越来越重要。我的原则是非必要不用SECURITY DEFINER如果非要用函数体里严禁拼接动态SQL并且把owner设在一个专用账号上而不是超级用户。4. 行级权限从数据库RLS到应用系统的行列权限设计4.1 行级安全策略的基本原理表级权限控制的是“你能不能读这张表”但很多业务需要做到“同一张表不同的人只能看到不同行”。PostgreSQL的行级安全Row Level SecurityRLS就是来解决这个问题的。开启方式很直接ALTER TABLE orders ENABLE ROW LEVEL SECURITY;CREATE POLICY tenant_isolation ON orders USING (tenant_id current_setting(app.tenant_id)::int);就这样当用户执行SELECT时PostgreSQL会在过滤条件里自动追加tenant_id匹配当前会话变量的条件。没有满足USING条件的行对用户来说就像不存在一样。需要注意开启RLS之后即使是表owner默认也会被策略约束除非明确设置FORCE ROW LEVEL SECURITY为false。这个设计初看反直觉实际上是为了防止拥有者绕过策略直接查看所有行。我建议在敏感表上保持FORCE状态。4.2 Java应用接入RLS的两种常见姿势业务系统里最常见的做法是Java应用在建立数据库连接后先执行SET语句SET app.tenant_id 1001;这样该连接上的所有SQL都自动带上租户过滤器。缺点是连接池里的连接会被复用如果某个线程没有重置变量下一个请求就可能读到上一个租户的数据。解决方式是在获取连接后初始化会话变量归还前重置SET app.tenant_id ; RESET app.tenant_id;或者用连接池的connectionInitSql参数在建立物理连接时执行初始化SQL。这样虽然每条新连接都会执行一次SET但整体开销很低。另一种做法是在Java代码里把所有SQL都拼上tenant_id条件不发SET语句。优点是逻辑对DBA透明缺点是所有写SQL的人都不能漏条件漏一条就是数据泄露事故。实际项目中我偏向用RLS兜底应用层正常写SQL数据库层做最后一道防线。4.3 行列权限的设计借鉴从大数据开源方案到PG落地热词里有个“大数据行、列权限设计开源”这让我想到Apache Ranger和Griffin那套思路。Ranger可以在Hive等组件上做行过滤和列屏蔽比如财务人员看销售表只能看到本部门行金额列还要脱敏。PostgreSQL虽然没内置完整的Ranger那样集中管理但通过组合能力可以实现类似效果。列权限实现很简单GRANT SELECT (order_id, customer_id) ON orders TO sales_role; REVOKE SELECT ON orders FROM sales_role;注意列权限只读不写。如果想限制某列只能看脱敏后的值可以用视图CREATE VIEW orders_mask AS SELECT order_id, customer_id, md5(card_no) AS card_no_mask FROM orders;配合RLS就能做到“行过滤列脱敏”逻辑都在数据库层应用层不用感知。这也是我给业务系统设计数据权限时的基础模型。4.4 用角色继承来组织权限组上一节说的行级策略最终要落到角色身上。我习惯先用“权限组”把能力分档CREATE ROLE base_read_only; CREATE ROLE base_read_write; CREATE ROLE data_analyst;然后把具体账号挂到组下面GRANT base_read_only TO app_service; GRANT base_read_write TO etl_user; GRANT data_analyst TO report_user;RLS策略授权给组而不是具体账号。这样人员离职或转岗时只需要调整成员关系不用把几十张表的权限逐个REVOKE。权限组的命名尽量直接用业务语义方便审计。5. 权限巡检SQL与常见误区把系统里到底谁有啥权限看清楚5.1 我常用的权限查询SQL清单权限梳理最现实的问题是对象一多光靠权限管理界面根本看不完。我建议直接用SQL查系统目录。下面是我每次做权限巡检都要跑的几段SQL。查看所有角色和属性SELECT rolname, rolsuper, rolcreaterole, rolcreatedb, rolcanlogin FROM pg_roles ORDER BY rolname;查看角色继承关系SELECT r.rolname AS role_name, m.rolname AS member_name FROM pg_auth_members am JOIN pg_roles r ON r.oid am.roleid JOIN pg_roles m ON m.oid am.member ORDER BY 1, 2;查看表级权限SELECT n.nspname AS schema_name, c.relname AS table_name, a.attname AS column_name, r.rolname AS grantee, string_agg(privilege_type, , ORDER BY privilege_type) AS privs FROM pg_attribute a JOIN pg_class c ON c.oid a.attrelid JOIN pg_namespace n ON n.oid c.relnamespace JOIN aclexplode(COALESCE(c.relacl, acldefault(r, c.relowner))) ON true JOIN pg_roles r ON r.oid grantee WHERE n.nspname NOT IN (pg_catalog, information_schema) AND a.attnum 0 GROUP BY 1,2,3,4 ORDER BY 1,2,3,4;查看schema权限SELECT nspname, r.rolname, a.privilege_type FROM pg_namespace n JOIN aclexplode(COALESCE(n.nspacl, acldefault(n, n.nspowner))) a ON true JOIN pg_roles r ON r.oid a.grantee WHERE nspname NOT LIKE pg_% ORDER BY 1,2,3;查看普通表属主和大小辅助判断哪些表属于“高危敏感对象”SELECT schemaname, tablename, tableowner FROM pg_tables WHERE schemaname NOT IN (pg_catalog, information_schema) ORDER BY schemaname, tablename;这些SQL里最关键的是aclexplode函数。它能把aclitem数组展开成一行行可读的权限记录省去手工解析系统目录的麻烦。5.2 用查询结果做权限盘点与审计我每次做权限梳理不是只看哪些权限有而是把结果导成表格后按“角色-对象-权限”三个维度交叉分析。比如发现app_service角色在一张业务表上有DELETE权限而这张表所在业务域根本不需要删除能力那就该走审批流程去掉DELETE。这类问题在长久运行的系统里特别常见权限会越积越多没有人主动收口。另外对比测试库和生产库的权限也是一种有效手段。两套环境结构一致权限却不同往往意味着生产环境曾经手动修过权限没有同步回测试库。每次版本发布前我都会跑一遍上面的SQL对比两个环境的输出避免测试通过、上线失败。5.3 巡检时最容易漏的三个点ALTER DEFAULT PRIVILEGES只影响未来的对象。很多人以为执行过这条命令新表就会有权限却忘了它只对“被授权角色后续创建的对象”生效而且它默认作用域是当前会话所在的schema跨schema需要重复设置。public schema的CREATE权限。PG15之前public schema默认允许所有连接用户CREATE对象。也就是说任何人都能在你的public schema里建表、建函数既可能污染命名空间也可能造成恶意对象覆盖。巡检时一定要检查这个默认授权。事件触发器、扩展函数和外部表。这类对象不常出现在常规对象授权清单里却可能开放出隐藏的数据访问通道。我见过有同事在安装扩展时不小心把某个函数授予PUBLIC结果所有角色都能调用该函数读取系统信息。巡检时要把扩展函数也纳入权限清单。6. 这些年权限管理的经验教训从“能用”到“管理得好”6.1 不要成为superuser迷信的重灾区刚入门时我也干过这种事某个用户老是报权限不足干脆ALTER USER xxx SUPERUSER效果立竿见影但接下来的半年里每次出了问题都分不清是代码bug还是权限问题因为所有人都有超级权限权限审计彻底失效。后来我把所有应用账号全部降为普通LOGIN角色只保留必要权限。早期会觉得麻烦现在反而轻松了权限边界越清晰问题定位越快。超级用户只留给专属的运维账号并且避免使用超级用户跑日常业务应用。6.2 权限变更不生效的四种真相“我明明GRANT了怎么还报权限不足”这类问题我排查过太多次原因基本逃不出这四种连接池里的旧连接还没释放。你改了权限应用里跑着的老连接不会立即刷新。需要确认应用是否保持了长连接。search_path指向了错误的schema。用户在public下的同名表有权限但当前search_path先找到了别人的schema访问的其实是另一个同名的对象。角色通过成员关系获得的权限没有得到显式体现。你查某个用户直接授权时看不到这张表的SELECT权限但用户确实可以用因为权限来自它的上级分组。默认权限与直接授权混在一起看不出哪些是未来继承的权限导致误判。遇到“权限不生效”我建议先跑一遍第5小节里的SQL看到底是哪个对象没授权不要凭感觉继续GRANT。6.3 一套轻量可落地的权限管理流程这些年实践下来我总结了一套轻量流程适合中小团队直接抄作业阶段操作说明初始化创建三个基准角色readonly、readwrite、admin权限分层清晰授权所有对象默认权限交给readonly或readwrite新表自动有权限账号管理人员账号只加入角色不单独授权调动时改组成员即可变更权限变更先跑pg_dumpall --roles-only备份至少能快速回滚巡检每月执行第5节巡检SQL收口长期累积的权限这套流程的核心思路是“角色驱动权限分组定期收敛”。很多系统不是死在权限缺失上而是死在权限失控上。6.4 最后分享一个我很小但很值钱的经验数据库权限变更前永远先执行一次pg_dumpall --roles-only -f roles_backup.sql这条命令只备份角色和角色属性不备份数据体积极小却能让你在误删角色成员关系或改错密码时快速恢复。我已经靠它救回过不止一次权限配置事故。权限梳理是一个需要习惯性维护的事不是一次授权就一劳永逸。每次给新业务开库、给新同事开账号都顺手按这套流程走一遍半年后你回头看会发现数据库清爽得多排查问题的时候也会感谢当初那个认真梳理权限的自己。