PostgreSQL / 系统课时

建立最小权限账户体系

建立最小权限账户体系

本篇把“一个数据库账号包办所有事情”拆成管理员、应用、只读和运维四类身份,并用 PostgreSQL 角色继承、对象授权与默认权限建立可持续的最小权限体系,最终能验证每类账号只拥有完成职责所需的能力。

学习目标

  • 区分登录身份、权限角色与对象所有者的职责
  • 为应用写入、只读查询、运维监控和管理员操作建立独立授权链
  • 验证现有对象与未来新对象都不会意外获得过量权限

把身份和权限拆开

PostgreSQL 的“用户”和“角色”本质上都是 role;带 LOGIN 的角色可以直接连接数据库,不带 LOGIN 的角色更适合承载一组权限。最小权限设计的关键,是让登录账号只负责“是谁”,让 NOLOGIN 角色负责“能做什么”。

对象所有者也应单独保留。应用运行账号不应拥有表,因为 owner 可以修改授权甚至删除对象。更稳妥的做法是让 app_owner 持有 schema 和表,由管理员在需要执行 DDL 时显式 SET ROLE app_owner。

管理员、应用、只读和运维角色的最小权限关系

建立四类账户的授权链

下面在练习数据库 appdb 中使用 lesson_acl schema 演示。首次建账需要由受控的引导 superuser 执行;日常应用和运维连接不应继续使用 superuser。

sql
CREATE ROLE app_owner NOLOGIN;
CREATE ROLE app_ro NOLOGIN;
CREATE ROLE app_rw NOLOGIN;

CREATE ROLE admin_login LOGIN CREATEDB CREATEROLE;
CREATE ROLE app_login LOGIN;
CREATE ROLE report_login LOGIN;
CREATE ROLE ops_login LOGIN;

GRANT app_owner TO admin_login;
GRANT app_ro TO app_rw;
GRANT app_rw TO app_login;
GRANT app_ro TO report_login;
GRANT pg_monitor TO ops_login;

REVOKE ALL ON DATABASE appdb FROM PUBLIC;
GRANT CONNECT ON DATABASE appdb
TO admin_login, app_login, report_login, ops_login;
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

CREATE SCHEMA lesson_acl AUTHORIZATION app_owner;

SET ROLE app_owner;
CREATE TABLE lesson_acl.orders (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  note text NOT NULL DEFAULT 'demo'
);
RESET ROLE;

GRANT USAGE ON SCHEMA lesson_acl TO app_ro, app_rw;
GRANT SELECT ON ALL TABLES IN SCHEMA lesson_acl TO app_ro;
GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA lesson_acl TO app_rw;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA lesson_acl TO app_rw;

预期结果

text
CREATE ROLE
CREATE ROLE
CREATE ROLE
CREATE ROLE
CREATE ROLE
CREATE ROLE
CREATE ROLE
GRANT ROLE
GRANT ROLE
GRANT ROLE
GRANT ROLE
GRANT ROLE
REVOKE
GRANT
REVOKE
CREATE SCHEMA
SET
CREATE TABLE
RESET
GRANT
GRANT
GRANT
GRANT

这里没有把密码写进 SQL 文件。实际环境应通过受控密钥系统提供凭据,或由管理员用 psql 的 \password 单独设置;不要把明文密码提交到仓库。

让未来新表继承同样边界

GRANT ... ON ALL TABLES 只覆盖当前已经存在的表。后续迁移创建新表时,如果不配置默认权限,只读账号可能看不到新表,团队又会回到临时手工授权。

默认权限按“创建对象的角色”生效,因此应以真正创建业务对象的 app_owner 为基准设置:

sql
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA lesson_acl
GRANT SELECT ON TABLES TO app_ro;

ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA lesson_acl
GRANT INSERT, UPDATE, DELETE ON TABLES TO app_rw;

ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA lesson_acl
GRANT USAGE, SELECT ON SEQUENCES TO app_rw;

预期结果

text
ALTER DEFAULT PRIVILEGES
ALTER DEFAULT PRIVILEGES
ALTER DEFAULT PRIVILEGES

如果实际迁移工具使用另一个 owner 创建对象,就必须针对那个创建者配置默认权限;只给 app_owner 配置但从不以它创建对象,不会产生预期效果。

用真实操作验证权限边界

授权完成后要验证行为,而不是只看 GRANT 是否成功。以下测试仍由引导 superuser 会话执行 SET ROLE,模拟应用与只读账号访问同一张表。

sql
SET ROLE app_login;
SELECT count(*) FROM lesson_acl.orders;
INSERT INTO lesson_acl.orders DEFAULT VALUES;
RESET ROLE;

SET ROLE report_login;
SELECT count(*) FROM lesson_acl.orders;
INSERT INTO lesson_acl.orders DEFAULT VALUES;

预期结果

text
SET
 count
-------
     0
(1 row)

INSERT 0 1
RESET
SET
 count
-------
     1
(1 row)

ERROR:  permission denied for table orders

最后还应检查 ops_login 是否只获得监控所需的 pg_monitor,以及普通应用账号是否没有 CREATEDB、CREATEROLE、SUPERUSER 等属性。最小权限不是“权限越少越好”,而是让每个身份拥有完成职责所需的最小能力,并把对象所有权、日常运行和监控职责彼此隔离。

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

目录与阅读设置

阅读设置

17

记录一个想法

建立最小权限账户体系

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

登录哪都通