Re: Monitoring roles patch

On Wed, Mar 22, 2017 at 11:32 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Fri, Feb 24, 2017 at 5:14 AM, Dave Page <dpage(at)pgadmin(dot)org> wrote:
>> - 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
>
> I like the pg_read_all_gucs role, which I agree with Peter should be
> called pg_read_all_settings.

No objection to that change.

> I'd be inclined to skip the rest of
> this. If an individual user wants to grant that bundle of privileges
> to a role, they can do it with or without pg_monitor.

GRANT cannot be used in all cases, as some of the functions changed
have hard-coded superuser checks. In those cases, I've added
pg_monitor membership to the permission checks in the C code.

The reason for having the role is to minimise the amount of work
required by the user to setup a role for the purposes of monitoring
the server. This is a practice which is seen in other DBMSs for
usability.

With the patch, complex monitoring systems can easily be setup with
something like:

CREATE ROLE monitoring_user LOGIN;
GRANT pg_monitor TO monitoring_role;

Without, the users setting up their monitoring system will have to run
a much more complex set of GRANTs, almost certainly requiring
scripting.

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

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