PostgreSQL 权限管理（五）：企业级角色设计与权限治理

一、企业权限管理的目标

企业级权限管理不是简单地“让用户能连上数据库”，而是要回答几个问题：

1
2
3
4
5
6
7
8
谁可以登录？
谁可以连接哪个数据库？
谁可以访问哪个 schema？
谁可以读写哪些表？
谁可以修改结构？
谁可以管理权限？
谁执行过高危操作？
账号离职或下线后如何安全清理？

权限治理的目标是：

满足业务访问需要
遵守最小权限原则
降低误操作和泄露风险
方便审计
方便账号生命周期管理
避免权限长期失控

二、最小权限原则

最小权限原则是企业权限管理的核心。

含义是：

1
一个用户或程序只拥有完成工作所必需的最小权限。

不推荐：

1
2
GRANT ALL PRIVILEGES ON DATABASE sales_db TO app_user;
ALTER ROLE app_user SUPERUSER;

三、职责分离

职责分离，也就是 SoD，Separation of Duties。

不同角色承担不同职责，避免一个账号权限过大。

常见角色设计：

职责	示例角色	权限范围
DBA 管理	`dba_admin`	数据库管理、权限管理、运维操作
应用读写	`app_user`	业务表 CRUD
报表只读	`report_user`	查询业务数据
ETL 导入	`etl_user`	指定 schema 的写入或批量导入
备份	`backup_user`	读取备份所需数据
监控	`monitor_user`	查看统计视图、连接状态
发布迁移	`migration_user`	建表、建索引、变更结构

一个典型原则：

1
2
3
4
应用运行账号 ≠ DDL 迁移账号
只读分析账号 ≠ 业务写入账号
普通 DBA 账号 ≠ 超级用户账号
备份账号 ≠ 应用账号

这样即使某个账号泄露，影响范围也相对有限。

四、使用权限组而不是直接给用户授权

不推荐直接给每个用户授权：

1
2
3
GRANT SELECT ON ALL TABLES IN SCHEMA sales TO john;
GRANT SELECT ON ALL TABLES IN SCHEMA sales TO sarah;
GRANT SELECT ON ALL TABLES IN SCHEMA sales TO tom;

推荐使用 NOLOGIN 角色作为权限组：

1
2
3
4
5
6
7
8
9
CREATE ROLE sales_readonly NOLOGIN;

GRANT CONNECT ON DATABASE sales_db TO sales_readonly;
GRANT USAGE ON SCHEMA sales TO sales_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA sales TO sales_readonly;

GRANT sales_readonly TO john;
GRANT sales_readonly TO sarah;
GRANT sales_readonly TO tom;

这样以后要调整只读权限，只需要调整 sales_readonly。

如果某个人离职，只需要：

1
2
REVOKE sales_readonly FROM john;
DROP ROLE john;

权限模型会更加清晰。

五、推荐的角色分层模型

可以采用三层模型：

1
2
3
第一层：对象所有者
第二层：权限组角色
第三层：登录用户

示例：

1
2
3
4
5
sales_owner：拥有 sales schema 和对象，不用于业务登录
sales_readonly：只读权限组，NOLOGIN
sales_app_rw：应用读写权限组，NOLOGIN
report_user：登录用户，继承 sales_readonly
app_user：登录用户，继承 sales_app_rw

创建示例：

1
2
3
4
5
6
7
8
9
CREATE ROLE sales_owner NOLOGIN;
CREATE ROLE sales_readonly NOLOGIN;
CREATE ROLE sales_app_rw NOLOGIN;

CREATE USER report_user WITH PASSWORD 'StrongPassword';
CREATE USER app_user WITH PASSWORD 'StrongPassword';

GRANT sales_readonly TO report_user;
GRANT sales_app_rw TO app_user;

对象由 sales_owner 拥有，普通应用用户只拥有访问权限，不拥有对象本身。

这样可以避免应用用户误删、误改对象所有权相关内容。

六、对象所有者和访问者要分开

PostgreSQL 中，对象所有者天然拥有对象的管理能力。

如果应用用户就是表 owner，它可能拥有很多额外能力，例如修改表、删除表、授权他人等。

更稳妥的设计是：

1
2
owner 角色拥有对象
app_user 只被授予必要 DML 权限

例如：

1
2
CREATE ROLE sales_owner NOLOGIN;
CREATE SCHEMA sales AUTHORIZATION sales_owner;

创建对象时使用 owner 或迁移账号执行。

然后给应用权限组授权：

1
2
GRANT USAGE ON SCHEMA sales TO sales_app_rw;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA sales TO sales_app_rw;

这样应用账号可以使用对象，但不是对象所有者。

七、迁移账号和运行账号分离

很多系统使用 Flyway、Liquibase、Django migration、Rails migration 等工具自动变更数据库结构。

建议区分：

1
2
app_runtime_user：应用运行账号，只做 DML
app_migration_user：发布迁移账号，可以 DDL

运行账号：

1
2
3
GRANT CONNECT ON DATABASE sales_db TO app_runtime_user;
GRANT USAGE ON SCHEMA sales TO app_runtime_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA sales TO app_runtime_user;

迁移账号：

1
2
GRANT CONNECT ON DATABASE sales_db TO app_migration_user;
GRANT USAGE, CREATE ON SCHEMA sales TO app_migration_user;

这样应用正常运行时没有建表、改表权限。只有发布流程使用迁移账号进行结构变更。

八、账号生命周期管理

企业中账号应有生命周期：

1
2
3
4
5
6
7
8
9
申请
审批
创建
授权
使用
审计
变更
回收
删除

创建账号时建议记录：

账号用途
负责人
所属系统
授权范围
有效期
是否允许外部连接
是否需要审计

PostgreSQL 中可以设置有效期：

1
2
3
CREATE USER report_user
WITH PASSWORD 'StrongPassword'
VALID UNTIL '2026-12-31';

限制连接数：

1
ALTER ROLE app_user CONNECTION LIMIT 50;

临时禁用登录：

1
ALTER ROLE old_user NOLOGIN;

九、删除用户的正确流程

不能简单认为：

1
DROP ROLE old_user;

一定能成功。

如果该用户拥有对象，或者仍然有权限依赖，可能会报错。

常见安全流程：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
-- 1. 禁止登录
ALTER ROLE old_user NOLOGIN;

-- 2. 转移其拥有的对象
REASSIGN OWNED BY old_user TO dba_user;

-- 3. 删除其权限依赖
DROP OWNED BY old_user;

-- 4. 删除角色
DROP ROLE old_user;

含义：

1
2
3
REASSIGN OWNED：把 old_user 拥有的对象转给其他角色
DROP OWNED：删除 old_user 在当前数据库中的权限依赖
DROP ROLE：删除角色

注意：DROP OWNED 和 REASSIGN OWNED 作用于当前数据库。如果角色在多个数据库中拥有对象，需要分别处理。

十、权限审计

定期审计权限非常重要。

查看角色：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
SELECT
    rolname,
    rolsuper,
    rolcreatedb,
    rolcreaterole,
    rolcanlogin,
    rolreplication,
    rolconnlimit,
    rolvaliduntil
FROM pg_roles
ORDER BY rolname;

查看角色成员关系：

1
2
3
4
5
6
7
8
SELECT
    r.rolname AS role_name,
    m.rolname AS member_name,
    am.admin_option
FROM pg_auth_members am
JOIN pg_roles r ON r.oid = am.roleid
JOIN pg_roles m ON m.oid = am.member
ORDER BY r.rolname, m.rolname;

查看表权限：

1
2
3
4
5
6
7
8
SELECT
    grantee,
    table_schema,
    table_name,
    privilege_type
FROM information_schema.table_privileges
WHERE grantee NOT IN ('postgres', 'PUBLIC')
ORDER BY grantee, table_schema, table_name, privilege_type;

检查超级用户：

1
2
3
SELECT rolname
FROM pg_roles
WHERE rolsuper;

检查能创建角色的用户：

1
2
3
SELECT rolname
FROM pg_roles
WHERE rolcreaterole;

这些账号都需要重点关注。

十一、监控账号设计

监控账号通常不需要业务表读写权限。

可以创建：

1
CREATE USER monitor_user WITH PASSWORD 'StrongPassword';

PostgreSQL 新版本中可以使用内置监控角色，例如：

1
GRANT pg_monitor TO monitor_user;

pg_monitor 包含一组查看系统状态的权限，适合监控工具使用。

如果只需要读取统计信息，也可以使用更细的内置角色，具体取决于版本和监控需求。

不要为了监控方便直接给：

1
SUPERUSER

十二、备份账号设计

PostgreSQL 提供了一些内置角色用于数据访问和备份场景，例如：

1
2
3
pg_read_all_data
pg_write_all_data
pg_monitor

如果备份工具需要读取全部数据，可以考虑：

1
GRANT pg_read_all_data TO backup_user;

但这个权限影响范围很大，意味着可以读取所有数据。生产环境应结合备份工具、合规要求和审计策略使用。

对于逻辑备份账号，要确认是否还需要：

1
2
3
4
5
连接数据库
读取 schema
读取表
读取序列
执行必要函数

十三、权限变更规范

企业环境中，权限变更建议遵守：

1
2
3
4
5
6
1. 不直接在线上手工随意授权
2. 授权 SQL 进入变更流程
3. 明确授权原因、对象、角色和有效期
4. 高危权限需要审批
5. 定期回收临时权限
6. 所有权限变更可追溯

高危权限包括：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
SUPERUSER
CREATEDB
CREATEROLE
REPLICATION
WITH GRANT OPTION
schema CREATE
表 DELETE / TRUNCATE
敏感函数 EXECUTE
pg_read_all_data
pg_write_all_data

十四、总结

企业级 PostgreSQL 权限设计的核心不是“把 SQL 跑通”，而是建立一套可维护的权限体系。

推荐原则：

1
2
3
4
5
6
7
8
使用 NOLOGIN 角色作为权限组
使用 LOGIN 角色作为实际账号
对象 owner 与访问用户分离
应用运行账号与迁移账号分离
只授予必要权限
默认回收 public schema 的 CREATE 权限
定期审计超级用户和高危权限
删除用户前先处理对象归属和权限依赖

一个健康的权限体系，应该让 DBA 能清楚回答：

1
2
3
4
5
6
这个账号是谁在用？
它为什么需要这些权限？
它能访问哪些数据？
它是否能修改结构？
它的权限何时过期？
它是否还能继续保留？

如果这些问题答不上来，就说明权限治理还需要继续完善。

一、企业权限管理的目标#

二、最小权限原则#

三、职责分离#

四、使用权限组而不是直接给用户授权#

五、推荐的角色分层模型#

六、对象所有者和访问者要分开#

七、迁移账号和运行账号分离#

八、账号生命周期管理#

九、删除用户的正确流程#

十、权限审计#

十一、监控账号设计#

十二、备份账号设计#

十三、权限变更规范#

十四、总结#

相关文章