别用superuser维护PG了, 很危险! 这几个内置角色了解一下?

用户4035096

发布于 2026-07-09 19:10:51

100

别用superuser维护PG了, 很危险! 这几个内置角色了解一下?

如果你的数据库是自建的, 维护数据库一般用什么角色? superuser对吧~~~

但是为了防止从任何地方都可以通过superuser登陆数据库, 通常可以在pg_hba.conf中进行控制, 例如只允许某个IP的来源登陆指定的superuser、只允许本地通过127.0.0.1地址登陆superuser、只允许本地通过socket登陆superuser等策略.

如果你是云厂商, 你肯定不会把superuser交给用户, 这意味着你是车主, 但方向盘在别人手里, 你还要为车损买单! 不可能,绝对不可能给superuser!

所以云厂商给的superuser通常都叫rds superuser, 阉割了一些危险操作(需要改造数据库内核), 特别是可以穿透到OS层的权限.

其实PG一直以来都在内置越来越多的内部role, 正逐步将权限细分为不同的内部角色. 所以既能保障安全、又能让你有权限维护数据库, 已逐渐成为可能!

让我们来看看都是写啥内置角色?

下面的内容翻译自: https://boringsql.com/posts/postgresql-predefined-roles/

别用superuser维护PG了, 很危险!

有多少人/服务拥有 PostgreSQL 集群的超级用户访问权限？你有没有问过为什么你的软件工程师需要它？或者你的 BI 团队？为什么这些用例需要与可以删除数据库的人相同的权限？

答案并非因为这些操作本身就很危险——而是因为 PostgreSQL 历来提供的操作访问权限选项有限，或者仅仅是因为没有足够多的人了解这些选项。因此，通常的做法是，要么获取基本权限，要么将权力移交给他人。

PostgreSQL内置的预定义角色通过为常见维护任务提供专用权限解决了这个问题。您无需授予超级用户访问权限来执行日常维护，而是可以委派特定的权限——监控团队获得全面的可观察性访问权限，备份服务获得数据读取功能，维护脚本获得所需的精确权限，仅此而已。

什么是预定义角色？

PostgreSQL 内置的管理角色是专门构建的权限集，可解决常见维护任务中依赖超级用户的困境。它提供了 15 个预定义角色，无需完全的超级用户权限即可提供对特定操作功能的精细访问权限。

虽然您可以在官方文档中查看它们的列表和描述，但在本文中，我们将更深入地探讨它们，同时研究系统目录以更好地理解它们。各个角色可以根据其功能进行分组，并且大多数角色都很容易掌握，从简单的监控访问到需要仔细考虑的强大文件系统操作。

数据访问角色

pg_database_owner - 数据库的ownership（特殊情况）
pg_read_all_data - 读取所有表、视图、序列的权限
pg_write_all_data - 对所有表、视图、序列的写访问权

监控与可观察性

pg_monitor - 实际上是监控元角色，包含下面列出的 3 个角色
pg_read_all_settings - 配置访问
pg_read_all_stats - 统计视图
pg_stat_scan_tables - 表扫描统计信息相关视图

系统操作

pg_signal_backend - cancel query和terminate sessions
pg_checkpoint - 运行 CHECKPOINT 命令
pg_maintain - VACUUM、ANALYZE、REINDEX 操作（PostgreSQL 17+）
pg_signal_autovacuum_worker - 向autovacuum woker发送信号（PostgreSQL 18+）

文件系统访问

pg_read_server_files - 从服务器文件系统读取文件
pg_write_server_files - 将文件写入服务器文件系统
pg_execute_server_program - 在服务器上执行程序

以及专门的用例

pg_create_subscription - 逻辑复制管理
pg_use_reserved_connections - 连接保留（PostgreSQL 16+）

为什么使用预定义角色？

预定义角色的主要优势在于扩展了能够安全管理 PostgreSQL 数据库的用户池。传统的 PostgreSQL 管理方式人为地制造了二元选择：要么用户拥有功能有限的基本访问权限，要么需要拥有完整的超级用户权限才能执行操作任务。这迫使许多组织仅仅为了执行监控、备份或维护等常规操作就授予过多的权限。

预定义角色打破了这一限制，允许对操作任务进行更精细的控制，而无需分配超级用户权限。组织现在可以将特定权限委托给合适的操作角色，而无需让少数拥有高权限的超级用户处理所有管理工作 —— 监控团队获得监控访问权限，备份服务获得数据读取权限，维护脚本获得所需的精确权限。

更深层次的好处在于抽象。您不仅可以从手动管理单个系统对象的权限切换到管理逻辑能力集。这将在您想要允许的内容和实际实现方式之间建立清晰的区分。

以下示例清楚地展示了其优势

-- granting select on each schema separately  
GRANT USAGE ON SCHEMA finance, hr, app, audit TO analytics_team;  
GRANT SELECT ON ALL TABLES IN SCHEMA finance TO analytics_team;  
GRANT SELECT ON ALL SEQUENCES IN SCHEMA finance TO analytics_team;  
-- 以上还缺少了 default privilege 的授予  
-- 参考 https://www.postgresql.org/docs/current/sql-alterdefaultprivileges.html   
  
-- versus one time setup  
GRANT pg_read_all_data TO analytics_team;

预定义角色方法自动涵盖：

所有当前表、视图和序列
所有schema
在GRANT操作之后创建的对象

好消息是，好处远不止于此。操作层面的真正好处在于这些预定义角色的作用范围 —— 它们适用于集群级别（而不是数据库级别）。唯一值得注意的例外是pg_database_owner角色，我们将在下文中介绍。

在讨论这个之前，让我们先简单提一下第二个好处，那就是 PostgreSQL 中的任何新操作功能都将被这些预定义的角色自动覆盖。

预定义角色的演变

了解预定义角色的引入时间和原因，有助于深入了解 PostgreSQL 的开发优先级，并解释一些设计决策。从 PostgreSQL 9.6 中的单一角色到如今的完整角色集，这一历程反映了 PostgreSQL 开发人员在生产环境中遇到的实际痛点。

PostgreSQL 9.6（2016）引入了第一个预定义角色pg_signal_backend来解决常见问题 —— 无法在不放弃超级用户权限的情况下cancel正在运行的查询。

-- This became possible in 9.6 without superuser privileges  
SELECT pg_cancel_backend(12345);  -- Cancel a query  
SELECT pg_terminate_backend(12345);  -- Terminate a session

PostgreSQL 10（2017）对预定义角色进行了最大程度的扩展，增加了四个特定于监控的角色，以提高数据库的可观察性，并支持数据库监控在生产环境中日益增长的重要性。

-- Create the monitoring user  
CREATE USER postgres_exporter WITH PASSWORD 'monitoring_password';  
  
-- Grant comprehensive monitoring access  
GRANT pg_monitor TO postgres_exporter;

提供对各种指标和设置的访问权限。通过单一 GRANT 功能，您可以访问以下数据：

从 pg_read_all_settings（通过 pg_monitor 继承）得到 SELECT * FROM pg_settings 权限 - 用于对错误配置和配置指标（如 shared_buffers、work_mem、max_connections）发出警报

来自 pg_read_all_stats（通过 pg_monitor 继承）：

-- Database-wide statistics (connections, transactions, blocks read/written)  
SELECT * FROM pg_stat_database  
-- Current query activity and connection states  
SELECT * FROM pg_stat_activity  
-- Replication lag and status  
SELECT * FROM pg_stat_replication  
-- Background writer performance  
SELECT * FROM pg_stat_bgwriter  
-- Lock contention monitoring  
SELECT * FROM pg_locks

来自 pg_stat_scan_tables（通过 pg_monitor 继承）：

观察顺序扫描信息增强表统计信息收集
表访问的更详细 I/O 统计信息

这个pg_monitor角色的设计非常巧妙。它并非单一的、单一的权限集，而是其他三个角色的综合。这允许在需要时进行细粒度的访问 —— 您可以授予 pg_read_all_stats 权限以进行基本监控（不包括配置访问权限），或者授予 pg_monitor 的完整权限包以获得全面的监控功能。

PostgreSQL 11（2018）引入了三个强大的角色，从根本上改变了 PostgreSQL 处理服务器端文件访问的方式，为安全的文件系统操作奠定了基础：

pg_read_server_files
pg_write_server_files
pg_execute_server_program

解决提供文件系统访问的许多长期存在的问题，例如需要读取 CSV 文件的 ETL 流程和通常涉及外部程序的数据处理管道。

文件系统角色代表了安全性的重大进步。在 PostgreSQL 11 之前，诸如服务器端 COPY FROM 文件之类的操作只能由超级用户执行，这迫使管理员要么授予过多权限，要么寻找变通方法。

PostgreSQL 14（2021）引入了三个重要的角色，用于解决不同的数据访问模式：

pg_read_all_data
pg_write_all_data
pg_database_owner

前两个方案解决了常见的备份和分析挑战。特殊情况 pg_database_owner 我们将在后面讨论。

-- create ETL extraction user  
CREATE USER etl_extractor WITH PASSWORD 'extract_password';  
GRANT pg_read_all_data TO etl_extractor;  
  
-- create ETL loader with write access  
CREATE USER etl_loader WITH PASSWORD 'loader_password';  
GRANT pg_write_all_data TO etl_loader;

所有这些数据角色都存在一个很大的问题：它们仍然（尽管名称可能暗示了这一点）遵循行级安全策略 (RLS)。这是 PostgreSQL 的设计选择，它默认优先考虑安全性 —— 即使对于拥有广泛数据访问权限的用户也是如此。避免 RLS 的唯一方法是显式授予BYPASSRLS角色属性。

PostgreSQL 15（2022）扩展了pg_checkpoint，反映了有时需要检查点操作进行维护但不需要完全超级用户权限的现实。

PostgreSQL 16（2023）扩展了列表，引入了

pg_use_reserved_connections - 解决了高并发数据库的一个关键问题，此前只有超级用户才能访问预留连接。引入此角色扩大了保留连接池的使用范围。例如，通过pg_signal_backend授权来断开会话的管理者。
pg_create_subscription - 能够创建逻辑复制订阅并确认 PostgreSQL 在分布式数据库场景中的位置。

PostgreSQL 17（2024）终于看到了pg_maintain，这个角色有着动荡的发展历史（最初致力于 PostgreSQL 16），允许非超级用户执行关键的维护任务，如 VACUUM、ANALYZE、REINDEX、REFRESH MATERIALIZED VIEW、CLUSTER 和 LOCK TABLE。

pg_maintain角色对于以下人群尤其有价值：

自动维护脚本现在可以以最小权限运行，从而减少了用superuser进行定期维护操作的安全威胁。
管理多个数据库的运营团队需要一致的维护能力，而无需每个数据库的超级用户访问权限。
云和托管环境中，维护操作需要委托给操作人员，而无需授予更广泛的系统访问权限。

PostgreSQL 18（2025）延续了这一趋势，pg_signal_autovacuum_worker 扩展了非超级用户向autovacuum_worker发出信号的能力，使他们能够取消特定表上的清理操作或终止可能在关键时期导致性能问题的自动清理会话(例如在业务高峰, 正在进行多个大表的vacuum, 并影响了业务的正常请求延迟, 使用这个可以中断autovacuum worker的工作)。

pg_database_owner 的魔力

如果你留意的话，你会发现有一个角色与众不同 —— pg_database_owner。虽然它不提供任何没有超级用户权限才能获得的特定权限，但它可以作为数据库所有者的标记。此角色对于维护数据库所有权并确保数据库由正确的用户管理至关重要。

在 PostgreSQL 15 之前，您将拥有postgres用户拥有的public schema。

demo=> \dn  
  List of schemas  
  Name  |  Owner  
--------+----------  
 public | postgres

它带有宽松的默认权限，允许每个用户（PUBLIC）创建对象。这让维护变得非常困难。

从 PostgreSQL 15 及更高版本开始，public模式归pg_database_owner角色所有。

demo=# \dn  
      List of schemas  
  Name  |       Owner  
--------+-------------------  
 public | pg_database_owner

与其他角色不同，pg_database_owner具有独特的行为 —— 其成员资格会随着当前数据库的变化而变化。正如上面的代码片段所示，该角色的“变形特性”固定了public schema与其关联的权限和复杂性。

另一个特殊行为是角色本身不附带任何权限。让我们来理解一下 —— 零权限。它的权力仅来自于你授予或继承的权限。这为复杂的模板继承系统打开了大门。

以下例子特别适合SaaS业务, 每个APP对应一个数据库, 但是模板都来自某个已定义好的数据库.

-- define functionality in template1  
\c template1 postgres  
CREATE OR REPLACE FUNCTION db_owner_stats() RETURN ...  SECURITY DEFINER;  
GRANT EXECUTE ON FUNCTION db_owner_stats() TO pg_database_owner;  
  
-- and let it automatically apply to all new databases  
CREATE DATABASE app_prod OWNER first_app;  
CREATE DATABASE demo_prod OWNER second_app;  
  
-- both first_app and second_app automatically inherit db_owner_stats function on their databases

而当我们谈论魔法的时候，角色的特殊地位使得这个角色不能被授予任何其他角色。

demo=# GRANT pg_database_owner TO labs_app;  
ERROR:  role "pg_database_owner" cannot have explicit members

多说一句, pg_database_owner 相当于一个隐式角色, 代表了数据库的owner. 而利用安全标签进行攻击的例子可参考:

《PostgreSQL 安全陷阱 - 利用触发器或规则，结合security invoker函数制造反噬陷阱》
《PostgreSQL function 会遗传security 属性吗？》
《PostgreSQL function's SECURITY DEFINER | INVOKER, SET configuration_parameterTO value= value | FROM CURRENT》
《PostgreSQL views privilege attack and security with security_barrier(视图攻击)》