Re: Monitoring roles patch

On Wed, Mar 29, 2017 at 2:51 PM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
>
> Dave's currently hacking on a new patch based on our discussion, so I'd
> suggest waiting another hour or so anyway until he's done.
>
> Might be a bit longer as he's trying to do it in a hallway at
> PGConf.US...

Thanks Stephen.

Here's an updated patch, and description of the changes. Simon,
Stephen and Robert have looked at the description and are all happy
with it \o/. Thank you to them for taking the time out of the
conference to go through it with me.

Here's what it does:

1) Creates the following default roles:

- pg_monitor - Top-level role that is GRANTed all of the following
roles by default. Also GRANTed access to some additional functions.
- pg_read_all_settings - A role that can read all GUCs.
- pg_read_all_stats - A role that can read un-redacted pg_stat_*
views via the functions supporting them, as well as
pg_database_size/pg_tablespace_size.
- pg_stat_scan_tables - A role that can execute monitoring functions
that may lock tables.

2) pg_database_size and pg_tablespace_size have hard-coded permission
checks updated to allow execution by pg_read_all_stats.

3) GUC read permission checks for superuser have been replaced with
checks for membership in pg_read_all_settings.

4) pg_buffercache functions have GRANTed execute permissions to pg_monitor.

5) pg_freespacemap functions have GRANTed execute permissions to
pg_stat_scan_tables.

6) pg_stat_statements has its hard-coded permission check updated to
allow execution by pg_read_all_stats, and the same role is GRANTed
permission to execute pg_stat_statements_reset().

7) pg_visibility functions have GRANTed executed permissions to
pg_stat_scan_tables.

8) pgrowlocks has it's hard-coded permission check updated to allow
execution by pg_stat_scan_tables,

9) pgstattuple functions have GRANTed executed permissions to
pg_stat_scan_tables.

10) pg_stat_get_wal_receiver has its hard-coded permission check
updated to allow execution by pg_read_all_stats

11) pg_ls_logdir and pg_ls_waldir have execute permissions GRANTed to pg_monitor

12) Un-redacted use of the functions underpinning the pg_stat_* views
is available to pg_read_all_stats.

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company