PostgreSQL 角色与权限模型

核心概念

PostgreSQL的角色与权限模型基于以下核心概念：

• 角色：用于管理数据库访问权限的实体，可以是用户或组
• 权限：控制角色对数据库对象的操作能力
• 继承：角色可以继承其他角色的权限
• 对象权限：针对表、视图、函数等具体对象的权限
• 系统权限：针对数据库系统级别的权限
• 默认权限：新创建对象时自动应用的权限

角色管理

创建与删除角色

-- 创建普通用户角色
CREATE ROLE app_user WITH LOGIN PASSWORD 'secure_password';

-- 创建管理员角色
CREATE ROLE db_admin WITH SUPERUSER CREATEDB CREATEROLE LOGIN PASSWORD 'admin_password';

-- 创建组角色（无登录权限）
CREATE ROLE read_only_group;

-- 删除角色
DROP ROLE IF EXISTS old_user;

角色属性管理

-- 修改角色属性
ALTER ROLE app_user WITH NOSUPERUSER NOCREATEDB NOCREATEROLE;

-- 修改角色密码
ALTER ROLE app_user WITH PASSWORD 'new_secure_password';

-- 锁定/解锁角色
ALTER ROLE app_user WITH NOLOGIN;
ALTER ROLE app_user WITH LOGIN;

-- 设置角色有效期
ALTER ROLE app_user WITH VALID UNTIL '2025-12-31';

角色继承与成员关系

-- 将用户添加到组
GRANT read_only_group TO app_user;

-- 从组中移除用户
REVOKE read_only_group FROM app_user;

-- 设置角色继承（PostgreSQL 10+）
ALTER ROLE app_user INHERIT;

-- 查看角色成员关系
SELECT * FROM pg_auth_members;

权限管理

对象权限授予与回收

-- 授予表的SELECT权限
GRANT SELECT ON TABLE public.orders TO read_only_group;

-- 授予表的所有权限
GRANT ALL PRIVILEGES ON TABLE public.orders TO db_admin;

-- 授予模式下所有表的SELECT权限
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only_group;

-- 回收权限
REVOKE INSERT, UPDATE ON TABLE public.orders FROM app_user;

默认权限设置

-- 设置新创建表的默认权限
ALTER DEFAULT PRIVILEGES FOR ROLE db_admin IN SCHEMA public 
GRANT SELECT ON TABLES TO read_only_group;

-- 设置新创建序列的默认权限
ALTER DEFAULT PRIVILEGES FOR ROLE db_admin IN SCHEMA public 
GRANT USAGE, SELECT ON SEQUENCES TO app_user;

系统权限管理

-- 授予创建数据库权限
GRANT CREATEDB TO app_admin;

-- 授予创建角色权限
GRANT CREATEROLE TO app_admin;

-- 授予超级用户权限（谨慎使用）
ALTER ROLE app_admin WITH SUPERUSER;

-- 查看角色权限
\du+ app_admin;

生产环境最佳实践

最小权限原则

• 为每个应用分配独立的角色，避免共享角色
• 只授予角色完成工作所需的最小权限
• 定期审查和回收不必要的权限
• 使用组角色管理权限，简化权限维护

角色分层设计

-- 设计示例：分层角色结构
-- 1. 基础组角色
CREATE ROLE base_role;
GRANT CONNECT ON DATABASE mydb TO base_role;
GRANT USAGE ON SCHEMA public TO base_role;

-- 2. 只读组角色
CREATE ROLE read_role IN ROLE base_role;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_role;
ALTER DEFAULT PRIVILEGES GRANT SELECT ON TABLES TO read_role;

-- 3. 读写组角色
CREATE ROLE write_role IN ROLE read_role;
GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO write_role;
ALTER DEFAULT PRIVILEGES GRANT INSERT, UPDATE, DELETE ON TABLES TO write_role;

-- 4. 应用角色
CREATE ROLE myapp_role IN ROLE write_role LOGIN PASSWORD 'app_password';

权限审计与监控

-- 查看角色权限分配
SELECT grantee, privilege_type, table_name 
FROM information_schema.role_table_grants 
WHERE table_schema = 'public';

-- 查看当前用户权限
SELECT * FROM session_roles;

-- 启用权限审计日志
ALTER SYSTEM SET log_connections = 'on';
ALTER SYSTEM SET log_disconnections = 'on';
ALTER SYSTEM SET log_statement = 'ddl';
SELECT pg_reload_conf();

版本差异考虑

• PostgreSQL 10+：引入了更灵活的角色继承机制
• PostgreSQL 11+：增强了默认权限管理
• PostgreSQL 12+：改进了权限检查性能
• PostgreSQL 14+：添加了更细粒度的权限控制选项

常见问题（FAQ）

Q1：如何查看角色的所有权限？

A1：可以使用以下方法查看角色权限：

-- 使用psql元命令
\du+ 角色名;

-- 查询系统表
SELECT r.rolname, r.rolsuper, r.rolcreaterole, r.rolcreatedb, 
       r.rolinherit, r.rolcanlogin
FROM pg_roles r
WHERE r.rolname = '角色名';

-- 查看对象权限
SELECT grantee, privilege_type, table_name
FROM information_schema.role_table_grants
WHERE grantee = '角色名';

Q2：如何实现行级权限控制？

A2：可以使用行级安全性（RLS）实现行级权限控制：

-- 启用行级安全性
ALTER TABLE employees ENABLE ROW LEVEL SECURITY;

-- 创建策略：只允许查看自己部门的员工
CREATE POLICY emp_dept_policy ON employees
    FOR SELECT
    USING (department_id = current_setting('app.current_dept')::integer);

-- 应用角色设置
CREATE ROLE hr_user WITH LOGIN;
GRANT SELECT ON employees TO hr_user;

Q3：如何处理权限继承不生效的问题？

A3：检查以下几点：

1. 确认角色设置了INHERIT属性：ALTER ROLE 角色名 INHERIT;
2. 确认使用了正确的权限授予语法：GRANT 组角色 TO 用户角色;
3. 检查PostgreSQL版本，确保支持角色继承
4. 确认没有使用NOINHERIT属性限制

Q4：如何批量管理角色权限？

A4：可以使用以下方法批量管理权限：

-- 批量授予模式下所有表的权限
GRANT SELECT ON ALL TABLES IN SCHEMA public TO 角色名;

-- 使用脚本批量处理
DO $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN SELECT tablename FROM pg_tables WHERE schemaname = 'public' LOOP
        EXECUTE format('GRANT SELECT ON TABLE public.%I TO read_only_group', r.tablename);
    END LOOP;
END$$;

Q5：如何安全地撤销超级用户权限？

A5：按以下步骤操作：

1. 创建一个新的管理员角色，只授予必要的权限
2. 测试新角色的功能是否正常
3. 将原超级用户的权限转移到新角色
4. 撤销原超级用户的超级权限：ALTER ROLE 原角色名 WITH NOSUPERUSER;
5. 监控系统运行情况，确保没有问题