Re: [HACKERS] pg_monitor role

On Tue, Feb 21, 2017 at 5:40 PM, Masahiko Sawada <sawada.mshk@gmail.com> wrote:
> On Mon, Feb 20, 2017 at 8:48 PM, Dave Page <dpage@pgadmin.org> wrote:
>> Further to the patch I just submitted
>> (https://www.postgresql.org/message-id/CA%2BOCxow-X%3DD2fWdKy%2BHP%2BvQ1LtrgbsYQ%3DCshzZBqyFT5jOYrFw%40mail.gmail.com)
>> I'd like to propose the addition of a default role, pg_monitor.
>>
>> The intent is to make it easy for users to setup a role for fully
>> monitoring their servers, without requiring superuser level privileges
>> which is a problem for many users working within strict security
>> policies.
>>
>> At present, functions or system config info that divulge any
>> installation path related info typically require superuser privileges.
>> This makes monitoring for unexpected changes in configuration or
>> filesystem level monitoring (e.g. checking for large numbers of WAL
>> files or log file info) impossible for non-privileged roles.
>>
>> A similar example is the restriction on the pg_stat_activity.query
>> column, which prevents non-superusers seeing any query strings other
>> than their own.
>>
>> Using ACLs is a problem for a number of reasons:
>>
>> - Users often don't like their database schemas to be modified
>> (cluttered with GRANTs).
>> - ACL modifications would potentially have to be made in every
>> database in a cluster.
>> - Using a pre-defined role minimises the setup that different tools
>> would have to require.
>> - Not all functionality has an ACL (e.g. SHOW)
>>
>> Other DBMSs solve this problem in a similar way.
>>
>> Initially I would propose that permission be granted to the role to:
>>
>> - Execute pg_ls_logdir() and pg_ls_waldir()
>> - Read pg_stat_activity, including the query column for all queries.
>> - Allow "SELECT pg_tablespace_size('pg_global')"
>> - Read all GUCs
>>
>
> Thank you for working on this.

You're welcome.

> What about granting to the role to read other statistic views such as
> pg_stat_replication and pg_stat_wal_receiver? Since these informations
> can only be seen by superuser the for example monitoring and
> clustering tool seems to have the same concern.

Yes, good point.

> And what about the diagnostic tools such as pageinspect and pgstattuple?

I think external/contrib modules should not be included. To install
them you need admin privileges anyway, so you can easily grant
whatever usage privileges you want at that time.