跳到主要内容

权限管理

前言​

如果说用户管理是数据库安全的第一道防线，那么权限管理就是这道防线的核心。在PostgreSQL中，权限管理就像是为城堡中的访客颁发不同的通行证，有些可以进入大厅，有些可以进入贵宾室，而有些则只能参观特定的展览区。精细的权限管理既能保障数据安全，又能确保用户能够顺利完成工作。

权限类型​

PostgreSQL中的权限主要分为以下几类：

数据库对象权限​

  • SELECT：查询权限
  • INSERT：插入权限
  • UPDATE：更新权限
  • DELETE：删除权限
  • REFERENCES：引用权限（外键）
  • TRIGGER：触发器权限
  • CREATE：创建对象权限
  • CONNECT：连接数据库权限
  • TEMPORARY：创建临时表权限
  • EXECUTE：执行函数权限
  • USAGE：使用模式/序列/类型权限

管理权限​

  • ALTER：修改对象结构
  • DROP：删除对象
  • OWNER：对象所有权

授权机制​

PostgreSQL采用基于角色的访问控制（RBAC）模型，通过GRANT和REVOKE命令来管理权限。

GRANT命令​

GRANT命令用于授予权限，基本语法如下：

GRANT privilege [, ...] ON object [, ...] TO grantee [, ...] [ WITH GRANT OPTION ]

例如，授予用户查询表的权限：

GRANT SELECT ON employees TO analyst;

授予用户多个权限：

GRANT SELECT, INSERT, UPDATE ON employees TO manager;

授予所有权限：

GRANT ALL PRIVILEGES ON employees TO admin;

使用WITH GRANT OPTION允许被授权者进一步授权：

GRANT SELECT ON employees TO manager WITH GRANT OPTION;

REVOKE命令​

REVOKE命令用于撤销权限，基本语法如下：

REVOKE [ GRANT OPTION FOR ] privilege [, ...] ON object [, ...] FROM grantee [, ...] [ CASCADE | RESTRICT ]

例如，撤销用户的查询权限：

REVOKE SELECT ON employees FROM analyst;

撤销用户的所有权限：

REVOKE ALL PRIVILEGES ON employees FROM analyst;

注意撤销权限时的级联效应：

REVOKE SELECT ON employees FROM manager CASCADE;

权限继承​

PostgreSQL支持角色继承，这大大简化了权限管理。角色继承允许子角色自动获得父角色的所有权限。

设置角色继承​

-- 创建角色
CREATE ROLE readonly;
CREATE ROLE readwrite;

-- 设置继承关系
GRANT readonly TO readwrite;

现在，readwrite角色将自动获得readonly角色的所有权限。

查看角色继承关系​

SELECT * FROM pg_auth_members;

权限检查​

有时候我们需要检查用户或角色是否拥有特定权限：

-- 检查特定权限
has_table_privilege('username', 'tablename', 'SELECT');

-- 检查所有权限
has_table_privilege('username', 'tablename', 'ALL');

常见权限问题与解决方案​

用户无法访问表​

问题：用户提示"permission denied for table xxx"

解决方案：

-- 授予查询权限
GRANT SELECT ON xxx TO username;

用户无法创建表​

问题：用户提示"permission denied to create table"

解决方案：

-- 授予创建表权限
GRANT CREATE ON SCHEMA public TO username;
-- 或者直接授予创建数据库的权限
ALTER USER username CREATEDB;

用户无法连接数据库​

问题：用户提示"permission denied for database xxx"

解决方案：

-- 授予连接权限
GRANT CONNECT ON DATABASE xxx TO username;

组角色管理权限​

组角色是PostgreSQL权限管理的高级特性，它允许将多个权限组合成一个角色，然后把这个角色授予用户或其他角色。这种模式类似于现实生活中的"通行证"，不同级别的通行证拥有不同的访问权限，用户只需持有相应的通行证即可获得对应权限。

创建组角色​

在PostgreSQL中，创建角色时可以使用INHERIT选项，使角色能够继承授予它的其他角色的权限。

-- 创建只读组角色（具有继承权限的能力）
CREATE ROLE readonly_role WITH INHERIT;

-- 创建读写组角色（具有继承权限的能力）
CREATE ROLE readwrite_role WITH INHERIT;

-- 创建管理员组角色（具有继承权限的能力）
CREATE ROLE admin_role WITH INHERIT;

-- 或者使用简写形式
CREATE ROLE readonly_role INHERIT;
CREATE ROLE readwrite_role INHERIT;
CREATE ROLE admin_role INHERIT;

INHERIT选项是PostgreSQL角色的默认行为，显式声明可以使代码更清晰，并确保角色能够正确继承权限。

为组角色分配权限​

-- 为只读组角色分配权限
GRANT CONNECT ON DATABASE company_db TO readonly_role;
GRANT USAGE ON SCHEMA public TO readonly_role;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_role;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly_role;

-- 为读写组角色分配权限
GRANT CONNECT ON DATABASE company_db TO readwrite_role;
GRANT USAGE ON SCHEMA public TO readwrite_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO readwrite_role;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO readwrite_role;

-- 为管理员组角色分配权限
GRANT ALL PRIVILEGES ON DATABASE company_db TO admin_role;
GRANT ALL ON SCHEMA public TO admin_role;
GRANT ALL ON ALL TABLES IN SCHEMA public TO admin_role;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO admin_role;

设置角色继承关系​

-- 管理员角色继承读写角色的权限
GRANT readwrite_role TO admin_role;

-- 读写角色继承只读角色的权限
GRANT readonly_role TO readwrite_role;

将组角色授予用户​

-- 创建用户
CREATE USER analyst WITH PASSWORD 'securePassword123';
CREATE USER developer WITH PASSWORD 'securePassword456';
CREATE USER dba WITH PASSWORD 'securePassword789';

-- 将用户分配到相应的组角色
GRANT readonly_role TO analyst;
GRANT readwrite_role TO developer;
GRANT admin_role TO dba;

查看组角色权限分配​

-- 查看角色继承关系
SELECT r.rolname AS role_name, 
       m.rolname AS member_name,
       m.rolcanlogin AS is_user
FROM pg_auth_members a
JOIN pg_roles r ON a.roleid = r.oid
JOIN pg_roles m ON a.member = m.oid
ORDER BY r.rolname, m.rolname;

-- 查看用户所属的角色
SELECT u.usename, r.rolname AS role_name
FROM pg_user u
JOIN pg_auth_members a ON u.usesysid = a.member
JOIN pg_roles r ON a.roleid = r.oid
ORDER BY u.usename, r.rolname;

组角色的动态管理​

-- 添加新权限到组角色
GRANT SELECT ON new_table TO readonly_role;
GRANT INSERT, UPDATE ON new_table TO readwrite_role;

-- 从组角色移除权限
REVOKE SELECT ON old_table FROM readonly_role;
REVOKE DELETE FROM readwrite_role ON old_table;

-- 添加新用户到组角色
GRANT readonly_role TO new_analyst;

-- 从组角色移除用户
REVOKE readonly_role FROM departed_user;

使用组角色进行模式级别的权限管理​

-- 创建新模式
CREATE SCHEMA finance;
CREATE SCHEMA hr;

-- 创建模式级别的组角色
CREATE ROLE finance_readonly;
CREATE ROLE finance_readwrite;
CREATE ROLE hr_readonly;

-- 为模式级别的组角色分配权限
GRANT USAGE ON SCHEMA finance TO finance_readonly, finance_readwrite;
GRANT SELECT ON ALL TABLES IN SCHEMA finance TO finance_readonly;
GRANT ALL ON ALL TABLES IN SCHEMA finance TO finance_readwrite;

GRANT USAGE ON SCHEMA hr TO hr_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA hr TO hr_readonly;

-- 将模式级别的组角色授予用户
GRANT finance_readonly TO finance_analyst;
GRANT finance_readwrite TO finance_manager;
GRANT hr_readonly TO hr_staff;

最佳实践​

  1. 最小权限原则：遵循最小权限原则，只授予必要的权限
  2. 使用角色：通过角色管理权限，而不是直接管理用户权限
  3. 定期审查：定期审查权限分配，确保权限分配合理
  4. 避免过度授权：谨慎使用WITH GRANT OPTION，避免权限扩散
  5. 分离敏感操作：将敏感操作分配给专门的角色，避免普通用户拥有过多权限
  6. 使用组角色：利用组角色简化权限管理，提高效率
  7. 按职能分组：根据用户职能创建相应的组角色，便于管理
  8. 文档化权限：记录每个组角色的权限范围，便于审计和维护

权限管理就像是为城堡中的访客分配不同的通行证，既要保证安全，又要不妨碍正常的工作。掌握PostgreSQL的权限管理，就像是掌握了城堡的钥匙分配系统，能够确保每个人都能在合适的区域活动，而不会踏入不该进入的区域。记住，良好的权限管理是数据库安全的核心，也是数据库管理员的基本功。