PostgreSQL / 系统课时

角色与默认权限

角色与默认权限

PostgreSQL 权限设计的关键不是给每个登录账号逐条 GRANT,而是把身份、权限集合和对象所有权分离。本课给出一套可落地的角色模型,并演示如何同时覆盖现有对象和未来新建对象。

学习目标

  • 能区分登录角色、组角色与对象所有者角色的职责
  • 能正确授予 schema、表和序列权限,而不是只给表权限
  • 能理解 ALTER DEFAULT PRIVILEGES 的作用范围,并验证未来对象权限

把身份、权限和所有权分开

推荐把可登录账号只当作身份,把业务权限放在 NOLOGIN 组角色上,再用独立的 NOLOGIN 所有者角色拥有 schema 和对象。这样人员或服务更换时只需调整成员关系,不必重写整套对象授权。

PostgreSQL 角色成员关系与默认权限

这个模型还有一个重要边界:对象所有者天然拥有管理对象的能力,普通 GRANT 不能完全替代所有权。因此应用运行账号通常不应成为表所有者,否则它既能读写数据,又能修改或删除自身依赖的数据库对象。

同时授权现有对象和未来对象

下面示例需要由具备 CREATEROLE、目标数据库 CREATE 权限,并且能够 SET ROLE app_owner 的管理角色执行。它先创建角色和 schema,再对现有对象执行 GRANT,最后用默认权限覆盖由 app_owner 将来创建的表和序列。

sql
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;

预期结果

text
 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 与对象所有权,这样权限审计和人员变更都会简单很多。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

角色与默认权限

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通