PostgreSQL / 系统课时

配置应用访问角色

配置应用访问角色

应用不应长期使用数据库所有者或超级用户连接 PostgreSQL。本节用“权限角色 + 登录角色”拆分读写、只读和运维访问,并补上现有对象与未来对象的授权,让权限边界可验证、可持续。

学习目标

  • 为读写、只读和运维场景设计独立角色,避免共享高权限账号。
  • 正确授权数据库、Schema、表和序列,并覆盖未来新建对象。
  • 使用权限检查函数验证账号实际拥有的能力。

先把登录身份和权限集合分开

推荐把“谁登录”和“拥有什么权限”拆成两层:app_ro_login、app_rw_login、app_ops_login 只负责登录;app_readonly、app_readwrite、app_ops 使用 NOLOGIN 保存权限集合。以后新增实例账号时,只需授予对应权限角色,不必重复维护大量 GRANT。

读写角色只拿业务 DML 权限;只读角色只拿查询权限;运维角色可获得必要的可观测性能力,但不应因此拥有修改业务数据或 DDL 的权限。PostgreSQL 预定义角色 pg_monitor 适合授予监控视图和统计信息的读取能力。

应用登录角色与权限角色的分层关系

配置最小权限

下面假设数据库名为 appdb、业务 Schema 为 app,业务对象由 app_owner 创建或拥有。应在管理员会话中执行;如果实际对象所有者不是 app_owner,将默认权限语句中的角色名替换为真实 owner。

sql
CREATE ROLE app_readonly NOLOGIN;
CREATE ROLE app_readwrite NOLOGIN;
CREATE ROLE app_ops NOLOGIN;

CREATE ROLE app_ro_login LOGIN;
CREATE ROLE app_rw_login LOGIN;
CREATE ROLE app_ops_login LOGIN;

GRANT app_readonly TO app_ro_login;
GRANT app_readwrite TO app_rw_login;
GRANT app_ops TO app_ops_login;
GRANT pg_monitor TO app_ops;

GRANT CONNECT ON DATABASE appdb TO app_readonly, app_readwrite, app_ops;
GRANT USAGE ON SCHEMA app TO app_readonly, app_readwrite, app_ops;

GRANT SELECT ON ALL TABLES IN SCHEMA app TO app_readonly, app_ops;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO app_readwrite;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA app TO app_readwrite;

ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
  GRANT SELECT ON TABLES TO app_readonly, app_ops;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_readwrite;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
  GRANT USAGE, SELECT ON SEQUENCES TO app_readwrite;

预期输出

text
CREATE ROLE
CREATE ROLE
CREATE ROLE
CREATE ROLE
CREATE ROLE
CREATE ROLE
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
ALTER DEFAULT PRIVILEGES
ALTER DEFAULT PRIVILEGES
ALTER DEFAULT PRIVILEGES

ALTER DEFAULT PRIVILEGES 只影响由指定 owner 未来创建的对象,不会补授权给已经存在的表和序列。因此现有对象要用 GRANT ... ON ALL ... 处理,未来对象再由默认权限接管。

验证权限而不是凭感觉判断

可以直接检查某个登录角色对具体对象是否具备权限。下面假设 app.orders 已存在。

sql
SELECT
  has_table_privilege('app_ro_login', 'app.orders', 'SELECT') AS ro_select,
  has_table_privilege('app_ro_login', 'app.orders', 'INSERT') AS ro_insert,
  has_table_privilege('app_rw_login', 'app.orders', 'INSERT') AS rw_insert,
  has_table_privilege('app_ops_login', 'app.orders', 'UPDATE') AS ops_update;

预期输出

text
 ro_select | ro_insert | rw_insert | ops_update
-----------+-----------+-----------+------------
 t         | f         | t         | f
(1 row)

如果结果与预期不符,按“登录角色是否继承权限角色 → 是否有数据库 CONNECT → 是否有 Schema USAGE → 是否有对象级权限 → 对象 owner 是否匹配默认权限配置”的顺序检查。这样能快速区分认证问题、命名空间问题和对象授权问题。

避免权限继续膨胀

应用账号不应拥有 SUPERUSER、CREATEDB、CREATEROLE,也不要为了省事授予业务 Schema 的 CREATE。需要迁移表结构时,使用独立的迁移身份;需要监控时,优先使用受控的只读运维身份。把“运行应用”“执行迁移”“观测数据库”分成不同主体,权限审计和事故隔离都会更清晰。

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

目录与阅读设置

阅读设置

17

记录一个想法

配置应用访问角色

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

登录哪都通