SQL如何管理角色的权限_CREATE ROLE与多用户权限统一分配-SQL-PHP中文网

create role 后权限不生效，因角色需显式授予对象权限（如 grant select on pg_stat_activity to role_monitor），且用户须重新连接或重置会话才能生效。

sql如何管理角色的权限_create role与多用户权限统一分配

CREATE ROLE 之后权限不生效？先检查 `GRANT` 是否漏了

角色创建只是第一步，CREATE ROLE 本身不赋予任何权限。常见错误是建完角色就直接给用户 GRANT 角色，但忘了把底层对象权限（比如表的 SELECT）先授给该角色。

典型场景：想让运维组统一有 pg_stat_activity 查询权，建了 role_monitor，却没执行：

GRANT SELECT ON pg_stat_activity TO role_monitor;

结果用户虽然 GRANT role_monitor TO alice 成功，查询时仍报 permission denied for relation pg_stat_activity。

角色必须显式获得对象权限，不能靠“继承”或“默认”自动获取
PostgreSQL 不支持角色嵌套继承权限（如 A 角色 GRANT 给 B，B 再 GRANT 给用户，A 的权限不会穿透到用户）
GRANT 权限时注意目标对象是否在当前 search_path 中，否则需写全名如 public.my_table

多个用户统一分配权限，用 `GRANT ... TO ROLE` 而非逐个 `GRANT ... TO USER`

批量管理的本质是「权限集中到角色，用户只绑定角色」。一旦改权限，只需更新角色，不用遍历所有用户。

比如新增一个报表组，要给 12 个用户读取 5 张表 + 执行 3 个函数：

CREATE ROLE role_report;
GRANT SELECT ON t_sales, t_customers, t_products, t_regions, t_dates TO role_report;
GRANT EXECUTE ON FUNCTION f_daily_summary(), f_monthly_trend(), f_top_items() TO role_report;
GRANT role_report TO user_a, user_b, user_c, ...;

后续加表或改函数权限，只动第一段 GRANT；删用户，只 REVOKE role_report FROM user_x。

避免用 GRANT ... TO GROUP（已废弃），PostgreSQL 8.1+ 全部用 ROLE
角色可被 INHERIT（默认）或 NOINHERIT，普通业务用户建议保持 INHERIT，否则每次要用 SET ROLE
角色名区分大小写，但未加引号时会转小写，CREATE ROLE "Admin" 和 CREATE ROLE admin 是两个角色

权限变更后用户查不到新数据？可能卡在事务或连接缓存里

PostgreSQL 的权限检查发生在语句执行时，但某些情况会让变更“看起来没生效”：

用户已开启长事务，且在权限变更前执行过 SELECT —— 这条语句不会失败，但新权限对它无效；下次新查询才生效
连接池（如 pgbouncer）复用旧连接，而 SET ROLE 或权限变更只对当前会话有效，重启连接才能刷新角色上下文
用户用了 SET ROLE role_name 切换身份，但没 RESET ROLE，后续操作仍以该角色权限运行，容易误判权限范围

验证方法：连上后立刻执行 SELECT current_role, session_user;，再试目标语句。如果权限刚加完，别急着测，先断开重连或新开 psql 会话。

用 `REVOKE` 收权时，小心级联失效和依赖残留

REVOKE 不是简单逆向 GRANT。例如你曾用 GRANT ALL PRIVILEGES ON TABLE t_log TO role_audit，后来想只保留 SELECT，不能只 REVOKE INSERT, UPDATE, DELETE —— 因为 ALL 是原子授权，部分回收需先 REVOKE ALL，再重新 GRANT SELECT。

更隐蔽的问题是权限依赖：若 role_admin 拥有 CREATE 权限并建过函数，之后 REVOKE CREATE ON SCHEMA public FROM role_admin，不影响已有函数，但函数若用到其他被撤权的对象（比如它内部 SELECT 一张已被收回权限的表），运行时报错会指向函数体而非调用处。