PostgreSQL / 系统课时
角色与默认权限
角色与默认权限
PostgreSQL 权限设计的关键不是给每个登录账号逐条 GRANT,而是把身份、权限集合和对象所有权分离。本课给出一套可落地的角色模型,并演示如何同时覆盖现有对象和未来新建对象。
学习目标
- 能区分登录角色、组角色与对象所有者角色的职责
- 能正确授予 schema、表和序列权限,而不是只给表权限
- 能理解
ALTER DEFAULT PRIVILEGES的作用范围,并验证未来对象权限
把身份、权限和所有权分开
推荐把可登录账号只当作身份,把业务权限放在 NOLOGIN 组角色上,再用独立的 NOLOGIN 所有者角色拥有 schema 和对象。这样人员或服务更换时只需调整成员关系,不必重写整套对象授权。

这个模型还有一个重要边界:对象所有者天然拥有管理对象的能力,普通 GRANT 不能完全替代所有权。因此应用运行账号通常不应成为表所有者,否则它既能读写数据,又能修改或删除自身依赖的数据库对象。
同时授权现有对象和未来对象
下面示例需要由具备 CREATEROLE、目标数据库 CREATE 权限,并且能够 SET ROLE app_owner 的管理角色执行。它先创建角色和 schema,再对现有对象执行 GRANT,最后用默认权限覆盖由 app_owner 将来创建的表和序列。
CREATE ROLE app_owner NOLOGIN;
CREATE ROLE app_ro NOLOGIN;
CREATE ROLE app_rw NOLOGIN;
CREATE ROLE app_api LOGIN;
GRANT app_rw TO app_api;
GRANT app_owner TO CURRENT_USER;
CREATE SCHEMA app AUTHORIZATION app_owner;
SET ROLE app_owner;
CREATE TABLE app.orders (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
amount numeric(12, 2) NOT NULL
);
GRANT USAGE ON SCHEMA app TO app_ro, app_rw;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO app_ro;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO app_rw;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA app TO app_rw;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
GRANT SELECT ON TABLES TO app_ro;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_rw;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
GRANT USAGE, SELECT ON SEQUENCES TO app_rw;
CREATE TABLE app.invoices (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
amount numeric(12, 2) NOT NULL
);
RESET ROLE;
SELECT
pg_has_role('app_api', 'app_rw', 'MEMBER') AS api_is_rw_member,
has_table_privilege('app_ro', 'app.invoices', 'SELECT') AS ro_can_select,
has_table_privilege('app_rw', 'app.invoices', 'INSERT') AS rw_can_insert;预期结果
api_is_rw_member | ro_can_select | rw_can_insert
------------------+---------------+---------------
t | t | t
(1 row)如果重复执行整段脚本会因为角色或 schema 已存在而报错;该示例按“新测试数据库执行一次”设计。生产环境应通过迁移脚本管理角色和授权变更。
理解默认权限真正绑定到谁
ALTER DEFAULT PRIVILEGES 不会修改已有对象,它只改变某个创建者角色未来创建对象时使用的默认 ACL。因此示例必须写成 FOR ROLE app_owner:如果未来表实际上由另一个迁移角色创建,那么 app_owner 的默认权限不会自动套用过去。
角色成员关系也不会把默认权限“继承给创建动作”。部署程序在建表前应明确 SET ROLE app_owner,或者直接使用约定的所有者角色执行迁移,确保新对象的创建者稳定。否则同一个 schema 中可能出现权限策略不一致的对象。
别漏掉 schema 和序列
表权限并不等于“应用一定能访问表”。角色还需要 schema 的 USAGE 才能通过 app.orders 解析对象;使用 serial 或 identity 列时,插入还可能访问底层序列,所以写角色通常还需要序列权限。
最稳妥的授权方式是把“现有对象授权”和“未来对象默认权限”成对维护,并固定对象创建者。登录角色只保留成员关系,权限角色只表达业务能力,所有者角色只负责 DDL 与对象所有权,这样权限审计和人员变更都会简单很多。