深色模式
PostgreSQL 数据库、角色、权限与认证
概述
PostgreSQL 里最容易混的，不是 SQL 语法，而是实例、数据库、schema、角色、权限、认证规则这些对象到底各管什么。很多连接失败、权限不足、迁移脚本报错，最后都是这些边界没理顺。
这一篇不铺很大的配置清单，只讲最常用的部分：库怎么建、角色怎么拆、权限怎么给、pg_hba.conf 怎么读、postgresql.conf 里哪些连接项最值得先看。把这几件事收住，日常开发和部署已经会稳很多。
对象关系
- 实例：正在运行的 PostgreSQL 服务
- 数据库：实例里的一套逻辑数据库，比如
app - schema：数据库里的命名空间，默认常见的是
public - 角色：登录身份和权限主体，既可以是人，也可以是应用
最常见的误会是把“装好了 PostgreSQL”当成“业务库已经准备好了”。其实装好实例，只说明数据库服务跑起来了；业务数据库、业务角色、权限边界，后面还要自己建。
角色拆分
中小项目里，比较顺手的做法通常是把角色拆成这几类：
postgres：管理员，只做初始化和运维app_owner：业务库拥有者，负责迁移和建表app_rw：应用读写账号app_ro：只读账号，给报表、只读接口或排障使用
可以先这样建：
sql
CREATE ROLE app_owner LOGIN PASSWORD 'owner_pass';
CREATE ROLE app_rw LOGIN PASSWORD 'rw_pass';
CREATE ROLE app_ro LOGIN PASSWORD 'ro_pass';
CREATE DATABASE app OWNER app_owner;
GRANT CONNECT ON DATABASE app TO app_rw, app_ro;接着连到 app 库里，把 public schema 的权限收一收：
sql
\connect app
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
GRANT USAGE, CREATE ON SCHEMA public TO app_owner;
GRANT USAGE ON SCHEMA public TO app_rw, app_ro;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_rw;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_rw;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_ro;如果表是由 app_owner 创建的，还要把默认权限补上，不然新表出来后，app_rw 和 app_ro 还是拿不到权限：
sql
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_rw;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA public
GRANT USAGE, SELECT ON SEQUENCES TO app_rw;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA public
GRANT SELECT ON TABLES TO app_ro;如果项目还很小，暂时没必要拆到 app_owner、app_rw、app_ro 这么细，至少也别让应用长期使用超级用户。
权限层级
这是 PostgreSQL 新手最容易踩的地方之一。给了：
sql
GRANT CONNECT ON DATABASE app TO app_rw;只表示 app_rw 可以连进 app 这个数据库，不表示它已经能：
- 使用某个 schema
- 查询已有表
- 插入新数据
- 使用序列
所以授权通常至少要分 3 层看：
- 数据库级：能不能连进来
- schema 级：能不能使用这个命名空间、能不能创建对象
- 对象级：能不能查表、改表、用序列、执行函数
很多“明明已经授权了，为什么还报 permission denied”的问题，最后都是漏掉了其中一层。
认证规则
pg_hba.conf 决定哪些客户端可以连进来，以及使用什么认证方式。一个常见示例：
ini
# 本机管理员通过 Unix socket 登录
local all postgres peer
# 本地开发机访问业务库
host app app_owner 127.0.0.1/32 scram-sha-256
host app app_rw 127.0.0.1/32 scram-sha-256
host app app_ro 127.0.0.1/32 scram-sha-256
# Docker / 局域网应用访问业务库
host app app_rw 172.16.0.0/12 scram-sha-256
host app app_ro 172.16.0.0/12 scram-sha-256这几列依次表示：
- 连接类型：
local、host - 数据库：允许访问哪个数据库
- 角色：允许谁访问
- 地址范围：客户端来源地址
- 认证方式：如
peer、scram-sha-256
有两个规则非常重要：
pg_hba.conf从上往下匹配，命中第一条就停止- 规则越具体越应该写在前面
所以别先来一条大范围 host all all 0.0.0.0/0 ...，后面再写更细的规则，那些细规则根本轮不到生效。
认证方式
peer
只适合本机 Unix socket 登录，数据库会把操作系统用户名映射成角色名。管理员在数据库服务器本机登录时很方便。
scram-sha-256
这是目前更推荐的密码认证方式。新项目优先用它，和 password_encryption = scram-sha-256 搭配最自然。
md5
老项目里还会碰到，但已经更偏兼容用途。新项目没有特别理由，不建议再以它为默认选择。
trust
完全不校验密码。只适合极少数临时、本机、受控的开发场景，别把它留到线上，更别给公网入口配 trust。
连接配置
和连接、认证最相关的配置，通常是这几个：
listen_addresses
决定 PostgreSQL 监听哪些地址：
ini
listen_addresses = 'localhost'只允许本机访问时，用 localhost 就够了。Docker、局域网或别的机器要连接时，再按实际场景放开到具体地址或 '*'。
port
默认端口是 5432：
ini
port = 5432多实例部署时，数据库内部端口和容器外映射端口要分开看，别把这两个概念混掉。
password_encryption
新项目建议明确写上：
ini
password_encryption = scram-sha-256这样后面 ALTER ROLE ... PASSWORD ... 时，保存出来的密码哈希和认证方式就一致了。
ssl
postgresql.conf 里服务端可以控制是否启用 SSL。和它一起经常出现的，还有客户端连接参数里的：
text
sslmode=disable本地开发里这样写没问题，但仅限本地或受控内网。线上是否启用 SSL，取决于部署方式、代理层和安全要求，不能把开发环境的连接参数直接照搬过去。
已加载配置
不要只改文件，最好再让数据库自己说一遍当前用的是哪个文件：
sql
SHOW config_file;
SHOW hba_file;如果想直接看 pg_hba.conf 解析后的规则，可以查：
sql
SELECT line_number, type, database, user_name, address, auth_method, error
FROM pg_hba_file_rules;这张视图对排查规则顺序和格式错误很有帮助。
生效方式
改了 pg_hba.conf、日志类配置，很多时候可以先重载：
sql
SELECT pg_reload_conf();下面这些改动，通常要重启实例：
listen_addressesport- 其他需要 postmaster 级别重启的配置
在 Docker 里，这通常对应 docker compose restart postgres。别指望每种配置都能靠 reload 解决。
常见失误
应用直接用 postgres
开发时看起来省事，后面权限边界会一团糟。应用账号应该只拿到业务需要的权限。
只给了数据库权限，忘了 schema 和 sequence
PostgreSQL 的权限粒度比很多人想的细。能连进库，不代表能建表、读表、用序列。
pg_hba.conf 范围写得过大
本地开发为了方便可以适当放宽，线上一定要收紧来源地址。先明确网络边界，再决定放给谁。