标签
PostgreSQL , 10.0 , 内置角色 , 监控角色 , 管理用户会话角色 , pg_backend_pid , pg_monitor
背景
PostgreSQL 10.0 开始植入了一些内置的角色，例如pg_backend_pid角色可用于cancel, terminate任何PID，除此之外没有其他超级用户的权限，这种用户可以给DBA或者给一些业务OWNER使用，便于它们在紧急情况下行使权力，杀掉一些会话。
同时为了便于DBA或者业务OWNER查看到数据库的健康状况，PostgreSQL也增加了一个监控角色pg_monitor，拥有监控角色的权限后，可以查看一些统计信息。
同时添加了一个角色pg_read_all_gucs，这个角色可以查看所有的GUC配置。
Per the discussion at
https://www.postgresql.org/message-id/CA%2BOCxoyYxO%2BJmzv2Micj4uAaQdAi6nq0w25BPQgLLxsrvTmREw%40mail.gmail.com,
attached is a patch that implements the following:
- Adds a default role called pg_monitor
- Gives members of the pg_monitor role full access to:
pg_ls_logdir() and pg_ls_waldir()
pg_stat_* views and functions
pg_tablespace_size() and pg_database_size()
Contrib modules:
pg_buffercache,
pg_freespacemap,
pgrowlocks,
pg_stat_statements,
pgstattuple and
pg_visibility (but NOT pg_truncate_visibility_map() )
- Adds a default role called pg_read_all_gucs
- Allows members of pg_read_all_gucs to, well, read all GUCs
- Grants pg_read_all_gucs to pg_monitor
Note that updates to contrib modules followed the strategy recently
used in changes to pgstattuple following discussion here, in which the
installation SQL script is left at the prior version, and an update
script is added and default version number bumped to match that of the
upgrade script.
Patch includes doc updates, and is dependent on my pg_ls_logdir() and
pg_ls_waldir() patch
(https://www.postgresql.org/message-id/CA+OCxow-X=D2fWdKy+HP+vQ1LtrgbsYQ=CshzZBqyFT5jOYrFw@mail.gmail.com).
--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
未来PostgreSQL还会对植入更多的内置角色，让数据库的权限分组管理更加便捷。逐步形成像Oracle这样内部有许多角色可选的状况。
这个patch的讨论，详见邮件组，本文末尾URL。
PostgreSQL社区的作风非常严谨，一个patch可能在邮件组中讨论几个月甚至几年，根据大家的意见反复的修正，patch合并到master已经非常成熟，所以PostgreSQL的稳定性也是远近闻名的。
参考
https://www.postgresql.org/message-id/attachment/49958/pg_monitor.diff
