PostgreSQL 角色与权限管理

角色与权限概述

什么是RBAC

基于角色的访问控制（Role-Based Access Control，RBAC）是一种数据库安全模型，通过将权限分配给角色，再将角色分配给用户，实现对数据库资源的访问控制。PostgreSQL从8.1版本开始全面支持RBAC模型。

角色与用户的区别

在PostgreSQL中，角色和用户本质上是相同的，唯一区别是创建时是否带有LOGIN属性：

• 用户：带有LOGIN属性的角色，可以用于登录数据库
• 角色：可以带有或不带有LOGIN属性，主要用于权限管理

权限类型

PostgreSQL的权限可以分为以下几类：

1. 对象权限：对数据库对象（表、视图、序列、函数等）的操作权限
2. 语句权限：执行特定SQL语句的权限（如CREATE、DROP、ALTER等）
3. 数据库权限：对数据库的连接、创建对象等权限
4. 模式权限：对模式的访问和操作权限

角色创建与管理

1. 创建角色

-- 创建带有登录权限的角色（用户）
CREATE ROLE dbuser WITH LOGIN PASSWORD 'dbuser_password';

-- 创建不带登录权限的角色（用于权限管理）
CREATE ROLE dbrole;

-- 创建具有多种属性的角色
CREATE ROLE admin_user WITH 
    LOGIN 
    PASSWORD 'admin_password' 
    SUPERUSER 
    CREATEDB 
    CREATEROLE 
    INHERIT 
    CONNECTION LIMIT 10;

2. 修改角色

-- 修改角色密码
ALTER ROLE dbuser WITH PASSWORD 'new_password';

-- 添加角色属性
ALTER ROLE dbuser WITH CREATEDB;

-- 移除角色属性
ALTER ROLE admin_user WITH NOSUPERUSER;

-- 修改角色名称
ALTER ROLE old_role RENAME TO new_role;

3. 删除角色

-- 删除角色（如果角色拥有对象，需要先移除对象或转移所有权）
DROP ROLE dbuser;

-- 强制删除角色及其拥有的所有对象
DROP ROLE IF EXISTS dbuser CASCADE;

4. 查看角色信息

-- 查看所有角色
\du

-- 查看角色详细信息
\du+ dbuser

-- 查询系统视图获取角色信息
SELECT * FROM pg_roles WHERE rolname = 'dbuser';

权限分配与管理

1. 对象权限

表权限

-- 授予SELECT权限
GRANT SELECT ON TABLE employees TO dbuser;

-- 授予多种权限
GRANT SELECT, INSERT, UPDATE ON TABLE employees TO dbuser;

-- 授予所有权限
GRANT ALL PRIVILEGES ON TABLE employees TO admin_user;

-- 授予表权限给角色
GRANT SELECT ON TABLE employees TO dbrole;

-- 授予表上的列权限
GRANT SELECT (id, name), UPDATE (salary) ON TABLE employees TO dbuser;

-- 撤销权限
REVOKE INSERT ON TABLE employees FROM dbuser;

-- 授予所有表的权限
GRANT SELECT ON ALL TABLES IN SCHEMA public TO dbrole;

-- 为未来创建的表自动授予权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO dbrole;

视图权限

-- 授予视图权限
GRANT SELECT ON VIEW employee_view TO dbuser;

-- 授予所有视图权限
GRANT SELECT ON ALL VIEWS IN SCHEMA public TO dbrole;

序列权限

-- 授予序列权限
GRANT USAGE, SELECT, UPDATE ON SEQUENCE employees_id_seq TO dbuser;

-- 授予所有序列权限
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO dbrole;

函数权限

-- 授予函数执行权限
GRANT EXECUTE ON FUNCTION get_employee_salary(int) TO dbuser;

-- 授予所有函数权限
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO dbrole;

2. 语句权限

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

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

-- 授予超级用户权限
GRANT SUPERUSER TO admin_user;

-- 撤销语句权限
REVOKE CREATEDB FROM dbuser;

3. 数据库权限

-- 授予数据库连接权限
GRANT CONNECT ON DATABASE mydb TO dbuser;

-- 授予数据库创建对象权限
GRANT CREATE ON DATABASE mydb TO admin_user;

-- 撤销数据库权限
REVOKE CONNECT ON DATABASE mydb FROM dbuser;

4. 模式权限

-- 授予模式使用权限
GRANT USAGE ON SCHEMA public TO dbuser;

-- 授予模式创建对象权限
GRANT CREATE ON SCHEMA public TO admin_user;

-- 授予所有模式权限
GRANT USAGE, CREATE ON ALL SCHEMAS IN DATABASE mydb TO admin_user;

角色继承与组管理

1. 角色继承

-- 创建角色并启用继承
CREATE ROLE parent_role WITH INHERIT;

-- 创建子角色，继承父角色权限
CREATE ROLE child_role IN ROLE parent_role;

-- 或使用GRANT语句
GRANT parent_role TO child_role;

-- 禁用角色继承
ALTER ROLE child_role WITH NOINHERIT;

-- 切换到角色执行命令
SET ROLE parent_role;
-- 执行需要父角色权限的操作
RESET ROLE;

2. 角色组管理

-- 创建只读角色组
CREATE ROLE readonly_role;

-- 授予只读权限
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_role;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO readonly_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE ON SEQUENCES TO readonly_role;

-- 创建读写角色组
CREATE ROLE readwrite_role;

-- 授予读写权限
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO readwrite_role;
GRANT USAGE, UPDATE ON ALL SEQUENCES IN SCHEMA public TO readwrite_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO readwrite_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE, UPDATE ON SEQUENCES TO readwrite_role;

-- 将用户添加到角色组
GRANT readonly_role TO user1, user2;
GRANT readwrite_role TO user3, user4;

3. 权限查看

-- 查看表的权限
\dp employees

-- 查看视图权限
\dp employee_view

-- 查看函数权限
\df+ get_employee_salary

-- 查询系统视图获取权限信息
SELECT * FROM information_schema.table_privileges WHERE grantee = 'dbuser';
SELECT * FROM information_schema.role_table_grants WHERE grantee = 'dbuser';

权限最佳实践

1. 最小权限原则

• 只授予用户完成工作所需的最小权限
• 避免使用超级用户进行日常操作
• 定期审查和回收不必要的权限

2. 角色分层设计

• 超级用户：仅用于数据库管理
• 管理员角色：负责数据库对象创建和管理
• 开发角色：负责应用开发和测试
• 只读角色：用于报表和查询用户
• 读写角色：用于业务应用

3. 权限管理流程

• 建立正式的权限申请和审批流程
• 定期（如每季度）进行权限审查
• 离职员工及时回收权限
• 记录权限变更日志

4. 使用默认权限

• 为新创建的对象自动授予适当的权限
• 减少手动权限管理的工作量
• 确保权限一致性

-- 为管理员设置默认权限
ALTER DEFAULT PRIVILEGES FOR ROLE admin_user IN SCHEMA public 
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO readwrite_role;

-- 为所有用户设置默认权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
    GRANT SELECT ON TABLES TO readonly_role;

5. 模式隔离

• 使用模式组织数据库对象
• 基于模式进行权限管理
• 不同应用或业务使用不同模式

-- 创建应用模式
CREATE SCHEMA app_schema;

-- 授予模式权限
GRANT USAGE, CREATE ON SCHEMA app_schema TO app_admin;
GRANT USAGE ON SCHEMA app_schema TO app_user;

-- 在模式中创建对象
CREATE TABLE app_schema.employees (...);

6. 定期权限审查

-- 审查用户权限
SELECT 
    grantee,
    table_schema,
    table_name,
    privilege_type
FROM information_schema.role_table_grants
WHERE table_schema NOT IN ('pg_catalog', 'information_schema');

-- 审查角色继承关系
SELECT 
    r1.rolname AS granted_role,
    r2.rolname AS grantee
FROM pg_auth_members m
JOIN pg_roles r1 ON m.roleid = r1.oid
JOIN pg_roles r2 ON m.member = r2.oid;

常见权限问题与解决方案

1. 权限不足错误

问题现象：

ERROR: permission denied for table employees

解决方案：

• 检查用户是否有表的访问权限
• 检查用户是否有模式的使用权限
• 检查角色继承是否正确

2. 无法创建对象

问题现象：

ERROR: permission denied to create relation in schema public

解决方案：

• 授予CREATE权限
• 检查用户是否有模式的CREATE权限
• 确保用户连接到正确的数据库

3. 无法访问序列

问题现象：

ERROR: permission denied for sequence employees_id_seq

解决方案：

• 授予序列的USAGE和UPDATE权限
• 检查默认权限设置
• 确保序列与表的权限一致

4. 角色继承不生效

问题现象：

• 用户拥有角色，但无法使用角色权限

解决方案：

• 检查角色是否启用了INHERIT属性
• 使用SET ROLE显式切换角色
• 检查角色权限是否正确授予

版本差异注意事项

PostgreSQL 9.x

• 权限管理：权限管理功能基础，但支持核心RBAC功能
• 默认权限：ALTER DEFAULT PRIVILEGES支持有限
• 系统视图：权限相关系统视图较少

PostgreSQL 10+

• 权限增强：改进了默认权限管理
• 角色属性：新增了更多角色属性
• 系统视图：提供了更丰富的权限查询视图

PostgreSQL 12+

• 权限管理：增强了权限管理功能
• 默认权限：支持更细粒度的默认权限设置
• 安全增强：改进了权限检查机制

PostgreSQL 14+

• 权限功能：新增了一些权限相关功能
• 安全增强：加强了权限验证
• 性能优化：权限检查性能优化

总结

PostgreSQL的RBAC模型提供了强大的权限管理功能，通过合理设计角色和权限，可以确保数据库的安全性和完整性。在实际生产环境中，建议：

1. 遵循最小权限原则，只授予必要的权限
2. 设计合理的角色层次结构，便于权限管理
3. 使用默认权限减少手动管理工作量
4. 定期审查和回收不必要的权限
5. 结合模式隔离，实现更细粒度的权限控制
6. 建立完善的权限管理流程和审计机制

通过科学的角色与权限管理，可以有效保护数据库资源，防止未授权访问和误操作，提高数据库系统的安全性和可靠性。