PostgreSQL 10.0 preview 功能增强 - 角色化权限管理 - 新增几个内置角色(统计信息查看、管理用户会话)

背景

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

https://www.postgresql.org/message-id/flat/CA+OCxoyRdsc1xyLfF9s698gUGyPXBs4CvJ+0Gwo8U65NmYJ7pw@mail.gmail.com#CA+OCxoyRdsc1xyLfF9s698gUGyPXBs4CvJ+0Gwo8U65NmYJ7pw@mail.gmail.com

https://commitfest.postgresql.org/13/1031/

digoal’s 大量PostgreSQL文章入口