PostgreSQL 权限管理规范

角色与权限体系

PostgreSQL 使用基于角色的访问控制（RBAC）体系，角色可以是用户或组。角色可以拥有对象（表、视图、函数等）的权限，也可以继承其他角色的权限。PostgreSQL 的权限体系包括：

角色类型

• 登录角色：可以用于登录数据库的角色，相当于传统意义上的用户
• 组角色：用于权限管理的角色，不能直接登录，用于批量管理权限
• 系统角色：PostgreSQL 内置的角色，如 postgres、pg_read_all_data 等
• 自定义角色：用户根据业务需求创建的角色

权限类型

PostgreSQL 的权限可以分为对象权限和语句权限：

• 对象权限：对数据库对象（表、视图、函数、序列等）的操作权限，如 SELECT、INSERT、UPDATE、DELETE、TRUNCATE、REFERENCES、TRIGGER、CREATE、CONNECT 等
• 语句权限：执行特定 SQL 语句的权限，如 CREATE DATABASE、CREATE ROLE、CREATE TABLESPACE、LOGIN、SUPERUSER 等

继承与授权

角色可以继承其他角色的权限，通过 GRANT role TO role 语句实现。权限继承可以简化权限管理，减少重复授权。此外，PostgreSQL 14 引入了预定义角色，如 pg_read_all_data、pg_write_all_data 等，用于快速授予系统级权限。

-- 创建组角色
CREATE ROLE read_only_role NOLOGIN;
CREATE ROLE write_role NOLOGIN;

-- 创建登录角色并继承组角色权限
CREATE ROLE app_user WITH LOGIN PASSWORD 'password' IN ROLE read_only_role;
CREATE ROLE admin_user WITH LOGIN PASSWORD 'admin_password' IN ROLE write_role;

-- 授权组角色访问数据库
GRANT CONNECT ON DATABASE app_db TO read_only_role, write_role;

-- 授权对象权限
GRANT USAGE ON SCHEMA public TO read_only_role, write_role;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only_role;
GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO write_role;

-- 授权未来创建的表的权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO read_only_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT INSERT, UPDATE, DELETE ON TABLES TO write_role;

最小权限原则

最小权限原则是权限管理的核心原则，即授予用户完成工作所需的最小权限集。遵循最小权限原则可以减少安全风险，限制潜在的破坏范围。

最小权限的实现方法

1. 细粒度授权：根据业务需求，精确授予所需的权限，避免过度授权
2. 角色分离：将不同职责的权限分配给不同的角色，如只读角色、读写角色、管理员角色等
3. 定期审查：定期审查角色的权限，移除不再需要的权限
4. 临时权限：对于临时需求，授予临时权限并在完成后回收
5. 权限继承：使用角色继承简化权限管理，避免直接向用户授予权限

常见角色设计

角色名称	权限范围	适用场景
只读角色	SELECT	报表查询、数据分析
读写角色	SELECT, INSERT, UPDATE, DELETE	应用程序访问
管理员角色	所有对象权限	数据库管理
DDL 角色	CREATE, ALTER, DROP	架构管理
监控角色	SELECT ON pg_* 视图	监控人员

-- 创建细粒度角色
CREATE ROLE readonly_role NOLOGIN;
CREATE ROLE write_role NOLOGIN;
CREATE ROLE ddl_role NOLOGIN;
CREATE ROLE monitor_role NOLOGIN;

-- 授予最小权限
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_role;
GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO write_role;
GRANT CREATE, ALTER, DROP ON SCHEMA public TO ddl_role;
GRANT SELECT ON pg_stat_activity, pg_stat_replication TO monitor_role;

角色创建与管理

角色创建规范

1. 命名规范：角色名称应反映其用途，使用小写字母、下划线分隔，如 app_user_ro、app_user_rw 等
2. 密码策略：为登录角色设置强密码，长度至少 12 位，包含大小写字母、数字和特殊字符
3. 有效期：根据业务需求设置角色有效期，避免长期未使用的角色存在安全风险
4. 最小权限：初始创建时只授予必要的权限，后续根据需求逐步授予

密码管理

PostgreSQL 支持多种密码验证方法，包括 md5、scram-sha-256、password、ident 等。建议使用 scram-sha-256 加密方法，提供更高的安全性。

-- 创建角色时设置强密码
CREATE ROLE app_user WITH LOGIN PASSWORD 'Str0ngP@ssw0rd' VALID UNTIL '2025-12-31';

-- 修改密码
ALTER ROLE app_user WITH PASSWORD 'NewStr0ngP@ssw0rd';

-- 设置密码有效期
ALTER ROLE app_user VALID UNTIL '2025-12-31';

-- 启用 SCRAM-SHA-256 加密
ALTER SYSTEM SET password_encryption = 'scram-sha-256';
SELECT pg_reload_conf();

角色生命周期管理

角色的生命周期管理包括创建、修改、禁用和删除等环节：

1. 创建：根据业务需求创建角色，遵循命名规范和最小权限原则
2. 修改：根据业务变化调整角色权限，如添加或移除权限、修改密码等
3. 禁用：对于不再使用的角色，先禁用其登录权限，观察一段时间后再删除
4. 删除：彻底移除不再需要的角色，确保数据安全

-- 禁用角色登录
ALTER ROLE app_user NOLOGIN;

-- 启用角色登录
ALTER ROLE app_user LOGIN;

-- 删除角色（级联删除依赖关系）
DROP ROLE IF EXISTS app_user CASCADE;

-- 查看角色信息
SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolvaliduntil
FROM pg_roles
WHERE rolname LIKE 'app%';

权限授予与回收

权限授予原则

1. 明确授权：使用明确的授权语句，避免使用 ALL PRIVILEGES 授予所有权限
2. 细粒度授权：根据业务需求，精确授予所需的权限
3. 批量授权：对于多个对象，使用 ALL TABLES、ALL SEQUENCES 等批量授权方式
4. 默认权限：为未来创建的对象设置默认权限，确保权限一致性
5. 记录审计：记录权限授予和回收操作，便于审计和追溯

权限回收原则

1. 及时回收：当用户不再需要某些权限时，及时回收
2. 谨慎回收：回收权限前，确认不会影响业务正常运行
3. 级联回收：对于继承的权限，考虑是否需要级联回收
4. 记录审计：记录权限回收操作，便于审计和追溯

-- 授予表权限
GRANT SELECT, INSERT, UPDATE ON table_name TO role_name;

-- 批量授予表权限
GRANT SELECT ON ALL TABLES IN SCHEMA public TO role_name;

-- 设置默认权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO role_name;

-- 回收权限
REVOKE UPDATE ON table_name FROM role_name;

-- 批量回收权限
REVOKE SELECT ON ALL TABLES IN SCHEMA public FROM role_name;

-- 查看角色权限
SELECT * FROM information_schema.role_table_grants WHERE grantee = 'role_name';
SELECT * FROM information_schema.role_routine_grants WHERE grantee = 'role_name';

schema 级权限管理

Schema 是 PostgreSQL 中对象的命名空间，用于组织数据库对象。Schema 级权限管理包括创建、使用和访问权限：

Schema 创建与权限

只有拥有 CREATE 权限的角色才能在 schema 中创建对象，只有拥有 USAGE 权限的角色才能访问 schema 中的对象。

-- 创建 schema
CREATE SCHEMA app_schema;

-- 授予 schema 权限
GRANT USAGE ON SCHEMA app_schema TO read_only_role;
GRANT CREATE ON SCHEMA app_schema TO write_role;

-- 授予 schema 中所有表的权限
GRANT SELECT ON ALL TABLES IN SCHEMA app_schema TO read_only_role;
GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app_schema TO write_role;

-- 设置 schema 的默认权限
ALTER DEFAULT PRIVILEGES IN SCHEMA app_schema GRANT SELECT ON TABLES TO read_only_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA app_schema GRANT INSERT, UPDATE, DELETE ON TABLES TO write_role;

跨 schema 访问

当需要访问其他 schema 中的对象时，需要拥有目标 schema 的 USAGE 权限和对象的相应权限。可以通过在查询中指定 schema 名称或设置 search_path 来访问其他 schema 中的对象。

-- 使用 schema 前缀访问对象
SELECT * FROM app_schema.table_name;

-- 设置 search_path
SET search_path TO app_schema, public;
SELECT * FROM table_name;  -- 会在 app_schema 和 public 中查找

-- 为角色设置默认 search_path
ALTER ROLE app_user SET search_path TO app_schema, public;

审计与监控

权限审计

权限审计是确保权限管理合规性的重要手段，包括：

1. 权限变更审计：记录所有权限授予和回收操作
2. 角色变更审计：记录角色的创建、修改和删除操作
3. 访问审计：记录用户对敏感数据的访问操作
4. 定期权限审查：定期审查角色权限，确保符合最小权限原则

审计日志配置

PostgreSQL 提供了多种审计日志配置选项，包括：

• log_statement：记录 SQL 语句，如 DDL、DML 等
• log_connections：记录连接事件
• log_disconnections：记录断开连接事件
• log_line_prefix：配置日志格式，包含时间、用户、数据库等信息
• pgaudit 扩展：提供更细粒度的审计功能

# postgresql.conf 审计配置
log_statement = 'ddl, mod'
log_connections = on
log_disconnections = on
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
log_timezone = 'Asia/Shanghai'

pgaudit 扩展

pgaudit 是 PostgreSQL 的审计扩展，可以提供更细粒度的审计功能，包括：

• 按会话或对象进行审计
• 记录详细的 SQL 语句和参数
• 支持多种审计事件类型，如 DDL、DML、函数执行等

-- 安装 pgaudit 扩展
CREATE EXTENSION IF NOT EXISTS pgaudit;

-- 配置 pgaudit
ALTER SYSTEM SET shared_preload_libraries = 'pgaudit';
ALTER SYSTEM SET pgaudit.log = 'ddl, dml';
SELECT pg_reload_conf();

-- 查看审计日志
SELECT * FROM pg_log WHERE message LIKE '%AUDIT: %';

常见权限问题排查

权限不足错误

当用户尝试执行没有权限的操作时，会收到权限不足错误。排查步骤包括：

1. 确认用户的角色和权限
2. 确认对象的所有者和权限设置
3. 确认 schema 权限和 search_path 设置
4. 确认是否存在权限继承问题

-- 查看用户角色
SELECT * FROM pg_roles WHERE rolname = current_user;

-- 查看角色成员关系
SELECT * FROM pg_auth_members WHERE member = current_user::regrole;

-- 查看对象权限
SELECT * FROM information_schema.role_table_grants WHERE table_name = 'table_name';

-- 查看 schema 权限
SELECT * FROM information_schema.role_schema_grants WHERE schema_name = 'schema_name';

-- 查看 search_path
SHOW search_path;

角色继承问题

当角色没有继承预期的权限时，可能是继承关系配置错误。排查步骤包括：

1. 确认角色继承关系
2. 确认角色是否设置了 INHERIT 属性
3. 确认权限授予是否正确

-- 查看角色继承关系
SELECT * FROM pg_auth_members WHERE member = 'role_name';

-- 查看角色属性
SELECT rolname, rolcanlogin, rolinherit FROM pg_roles WHERE rolname = 'role_name';

-- 手动继承权限（如果角色没有设置 INHERIT）
SET ROLE role_name;

默认权限问题

当新创建的对象没有预期的权限时，可能是默认权限设置错误。排查步骤包括：

1. 确认默认权限设置
2. 确认默认权限的所有者和 schema
3. 确认对象的创建者

-- 查看默认权限
SELECT * FROM pg_default_acl WHERE defaclrole = 'role_name'::regrole;

-- 查看默认权限的详细信息
SELECT 
    n.nspname AS schema_name,
    pg_get_userbyid(d.defaclrole) AS grantee,
    (SELECT string_agg(privilege_type, ', ') 
     FROM unnest(d.defaclacl) acl 
     JOIN pg_catalog.pg_privilege p ON acl = p.proname) AS privileges,
    CASE d.defaclobjtype 
        WHEN 'r' THEN 'table' 
        WHEN 'f' THEN 'function' 
        WHEN 'S' THEN 'sequence' 
        ELSE d.defaclobjtype 
    END AS object_type
FROM pg_default_acl d
JOIN pg_namespace n ON d.defaclnamespace = n.oid
WHERE d.defaclrole = 'role_name'::regrole;

最佳实践

1. 遵循最小权限原则

• 只授予用户完成工作所需的最小权限
• 避免使用 SUPERUSER 权限
• 定期审查和回收不必要的权限

2. 使用角色分离

• 将不同职责的权限分配给不同的角色
• 使用组角色管理权限，避免直接向用户授予权限
• 实现读写分离、开发测试分离

3. 定期审计和审查

• 配置详细的审计日志
• 定期审查角色权限和成员关系
• 定期进行权限合规性检查

4. 使用预定义角色

• PostgreSQL 14+ 使用内置的预定义角色，如 pg_read_all_data、pg_write_all_data 等
• 避免直接授予系统级权限

5. 密码管理

• 使用强密码策略
• 定期更换密码
• 禁用不必要的登录权限
• 使用外部认证机制，如 LDAP、Kerberos 等

6. 限制网络访问

• 使用 pg_hba.conf 限制网络访问
• 只允许必要的 IP 地址访问数据库
• 使用 SSL/TLS 加密网络连接

常见问题（FAQ）

Q1: 如何快速创建具有只读权限的用户？

A1: 可以使用 PostgreSQL 14+ 引入的预定义角色 pg_read_all_data，或者创建自定义只读角色：

-- 使用预定义角色（PostgreSQL 14+）
CREATE ROLE readonly_user WITH LOGIN PASSWORD 'password';
GRANT pg_read_all_data TO readonly_user;

-- 自定义只读角色
CREATE ROLE readonly_role NOLOGIN;
GRANT CONNECT ON DATABASE app_db TO readonly_role;
GRANT USAGE ON SCHEMA public TO readonly_role;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly_role;
CREATE ROLE readonly_user WITH LOGIN PASSWORD 'password' IN ROLE readonly_role;

Q2: 如何撤销继承的权限？

A2: 可以通过撤销角色成员关系或直接撤销权限来实现：

-- 撤销角色成员关系
REVOKE role_name FROM user_name;

-- 直接撤销继承的权限
REVOKE privilege ON object FROM user_name;

Q3: 如何查看用户的所有权限？

A3: 可以通过查询系统视图查看用户的权限：

-- 查看表权限
SELECT * FROM information_schema.role_table_grants WHERE grantee = current_user;

-- 查看视图权限
SELECT * FROM information_schema.role_routine_grants WHERE grantee = current_user;

-- 查看 schema 权限
SELECT * FROM information_schema.role_schema_grants WHERE grantee = current_user;

-- 查看数据库权限
SELECT * FROM information_schema.role_database_grants WHERE grantee = current_user;

Q4: 如何处理权限冲突？

A4: 权限冲突通常是由于多个角色授予了相互矛盾的权限导致的。处理方法包括：

1. 明确权限优先级
2. 简化角色继承关系
3. 使用 REVOKE 语句撤销冲突权限
4. 重新设计角色体系

Q5: 如何实现跨数据库权限管理？

A5: PostgreSQL 不支持直接跨数据库授权，每个数据库是独立的权限域。跨数据库权限管理的方法包括：

1. 在每个数据库中创建相同的角色和权限
2. 使用外部认证机制，如 LDAP、Kerberos 等
3. 使用联邦数据库或数据联邦工具
4. 使用逻辑复制或物理复制同步数据

Q6: 如何审计权限变更？

A6: 可以通过配置审计日志或使用 pgaudit 扩展来审计权限变更：

# 配置审计日志记录 DDL 语句
log_statement = 'ddl'

或者使用 pgaudit 扩展：

ALTER SYSTEM SET pgaudit.log = 'role';
SELECT pg_reload_conf();