一个预定义角色
是一个内置角色,它提供对某些常用特权能力和信息的访问权限。此类角色的成员资格可以授予给单个用户,而无需将该用户设为超级用户。
预定义角色
是在PostgreSQL 9.6中引入的,尽管最初只有一个可用的角色(pg_signal_backend
)。
可用性
18 | 17 | 16 | 15 | 14 | 13 | 12 | 11 | 10 | 9.6 | |
---|---|---|---|---|---|---|---|---|---|---|
pg_checkpoint | ||||||||||
pg_create_subscription | ||||||||||
pg_database_owner | ||||||||||
pg_execute_server_program | ||||||||||
pg_maintain | ||||||||||
pg_monitor | ||||||||||
pg_read_all_data | ||||||||||
pg_read_all_settings | ||||||||||
pg_read_all_stats | ||||||||||
pg_read_server_files | ||||||||||
pg_signal_autovacuum_worker | ||||||||||
pg_signal_backend | ||||||||||
pg_stat_scan_tables | ||||||||||
pg_use_reserved_connections | ||||||||||
pg_write_all_data | ||||||||||
pg_write_server_files |
pg_monitor
pg_monitor
角色结合了以下默认角色
pg_read_all_settings
pg_read_all_stats
pg_stat_scan_tables
以及为以下文件系统函数提供执行权限
(但不包括pg_ls_dir()
)。
另请参见:src/backend/catalog/system_views.sql。
源代码
以下常量代表每个默认角色的OID,在编译时创建(src/include/catalog/pg_authid.dat)
ROLE_PG_CHECKPOINT
ROLE_PG_CREATE_SUBSCRIPTION
ROLE_PG_DATABASE_OWNER
ROLE_PG_EXECUTE_SERVER_PROGRAM
ROLE_PG_MAINTAIN
ROLE_PG_MONITOR
ROLE_PG_READ_ALL_DATA
ROLE_PG_READ_ALL_SETTINGS
ROLE_PG_READ_ALL_STATS
ROLE_PG_READ_SERVER_FILES
ROLE_PG_SIGNAL_AUTOVACUUM_WORKER
ROLE_PG_SIGNAL_BACKENDID
ROLE_PG_STAT_SCAN_TABLES
ROLE_PG_USE_RESERVED_CONNECTIONS
ROLE_PG_WRITE_ALL_DATA
ROLE_PG_WRITE_SERVER_FILES
注意:在PostgreSQL 13及更早版本中,这些常量的前缀是“DEFAULT_ROLE_
”。
重命名提案
在PostgreSQL 13开发周期中,有人提议将“default_role
”重命名为“predefined role
”,并且该更改实际上已提交(提交0e936a21),但随后被撤销以待进一步讨论(提交c185a577)。该更改后来在PostgreSQL 14中实现(提交c9c41c7a)。
变更历史
- PostgreSQL 18
- 添加了
pg_signal_autovacuum_worker
角色(提交ccd38024)
- 添加了
- PostgreSQL 17
- 添加了
pg_maintain
角色(提交ecb0fd33)
- 添加了
- PostgreSQL 16
- PostgreSQL 15
- 添加了
pg_checkpoint
角色(提交4168a474和b9eb0ff0) pg_read_all_stats
现在可以访问pg_backend_memory_contexts
和pg_shmem_allocations
(提交77ea4f94)
- 添加了
- PostgreSQL 14
- PostgreSQL 11 - 添加了以下默认角色(提交0fdc8495)
pg_read_server_files
pg_write_server_files
pg_execute_server_program
- PostgreSQL 10 - 添加了以下默认角色(提交25fff407)
pg_read_all_settings
pg_read_all_stats
pg_stat_scan_tables
pg_monitor
- PostgreSQL 9.6
- 添加了
pg_signal_backend
(提交7a542700)
- 添加了
请注意,pg_maintain
角色首次添加于PostgreSQL 16(提交60684dd8),但随后被撤销(提交151c22de)。
示例
postgres=> \du someuser List of roles Role name | Attributes | Member of -----------+------------+----------- someuser | | {} postgres=> SHOW data_directory; ERROR: must be superuser or a member of pg_read_all_settings to examine "data_directory" postgres=> \c - postgres You are now connected to database "postgres" as user "postgres". postgres=# GRANT pg_read_all_settings TO someuser; GRANT ROLE postgres=# \c - someuser You are now connected to database "postgres" as user "someuser". postgres=> \du someuser List of roles Role name | Attributes | Member of -----------+------------+------------------------ someuser | | {pg_read_all_settings} postgres=> SHOW data_directory; data_directory --------------------- /var/lib/pgsql/data (1 row)
C语言中的使用示例
#include "utils/acl.h" #include "catalog/pg_authid.h" ... if (is_member_of_role(GetUserId(), ROLE_PG_READ_ALL_STATS) == false) { elog(ERROR, "must be superuser or a member of the pg_read_all_stats role"); } ...
有用链接
- 系统角色 - 为什么、是什么、如何做? - 2023年3月 Hubert Lubaczewski 的博客文章