PostgreSQL 权限体系ACL RLS【一、权限体系总览】PostgreSQL 权限分两层ACL → 对象级能不能访问这张表RLS → 行级能访问表里的哪几行生效顺序先检查 ACL → 再检查 RLS → 最终返回数据【二、ACL 核心概念】ACLAccess Control List控制谁对什么对象有什么权限。• Owner创建者拥有全部权限可授权/回收不可被 REVOKE 剥夺• Superuser绕过所有 ACL 检查• 普通角色严格匹配 ACL无权限则报错• PUBLIC伪角色代表所有用户包括未来创建的用户【三、权限缩写速查表】┌─────────────┬─────────┬────────────────────────────────────┐ │ 权限 │ 缩写 │ 适用对象 │ ├─────────────┼─────────┼────────────────────────────────────┤ │ SELECT │ r │ 表、视图、序列、大对象 │ │ INSERT │ a │ 表、视图 │ │ UPDATE │ w │ 表、视图、列、大对象 │ │ DELETE │ d │ 表、视图 │ │ TRUNCATE │ D │ 表 │ │ REFERENCES │ x │ 表、列外键引用 │ │ TRIGGER │ t │ 表 │ │ EXECUTE │ X │ 函数、过程 │ │ USAGE │ U │ 模式、序列、类型、域 │ │ CREATE │ C │ 模式、数据库 │ │ CONNECT │ c │ 数据库 │ │ TEMPORARY │ T │ 数据库创建临时表 │ └─────────────┴─────────┴────────────────────────────────────┘ ALL PRIVILEGES表 arwdDxt * 可转授WITH GRANT OPTION【四、ACL 格式解析】格式{granteeprivs/grantor,...} 示例 {normal_userar*/test1} -- normal_user 有 SELECTINSERT 且可转授 {w/test1} -- PUBLIC 有 UPDATE由 test1 授予 {test1arwdDxt/test1} -- owner 拥有全部权限 字段说明 grantee被授权角色空 PUBLIC privs权限缩写串 *紧跟在权限后表示 WITH GRANT OPTION grantor授权角色【五、常用授权 / 回收语法】-- 5.1 表级授权GRANT SELECT, INSERT, UPDATE ON TABLE t1 TO u1;GRANT ALL PRIVILEGES ON TABLE t1 TO u1 WITH GRANT OPTION;GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_role;-- 5.2 列级授权GRANT SELECT (col1, col2), UPDATE (col3) ON TABLE t1 TO u1;-- 5.3 Schema 级GRANT USAGE, CREATE ON SCHEMA public TO u1;GRANT ALL ON SCHEMA hr TO hr_admin;-- 5.4 数据库级GRANT CONNECT, CREATE, TEMPORARY ON DATABASE testdb TO u1;-- 5.5 序列级GRANT USAGE, SELECT, UPDATE ON SEQUENCE seq1 TO u1;-- 5.6 函数级GRANT EXECUTE ON FUNCTION func1(int) TO u1;-- 5.7 默认权限自动应用到该用户未来新建的对象ALTER DEFAULT PRIVILEGES IN SCHEMA publicGRANT SELECT, INSERT ON TABLES TO app_role;ALTER DEFAULT PRIVILEGES FOR ROLE adminREVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;-- 5.8 回收权限REVOKE SELECT ON TABLE t1 FROM u1;REVOKE GRANT OPTION FOR UPDATE ON TABLE t1 FROM u1; -- 只回收转授权REVOKE ALL PRIVILEGES ON TABLE t1 FROM u1;-- 5.9 转移所有者owner 权限不可回收只能转移ALTER TABLE t1 OWNER TO new_owner;ALTER SCHEMA hr OWNER TO new_owner;【六、查看权限】-- 6.1 psql 元命令\dp [pattern] -- 表/视图权限自动格式化 ACL\dn [pattern] -- Schema 权限\df [pattern] -- 函数权限\l [pattern] -- 数据库权限\ddp -- 默认权限\du [pattern] -- 角色/成员关系-- 6.2 SQL 查询表 ACLSELECT n.nspname AS schema, c.relname AS table, c.relacl AS aclFROM pg_class cJOIN pg_namespace n ON c.relnamespace n.oidWHERE c.relkind r AND n.nspname NOT IN (pg_catalog,information_schema);-- 6.3 SQL 查询用户权限汇总SELECT * FROM information_schema.table_privileges WHERE grantee u1;SELECT * FROM information_schema.column_privileges WHERE grantee u1;-- 6.4 SQL 查询角色继承SELECT r.rolname role, m.rolname memberFROM pg_auth_members amJOIN pg_roles r ON am.roleid r.oidJOIN pg_roles m ON am.member m.oid;-- 6.5 解析 ACL 为可读格式SELECT relname,(aclexplode(relacl)).*FROM pg_class WHERE relname t1;【七、RLS 行级安全策略】7.1 核心概念• 粒度行级精确到每一行数据• 前提表必须 ALTER TABLE ... ENABLE ROW LEVEL SECURITY• 优先级ACL 之后生效先过 ACL再过 RLS• Owner/Superuser 默认绕过 RLS除非 FORCE7.2 启用与创建-- 启用 RLSALTER TABLE employees ENABLE ROW LEVEL SECURITY;-- 强制 owner 也遵守可选ALTER TABLE employees FORCE ROW LEVEL SECURITY;-- 创建策略CREATE POLICY emp_isolation ON employeesFOR ALL -- ALL / SELECT / INSERT / UPDATE / DELETETO app_user -- 对哪些角色生效省略 所有角色USING (department current_user) -- 控制可见行SELECT/UPDATE/DELETEWITH CHECK (department current_user); -- 控制可插入/更新行7.3 策略类型• PERMISSIVE默认多个策略之间是或关系满足任一即可• RESTRICTIVE多个策略之间是与关系必须全部满足示例CREATE POLICY p1 ON t1 USING (true); -- PERMISSIVECREATE POLICY p2 ON t1 AS RESTRICTIVE -- RESTRICTIVEUSING (col1 0);7.4 特殊权限-- 给用户 BYPASSRLS 权限不推荐常规使用ALTER USER admin WITH BYPASSRLS;-- 会话级临时关闭superuser 可用SET row_security off;7.5 查看 RLS-- 查看启用 RLS 的表SELECT n.nspname, c.relname, c.relforcerowsecurityFROM pg_class cJOIN pg_namespace n ON c.relnamespace n.oidWHERE c.relrowsecurity true;-- 查看策略定义SELECT schemaname, tablename, policyname, permissive, roles, cmd, qual, with_checkFROM pg_policies;【八、ACL vs RLS 深度对比】┌─────────────────────┬──────────────────────────┬──────────────────────────┐ │ 对比维度 │ ACL │ RLS │ ├─────────────────────┼──────────────────────────┼──────────────────────────┤ │ 控制粒度 │ 对象级表/列 │ 行级某几行数据 │ │ 控制范围 │ 能否访问整张表 │ 能访问表中的哪些行 │ │ 生效时机 │ 对象访问前 │ 查询执行时ACL 之后 │ │ 核心语法 │ GRANT / REVOKE │ CREATE POLICY │ │ 存储位置 │ pg_class.relacl 等 │ pg_policy 系统表 │ │ 适用场景 │ 模块/功能隔离 │ 多租户、数据隔离、SaaS │ │ 性能影响 │ 极小权限检查 │ 中等策略表达式过滤 │ │ 列级控制 │ 支持 │ 不支持仅行级 │ │ 函数/过程 │ 支持 │ 不支持仅表/视图 │ │ 转授机制 │ WITH GRANT OPTION │ 无策略绑定角色 │ └─────────────────────┴──────────────────────────┴──────────────────────────┘【九、权限继承层次】数据库 (CONNECT / CREATE / TEMPORARY)└── Schema (USAGE / CREATE)└── 表 (SELECT / INSERT / UPDATE / DELETE / TRUNCATE / REFERENCES / TRIGGER)└── 列 (SELECT / UPDATE / REFERENCES)└── 行 (RLS Policy USING / WITH CHECK)关键规则• 每一层都必须有权限才能访问• 有表 SELECT 但没 Schema USAGE → 访问不了• 有 ACL 但没 RLS 策略匹配 → 返回空结果集不是报错【十、最佳实践】1. 最小权限原则只授予必需权限PUBLIC 默认不给 DELETE / TRUNCATE / CREATE2. 角色层级管理用角色封装权限集再 GRANT role TO user避免重复授权3. 默认权限ALTER DEFAULT PRIVILEGES 确保新建对象自动继承权限策略4. RLS 强制启用核心表建议 ENABLE FORCE ROW LEVEL SECURITY防止 owner 误操作5. 定期审计SELECT * FROM information_schema.table_privileges定期清理离职人员/废弃角色的残留权限6. 避免 PUBLIC 高危权限REVOKE CREATE ON SCHEMA public FROM PUBLIC; -- 安全加固