PostgreSQL 用户、角色与权限管理-未完纪

下面学习如何创建和管理 PostgreSQL 数据库用户角色。

群组与用户

一个角色既可以代表单个数据库用户，也可以代表一组用户。群组可以对应某种工作职责，例如会计、销售或市场。

数据库角色在整个数据库集群安装范围内全局有效，并不属于某一个数据库。

显示全部角色

在 psql 中随时可以查看角色：

\du

准备测试数据库

创建一个简单数据库，探索角色管理：

 CREATE DATABASE finance;
CREATE SCHEMA accounting;

 CREATE TABLE accounting.invoices(
  invoice_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  invoice_date DATE,
  amount NUMERIC
);

 INSERT INTO accounting.invoices(invoice_date, amount)
VALUES ('2024-03-15', 250.50);
INSERT INTO accounting.invoices(invoice_date, amount)
VALUES ('2024-01-20', 110.99);
INSERT INTO accounting.invoices(invoice_date, amount)
VALUES ('2024-03-29', 1000);

创建用户角色

为名叫 simon 的人创建角色。Postgres 的标识符大小写规则需要注意，使用全小写可以让基本 SQL 与 psql 交互更简单。创建带密码、允许登录的角色：

 CREATE ROLE simon WITH PASSWORD '21654641seeswf!2@' LOGIN;

在当前会话切换为 simon：

 SET ROLE simon;

为角色添加权限

使用新身份查询发票表：

 SELECT * FROM accounting.invoices;

ERROR:  permission denied for schema accounting
LINE 1: SELECT FROM accounting.invoices;

出现这个错误符合预期，因为 simon 目前只有登录权限。先切回 postgres：

 SET ROLE postgres;

加入查询发票表所需权限。GRANT SELECT 授予读取权限：

 GRANT CONNECT ON DATABASE finance to simon;
GRANT USAGE ON SCHEMA accounting TO simon;
GRANT SELECT ON accounting.invoices TO simon;

也可以按需分别授予插入、更新或删除权限：

 GRANT INSERT, UPDATE, DELETE ON accounting.invoices TO simon;

再次切换到 simon：

 SET ROLE simon;

确认能够查询发票表：

 SELECT * FROM accounting.invoices;

此时 simon 已经能够读取 accounting.invoices。

撤销角色权限

使用 REVOKE 与 FROM 撤销权限。先切换为 postgres：

 SET ROLE postgres;

与之前的授权命令类似，将 GRANT 改为 REVOKE，将 TO 改为 FROM：

 REVOKE CONNECT ON DATABASE finance FROM simon;
REVOKE USAGE ON SCHEMA accounting FROM simon;
REVOKE SELECT, INSERT, UPDATE, DELETE ON accounting.invoices FROM simon;

创建群组角色

员工很多时，逐个分配权限很快就会变得繁琐。建议采用以下方式：

创建“群组”角色。
将完成工作所需的权限授予该角色。
将这个角色授予用户角色。

Postgres 过去曾区分群组和角色，后来统一为角色嵌套。

为会计部门创建只读角色 accounting_ro：

 SET ROLE postgres;

 CREATE ROLE accounting_ro NOLOGIN;
GRANT CONNECT ON DATABASE finance TO accounting_ro;
GRANT USAGE ON SCHEMA accounting TO accounting_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA accounting TO accounting_ro;

这里使用 NOLOGIN，确保没人可以直接以该角色登录。它更像是一个群组的权限定义。

将 accounting_ro 成员资格授予 simon：

 GRANT accounting_ro TO simon;

查看角色列表：

\du

可以看到新角色 accounting_ro，且 simon 已成为其成员。

创建新的 customers 表：

 CREATE TABLE accounting.customers(
  customer_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name TEXT,
  address TEXT
);

切换到 simon：

 SET ROLE simon;

确认能够查询 invoices：

 SELECT * FROM accounting.invoices;

再尝试 customers：

 SELECT * FROM accounting.customers;

ERROR:  permission denied for table customers

这也是预期错误。执行 GRANT SELECT ON ALL TABLES 时，customers 表尚不存在。可以补充授予该表的权限。切换为 postgres 后重新授权：

 SET ROLE postgres;

 GRANT SELECT ON ALL TABLES IN SCHEMA accounting TO accounting_ro;

自动授予未来新表的访问权限

如果希望未来新建的表自动获得相应权限，需要设置模式中的默认权限：

 ALTER DEFAULT PRIVILEGES IN SCHEMA accounting GRANT SELECT ON TABLES TO accounting_ro;

预定义角色与 MAINTAIN

除自建群组角色外，现代 Postgres 还提供实用的预定义角色，无需反复手动配置广泛的读写授权：

pg_read_all_data：读取全部表与视图。
pg_write_all_data：在全部表与视图上插入、更新和删除。
pg_monitor：读取监控视图和相关函数。

 GRANT pg_read_all_data TO accounting_ro;

Postgres 17 还增加了 MAINTAIN 权限和 pg_maintain 角色，用于 VACUUM、ANALYZE、REINDEX、REFRESH MATERIALIZED VIEW 等操作，而无需授予完整表所有权。仅需要维护访问的运维或自动化角色，应优先考虑此权限。

为应用与工具创建角色

与为 simon 授权类似，每个连接数据库的服务或应用也需要相应角色。例如，可以创建 application 和 analytics 角色。

连接字符串中的角色与登录

在数据库中创建角色后，其他应用或服务连接数据库时，也可能需要使用这些角色。

数据库 URL 形式的连接字符串包括：

协议：postgres://。
用户名：例如 postgres、application 或其他用户。
密码。
主机名：集群主机名。
端口：默认 5432，除非已经修改。
数据库名：通常为 postgres，除非另建数据库。

下面是连接字符串的可视化示例：

最小权限原则

分配角色与权限时，应从数据安全和业务安全出发，仅授予该角色履行核心职责所需的最小权限。不要默认授予更多，也不要添加无关职责的角色。角色可以随时再调整。

结语

角色是数据库的重要组成部分。Postgres 角色灵活而强大，可以利用角色成员关系简化维护，也可以按需求定制权限，控制谁能访问数据。

始终遵循最小权限原则，确保数据访问严格且安全。更多角色与表级保护方法，见 Crunchy Data 的行级安全教程。

原文：Postgres Users and Roles。作者／维护方：Crunchy Data 教程团队。本文为中文翻译，代码及命令保留原文。

文章版权归作者所有，未经允许请勿转载。

THE END

技术译文

PostgreSQL 用户、角色与权限管理