SQL 用户与角色管理最佳实践

新建用户连不上数据库因未设密码或pg_hba.conf无匹配规则；需显式设密码并配置认证规则。grant connect仅允连接，还需grant usage on schema及表级权限才能访问数据。

CREATE USER 时为什么连不上数据库

新建用户后 psql -U newuser -d mydb 报 password authentication failed，大概率是没设密码，或没配 pg_hba.conf 规则。

postgresql 默认不给新用户任何连接权限，也不自动启用密码认证。必须显式指定密码，并确保 pg_hba.conf 中有匹配的行（比如 host all all 0.0.0.0/0 md5），且 reload 配置（pg_ctl reload 或 select pg_reload_conf()）。

• CREATE USER 不带 PASSWORD 子句 → 用户无密码，无法通过 md5 或 scram-sha-256 认证
• 用 CREATE ROLE ... LOGIN PASSWORD 'xxx' 更明确，USER 是 ROLE 的语法糖
• 本地连接（peer 或 trust）可能绕过密码，但生产环境不该依赖这个

GRANT CONNECT ON database 和 GRANT USAGE ON SCHEMA 的区别

用户能连上库，却执行 SELECT * FROM users 报 permission denied for table users，说明只给了库级连接权，没给模式和对象权限。

CONNECT 只允许建立连接；USAGE 在 SCHEMA 上才允许访问其下的表、视图等；真正读写还得单独 GRANT SELECT、INSERT 等。

• 缺 GRANT USAGE ON SCHEMA public TO myuser → 即使表存在，也看不到它（dt 不显示）
• public 模式不是默认可读的，新用户对它完全无权限，除非显式授权
• 想批量授权：先 GRANT USAGE ON SCHEMA public TO myrole，再 GRANT SELECT ON ALL TABLES IN SCHEMA public TO myrole，并用 ALTER default PRIVILEGES 覆盖后续新建表

如何避免 accidentally DROP OWNED BY 或 REASSIGN OWNED 的误操作

执行 DROP OWNED BY olduser 后，整个业务 schema 消失，因为该用户拥有函数、序列、表等——这是最常导致线上事故的权限操作之一。

这类命令不加确认、不可回滚，且会级联删除所有归属对象。别在生产库直接跑，尤其别用脚本批量处理未验证的用户名。

• 先查清归属：SELECT * FROM pg_depend WHERE refobjid = 'olduser'::regrole; 或更安全地用 pg_dump --schema-only --no-owner 看导出结构
• REASSIGN OWNED BY olduser TO newuser 比 DROP OWNED 温和，但依然要确保 newuser 已存在且有对应权限
• 运维账号应禁用 CREATEROLE 和 CREATEDB，普通应用账号只授最小数据权限，不授所有权

PostgreSQL 15+ 的 row level security 与角色权限怎么共存

开了 RLS 策略后，用户仍报 permission denied for table，不是策略没生效，而是根本没获得基础的 SELECT 权限——RLS 不替代 GRANT，它只在已有权限基础上做行级过滤。

必须先 GRANT SELECT ON accounts TO analyst，再在 accounts 表上 ENABLE ROW LEVEL SECURITY 并定义策略，否则策略压根不触发。

• 策略函数里用 CURRENT_ROLE 或 current_setting('app.user_id', true) 获取上下文，别硬编码角色名
• using 策略控制 SELECT/UPDATE/delete 可见行，WITH CHECK 控制 INSERT/UPDATE 可写入行，二者逻辑不同，别混用
• 超级用户默认绕过 RLS，测试时要用普通角色连接，或临时 SET session AUTHORIZATION

权限系统是分层的：连接 → 库 → 模式 → 表 → 行 → 列。漏掉任意一层，就会卡在“看不见”或“不能动”。最常被跳过的，是 USAGE ON SCHEMA 这一层。