尧图网页设计 
PostgreSQL这套权限体系第一次接触的人十有八九会懵明明建的是用户报错里却总蹦出角色两个字照着教程敲了CREATE USER回头一看\du里躺着一条记录还是叫角色更别提授权之后应用还是连不上翻来覆去查了半天最后发现是pg_hba.conf那一行的认证方式没对上。我自己刚接手第一个生产库的时候就干过把超级用户密码直接写进应用配置文件的事被前辈逮住教育了一下午。这篇就把 PostgreSQL 里用户和角色的创建、授权、认证、回收、审计这一整条链路捋清楚从语法参数到生产实践,尽量把每个参数背后的取舍讲透让刚上手的朋友能照着抄也让踩过坑的同学找到几个疑难的答案。1. 先把用户和角色这对概念理清楚1.1 角色是容器用户只是能登录的角色在 PostgreSQL 里角色Role是一个统一的权限主体概念用户User只是带有 LOGIN 属性的角色。这句话是理解整套体系的钥匙。你敲的CREATE USER alice在数据库内核看来完全等价于CREATE ROLE alice LOGIN。反过来CREATE ROLE dev_group默认是不带 LOGIN 的也就是它只能被当作权限集合来用不能拿来登录。我习惯用公司组织来类比角色就像岗位运维岗数据分析岗用户是具体的人。岗位本身不打卡但它挂着一堆权限人打卡上班但权限是从岗位继承过来的。PostgreSQL 之所以不区分两套体系是因为这样做权限管理模型特别统一——授权、继承、嵌套、回收全部走同一套GRANT/REVOKE语法。你可以在psql里用\du新版本也可以\dg看到所有角色输出里会分两列Role name和Attributes、Member of。Attributes列显示的Superuser、Create role、Create DB、Cannot login就是 NOLOGIN 的提示这些就是角色身上挂的属性开关。1.2 为什么 PostgreSQL 要这么设计这种设计的直接好处有三个。第一权限可以分层把一堆细碎的表权限打包成一个角色未来加人只需要GRANT 角色 TO 新人不用挨个表重新授权。第二归属清晰对象有 owner所有者owner 天然拥有该对象的全部权限业务账号可以只读、只写、只连职责分得开。第三继承可控默认角色之间的成员关系是继承的但也有NOINHERIT这种开关用来做更精细的控制比如某个用户加入了管理员角色但只有显式SET ROLE之后才真正获得管理员权限。需要注意的是在 PostgreSQL 15 之前publicschema 默认对所有角色开放 CREATE 权限导致任何一个能连上库的账号都能建表。从 15 版本开始这个默认被收紧了普通角色在publicschema 里没有 CREATE 权限了。这条变动如果没注意到从老版本升级过来会一头雾水——明明以前能建的表现在报permission denied for schema public。所以下文讲权限授予的时候我会把 schema 级的授权单独拎出来讲。1.3 动手前必须先确认的三件事在敲第一个CREATE ROLE之前我建议先确认三件事能省掉后面一大堆麻烦。谁来执行建号普通账号只有在拿到了CREATEROLE属性之后才能建角色但要注意CREATEROLE账号只能操作自己拥有成员关系的角色想改别人的角色属性是不行的。生产上更常见的做法是统一用postgres或专设的运维账号来做。默认权限加密方式先SHOW password_encryption;如果是md5强烈建议改成scram-sha-256改法在 2.3 会写。这个参数决定了你新建角色时密码用什么算法存进pg_authid。认证配置文件在哪SHOW hba_file;输出就是pg_hba.conf的绝对路径。改完这个文件要SELECT pg_reload_conf();或pg_ctl reload才生效它不是动态读取每一个请求的。这三件事确认完后面建号、授权、连接测试就能一路顺畅。2. 创建角色与用户语法、参数与实战取舍2.1 CREATE ROLE 和 CREATE USER 的差异与等价写法先看最基本的两种写法-- 建一个不能登录的角色用作权限组 CREATE ROLE app_readonly; -- 建一个能登录的用户 CREATE USER alice WITH PASSWORD Str0ng_Pass!_2024;CREATE USER只是CREATE ROLE ... LOGIN的语法糖另外CREATE USER默认会给一个PASSWORD占位即使你没写密码它也会让这个角色带上密码为空但占位的属性,实际是 MD5 空串或类似的表现具体取决于版本而CREATE ROLE不写 PASSWORD 就是完全没密码。这点微妙差异在新版本上已经不那么显著但知道它对排查老库的问题有帮助。创建的时候可以一次性把属性写全CREATE ROLE app_rw WITH LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT CONNECTION LIMIT 50 PASSWORD Str0ng_Pass!_2024 VALID UNTIL 2025-12-31;NOINHERIT在这里是关键——它让 app_rw 默认不继承它所属角色的权限必须显式SET ROLE才生效。这对应用账号其实是有帮助的因为应用通常只需要它自己的直接权限不必意外拿到组的全部权限。2.2 每个属性到底管什么逐项拆解很多人建号只写个 LOGIN 加密码属性全靠默认结果埋下一堆隐患。下面这张表把常用属性说清楚方便对照。属性默认值作用什么时候要动它SUPERUSERNOSUPERUSER绕过所有权限检查只在极少数维护场景开生产别给CREATEDBNOCREATEDB允许创建数据库给需要临时建库的租户账号CREATEROLENOCREATEROLE允许创建/管理角色给 DBA 团队专用账号LOGINNOLOGINROLE/ LOGINUSER允许连接数据库只有真实登录的账号才开INHERITINHERIT自动继承所属角色的权限权限组通常用默认 INHERITREPLICATIONNOREPLICATION允许发起物理复制连接只给复制相关账号BYPASSRLSNOBYPASSRLS绕过行级安全策略除非明确需要否则关CONNECTION LIMIT-1无限制该角色最大并发连接数给小号做限流很有用VALID UNTIL无穷密码有效期临时账号、外包账号一定要设关于CONNECTION LIMIT再补一句这个限制是该角色能同时持有的连接数跟数据库级别、实例级别的限制是分开计算的。生产上给报表类账号设个 10~20能挡住那些忘了关连接、把连接池开爆的脚本。VALID UNTIL这个属性特别容易被忽略但它是外包、临时账号的最好归宿。设置后到时间点账号就自然失效比事后翻台账删号靠谱得多。2.3 密码策略scram-sha-256 与有效期密码这一块从 PostgreSQL 10 开始引入了scram-sha-256到 14 之后新集群默认就是它了。检查和修改方式-- 查看当前设置 SHOW password_encryption; -- 全局改成 scram-sha-256需要重启或 reload 视版本而定 ALTER SYSTEM SET password_encryption scram-sha-256; SELECT pg_reload_conf(); -- 改已有角色的密码会按当前 password_encryption 重新加密 ALTER ROLE alice WITH PASSWORD NewStr0ng_Pass!_2024;scram-sha-256相比老的md5有几个实打实的好处服务端不存明文可逆的哈希、支持信道绑定、抗离线暴力破解更强。生产上不允许再用 md5 存储密码这是底线共识。如果你手里有老库还在用 md5迁移路径是改password_encryption→ 逐个ALTER ROLE ... PASSWORD重设一遍 → 把pg_hba.conf里对应行的 md5 换成 scram-sha-256。另一个跟密码相关的操作是强制过期ALTER ROLE alice VALID UNTIL 2024-10-01;过了这个时间点连接就会被拒绝报password authentication failed或者role alice is not permitted to log in之类的错误。审计的时候如果你看到某账号明明密码对却登录不上第一反应就该查rolvaliduntilSELECT rolname, rolvaliduntil FROM pg_roles WHERE rolname alice;2.4 实战给一个电商项目建三类账号假设一个典型的电商后台需要三类账号读写的应用账号、只读的报表账号、负责 DDL 的迁移账号。我会这么建-- 1) 权限组角色不登录 CREATE ROLE grp_rw NOLOGIN; CREATE ROLE grp_ro NOLOGIN; CREATE ROLE grp_ddl NOLOGIN; -- 2) 应用账号属于读写组 CREATE USER app_rw WITH PASSWORD App_Rw#2024 CONNECTION LIMIT 100; GRANT grp_rw TO app_rw; -- 3) 报表账号只读连接数限死 CREATE USER report_ro WITH PASSWORD Rep_Ro#2024 CONNECTION LIMIT 15; GRANT grp_ro TO report_ro; -- 4) 迁移账号DDL 专用加密码有效期 CREATE USER migrator WITH PASSWORD Mig#2024!x VALID UNTIL 2025-06-30; GRANT grp_ddl TO migrator; -- 5) 数据库层权限 GRANT CONNECT ON DATABASE shop TO grp_rw, grp_ro, grp_ddl; GRANT USAGE ON SCHEMA public TO grp_rw, grp_ro, grp_ddl; -- 6) 组权限映射到具体对象 GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO grp_rw; GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO grp_rw; GRANT SELECT ON ALL TABLES IN SCHEMA public TO grp_ro; GRANT CREATE ON SCHEMA public TO grp_ddl; GRANT ALL ON ALL TABLES IN SCHEMA public TO grp_ddl;这套结构的好处是把账号和权限解耦了。以后来了第二台应用服务器再加一个账号GRANT grp_rw TO app_rw2就完事不用碰任何表级授权。哪怕后面想把读写组的DELETE权限收掉也只是针对grp_rw一次REVOKE的事。注意GRANT ... ON ALL TABLES IN SCHEMA只会作用于当前已存在的表之后新建的表不会自动带上权限。这一点后面第 3.4 节会用ALTER DEFAULT PRIVILEGES补上。3. 权限授予与回收把最小权限落到实处3.1 三层权限模型数据库、模式、对象PostgreSQL 的权限是分层的从粗到细大致是实例级 → 数据库级 → 模式schema级 → 对象级表/序列/函数/视图→ 列级 → 行级。要访问一张表里的数据前面每一层的门都得先打开。这是新手最容易漏的地方明明给了表的 SELECT还是报错十有八九是 schema 的 USAGE 没给或者数据库的 CONNECT 没给。-- 数据库级 GRANT CONNECT ON DATABASE shop TO app_rw; REVOKE TEMP ON DATABASE shop FROM app_rw; -- 不想让业务号建临时表就收掉 -- 模式级 GRANT USAGE ON SCHEMA public TO app_rw; GRANT CREATE ON SCHEMA public TO migrator; -- 只有迁移号能建表 -- 对象级 GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE orders TO grp_rw; -- 列级细到某个字段 GRANT SELECT (order_id, amount, created_at) ON TABLE orders TO report_ro;列级授权这个能力非常实用。比如订单表里有user_phone、id_card这种敏感字段报表账号只需要看金额和时间那就只给它几个列的 SELECT不用为它单独建视图省事。3.2 GRANT 角色成员关系与继承机制角色之间可以用GRANT 角色A TO 角色B建立成员关系B 就是 A 的成员。默认情况下B 会继承 A 的权限除非 B 设了NOINHERIT。这一点跟 Linux 用户组的逻辑很像用户属于某个组就继承了组对文件的权限。GRANT grp_rw TO app_rw; -- app_rw 成为 grp_rw 成员直接继承其权限 GRANT grp_ddl TO migrator WITH ADMIN OPTION; -- 还能把 grp_ddl 再授给其他人WITH ADMIN OPTION是我不仅能授给你你还能继续往下授的意思权限管理上的传销链。生产环境慎用一般只有 DBA 组的角色才值得带这个选项。想查一个角色到底属于哪些组、又能往下授什么SELECT m.rolname AS member, r.rolname AS role, am.admin_option FROM pg_auth_members am JOIN pg_roles r ON r.oid am.roleid JOIN pg_roles m ON m.oid am.member WHERE m.rolname app_rw;如果某个账号设了NOINHERIT那它虽然加入了组但不自动用组的权限必须显式SET ROLE grp_rw;才切换过去。这种模式在安全要求高的场景很常见相当于持有钥匙但平时不上膛。3.3 组角色玩法一次授权全员生效组角色Group Role是 PostgreSQL 权限管理里最省心的一个模式。核心思路是永远不要给具体用户直接授权而是给组授权用户只负责加入组。这样人员的进出只是GRANT和REVOKE成员关系表级别的授权不需要动。实际运维里我一般会把组角色按职能来划分比如组角色典型权限给谁用grp_connectCONNECT on db, USAGE on schema所有业务账号的基础组grp_roSELECT on all tables报表、BI、埋点分析grp_rwSELECT/INSERT/UPDATE/DELETE后端应用主账号grp_ddlCREATE on schema ALL on tables迁移、发布流水线grp_maintain复制、备份、VACUUM 相关运维专用配合pg_hba.conf里的host all grp_ro ...这种写法号前缀代表组甚至能做到只在某个网段允许这个组的所有成员连接。这种组合拳在生产上非常实用。3.4 ALTER DEFAULT PRIVILEGES让新表的权限自动到位GRANT ON ALL TABLES IN SCHEMA最大的坑前面提过——只管当下不管未来。每加一张新表都得重新跑一遍授权忘了就会在半夜收到应用告警。解决办法就是ALTER DEFAULT PRIVILEGES简称 ADP它能给将来由某个角色创建的对象预置权限。-- 让 migrator 以后建的表自动给 grp_rw 和 grp_ro 授予相应权限 ALTER DEFAULT PRIVILEGES FOR ROLE migrator IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO grp_rw; ALTER DEFAULT PRIVILEGES FOR ROLE migrator IN SCHEMA public GRANT SELECT ON TABLES TO grp_ro; ALTER DEFAULT PRIVILEGES FOR ROLE migrator IN SCHEMA public GRANT USAGE, SELECT ON SEQUENCES TO grp_rw;FOR ROLE migrator这段很重要。ADP 规则是跟着创建者走的如果你的建表脚本用migrator执行那就必须FOR ROLE migrator如果你用postgres建表那要写FOR ROLE postgres。这个细节我在一次项目迁移里栽过——迁移脚本换了个执行账号结果新表一张张都没权限排查了大半天才发现 ADP 挂在了旧账号上。查看当前已经设置的 ADPSELECT pg_get_userbyid(defaclrole) AS owner, nspname, defaclobjtype, defaclacl FROM pg_default_acl JOIN pg_namespace ON pg_namespace.oid defaclnamespace;3.5 REVOKE 与 CASCADE 的坑REVOKE本身语法跟GRANT对称但用起来有几个细节要留意。-- 回收一个权限 REVOKE DELETE ON TABLE orders FROM grp_rw; -- 回收角色成员关系 REVOKE grp_rw FROM app_rw; -- 回收某人所有对象的权限用于清退账号前 REASSIGN OWNED BY old_user TO migrator; DROP OWNED BY old_user;CASCADE要慎用REVOKE ... CASCADE会把这个权限再授权出去的链路一并断掉。什么意思呢——你把某权限给了 A带 adminA 又给了 B如果你用REVOKE ... CASCADE从 A 手里收走B 手里的那份也会被连带收回。如果只想收 A 的、不动 B 的那就用默认的RESTRICT。另外一点容易踩的坑是PUBLIC伪角色。PostgreSQL 里有个对所有角色隐式生效的PUBLIC默认情况下数据库有 CONNECT、schema 有 USAGE旧版本还有 public schema 的 CREATE。如果你想把权限收得干净得先跟PUBLIC划清界限-- 收紧数据库的默认连接权限 REVOKE CONNECT ON DATABASE shop FROM PUBLIC; GRANT CONNECT ON DATABASE shop TO grp_connect; -- 收紧 schema 级默认权限PG15 起 public schema 默认就没 CREATE 了 REVOKE ALL ON SCHEMA public FROM PUBLIC; GRANT USAGE ON SCHEMA public TO grp_connect;这一步做完就相当于把任人可进改成只有发钥匙的人能进是安全加固里性价比很高的一步。4. 登录认证链路pg_hba.conf 与连接控制4.1 pg_hba.conf 匹配规则从上到下命中即停建完号连不上绝大多数问题出在pg_hba.conf。这个文件里的规则从第一行开始逐行匹配匹配到第一条就停止所以顺序非常关键。典型的几行长这样# TYPE DATABASE USER ADDRESS METHOD local all postgres peer local all all scram-sha-256 host shop grp_rw 10.0.1.0/24 scram-sha-256 host shop grp_ro 10.0.1.0/24 scram-sha-256 host all all 127.0.0.1/32 scram-sha-256 host all all ::1/128 scram-sha-256几处值得展开的细节。第一列TYPE里local走的是 Unix sockethost走的是 TCPhostssl强制 SSL。第二列的DATABASE和第三列的USER都可以写all、具体名字、逗号列表或者组名grp_rw表示属于该组的所有角色。第四列是 CIDR 格式的网段。第五列是认证方式常见取值peer用操作系统用户名匹配、scram-sha-256、md5不推荐、trust完全放行测试用生产禁用、cert客户端证书、reject拒绝。改完之后必须让 PostgreSQL 重新加载配置# 命令行方式需要能访问数据目录的用户 pg_ctl reload -D /var/lib/pgsql/data # 或者在 psql 里 SELECT pg_reload_conf();pg_reload_conf()只是重读配置文件不影响现有连接所以可以放心在生产上执行。但如果改了postgresql.conf里需要重启的参数比如listen_addresses那还是得老老实实重启服务。4.2 认证方式的选择peer、scram-sha-256 还是 certpeer认证主要用于本地命令行工具psql的免密登录比较的是发起连接的操作系统用户名和数据库角色名一致就放行。所以服务器上用 root 或 postgres 用户直接psql进库通常就是靠它。缺点是它只对本地 socket 有效远程连接用不了。scram-sha-256是现在推荐的默认选择安全性比 md5 高一个档次语法上也最省事客户端新版本都支持。要迁移到 scram注意两件事客户端驱动要够新老的 JDBC、libpq 可能只支持 md5以及密码要用新算法重设一遍。cert走客户端证书双向校验适合内部服务之间的自动化连接——比如应用服务器到数据库之间的连接用证书就不用担心密码文件落盘的泄露风险。配置略微复杂但一次配好一劳永逸。提示任何情况下都不要把trust留在生产pg_hba.conf里。哪怕只开放给127.0.0.1一旦攻击者拿到服务器上的任意账号就能直接连库。这个坑我见过不止一次。4.3 连接数限制与资源约束连接数这件事PostgreSQL 的max_connections是全局上限默认 100太多会吃内存。角色级别可以用CONNECTION LIMIT做下游限制数据库级别也有一个同名参数-- 角色级限流 ALTER ROLE report_ro CONNECTION LIMIT 15; -- 数据库级限流 ALTER DATABASE shop CONNECTION LIMIT 300; -- 查当前连接分布 SELECT datname, usename, state, count(*) FROM pg_stat_activity GROUP BY 1, 2, 3 ORDER BY 4 DESC;如果某个账号的连接数超了新连接会收到FATAL: too many connections for role report_ro。运维值班时看到这条第一反应是去看是不是报表定时任务没关连接或者连接池配置跑偏了。生产上的通用经验是应用层一定要配连接池密码里的连接数上限只做最后一道防线不能真的靠它兜底。4.4 实操新增账号后连不上怎么排查这是我总结的一套固定排查顺序基本能覆盖 95% 的登录问题psql -h host -U user -d db直接试连看报错原文。报no pg_hba.conf entry就是 hba 没配上password authentication failed看密码/加密方式role xxx does not exist看是不是建号没成功或者连错实例。在能连的超级用户 session 里SELECT * FROM pg_roles WHERE rolnamexxx;确认角色存在看rolcanlogin是不是t。\du xxx看属性重点看Cannot login和Valid until。SHOW hba_file;拿到路径检查对应行是否匹配注意顺序有没有被更靠前的reject或all拦掉。SELECT pg_reload_conf();后重试避免刚改完没生效。如果是改了密码还登录不上检查pg_hba.conf的 METHOD 和客户端的协议兼容性尤其老客户端连 scram 认证会有问题。按这个顺序走别乱改配置效率会高很多。5. 日常运维改名、删号、审计与备份5.1 角色重命名与所有权转移角色改名很简单ALTER ROLE old_name RENAME TO new_name;但注意改名不会自动改掉它拥有的对象的所有者这个在新版本里已经是自动跟随的pg_roles里存的 oid 不变只是名字变了权限关系也不会断。真正要小心的是对象所有权在谁名下——如果你想让某个新账号接手旧账号的全部对象-- 目标库中执行一定要连到目标库 REASSIGN OWNED BY old_app TO new_app;REASSIGN OWNED必须在每个相关数据库里分别执行它不会跨库生效因为对象所有权是库内概念。这是新手最常忽略的一点在主库改了忘了从库或者在别的业务库里也有一堆对象结果一半迁移成功一半没动。5.2 删除角色失败依赖对象怎么查DROP ROLE alice;报错role alice cannot be dropped because some objects depend on it意思是这个角色还有牵挂——它拥有的对象、它持有的权限、它是某个对象的 owner 等等。两步清理法-- 1) 把它拥有的对象都转给别人必须在每个库里执行 REASSIGN OWNED BY alice TO postgres; -- 2) 把它身上的权限全清掉 DROP OWNED BY alice; -- 3) 然后就能删了 DROP ROLE alice;DROP OWNED BY会删除该角色拥有的所有对象及其权限reassign 只处理所有权。这两个顺序不能反先REASSIGN再DROP OWNED否则DROP OWNED可能因依赖还没清干净而失败。全实例范围清退一个人我一般是写个脚本遍历所有非模板库跑一遍这两段 SQL最后在主库DROP ROLE。查具体是什么对象卡住了SELECT classid::regclass AS obj_class, objid, objsubid FROM pg_shdepend WHERE refclassid pg_roles::regclass AND refobjid (SELECT oid FROM pg_roles WHERE rolname alice);pg_shdepend记录了跨库的共享依赖查出来的行数就是牵挂的数量。5.3 权限审计几条 SQL 看清谁有什么安全审计场景经常需要回答某账号到底能干什么。常用的几条查询-- 表级权限总览 SELECT grantee, table_schema, table_name, privilege_type FROM information_schema.role_table_grants WHERE grantee IN (grp_rw, app_rw) ORDER BY table_name, grantee; -- 角色成员关系 SELECT r.rolname AS role, m.rolname AS member, am.admin_option FROM pg_auth_members am JOIN pg_roles r ON r.oid am.roleid JOIN pg_roles m ON m.oid am.member; -- 角色属性快照 SELECT rolname, rolsuper, rolcreatedb, rolcreaterole, rolcanlogin, rolconnlimit, rolvaliduntil FROM pg_roles WHERE rolname NOT LIKE pg_% ORDER BY rolname; -- schema 级权限 SELECT nspname, nspacl FROM pg_namespace WHERE nspname public;审计时候的核心是最小权限核查生产库上不该有超出职责的超级用户不该有 NOLOGIN 的组角色却带着 LOGIN不该有VALID UNTIL已经过期的僵尸账号。我会把这些列成检查清单配合定期跑。5.4 角色与权限的备份恢复角色信息本身不在业务库里它存在全局的pg_authid不直接可读用pg_roles视图。用pg_dumpall的--roles-only就能只导角色# 只备份角色定义含密码哈希注意保管 pg_dumpall -U postgres --roles-only -f /backup/roles.sql # 恢复 psql -U postgres -f /backup/roles.sql导出的 roles.sql 里包含加密后的密码串这个文件的敏感程度等同于数据库密码本身权限别写成 644最少 600 且只给 DBA 账号读。另外pg_dump单库备份时会带上ALTER ... OWNER TO之类的语句恢复到新实例前记得先把角色建好否则恢复会报 owner 不存在的错可以配合--no-owner绕过。6. 常见问题与排查速查表6.1 典型报错与解决报错信息常见原因处理办法role xxx does not exist角色没建 / 连错实例\du确认检查是不是连到只读副本或别的端口password authentication failed密码错 / 加密方式不匹配重设密码检查password_encryption与pg_hba.conf的 METHODno pg_hba.conf entry for hosthba 规则没覆盖这个网段或角色加对应行pg_reload_conf()permission denied for schema publicschema USAGE/CREATE 没给GRANT USAGE/CREATE ON SCHEMA public TO ...permission denied for table xxx表级权限没给或没继承GRANT补齐检查是否 NOINHERIT 需要SET ROLEtoo many connections for role xxx达到角色连接上限提高CONNECTION LIMIT或排查连接泄漏role xxx cannot be dropped because some objects depend on it角色仍拥有对象/权限REASSIGN OWNEDDROP OWNED后再删role xxx is not permitted to log in角色没有 LOGIN 属性或已过期ALTER ROLE xxx LOGIN;检查rolvaliduntil这张表可以贴在工位上绝大多数权限相关的报错都能在上面找到线索。6.2 易踩的坑与实操心得几个我在实际项目里反复验证过的经验值得单独拎出来说。第一GRANT ON ALL TABLES IN SCHEMA只是快照。这句话值得重复一遍它不覆盖未来。所有靠它建立权限的团队迟早会被新表的权限问题咬到。正确的做法是对象级 GRANT 建立基线 ADP 兜住未来两条腿走路。第二NOINHERIT和组角色配合要小心。有个项目里为了安全给所有应用账号设了 NOINHERIT结果每次授权都有一部分不生效排查半天才发现得让应用启动后显式SET ROLE。安全固然重要但设计的时候一定要把应用侧怎么用想清楚否则徒增复杂度。第三角色名和操作系统用户名不要搞混。peer认证下本地连接看的是 OS 用户跟你数据库里建了什么角色没关系。曾经有同事在服务器上su - alice之后psql怎么都进不去其实就是 alice 这个 OS 用户对应的数据库角色不存在改scram-sha-256认证或者建同名角色就能通。第四密码里的特殊字符在 shell 里要转义。密码里有!、$的时候在 bash 里写psql -U user -W交互输入没问题但如果直接写进连接串或者环境变量记得用单引号包起来$会被 shell 当变量展开!在某些 shell 里会触发历史展开。这类问题排查起来特别费时间因为报错看起来像密码错了其实是 shell 给改了。第五定期用 SQL 巡检僵尸账号。我会在监控里跑这么一句任何有结果就告警SELECT rolname, rolvaliduntil FROM pg_roles WHERE rolvaliduntil IS NOT NULL AND rolvaliduntil now() AND rolcanlogin;这个能提前发现快过期的临时账号避免到期当天业务失联。6.3 从最小权限到权限回收的完整闭环最后把整套流程串一下方便记忆建号 → 归组 → 授权 → 审计 → 回收。每一步都有对应的工具CREATE ROLE/CREATE USER建GRANT 角色 TO 用户归组GRANT 权限 ON 对象授权information_schema和pg_catalog里的视图做审计权限变更或者离职时用REVOKE/REASSIGN OWNED/DROP OWNED回收。这套闭环跑通之后你会发现 PostgreSQL 处理人员变动其实很轻松——新人入职加一行GRANT grp_xxx TO 新账号老人离职跑两个清理语句剩下的历史包袱不会越积越厚。真正麻烦的是从头就没规划过角色体系、权限全都直接授到具体用户上的那种存量环境改造起来要一层一层剥。所以如果现在还在起步阶段我强烈建议一步到位把组角色这件事做对后面省下的时间不是一点点。我个人在这几年实际维护中的体会是权限管理最贵的从来不是技术难度而是纪律——确保每一张新表都走了统一授权流程确保每一次人员变动都在库里落了痕迹。工具和 SQL 只是把这个纪律执行下去的手段真正的门槛在于团队愿不愿意每季度花半天时间把上面那几条审计语句跑一遍。跑完之后心里有底出问题的时候也有据可查这个习惯一旦养成就再也不想回到凭印象管理权限的日子了。 PostgreSQL用户与角色权限管理：创建、授权、认证与审计
PostgreSQL这套权限体系第一次接触的人十有八九会懵明明建的是用户报错里却总蹦出角色两个字照着教程敲了CREATE USER回头一看\du里躺着一条记录还是叫角色更别提授权之后应用还是连不上翻来覆去查了半天最后发现是pg_hba.conf那一行的认证方式没对上。我自己刚接手第一个生产库的时候就干过把超级用户密码直接写进应用配置文件的事被前辈逮住教育了一下午。这篇就把 PostgreSQL 里用户和角色的创建、授权、认证、回收、审计这一整条链路捋清楚从语法参数到生产实践,尽量把每个参数背后的取舍讲透让刚上手的朋友能照着抄也让踩过坑的同学找到几个疑难的答案。1. 先把用户和角色这对概念理清楚1.1 角色是容器用户只是能登录的角色在 PostgreSQL 里角色Role是一个统一的权限主体概念用户User只是带有 LOGIN 属性的角色。这句话是理解整套体系的钥匙。你敲的CREATE USER alice在数据库内核看来完全等价于CREATE ROLE alice LOGIN。反过来CREATE ROLE dev_group默认是不带 LOGIN 的也就是它只能被当作权限集合来用不能拿来登录。我习惯用公司组织来类比角色就像岗位运维岗数据分析岗用户是具体的人。岗位本身不打卡但它挂着一堆权限人打卡上班但权限是从岗位继承过来的。PostgreSQL 之所以不区分两套体系是因为这样做权限管理模型特别统一——授权、继承、嵌套、回收全部走同一套GRANT/REVOKE语法。你可以在psql里用\du新版本也可以\dg看到所有角色输出里会分两列Role name和Attributes、Member of。Attributes列显示的Superuser、Create role、Create DB、Cannot login就是 NOLOGIN 的提示这些就是角色身上挂的属性开关。1.2 为什么 PostgreSQL 要这么设计这种设计的直接好处有三个。第一权限可以分层把一堆细碎的表权限打包成一个角色未来加人只需要GRANT 角色 TO 新人不用挨个表重新授权。第二归属清晰对象有 owner所有者owner 天然拥有该对象的全部权限业务账号可以只读、只写、只连职责分得开。第三继承可控默认角色之间的成员关系是继承的但也有NOINHERIT这种开关用来做更精细的控制比如某个用户加入了管理员角色但只有显式SET ROLE之后才真正获得管理员权限。需要注意的是在 PostgreSQL 15 之前publicschema 默认对所有角色开放 CREATE 权限导致任何一个能连上库的账号都能建表。从 15 版本开始这个默认被收紧了普通角色在publicschema 里没有 CREATE 权限了。这条变动如果没注意到从老版本升级过来会一头雾水——明明以前能建的表现在报permission denied for schema public。所以下文讲权限授予的时候我会把 schema 级的授权单独拎出来讲。1.3 动手前必须先确认的三件事在敲第一个CREATE ROLE之前我建议先确认三件事能省掉后面一大堆麻烦。谁来执行建号普通账号只有在拿到了CREATEROLE属性之后才能建角色但要注意CREATEROLE账号只能操作自己拥有成员关系的角色想改别人的角色属性是不行的。生产上更常见的做法是统一用postgres或专设的运维账号来做。默认权限加密方式先SHOW password_encryption;如果是md5强烈建议改成scram-sha-256改法在 2.3 会写。这个参数决定了你新建角色时密码用什么算法存进pg_authid。认证配置文件在哪SHOW hba_file;输出就是pg_hba.conf的绝对路径。改完这个文件要SELECT pg_reload_conf();或pg_ctl reload才生效它不是动态读取每一个请求的。这三件事确认完后面建号、授权、连接测试就能一路顺畅。2. 创建角色与用户语法、参数与实战取舍2.1 CREATE ROLE 和 CREATE USER 的差异与等价写法先看最基本的两种写法-- 建一个不能登录的角色用作权限组 CREATE ROLE app_readonly; -- 建一个能登录的用户 CREATE USER alice WITH PASSWORD Str0ng_Pass!_2024;CREATE USER只是CREATE ROLE ... LOGIN的语法糖另外CREATE USER默认会给一个PASSWORD占位即使你没写密码它也会让这个角色带上密码为空但占位的属性,实际是 MD5 空串或类似的表现具体取决于版本而CREATE ROLE不写 PASSWORD 就是完全没密码。这点微妙差异在新版本上已经不那么显著但知道它对排查老库的问题有帮助。创建的时候可以一次性把属性写全CREATE ROLE app_rw WITH LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT CONNECTION LIMIT 50 PASSWORD Str0ng_Pass!_2024 VALID UNTIL 2025-12-31;NOINHERIT在这里是关键——它让 app_rw 默认不继承它所属角色的权限必须显式SET ROLE才生效。这对应用账号其实是有帮助的因为应用通常只需要它自己的直接权限不必意外拿到组的全部权限。2.2 每个属性到底管什么逐项拆解很多人建号只写个 LOGIN 加密码属性全靠默认结果埋下一堆隐患。下面这张表把常用属性说清楚方便对照。属性默认值作用什么时候要动它SUPERUSERNOSUPERUSER绕过所有权限检查只在极少数维护场景开生产别给CREATEDBNOCREATEDB允许创建数据库给需要临时建库的租户账号CREATEROLENOCREATEROLE允许创建/管理角色给 DBA 团队专用账号LOGINNOLOGINROLE/ LOGINUSER允许连接数据库只有真实登录的账号才开INHERITINHERIT自动继承所属角色的权限权限组通常用默认 INHERITREPLICATIONNOREPLICATION允许发起物理复制连接只给复制相关账号BYPASSRLSNOBYPASSRLS绕过行级安全策略除非明确需要否则关CONNECTION LIMIT-1无限制该角色最大并发连接数给小号做限流很有用VALID UNTIL无穷密码有效期临时账号、外包账号一定要设关于CONNECTION LIMIT再补一句这个限制是该角色能同时持有的连接数跟数据库级别、实例级别的限制是分开计算的。生产上给报表类账号设个 10~20能挡住那些忘了关连接、把连接池开爆的脚本。VALID UNTIL这个属性特别容易被忽略但它是外包、临时账号的最好归宿。设置后到时间点账号就自然失效比事后翻台账删号靠谱得多。2.3 密码策略scram-sha-256 与有效期密码这一块从 PostgreSQL 10 开始引入了scram-sha-256到 14 之后新集群默认就是它了。检查和修改方式-- 查看当前设置 SHOW password_encryption; -- 全局改成 scram-sha-256需要重启或 reload 视版本而定 ALTER SYSTEM SET password_encryption scram-sha-256; SELECT pg_reload_conf(); -- 改已有角色的密码会按当前 password_encryption 重新加密 ALTER ROLE alice WITH PASSWORD NewStr0ng_Pass!_2024;scram-sha-256相比老的md5有几个实打实的好处服务端不存明文可逆的哈希、支持信道绑定、抗离线暴力破解更强。生产上不允许再用 md5 存储密码这是底线共识。如果你手里有老库还在用 md5迁移路径是改password_encryption→ 逐个ALTER ROLE ... PASSWORD重设一遍 → 把pg_hba.conf里对应行的 md5 换成 scram-sha-256。另一个跟密码相关的操作是强制过期ALTER ROLE alice VALID UNTIL 2024-10-01;过了这个时间点连接就会被拒绝报password authentication failed或者role alice is not permitted to log in之类的错误。审计的时候如果你看到某账号明明密码对却登录不上第一反应就该查rolvaliduntilSELECT rolname, rolvaliduntil FROM pg_roles WHERE rolname alice;2.4 实战给一个电商项目建三类账号假设一个典型的电商后台需要三类账号读写的应用账号、只读的报表账号、负责 DDL 的迁移账号。我会这么建-- 1) 权限组角色不登录 CREATE ROLE grp_rw NOLOGIN; CREATE ROLE grp_ro NOLOGIN; CREATE ROLE grp_ddl NOLOGIN; -- 2) 应用账号属于读写组 CREATE USER app_rw WITH PASSWORD App_Rw#2024 CONNECTION LIMIT 100; GRANT grp_rw TO app_rw; -- 3) 报表账号只读连接数限死 CREATE USER report_ro WITH PASSWORD Rep_Ro#2024 CONNECTION LIMIT 15; GRANT grp_ro TO report_ro; -- 4) 迁移账号DDL 专用加密码有效期 CREATE USER migrator WITH PASSWORD Mig#2024!x VALID UNTIL 2025-06-30; GRANT grp_ddl TO migrator; -- 5) 数据库层权限 GRANT CONNECT ON DATABASE shop TO grp_rw, grp_ro, grp_ddl; GRANT USAGE ON SCHEMA public TO grp_rw, grp_ro, grp_ddl; -- 6) 组权限映射到具体对象 GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO grp_rw; GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO grp_rw; GRANT SELECT ON ALL TABLES IN SCHEMA public TO grp_ro; GRANT CREATE ON SCHEMA public TO grp_ddl; GRANT ALL ON ALL TABLES IN SCHEMA public TO grp_ddl;这套结构的好处是把账号和权限解耦了。以后来了第二台应用服务器再加一个账号GRANT grp_rw TO app_rw2就完事不用碰任何表级授权。哪怕后面想把读写组的DELETE权限收掉也只是针对grp_rw一次REVOKE的事。注意GRANT ... ON ALL TABLES IN SCHEMA只会作用于当前已存在的表之后新建的表不会自动带上权限。这一点后面第 3.4 节会用ALTER DEFAULT PRIVILEGES补上。3. 权限授予与回收把最小权限落到实处3.1 三层权限模型数据库、模式、对象PostgreSQL 的权限是分层的从粗到细大致是实例级 → 数据库级 → 模式schema级 → 对象级表/序列/函数/视图→ 列级 → 行级。要访问一张表里的数据前面每一层的门都得先打开。这是新手最容易漏的地方明明给了表的 SELECT还是报错十有八九是 schema 的 USAGE 没给或者数据库的 CONNECT 没给。-- 数据库级 GRANT CONNECT ON DATABASE shop TO app_rw; REVOKE TEMP ON DATABASE shop FROM app_rw; -- 不想让业务号建临时表就收掉 -- 模式级 GRANT USAGE ON SCHEMA public TO app_rw; GRANT CREATE ON SCHEMA public TO migrator; -- 只有迁移号能建表 -- 对象级 GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE orders TO grp_rw; -- 列级细到某个字段 GRANT SELECT (order_id, amount, created_at) ON TABLE orders TO report_ro;列级授权这个能力非常实用。比如订单表里有user_phone、id_card这种敏感字段报表账号只需要看金额和时间那就只给它几个列的 SELECT不用为它单独建视图省事。3.2 GRANT 角色成员关系与继承机制角色之间可以用GRANT 角色A TO 角色B建立成员关系B 就是 A 的成员。默认情况下B 会继承 A 的权限除非 B 设了NOINHERIT。这一点跟 Linux 用户组的逻辑很像用户属于某个组就继承了组对文件的权限。GRANT grp_rw TO app_rw; -- app_rw 成为 grp_rw 成员直接继承其权限 GRANT grp_ddl TO migrator WITH ADMIN OPTION; -- 还能把 grp_ddl 再授给其他人WITH ADMIN OPTION是我不仅能授给你你还能继续往下授的意思权限管理上的传销链。生产环境慎用一般只有 DBA 组的角色才值得带这个选项。想查一个角色到底属于哪些组、又能往下授什么SELECT m.rolname AS member, r.rolname AS role, am.admin_option FROM pg_auth_members am JOIN pg_roles r ON r.oid am.roleid JOIN pg_roles m ON m.oid am.member WHERE m.rolname app_rw;如果某个账号设了NOINHERIT那它虽然加入了组但不自动用组的权限必须显式SET ROLE grp_rw;才切换过去。这种模式在安全要求高的场景很常见相当于持有钥匙但平时不上膛。3.3 组角色玩法一次授权全员生效组角色Group Role是 PostgreSQL 权限管理里最省心的一个模式。核心思路是永远不要给具体用户直接授权而是给组授权用户只负责加入组。这样人员的进出只是GRANT和REVOKE成员关系表级别的授权不需要动。实际运维里我一般会把组角色按职能来划分比如组角色典型权限给谁用grp_connectCONNECT on db, USAGE on schema所有业务账号的基础组grp_roSELECT on all tables报表、BI、埋点分析grp_rwSELECT/INSERT/UPDATE/DELETE后端应用主账号grp_ddlCREATE on schema ALL on tables迁移、发布流水线grp_maintain复制、备份、VACUUM 相关运维专用配合pg_hba.conf里的host all grp_ro ...这种写法号前缀代表组甚至能做到只在某个网段允许这个组的所有成员连接。这种组合拳在生产上非常实用。3.4 ALTER DEFAULT PRIVILEGES让新表的权限自动到位GRANT ON ALL TABLES IN SCHEMA最大的坑前面提过——只管当下不管未来。每加一张新表都得重新跑一遍授权忘了就会在半夜收到应用告警。解决办法就是ALTER DEFAULT PRIVILEGES简称 ADP它能给将来由某个角色创建的对象预置权限。-- 让 migrator 以后建的表自动给 grp_rw 和 grp_ro 授予相应权限 ALTER DEFAULT PRIVILEGES FOR ROLE migrator IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO grp_rw; ALTER DEFAULT PRIVILEGES FOR ROLE migrator IN SCHEMA public GRANT SELECT ON TABLES TO grp_ro; ALTER DEFAULT PRIVILEGES FOR ROLE migrator IN SCHEMA public GRANT USAGE, SELECT ON SEQUENCES TO grp_rw;FOR ROLE migrator这段很重要。ADP 规则是跟着创建者走的如果你的建表脚本用migrator执行那就必须FOR ROLE migrator如果你用postgres建表那要写FOR ROLE postgres。这个细节我在一次项目迁移里栽过——迁移脚本换了个执行账号结果新表一张张都没权限排查了大半天才发现 ADP 挂在了旧账号上。查看当前已经设置的 ADPSELECT pg_get_userbyid(defaclrole) AS owner, nspname, defaclobjtype, defaclacl FROM pg_default_acl JOIN pg_namespace ON pg_namespace.oid defaclnamespace;3.5 REVOKE 与 CASCADE 的坑REVOKE本身语法跟GRANT对称但用起来有几个细节要留意。-- 回收一个权限 REVOKE DELETE ON TABLE orders FROM grp_rw; -- 回收角色成员关系 REVOKE grp_rw FROM app_rw; -- 回收某人所有对象的权限用于清退账号前 REASSIGN OWNED BY old_user TO migrator; DROP OWNED BY old_user;CASCADE要慎用REVOKE ... CASCADE会把这个权限再授权出去的链路一并断掉。什么意思呢——你把某权限给了 A带 adminA 又给了 B如果你用REVOKE ... CASCADE从 A 手里收走B 手里的那份也会被连带收回。如果只想收 A 的、不动 B 的那就用默认的RESTRICT。另外一点容易踩的坑是PUBLIC伪角色。PostgreSQL 里有个对所有角色隐式生效的PUBLIC默认情况下数据库有 CONNECT、schema 有 USAGE旧版本还有 public schema 的 CREATE。如果你想把权限收得干净得先跟PUBLIC划清界限-- 收紧数据库的默认连接权限 REVOKE CONNECT ON DATABASE shop FROM PUBLIC; GRANT CONNECT ON DATABASE shop TO grp_connect; -- 收紧 schema 级默认权限PG15 起 public schema 默认就没 CREATE 了 REVOKE ALL ON SCHEMA public FROM PUBLIC; GRANT USAGE ON SCHEMA public TO grp_connect;这一步做完就相当于把任人可进改成只有发钥匙的人能进是安全加固里性价比很高的一步。4. 登录认证链路pg_hba.conf 与连接控制4.1 pg_hba.conf 匹配规则从上到下命中即停建完号连不上绝大多数问题出在pg_hba.conf。这个文件里的规则从第一行开始逐行匹配匹配到第一条就停止所以顺序非常关键。典型的几行长这样# TYPE DATABASE USER ADDRESS METHOD local all postgres peer local all all scram-sha-256 host shop grp_rw 10.0.1.0/24 scram-sha-256 host shop grp_ro 10.0.1.0/24 scram-sha-256 host all all 127.0.0.1/32 scram-sha-256 host all all ::1/128 scram-sha-256几处值得展开的细节。第一列TYPE里local走的是 Unix sockethost走的是 TCPhostssl强制 SSL。第二列的DATABASE和第三列的USER都可以写all、具体名字、逗号列表或者组名grp_rw表示属于该组的所有角色。第四列是 CIDR 格式的网段。第五列是认证方式常见取值peer用操作系统用户名匹配、scram-sha-256、md5不推荐、trust完全放行测试用生产禁用、cert客户端证书、reject拒绝。改完之后必须让 PostgreSQL 重新加载配置# 命令行方式需要能访问数据目录的用户 pg_ctl reload -D /var/lib/pgsql/data # 或者在 psql 里 SELECT pg_reload_conf();pg_reload_conf()只是重读配置文件不影响现有连接所以可以放心在生产上执行。但如果改了postgresql.conf里需要重启的参数比如listen_addresses那还是得老老实实重启服务。4.2 认证方式的选择peer、scram-sha-256 还是 certpeer认证主要用于本地命令行工具psql的免密登录比较的是发起连接的操作系统用户名和数据库角色名一致就放行。所以服务器上用 root 或 postgres 用户直接psql进库通常就是靠它。缺点是它只对本地 socket 有效远程连接用不了。scram-sha-256是现在推荐的默认选择安全性比 md5 高一个档次语法上也最省事客户端新版本都支持。要迁移到 scram注意两件事客户端驱动要够新老的 JDBC、libpq 可能只支持 md5以及密码要用新算法重设一遍。cert走客户端证书双向校验适合内部服务之间的自动化连接——比如应用服务器到数据库之间的连接用证书就不用担心密码文件落盘的泄露风险。配置略微复杂但一次配好一劳永逸。提示任何情况下都不要把trust留在生产pg_hba.conf里。哪怕只开放给127.0.0.1一旦攻击者拿到服务器上的任意账号就能直接连库。这个坑我见过不止一次。4.3 连接数限制与资源约束连接数这件事PostgreSQL 的max_connections是全局上限默认 100太多会吃内存。角色级别可以用CONNECTION LIMIT做下游限制数据库级别也有一个同名参数-- 角色级限流 ALTER ROLE report_ro CONNECTION LIMIT 15; -- 数据库级限流 ALTER DATABASE shop CONNECTION LIMIT 300; -- 查当前连接分布 SELECT datname, usename, state, count(*) FROM pg_stat_activity GROUP BY 1, 2, 3 ORDER BY 4 DESC;如果某个账号的连接数超了新连接会收到FATAL: too many connections for role report_ro。运维值班时看到这条第一反应是去看是不是报表定时任务没关连接或者连接池配置跑偏了。生产上的通用经验是应用层一定要配连接池密码里的连接数上限只做最后一道防线不能真的靠它兜底。4.4 实操新增账号后连不上怎么排查这是我总结的一套固定排查顺序基本能覆盖 95% 的登录问题psql -h host -U user -d db直接试连看报错原文。报no pg_hba.conf entry就是 hba 没配上password authentication failed看密码/加密方式role xxx does not exist看是不是建号没成功或者连错实例。在能连的超级用户 session 里SELECT * FROM pg_roles WHERE rolnamexxx;确认角色存在看rolcanlogin是不是t。\du xxx看属性重点看Cannot login和Valid until。SHOW hba_file;拿到路径检查对应行是否匹配注意顺序有没有被更靠前的reject或all拦掉。SELECT pg_reload_conf();后重试避免刚改完没生效。如果是改了密码还登录不上检查pg_hba.conf的 METHOD 和客户端的协议兼容性尤其老客户端连 scram 认证会有问题。按这个顺序走别乱改配置效率会高很多。5. 日常运维改名、删号、审计与备份5.1 角色重命名与所有权转移角色改名很简单ALTER ROLE old_name RENAME TO new_name;但注意改名不会自动改掉它拥有的对象的所有者这个在新版本里已经是自动跟随的pg_roles里存的 oid 不变只是名字变了权限关系也不会断。真正要小心的是对象所有权在谁名下——如果你想让某个新账号接手旧账号的全部对象-- 目标库中执行一定要连到目标库 REASSIGN OWNED BY old_app TO new_app;REASSIGN OWNED必须在每个相关数据库里分别执行它不会跨库生效因为对象所有权是库内概念。这是新手最常忽略的一点在主库改了忘了从库或者在别的业务库里也有一堆对象结果一半迁移成功一半没动。5.2 删除角色失败依赖对象怎么查DROP ROLE alice;报错role alice cannot be dropped because some objects depend on it意思是这个角色还有牵挂——它拥有的对象、它持有的权限、它是某个对象的 owner 等等。两步清理法-- 1) 把它拥有的对象都转给别人必须在每个库里执行 REASSIGN OWNED BY alice TO postgres; -- 2) 把它身上的权限全清掉 DROP OWNED BY alice; -- 3) 然后就能删了 DROP ROLE alice;DROP OWNED BY会删除该角色拥有的所有对象及其权限reassign 只处理所有权。这两个顺序不能反先REASSIGN再DROP OWNED否则DROP OWNED可能因依赖还没清干净而失败。全实例范围清退一个人我一般是写个脚本遍历所有非模板库跑一遍这两段 SQL最后在主库DROP ROLE。查具体是什么对象卡住了SELECT classid::regclass AS obj_class, objid, objsubid FROM pg_shdepend WHERE refclassid pg_roles::regclass AND refobjid (SELECT oid FROM pg_roles WHERE rolname alice);pg_shdepend记录了跨库的共享依赖查出来的行数就是牵挂的数量。5.3 权限审计几条 SQL 看清谁有什么安全审计场景经常需要回答某账号到底能干什么。常用的几条查询-- 表级权限总览 SELECT grantee, table_schema, table_name, privilege_type FROM information_schema.role_table_grants WHERE grantee IN (grp_rw, app_rw) ORDER BY table_name, grantee; -- 角色成员关系 SELECT r.rolname AS role, m.rolname AS member, am.admin_option FROM pg_auth_members am JOIN pg_roles r ON r.oid am.roleid JOIN pg_roles m ON m.oid am.member; -- 角色属性快照 SELECT rolname, rolsuper, rolcreatedb, rolcreaterole, rolcanlogin, rolconnlimit, rolvaliduntil FROM pg_roles WHERE rolname NOT LIKE pg_% ORDER BY rolname; -- schema 级权限 SELECT nspname, nspacl FROM pg_namespace WHERE nspname public;审计时候的核心是最小权限核查生产库上不该有超出职责的超级用户不该有 NOLOGIN 的组角色却带着 LOGIN不该有VALID UNTIL已经过期的僵尸账号。我会把这些列成检查清单配合定期跑。5.4 角色与权限的备份恢复角色信息本身不在业务库里它存在全局的pg_authid不直接可读用pg_roles视图。用pg_dumpall的--roles-only就能只导角色# 只备份角色定义含密码哈希注意保管 pg_dumpall -U postgres --roles-only -f /backup/roles.sql # 恢复 psql -U postgres -f /backup/roles.sql导出的 roles.sql 里包含加密后的密码串这个文件的敏感程度等同于数据库密码本身权限别写成 644最少 600 且只给 DBA 账号读。另外pg_dump单库备份时会带上ALTER ... OWNER TO之类的语句恢复到新实例前记得先把角色建好否则恢复会报 owner 不存在的错可以配合--no-owner绕过。6. 常见问题与排查速查表6.1 典型报错与解决报错信息常见原因处理办法role xxx does not exist角色没建 / 连错实例\du确认检查是不是连到只读副本或别的端口password authentication failed密码错 / 加密方式不匹配重设密码检查password_encryption与pg_hba.conf的 METHODno pg_hba.conf entry for hosthba 规则没覆盖这个网段或角色加对应行pg_reload_conf()permission denied for schema publicschema USAGE/CREATE 没给GRANT USAGE/CREATE ON SCHEMA public TO ...permission denied for table xxx表级权限没给或没继承GRANT补齐检查是否 NOINHERIT 需要SET ROLEtoo many connections for role xxx达到角色连接上限提高CONNECTION LIMIT或排查连接泄漏role xxx cannot be dropped because some objects depend on it角色仍拥有对象/权限REASSIGN OWNEDDROP OWNED后再删role xxx is not permitted to log in角色没有 LOGIN 属性或已过期ALTER ROLE xxx LOGIN;检查rolvaliduntil这张表可以贴在工位上绝大多数权限相关的报错都能在上面找到线索。6.2 易踩的坑与实操心得几个我在实际项目里反复验证过的经验值得单独拎出来说。第一GRANT ON ALL TABLES IN SCHEMA只是快照。这句话值得重复一遍它不覆盖未来。所有靠它建立权限的团队迟早会被新表的权限问题咬到。正确的做法是对象级 GRANT 建立基线 ADP 兜住未来两条腿走路。第二NOINHERIT和组角色配合要小心。有个项目里为了安全给所有应用账号设了 NOINHERIT结果每次授权都有一部分不生效排查半天才发现得让应用启动后显式SET ROLE。安全固然重要但设计的时候一定要把应用侧怎么用想清楚否则徒增复杂度。第三角色名和操作系统用户名不要搞混。peer认证下本地连接看的是 OS 用户跟你数据库里建了什么角色没关系。曾经有同事在服务器上su - alice之后psql怎么都进不去其实就是 alice 这个 OS 用户对应的数据库角色不存在改scram-sha-256认证或者建同名角色就能通。第四密码里的特殊字符在 shell 里要转义。密码里有!、$的时候在 bash 里写psql -U user -W交互输入没问题但如果直接写进连接串或者环境变量记得用单引号包起来$会被 shell 当变量展开!在某些 shell 里会触发历史展开。这类问题排查起来特别费时间因为报错看起来像密码错了其实是 shell 给改了。第五定期用 SQL 巡检僵尸账号。我会在监控里跑这么一句任何有结果就告警SELECT rolname, rolvaliduntil FROM pg_roles WHERE rolvaliduntil IS NOT NULL AND rolvaliduntil now() AND rolcanlogin;这个能提前发现快过期的临时账号避免到期当天业务失联。6.3 从最小权限到权限回收的完整闭环最后把整套流程串一下方便记忆建号 → 归组 → 授权 → 审计 → 回收。每一步都有对应的工具CREATE ROLE/CREATE USER建GRANT 角色 TO 用户归组GRANT 权限 ON 对象授权information_schema和pg_catalog里的视图做审计权限变更或者离职时用REVOKE/REASSIGN OWNED/DROP OWNED回收。这套闭环跑通之后你会发现 PostgreSQL 处理人员变动其实很轻松——新人入职加一行GRANT grp_xxx TO 新账号老人离职跑两个清理语句剩下的历史包袱不会越积越厚。真正麻烦的是从头就没规划过角色体系、权限全都直接授到具体用户上的那种存量环境改造起来要一层一层剥。所以如果现在还在起步阶段我强烈建议一步到位把组角色这件事做对后面省下的时间不是一点点。我个人在这几年实际维护中的体会是权限管理最贵的从来不是技术难度而是纪律——确保每一张新表都走了统一授权流程确保每一次人员变动都在库里落了痕迹。工具和 SQL 只是把这个纪律执行下去的手段真正的门槛在于团队愿不愿意每季度花半天时间把上面那几条审计语句跑一遍。跑完之后心里有底出问题的时候也有据可查这个习惯一旦养成就再也不想回到凭印象管理权限的日子了。