PostgreSQL 中管理用户、角色和权限的基本命令

数据库安全性依赖于强大的访问控制。在 PostgreSQL 中，这种控制主要通过角色和相关的权限（或特权）来管理。理解如何创建、修改和管理这些安全实体对于任何数据库管理员或开发人员来说都至关重要。

本综合指南探讨了在 PostgreSQL 环境中安全管理用户、定义访问策略以及实施最小权限原则所需的基本 SQL 命令。我们将涵盖角色创建、属性设置、组成员资格以及使用 GRANT 和 REVOKE 进行详细的对象权限管理。

理解 PostgreSQL 角色

与其他严格区分用户和组的数据库系统不同，PostgreSQL 使用了一个统一的概念：角色。一个角色可以代表：

数据库用户（可以登录的实体，通常具有 LOGIN 属性）。
用户组（仅用于权限分组的实体，通常不具有 LOGIN 属性）。

有效的安全性始于定义能够准确反映访问数据库的个人或应用程序职责的角色。

1. 角色管理：创建、修改和删除

管理数据库主体的主要命令围绕着 CREATE ROLE、ALTER ROLE 和 DROP ROLE。

创建新角色

创建角色时，您必须指定其属性，特别是它是否可以登录 (LOGIN) 及其认证凭据 (PASSWORD)。

创建基本登录用户

要创建一个需要密码才能连接的标准用户角色：

CREATE ROLE app_user WITH LOGIN PASSWORD 'strong_password_here';

创建具有特定属性的角色

角色可以拥有定义其能力和系统权限的各种属性：

属性	描述
`LOGIN`	允许角色连接到数据库。
`SUPERUSER`	授予所有数据库特权（谨慎使用）。
`CREATEROLE`	允许角色创建、修改和删除其他角色。
`CREATEDB`	允许角色创建新数据库。
`REPLICATION`	允许角色启动流复制。
`INHERIT` / `NOINHERIT`	控制角色是否自动继承其所属角色的权限（默认为 `INHERIT`）。

示例：创建管理员角色（非超级用户）

CREATE ROLE db_admin WITH
    LOGIN
    PASSWORD 'admin_secret'
    CREATEROLE
    CREATEDB
    VALID UNTIL '2025-01-01'; -- 可选的过期日期

修改现有角色

使用 ALTER ROLE 修改现有角色的属性或更新密码。

-- 更改现有用户的密码
ALTER ROLE app_user WITH PASSWORD 'new_strong_password';

-- 撤销登录能力（将用户转变为组）
ALTER ROLE db_admin NOLOGIN;

-- 将用户锁定
ALTER ROLE old_employee NOLOGIN;

删除角色

在删除角色之前，请确保该角色不拥有任何数据库对象（表、模式等）。如果拥有，您必须首先使用 REASSIGN OWNED 转移所有权。

-- 删除角色拥有的所有对象并将其重新分配给 'postgres'
REASSIGN OWNED BY old_employee TO postgres;

-- 然后，删除角色
DROP ROLE old_employee;

警告： 删除角色是不可逆的。请谨慎操作，特别是对于拥有许多对象的角色。

2. 管理组成员资格

角色通常作为组来简化权限管理。与其向 50 个独立用户授予权限，不如将权限授予一个组角色，然后将这些用户设为该组的成员。

创建组角色

组通常在没有 LOGIN 属性的情况下创建。

CREATE ROLE data_analysts NOLOGIN;

授予和撤销组成员资格

使用 GRANT 命令将成员添加到组角色，使用 REVOKE 命令移除成员。

-- 将用户 'alice' 和用户 'bob' 添加到 'data_analysts' 组
GRANT data_analysts TO alice, bob;

-- 从 'data_analysts' 组中移除 'bob'
REVOKE data_analysts FROM bob;

`WITH ADMIN OPTION` 选项

如果您在授予成员资格时包含 WITH ADMIN OPTION，则接收角色可以进一步将该组的成员资格授予其他人，并且还可以 DROP 该组角色。

GRANT data_analysts TO supervisor WITH ADMIN OPTION;

3. 管理对象权限（特权）

权限定义了角色可以对哪些数据库对象（表、视图、函数、模式等）执行什么操作。这是数据库安全的核心。

`GRANT` 命令语法

GRANT privilege_list ON object_type object_name TO role_name [WITH GRANT OPTION];

常见特权和示例

表权限

特权	允许的操作
`SELECT`	从表中读取数据。
`INSERT`	添加新行。
`UPDATE`	修改现有行。
`DELETE`	删除现有行。
`TRUNCATE`	完全清空表。
`REFERENCES`	创建外键约束。

示例： 授予对特定表的只读访问权限。

GRANT SELECT ON TABLE production.orders TO data_analysts;

-- 授予对表的所有 DML 操作
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE staging.temp_data TO app_user;

数据库和模式权限

数据库和模式权限对于控制环境结构至关重要。

对象	关键特权	目的
数据库	`CONNECT`	允许连接到数据库。
数据库	`CREATE`	允许创建新的模式、表空间等。
模式	`USAGE`	允许访问模式内的对象。
模式	`CREATE`	允许在模式内创建新对象。

示例：授予模式访问权限

如果用户需要访问 app_schema 中的表，他们必须拥有该模式的 USAGE 权限。

GRANT USAGE ON SCHEMA app_schema TO app_user;
GRANT SELECT ON ALL TABLES IN SCHEMA app_schema TO app_user;

序列和函数权限

序列使用（用于自增 ID）和函数执行需要特定的权限。

-- 允许用户推进序列（INSERT 操作所必需）
GRANT USAGE, SELECT ON SEQUENCE app_schema.user_id_seq TO app_user;

-- 允许执行特定的存储过程或函数
GRANT EXECUTE ON FUNCTION audit_log_insert(text) TO app_user;

撤销权限

使用 REVOKE 命令移除先前授予的特定权限。其语法与 GRANT 相似。

-- 撤销向 orders 表插入新记录的能力
REVOKE INSERT ON TABLE production.orders FROM app_user;

-- 撤销之前授予的所有权限（注意：不撤销所有权）
REVOKE ALL PRIVILEGES ON TABLE production.orders FROM data_analysts;

关于撤销的注意事项： 如果权限被授予给某个角色，并且该角色是另一个组的成员，那么撤销权限只影响直接授予的权限。如果该角色仍然通过组成员资格继承权限，他们将保留访问权限。

将权限应用于未来对象

管理尚未存在的对象的权限需要使用 ALTER DEFAULT PRIVILEGES。这对于应用程序经常创建新表的模式至关重要。

示例：确保用户可以从 app_owner 角色在 app_schema 中创建的所有未来表中进行 SELECT 操作：

ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app_schema
    GRANT SELECT ON TABLES TO app_user;

4. 查看当前权限

为了审计安全设置，PostgreSQL 提供了几种工具和目录视图。

命令/视图	描述
`\du` (在 psql 中)	列出所有角色及其属性。
`\du+ role_name` (在 psql 中)	显示详细的角色属性和成员资格。
`\dp table_name` (在 psql 中)	列出授予表上的权限（特权）。
`pg_roles`	包含角色定义的系统目录视图。
`information_schema.table_privileges`	显示授予表上特权的视图。

示例：通过 psql 检查表的权限

=> \dp production.orders

用户管理的安全最佳实践

最小权限原则 (PoLP)： 只授予角色执行其功能所需的最小权限集。除非绝对必要，否则避免授予 ALL PRIVILEGES 或 SUPERUSER。
分离应用程序角色： 为应用程序（例如，api_service_role）使用专用角色，并将其与人工 DBA 角色分开。应用程序通常只应拥有 DML 权限（SELECT、INSERT、UPDATE、DELETE），而不应拥有 DDL 权限（CREATE、DROP）。
广泛使用组： 创建非登录角色（组）来管理权限集（例如，read_only_group、etl_writer_group）。将单个用户分配到这些组，而不是单独授予权限。
避免使用默认角色： 切勿将 postgres 超级用户角色用于一般的应用程序或开发任务。
安全认证： 始终使用强密码，并在可能的情况下，利用客户端证书认证或集中式身份管理解决方案，而不是基于密码的认证。

结论

有效管理角色和权限是 PostgreSQL 安全的基石。通过掌握 CREATE ROLE、ALTER ROLE、GRANT 和 REVOKE，数据库管理员可以实现精细的控制，确保每个访问数据库的用户或应用程序都拥有所需的确切访问权限，不多也不少。始终如一地应用最小权限原则并利用组角色，可以简化长期维护，并增强数据库抵御未经授权访问的能力。