postgres 用户与密码设置
PostgreSQL 安装后会创建一个名为 postgres 的操作系统用户与同名数据库超级用户，它是集群的最高权限入口。本节将介绍 postgres 用户的双重身份、密码设置方法、业务用户创建规范与权限回收。
前置知识
阅读本节前，建议先了解：命令行美化（pgcli）
基础概念
初学者最容易混淆的一点是：postgres 既是操作系统用户，也是数据库角色（Role）。两者名字相同，但处于不同层次：
| 层次 | 名称 | 作用 |
|---|---|---|
| 操作系统层 | postgres 系统用户 | 运行 postgres 服务进程，拥有数据目录 PGDATA 的属主身份 |
| 数据库层 | postgres 数据库角色 | 集群初始化时创建的超级用户（Superuser），可管理所有库与角色 |
| 数据库层 | app_user 业务角色 | 应用实际使用的低权限账号，只拥有业务库的必要权限 |
数据库服务进程以系统用户 postgres 的身份运行，因此它对该用户拥有的文件天然可信——这正是初始 pg_hba.conf 中本地连接使用 peer 或 trust 认证的原因。postgres 角色则是用来做管理的，绝不应该给应用程序使用。
| 术语 | 英文名 | 说明 |
|---|---|---|
| 角色 | Role | PostgreSQL 中用户与组的统一概念，可登录的角色即「用户」 |
| 超级用户 | Superuser | 绕过所有权限检查的角色，postgres 默认是超级用户 |
| 对等认证 | peer | 依据操作系统用户名判断数据库身份的本地认证方式 |
| 标识认证 | ident | 通过 ident 服务查询远端操作系统用户的认证方式 |
| 密码文件 | .pgpass | 客户端保存密码的文件，需 chmod 600 |
| 权限 | Privilege | 对对象（表、序列、schema）的操作许可 |
| 所有权 | Ownership | 对象的属主，隐含全部权限 |
语法与用法
创建角色与设置密码
sql
-- 创建可登录角色（LOGIN 才能连接）
CREATE ROLE app_user LOGIN PASSWORD 'Strong_Pass_2026';
-- 创建时直接授予部分属性
CREATE ROLE report_user LOGIN
PASSWORD 'Another_Strong_2026'
CONNECTION LIMIT 20
VALID UNTIL '2027-01-01';
-- 修改已有角色密码
ALTER ROLE app_user PASSWORD 'New_Strong_Pass_2026';
-- 让密码永不过期（默认即为 infinity，显式写出更清晰）
ALTER ROLE app_user VALID UNTIL 'infinity';
-- 删除角色（需先转移或删除其拥有的对象）
DROP ROLE IF EXISTS temp_user;sql
-- 设置 postgres 超级用户密码（在集群本机以 postgres 系统用户执行）
\password postgres
-- 或使用 SQL（密码会进入日志与历史，不推荐）
ALTER ROLE postgres PASSWORD 'Pg_Super_Strong_2026';bash
# 以 postgres 系统用户进入 psql（本地 peer 认证，无需密码）
sudo -u postgres psql
# 或显式指定角色
sudo -u postgres psql -d postgres -U postgres
# macOS（Homebrew 安装）：默认当前用户即超级用户
psql -d postgres角色属性一览
| 属性 | 语法 | 说明 |
|---|---|---|
LOGIN | CREATE ROLE x LOGIN | 允许连接数据库 |
SUPERUSER / NOSUPERUSER | ALTER ROLE x NOSUPERUSER | 是否绕过权限检查 |
CREATEDB / NOCREATEDB | ALTER ROLE x CREATEDB | 是否可创建数据库 |
CREATEROLE / NOCREATEROLE | ALTER ROLE x NOCREATEROLE | 是否可创建/修改角色 |
INHERIT / NOINHERIT | ALTER ROLE x INHERIT | 是否自动继承所属角色的权限 |
REPLICATION | ALTER ROLE x REPLICATION | 是否可发起物理复制连接 |
BYPASSRLS | ALTER ROLE x BYPASSRLS | 是否绕过行级安全策略 |
CONNECTION LIMIT | ALTER ROLE x CONNECTION LIMIT 20 | 最大并发连接数，-1 为不限 |
VALID UNTIL | ALTER ROLE x VALID UNTIL '...' | 账号到期时间 |
PASSWORD | ALTER ROLE x PASSWORD '...' | 设置密码，NULL 表示清除 |
PostgreSQL 14 起，
CREATEDB、CREATEROLE等权限不再隐含在SUPERUSER之外的其他属性中；CREATEROLE也不再自动获得对其他角色的管理权。给业务角色授权时不要顺手给CREATEROLE。
授权与回收
sql
-- 库级：允许连接与创建对象
GRANT CONNECT ON DATABASE demo TO app_user;
GRANT USAGE, CREATE ON SCHEMA public TO app_user;
-- 表级：读写业务表
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
-- 序列级：使用自增序列（比表权限更容易被遗忘）
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user;
-- 让后续新建对象自动带上权限（对当前角色创建的对象生效）
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT USAGE, SELECT ON SEQUENCES TO app_user;
-- 只读账号：仅授予查询能力
GRANT CONNECT ON DATABASE demo TO report_user;
GRANT USAGE ON SCHEMA public TO report_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO report_user;sql
-- 回收权限：默认是 RESTRICT，有依赖时会报错，可加 CASCADE
REVOKE CREATE ON SCHEMA public FROM app_user;
-- 回收所有表的写权限（打造只读角色）
REVOKE INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public FROM app_user;
-- 查看某角色的全部权限
\dp public.users
\ddpsql
-- PostgreSQL 15 起 public schema 默认不再允许所有人 CREATE
-- 显式确认当前状态（新集群应为 OWNER 为 pg_database_owner）
SELECT nspname, nspowner::regrole AS owner, nspacl
FROM pg_namespace
WHERE nspname = 'public';
-- 若仍是旧行为，显式收紧
REVOKE CREATE ON SCHEMA public FROM PUBLIC;详细说明
认证方式如何决定「要不要密码」
postgres 密码是否有用，取决于 pg_hba.conf 中该连接匹配到的认证方法：
| 认证方法 | 是否需要密码 | 典型场景 |
|---|---|---|
trust | 不需要，任何人可连 | 仅限隔离的本地开发环境 |
peer | 不需要，凭系统用户身份 | Linux 上 sudo -u postgres psql |
ident | 不需要，凭 ident 服务 | 早期内网部署 |
md5 | 需要（MD5 摘要） | 兼容老客户端 |
scram-sha-256 | 需要（SCRAM，默认推荐） | 生产环境唯一正确选择 |
cert | 需要客户端证书 | 双向 TLS 场景 |
reject | 直接拒绝 | 黑名单 |
这意味着：如果你用 peer 认证连本地，改 postgres 密码不会影响你的登录；而远程连接用 scram-sha-256 时，密码就是唯一的门槛。
sql
-- 查看密码加密方式（password_encryption 决定新建密码的存储算法）
SHOW password_encryption;
-- scram-sha-256
-- 查看角色的密码有效期与是否已设置密码
SELECT rolname, rolcanlogin, rolsuper, rolvaliduntil,
(rolpassword IS NOT NULL) AS has_password,
rolpassword LIKE 'SCRAM-SHA-256%' AS is_scram
FROM pg_authid
WHERE rolname IN ('postgres', 'app_user');bash
# 配置密码存储算法（postgresql.conf），改后需重载
# password_encryption = scram-sha-256
sudo systemctl reload postgresql密码管理的三条铁律
- 密码只走
scram-sha-256：md5已被证明不适合作为长期方案，且旧客户端可能无法协商。设置password_encryption = scram-sha-256后，ALTER ROLE ... PASSWORD才会以 SCRAM 格式落库。 - 应用永不使用超级用户：
postgres角色能DROP DATABASE、能读所有表、能创建新角色。应用被注入或配置泄露时，损失是全局的。 - 密码不进代码与命令行：命令行参数会进入 shell 历史与
ps输出；代码里的明文会进 Git。改用PGPASSWORD、~/.pgpass或密钥管理服务。
bash
# 正确：用 .pgpass，权限必须是 600，否则 libpq 会直接忽略该文件
cat > ~/.pgpass <<'EOF'
127.0.0.1:5432:demo:app_user:Strong_Pass_2026
db.example.com:5432:demo:app_user:Strong_Pass_2026
EOF
chmod 600 ~/.pgpass
# 验证：不再需要交互输入密码
psql -h 127.0.0.1 -U app_user -d demo -c "SELECT current_user;"bash
# 错误！密码出现在命令行与历史中，且可能被 ps 捕获
# 不推荐
psql "postgresql://app_user:Strong_Pass_2026@127.0.0.1:5432/demo"
# 错误！把密码写进代码仓库
# 不推荐
echo 'DB_PASSWORD=Strong_Pass_2026' >> app/.env && git add app/.env业务角色的最小权限模型
一个可落地的权限模型通常包含三类角色：
| 角色 | 权限 | 用途 |
|---|---|---|
app_owner | 拥有 schema 与表，可 DDL | 迁移脚本执行者（CI/CD 专用） |
app_user | 表的 DML（增删改查）+ 序列 USAGE | 应用运行时账号 |
report_user | 仅 SELECT | 报表、BI、排查问题 |
sql
-- 完整落地示例：在 demo 库中建立三层角色
-- 1) 迁移专用账号（拥有对象，可执行 DDL）
CREATE ROLE app_owner LOGIN PASSWORD 'Owner_Pass_2026';
CREATE SCHEMA IF NOT EXISTS app AUTHORIZATION app_owner;
-- 2) 应用运行账号（只做 DML）
CREATE ROLE app_user LOGIN PASSWORD 'App_Pass_2026' CONNECTION LIMIT 100;
-- 3) 只读账号
CREATE ROLE report_user LOGIN PASSWORD 'Report_Pass_2026';
-- 授权：让 app_owner 建的对象默认给 app_user 权限
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
GRANT USAGE, SELECT ON SEQUENCES TO app_user;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
GRANT SELECT ON TABLES TO report_user;
GRANT USAGE ON SCHEMA app TO app_user, report_user;排查「连不上」的通用顺序
| 现象 | 首要怀疑 | 验证方式 |
|---|---|---|
password authentication failed | 密码错 / pg_hba.conf 方法不匹配 | 查看服务端日志中的详细行 |
no pg_hba.conf entry for host | pg_hba.conf 没有覆盖该来源 | SELECT * FROM pg_hba_file_rules; |
role "xxx" does not exist | 角色未创建 / 连到了另一个集群 | \du |
permission denied for table xxx | 缺对象权限 | \dp xxx |
too many connections | 超出连接上限 | 见下文查询 |
FATAL: remaining connection slots... | 连接数耗尽 | 检查应用连接池 |
sql
-- 查看当前连接使用情况与各角色连接数
SELECT usename, count(*) AS conns, state
FROM pg_stat_activity
GROUP BY usename, state
ORDER BY conns DESC;
-- 查看角色级连接上限与全局上限
SELECT rolname, rolconnlimit FROM pg_roles WHERE rolcanlogin;
SHOW max_connections;实战示例
示例一：把默认的「裸」集群改造为可上线的账号体系
bash
# 第 1 步：用 peer 认证进入，创建业务角色（此时还没有任何密码风险）
sudo -u postgres psql -d postgressql
-- 第 2 步：为 postgres 设置强密码（后续改 pg_hba 后可远程管理）
\password postgres
-- 第 3 步：创建业务库与角色
CREATE DATABASE demo OWNER app_owner;
CREATE ROLE app_owner LOGIN PASSWORD 'Owner_Pass_2026';
CREATE ROLE app_user LOGIN PASSWORD 'App_Pass_2026' CONNECTION LIMIT 100;
CREATE ROLE report_user LOGIN PASSWORD 'Report_Pass_2026';
-- 第 4 步：在 demo 库中授权
\c demo
GRANT CONNECT ON DATABASE demo TO app_user, report_user;
GRANT USAGE ON SCHEMA public TO app_user, report_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO report_user;
GRANT SELECT, USAGE ON ALL SEQUENCES IN SCHEMA public TO report_user;bash
# 第 5 步：用业务账号验证「能连、但权限受限」
psql "host=127.0.0.1 dbname=demo user=app_user" -c "\conninfo"
# 验证超级用户操作被拒绝（应当报错，这才是正确的）
psql "host=127.0.0.1 dbname=demo user=app_user" -c "CREATE DATABASE hack;"
# ERROR: permission denied to create database示例二：审计并清理多余的超级用户
sql
-- 列出所有超级用户（正常应只有 postgres 与极少数运维账号）
SELECT rolname, rolcanlogin, rolcreatedb, rolcreaterole, rolvaliduntil
FROM pg_roles
WHERE rolsuper
ORDER BY rolname;
-- 列出长期未使用的可登录角色（需先启用连接统计）
SELECT r.rolname,
coalesce(s.last_use, '从未使用') AS last_use
FROM pg_roles AS r
LEFT JOIN (
SELECT usename, max(state_change)::date AS last_use
FROM pg_stat_activity
GROUP BY usename
) AS s ON s.usename = r.rolname
WHERE r.rolcanlogin
ORDER BY s.last_use NULLS LAST;sql
-- 降权：把误授的超级用户收回（保留 LOGIN，去掉一切特权）
ALTER ROLE legacy_admin NOSUPERUSER NOCREATEDB NOCREATEROLE NOREPLICATION;
-- 直接禁用账号（保留角色以便审计，但不能登录）
ALTER ROLE departed_user NOLOGIN;
-- 硬删除（先处理其拥有的对象）
REASSIGN OWNED BY departed_user TO app_owner;
DROP OWNED BY departed_user;
DROP ROLE departed_user;bash
# 修改后强制重载配置（若涉及 pg_hba.conf 或角色认证缓存）
sudo systemctl reload postgresql
# 用重建连接的方式验证（旧连接仍持有旧权限，必须重连）
psql -h 127.0.0.1 -U app_user -d demo -c "SELECT current_user, session_user;"注意事项
postgres角色不要用于应用：无论多小的服务，都应为它建独立账号。共享超级用户会让「谁改的数据」无法追溯。- 密码不会即时失效：
ALTER ROLE ... PASSWORD不会踢掉已建立的连接。改密后需主动断开旧连接（见下方 SQL）才能让新密码生效于下次认证。 role与user的差别：CREATE USER等价于CREATE ROLE ... LOGIN。日常用CREATE ROLE更明确。- 对象所有权决定删除权：
DROP ROLE前必须处理其拥有的对象，否则报错。REASSIGN OWNED+DROP OWNED是标准清理组合。 publicschema 的历史包袱：PostgreSQL 15 之前PUBLIC拥有CREATE权限，这是很多「为什么普通用户能建表」的根因。新集群已修正，但升级上来的库仍可能残留旧权限。.pgpass权限严格：权限不是600时 libpq 会静默忽略，表现为「配置了但还要输密码」。Windows 上该文件名为%APPDATA%\postgresql\pgpass.conf。- 别在 SQL 里写明文密码：
ALTER ROLE ... PASSWORD 'xxx'会记录到服务端日志（log_statement=all时）与psql历史。交互场景优先用\password。
sql
-- 改密后断开该角色的旧连接（谨慎：会中断业务，需配合重连）
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE usename = 'app_user'
AND pid <> pg_backend_pid();- 连接上限是全局资源：给角色设
CONNECTION LIMIT只能防单个角色抢占，真正的保护是应用侧连接池（如 PgBouncer）与max_connections的合理配置。
最佳实践
- 三层账号模型：
app_owner（DDL，仅 CI 使用）、app_user（DML，应用使用）、report_user（只读）。三者密码独立、互不复用。 - 密码策略：长度 ≥ 20 位、由密钥管理服务生成与轮换；
VALID UNTIL为运维账号设到期时间，业务账号用infinity但走定期轮换。 - 强制 SCRAM：
password_encryption = scram-sha-256，并在pg_hba.conf中只允许scram-sha-256（或cert）。 - 默认权限一步到位：用
ALTER DEFAULT PRIVILEGES保证新建表自动继承权限，避免「上线后发现新表没权限」。 - 权限也纳入版本控制：把角色与授权语句写进迁移脚本，与表结构一起评审、一起回滚。
- 定期审计：把「超级用户清单」「无密码角色」「长期不登录角色」「超出连接上限」做成巡检 SQL，纳入日常检查。
sql
-- 巡检模板：一次跑完四项账号安全检查
-- 1) 超级用户清单
SELECT 'superusers' AS check_item, string_agg(rolname, ', ') AS detail
FROM pg_roles WHERE rolsuper;
-- 2) 可登录但无密码的角色
SELECT 'no_password' AS check_item, string_agg(rolname, ', ') AS detail
FROM pg_authid WHERE rolcanlogin AND rolpassword IS NULL;
-- 3) 密码非 SCRAM 的角色
SELECT 'weak_password_algo' AS check_item, string_agg(rolname, ', ') AS detail
FROM pg_authid WHERE rolpassword IS NOT NULL AND rolpassword NOT LIKE 'SCRAM-SHA-256%';
-- 4) 当前连接数占用
SELECT 'connections' AS check_item,
count(*)::text || ' / ' || current_setting('max_connections') AS detail
FROM pg_stat_activity;下一节
继续学习：pg_hba.conf 访问控制