SQL 用户与角色高级管理

create user 是 create role 的语法糖，唯一区别是默认带 login 属性；角色本质是权限容器，可登录、授权、继承，三者不互斥；postgresql 15+ 已将 create user 标记为遗留语法，推荐统一使用 create role。

PostgreSQL 中 CREATE ROLE 与 CREATE USER 到底有什么区别？

本质没区别：CREATE USER 就是 CREATE ROLE 的语法糖，唯一默认差异是 LOGIN 属性 —— CREATE USER 默认带 LOGIN，CREATE ROLE 默认不带。

实际操作中别被名字误导：所谓 “user” 只是能登录的 role；所谓 “role” 是权限容器，可登录、可被授权、可继承其他 role，三者不互斥。

• CREATE USER alice; 等价于 CREATE ROLE alice WITH LOGIN;
• CREATE ROLE app_reader; 创建的是不能登录的纯权限组，适合赋给多个用户
• 想让一个 role 登录，必须显式加 WITH LOGIN，否则即使有密码也连不上
• PostgreSQL 15+ 开始，CREATE USER 已标记为“legacy syntax”，文档明确建议统一用 CREATE ROLE

如何安全地给应用账号授予权限而不暴露系统表？

直接 GRANT ALL ON database 或 GRANT ALL ON SCHEMA public 是常见错误，会导致应用账号能删表、改函数、甚至查 pg_authid 密码哈希（虽加密但不该可见）。

最小权限原则在这里不是口号：应用账号只需要对特定 schema 下的几张表做 select/INSERT/UPDATE，就只给这些。

• 先 GRANT CONNECT ON DATABASE mydb TO app_user;（否则连库都连不上）
• 再 GRANT USAGE ON SCHEMA billing TO app_user;（不加这句，哪怕表权限给了也查不了）
• 对具体表授予权限：GRANT SELECT, INSERT ON table billing.invoices TO app_user;
• 避免 GRANT ALL PRIVILEGES —— 它包含 TRUNCATE 和 REFERENCES，后者可能被用于外键探测表结构
• 如需批量授权，用 GRANT SELECT ON ALL TABLES IN SCHEMA billing TO app_user;，但要注意：该语句只对执行时已存在的表生效，新表需配合 ALTER default PRIVILEGES

ALTER DEFAULT PRIVILEGES 为什么总不生效？

它只影响“之后创建”的对象，且作用域极敏感：必须在目标 schema 下执行，且要指定 for ROLE 或 IN SCHEMA，否则默认作用于当前执行角色所拥有的 future objects —— 这和你想授权的对象往往不匹配。

典型失效场景：dba 在 postgres 用户下执行了默认授权，但开发用 dev_role 建表，结果新表权限还是空的。

• 正确做法：在目标 schema 中，以将拥有对象的角色身份执行：

  SET search_path = billing;<br>ALTER DEFAULT PRIVILEGES IN SCHEMA billing GRANT SELECT ON TABLES TO app_user;

• 如果表由 dev_role 创建，就得写成：ALTER DEFAULT PRIVILEGES FOR ROLE dev_role IN SCHEMA billing GRANT SELECT ON TABLES TO app_user;
• 它不追溯已有对象，补授权仍需手动 GRANT
• 注意：对 SEQUENCE、function 等也要单独设置，默认不联动

角色继承与 SET ROLE 的真实行为

PostgreSQL 的角色继承不是“临时切换身份”，而是权限叠加。一旦你 SET ROLE r2，当前会话就只能使用 r2 及其继承链上的权限，原角色权限被暂时屏蔽 —— 这点和很多人的直觉相反。

生产环境调试时容易误判权限问题，以为 “我有 r1 权限，又 SET ROLE r2，应该还能用 r1 的表”，其实不能。

• SET ROLE r2; 后，CURRENT_ROLE 变成 r2，SESSION_USER 仍是原始登录用户，但权限仅来自 r2 及其 INHERIT 的 role
• 想恢复原始权限，得 RESET ROLE;，不是 SET ROLE TO DEFAULT（后者无效）
• 检查当前有效权限用：SELECT * FROM pg_roles WHERE rolname = CURRENT_ROLE;，再看 rolinherit 字段是否为 t
• 避免在应用连接池里滥用 SET ROLE：连接复用时状态残留可能导致权限错乱，更稳妥的方式是用不同连接串对应不同角色

事情说清了就结束。角色系统看着简单，但权限叠加、默认权限作用域、SET ROLE 的屏蔽逻辑，三个地方一碰就容易出哑巴错——尤其是跨角色建表后查不到数据，十次有八次是 USAGE ON SCHEMA 漏了或者 DEFAULT PRIVILEGES 没对准角色。