PostgreSQL权限管理以角色（Role）为核心，构建覆盖数据库连接、模式访问及表/视图/序列/函数等对象的精细化四级权限体系，通过GRANT/REVOKE精准分配操作权限（如SELECT、INSERT、EXECUTE），支持角色继承、默认权限配置（ALTER DEFAULT PRIVILEGES），并严格遵循最小权限原则、撤销public模式默认权限、定期审计等安全实践，兼顾灵活性与安全性，实现高效可控的数据库访问治理。

PostgreSQL采用基于角色的权限管理系统，通过精细化的权限控制保障数据库安全。

一、权限体系结构

PostgreSQL权限管理采用四级层次结构：

复制连接权限 → 数据库权限 → 模式权限 → 对象权限

二、角色管理

在PostgreSQL中，角色是权限的载体，可以是登录角色（用户）或组角色（权限集合）：

-- 创建普通角色（无登录权限，用作组角色）
CREATE ROLE group_developer;

-- 创建登录角色（用户）并设置密码
CREATE ROLE app_user WITH LOGIN PASSWORD 'password';

-- 创建超级用户（拥有所有权限）
CREATE ROLE super_user WITH SUPERUSER;

-- 修改角色
ALTER ROLE app_user WITH PASSWORD 'new_password';

-- 删除角色
DROP ROLE app_user;

三、权限类型详解

1. 数据库级权限

  • CONNECT：允许连接到数据库
  • CREATE：允许在数据库中创建模式
  • TEMPORARY/TEMP：允许创建临时表
  • ALL PRIVILEGES：授予所有数据库权限
GRANT CONNECT ON DATABASE mydb TO app_user;
GRANT CREATE ON DATABASE mydb TO app_user;
GRANT ALL PRIVILEGES ON DATABASE mydb TO app_user;

2. 模式级权限

  • USAGE：允许访问模式中的对象
  • CREATE：允许在模式中创建新对象
GRANT USAGE ON SCHEMA public TO app_user;
GRANT CREATE ON SCHEMA public TO app_user;

3. 表/视图级权限

  • SELECT：读取表数据
  • INSERT：插入新数据
  • UPDATE：更新数据（可指定列）
  • DELETE：删除数据
  • TRUNCATE：清空表数据
  • REFERENCES：允许创建外键约束
  • TRIGGER：允许创建触发器
GRANT SELECT, INSERT, UPDATE, DELETE ON table_name TO app_user;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO app_user;

4. 序列权限

  • USAGE：使用序列
  • SELECT：查询序列当前值
  • UPDATE：推进序列
GRANT USAGE, SELECT ON SEQUENCE seq_name TO app_user;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO app_user;

5. 函数权限

  • EXECUTE：执行函数
GRANT EXECUTE ON FUNCTION func_name TO app_user;
GRANT ALL PRIVILEGES ON ALL ROUTINES IN SCHEMA public TO app_user;

四、角色继承与权限传递

-- 将角色添加到组（继承组权限）
GRANT group_developer TO app_user;

-- 撤销角色继承
REVOKE group_developer FROM app_user;

-- 允许角色将权限授予其他角色
GRANT SELECT ON table_name TO app_user WITH GRANT OPTION;

五、默认权限设置

为新创建的对象自动设置权限：

-- 为psi_root创建的表设置默认权限
ALTER DEFAULT PRIVILEGES FOR ROLE psi_root 
GRANT ALL ON TABLES TO pgrolereadwrite;

-- 为psi_root创建的序列设置默认权限
ALTER DEFAULT PRIVILEGES FOR ROLE psi_root 
GRANT ALL ON SEQUENCES TO pgrolereadwrite;

-- 为psi_root创建的函数设置默认权限
ALTER DEFAULT PRIVILEGES FOR ROLE psi_root IN SCHEMA public
GRANT ALL PRIVILEGES ON ROUTINES TO pgrolereadwrite;

六、权限管理最佳实践

1. 撤销public schema默认权限（安全加固）

-- 撤销public schema的CREATE权限（PostgreSQL 14及之前版本尤为重要）
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

-- 撤销数据库的所有默认权限
REVOKE ALL ON DATABASE lab_db FROM public;

2. 实现读写分离的权限配置

-- 创建只读角色和读写角色
CREATE ROLE pgrolereadonly;
CREATE ROLE pgrolereadwrite;

-- 为读写角色授予权限
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO pgrolereadwrite;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO pgrolereadwrite;

-- 为只读角色授予权限
GRANT SELECT ON ALL TABLES IN SCHEMA public TO pgrolereadonly;

-- 创建用户并分配角色
CREATE USER readonly_user WITH LOGIN PASSWORD 'password';
GRANT pgrolereadonly TO readonly_user;

CREATE USER readwrite_user WITH LOGIN PASSWORD 'password';
GRANT pgrolereadwrite TO readwrite_user;

3. 最小权限原则应用（监控场景）

-- 创建专用schema存放监控函数
CREATE SCHEMA pghero;

-- 创建SECURITY DEFINER函数访问系统表
CREATE OR REPLACE FUNCTION pghero.pg_stat_activity() 
RETURNS SETOF pg_stat_activity AS
$$
SELECT * FROM pg_catalog.pg_stat_activity;
$$ LANGUAGE sql VOLATILE SECURITY DEFINER;

-- 创建视图
CREATE VIEW pghero.pg_stat_activity AS
SELECT * FROM pghero.pg_stat_activity();

-- 授予监控所需最小权限
GRANT pg_read_all_settings TO monitor_user;
GRANT pg_read_all_stats TO monitor_user;
GRANT pg_monitor TO monitor_user;

七、权限查询与审计

-- 查看当前数据库中所有对象的权限
\dp

-- 查看特定角色的权限
SELECT * FROM pg_roles WHERE rolname = 'username';

-- 查看特定表的权限
SELECT grantee, privilege_type 
FROM information_schema.role_table_grants 
WHERE table_name = 'table_name';

-- 查看角色成员关系
\du

八、安全注意事项

  1. 避免使用超级用户进行日常操作：创建具有适当权限的专用角色
  2. 遵循最小权限原则：只授予完成任务所需的最小权限
  3. 定期审计权限：检查和清理不必要的权限
  4. 使用专用schema：不要将业务表放在public schema中
  5. 注意PostgreSQL版本差异：PostgreSQL 15+不再自动授予public schema的CREATE权限

通过合理配置PostgreSQL权限，可以有效保障数据库安全，同时满足不同业务场景的需求。