PostgreSQL - 权限

PostgreSQL - 管理权限和角色

数据库安全的核心是管理谁可以访问什么。在 PostgreSQL 中，这通过角色和权限系统进行处理。当创建对象（如表或函数）时，它会被分配一个所有者。默认情况下，只有所有者（或超级用户）才能访问或修改它。要允许其他用户与对象交互，你必须授予他们特定的权限。

理解角色

在现代 PostgreSQL 中，“用户”和“组”之间没有技术差异。两者都被视为角色。一个角色可以拥有特定的属性，例如登录能力（LOGIN）或创建数据库的能力（CREATEDB）。一个角色也可以是另一个角色的成员，从而允许你创建权限层次结构。

这是现代且推荐的方法：为特定目的创建角色（例如，read_only、data_analyst），授予权限给这些角色，然后将这些角色的成员资格授予你的应用程序或人类用户。

常用权限

以下是你将管理的一些最常用权限：

• SELECT：允许从表或视图读取数据。
• INSERT：允许向表中添加新行。
• UPDATE：允许修改表中的现有行。
• DELETE：允许从表中删除行。
• TRUNCATE：允许快速删除表中的所有行。
• USAGE：对于模式（schemas），允许访问该模式内的对象。对于序列（sequences），允许使用 currval 和 nextval。
• EXECUTE：允许调用函数。
• CONNECT：允许一个角色连接到特定数据库。

语法：GRANT 和 REVOKE

要分配和移除权限，你使用 GRANT 和 REVOKE 命令。

-- 授予权限
GRANT privilege_type [, ...] ON object_type object_name [, ...]
TO role_name [, ...];

-- 撤销权限
REVOKE privilege_type [, ...] ON object_type object_name [, ...]
FROM role_name [, ...];

实用示例：基于角色的访问控制

让我们循序渐进地讲解一个现代、最佳实践的示例。我们将不再直接向用户授予权限，而是创建功能性角色。

步骤 1：为我们的应用程序创建一个登录角色。

此角色可以登录，但最初没有其他权限。注意：在实际应用中，始终使用安全、生成的密码。

CREATE ROLE web_app LOGIN PASSWORD 'a_very_secure_password';

步骤 2：为只读访问创建一个组角色。

此角色本身无法登录；它只是一个权限容器。

CREATE ROLE read_only;

步骤 3：授予 read_only 角色必要的权限。

假设我们的表位于 dev_db 数据库的 public 模式中。

-- 允许连接到数据库
GRANT CONNECT ON DATABASE dev_db TO read_only;

-- 允许使用 'public' 模式
GRANT USAGE ON SCHEMA public TO read_only;

-- 授予对模式中所有现有表的 SELECT 权限
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only;

最佳实践：默认权限

将来创建的表怎么办？ALTER DEFAULT PRIVILEGES 确保由特定用户（例如，迁移用户）创建的任何新表将自动拥有正确的权限。

-- 作为将来创建表的用户（例如 'postgres' 或 'migration_role'）
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO read_only;

步骤 4：将组角色分配给我们的应用程序用户。

GRANT read_only TO web_app;

现在，web_app 角色继承了 read_only 角色的所有权限。这比逐个用户分配权限更具可维护性。

撤销权限和清理

要移除访问权限，只需撤销成员资格。

REVOKE read_only FROM web_app;

要删除角色本身：

DROP ROLE web_app;
DROP ROLE read_only;

高级主题：行级安全性（RLS）

为了更细粒度的控制，PostgreSQL 提供了行级安全性（Row-Level Security）。RLS 允许你定义策略，根据用户的角色或其他属性来控制用户可以查看或修改表中哪些行。这对于多租户应用程序是一个强大的功能，也是你进阶时需要探索的关键主题。