1949 字
10 分钟
PostgreSQL 用户、角色与权限管理
2026-11-15

先纠正一个概念:PostgreSQL 只有角色#

上一篇 《PostgreSQL 安装与初始配置》 里我们建过 appuser,用的命令是 CREATE USER。但在 PostgreSQL 的世界里,根本不存在与角色分开的「用户」概念:用户就是带 LOGIN 属性的角色。CREATE USER 只是 CREATE ROLE ... WITH LOGIN 的语法糖。理解这一点,后面的权限继承、组角色才不会绕晕。

为什么这套设计值得认真学?因为权限给错是数据库事故的一大来源:应用拿着超级用户跑,被 SQL 注入后等于把整个数据库双手奉上;离职同事的账号没回收,成了永久后门。所以这一篇的核心原则只有一句:每个使用者(人或程序)一个独立账号,只给它完成工作所必需的最小权限。

角色的属性#

创建角色时能控制的属性,决定了这个账号的「权力边界」:

CREATE ROLE appuser WITH
LOGIN -- 允许登录(没有它就只是个权限容器,即"组")
PASSWORD 'strong_password'
NOSUPERUSER -- 不是超级用户(默认就是,显式写出来更清楚)
NOCREATEDB -- 不能建库
NOCREATEROLE -- 不能建角色、不能给自己提权
CONNECTION LIMIT 20 -- 最多同时 20 个连接
VALID UNTIL '2027-01-01'; -- 到期自动失效,适合临时账号

几个属性的红线:

  • SUPERUSER 绕过一切权限检查,除了 postgres 本人,任何业务账号、应用账号都不许是超级用户。日常需要超级权限时临时用 postgres,不要图省事给应用开。
  • CREATEROLE 看似无害,实则接近超级用户:能建角色的人可以建出高权限角色或改动他人属性。只给 DBA。
  • 密码策略上,PostgreSQL 本身不做复杂度校验,强度靠流程把关;配合 pg_hba.conf 的 scram-sha-256 认证(上一篇讲过)使用。

查看现有角色:psql 里用 \du(下一篇 《psql 常用命令与操作实战》 会系统讲 psql),或查系统目录:

SELECT rolname, rolsuper, rolcanlogin, rolconnlimit FROM pg_roles;

三级权限体系:库、模式、表#

PostgreSQL 的对象权限分三层,授权要层层都给到,缺一层就报 permission denied:

层级关键权限控制什么
数据库CONNECT、CREATE能不能连进这个库、能不能在库里建模式
模式(schema)USAGE、CREATE能不能「看见并使用」模式里的对象、能不能建表
表SELECT、INSERT、UPDATE、DELETE 等对具体表能做什么操作

一个应用账号的标准授权全套(在目标库里执行):

-- 库级:允许连接
GRANT CONNECT ON DATABASE appdb TO appuser;
-- 模式级:public 模式的使用权
-- 注意:PostgreSQL 15 起,public 模式默认不再向所有人开放 CREATE 权限
GRANT USAGE ON SCHEMA public TO appuser;
-- 表级:现有全部表的读写
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO appuser;
-- 序列:自增主键背后的序列也要给,不然插入数据时会报错
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO appuser;
-- 关键一步:给"未来新建的表"预授权,否则下次建表后应用又没权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO appuser;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT USAGE, SELECT ON SEQUENCES TO appuser;

ALTER DEFAULT PRIVILEGES 是最容易被漏掉的一步:没有它,今天授权一切正常,明天新上线一张表,应用立刻报错。还要注意,它只对「执行这条命令的那个角色今后新建的对象」生效,所以要用实际建表的账号(通常是 postgres 或跑迁移脚本的账号)来执行。

用组角色管理权限:别逐个账号授权#

账号一多,逐个 GRANT 必乱。正确姿势是建不带 LOGIN 的组角色承载权限,个人账号只加入组:

-- 两个组角色:只读组、应用读写组
CREATE ROLE role_readonly NOLOGIN;
CREATE ROLE role_app NOLOGIN;
GRANT CONNECT ON DATABASE appdb TO role_readonly, role_app;
GRANT USAGE ON SCHEMA public TO role_readonly, role_app;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO role_readonly;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO role_app;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO role_app;
-- 个人/服务账号只入组,权限自动继承
CREATE USER alice WITH PASSWORD 'alice_password';
GRANT role_readonly TO alice;
CREATE USER report_svc WITH PASSWORD 'report_password';
GRANT role_readonly TO report_svc;

离职或转岗时只需 DROP USER alice;,组上的权限纹丝不动;要全员收紧某项权限,改组一处即可。这也是为什么在 《SQL 基础速成》 之后专门用一篇讲权限——它是团队协作的地基。

三类账号的落地模板#

账号类型给什么不给什么
应用账号(程序连库用)目标库 CONNECT、业务模式 USAGE、业务表增删改查、序列超级用户、建库、建角色、碰其他库
只读账号(报表、人工排查)CONNECT、USAGE、全表 SELECT一切写权限
运维账号(DBA 日常)建库建角色、全部对象权限平时不用超级用户,动大手术时才切 postgres

只读账号还有一个双保险:给它设默认只读事务,防手滑写库:

ALTER USER alice SET default_transaction_read_only = on;

改密码、回收权限与删号#

ALTER USER appuser WITH PASSWORD 'new_strong_password'; -- 改密码
ALTER USER appuser CONNECTION LIMIT 5; -- 改连接数上限
REVOKE DELETE ON TABLE orders FROM appuser; -- 回收单项权限
REVOKE ALL ON DATABASE appdb FROM appuser; -- 回收整库权限
DROP USER appuser; -- 删号(名下拥有对象时会失败)
WARNING

DROP USER 前如果这个账号拥有表等对象,会直接报错删不掉。标准处理顺序:先 REASSIGN OWNED BY 旧账号 TO 新账号; 把对象转给别人,再 DROP OWNED BY 旧账号; 清掉它的残留权限,最后 DROP USER 旧账号;。千万不要为了让删号成功就随手把对象 DROP 掉,那删掉的可能是业务表。

回收权限后想确认是否收干净,查信息模式最直观:

-- 某账号在各表上的实际权限一览
SELECT table_name, privilege_type
FROM information_schema.role_table_grants
WHERE grantee = 'appuser';

常见问题#

Q:授权全给了,应用还是报 permission denied for table xxx? A:按三层逐个查:库的 CONNECT 有没有、模式的 USAGE 有没有、这张表的权限有没有。最高频的原因是这张表建于授权之后、而没设 ALTER DEFAULT PRIVILEGES;其次是应用连错了库(开发、生产环境串了)。

Q:插入数据时报 permission denied for sequence? A:自增主键插入时要取序列的下一个值,序列是独立对象、独立授权。补 GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO appuser;,并同步补上默认权限。

Q:新建的数据库里,appuser 连得上却建不了表? A:建表需要模式的 CREATE 权限。PostgreSQL 15 起 public 模式收紧了默认权限,显式执行 GRANT CREATE ON SCHEMA public TO appuser; 即可。更推荐的做法是给应用建专属模式并把该模式的所有权给它。

Q:怎么看某个账号到底属于哪些组、继承了什么权限? A:查成员关系可以用 SELECT * FROM pg_auth_members; 配合 pg_roles 看;更省事的是在 psql 里执行 \du 账号名,直接看它的角色成员身份列表。

Q:忘记 postgres 超级用户密码怎么办? A:本机用系统用户 postgres 通过 peer 认证进去(sudo -u postgres psql),再用 ALTER USER postgres WITH PASSWORD '...' 重设。Docker 场景则 docker exec -it 容器 psql -U postgres 进去改——容器内本地连接默认走 trust,这正是官方镜像的默认行为。

小结#

PostgreSQL 权限管理的骨架就三句话:用户即带 LOGIN 的角色;权限分库、模式、表三层,层层都要给;用组角色承载权限、个人只入组,再用默认权限兜住未来的新表。下一篇换个工具视角,把每天都要用的 psql 客户端练熟。

PostgreSQL 用户、角色与权限管理
https://www.anexns.cloudns.org/posts/postgresql-users-permissions/
作者
Anexns
发布于
2026-11-15
许可协议
CC BY-NC-SA 4.0