Re: pg_monitor role

Le 20/02/2017 à 12:48, Dave Page a écrit :
> 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
>
> In the future I would also like to see us add additional roles for
> system administration functions, for example, a backup operator role
> that would have the appropriate rights to make and restore backups.
>
> Comments?

Hello,

That's something really useful. Some customers would like to use a
non-privileged user to connect their monitoring.

I've come to a set of hacks to give such features to a particular
customer, but that remains a hack. But this only works if the monitoring
tool does not prefix explicitly each view or functions with schema
pg_catalog.

I'm really looking forward such feature. Let me know if I can help in
some way.

Regards