[HACKERS] pg_monitor role

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?

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

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