Обсуждение: [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

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

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.

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.
And what about the diagnostic tools such as pageinspect and pgstattuple?

Regards,

--
Masahiko Sawada
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

Hi

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.

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

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

All,

* Magnus Hagander (magnus@hagander.net) wrote:
> On Wed, Feb 22, 2017 at 1:47 PM, Dave Page <dpage@pgadmin.org> wrote:
> > 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.
>
> I think basically pg_stat_* should be readable by this role.

Agreed.  I would explicitly point out that we do *not* want to include
'pg_statistic' in this as that would include actual data from the
tables.

> > > 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.
>
> I'll start by saying "why not cover contrib"?

+1.

> Then I'll say *absolutely* not pageinspect. That is a diagnostics tool and
> not a monitoring tool. And also, if you give me pageinspect I will happily
> open up your pg_authid and hack your database. This needs to be superuser
> only.

Agreed.

> pgstattuple can be discussed. It doesn't leak anything dangerous. But it
> does have views that are quite expensive.

For my 2c, I think pgstattuple should be included.  It wouldn't be
difficult to just have a GRANT at the end of the extension creation
script to provide the appropriate rights to pg_monitor (or whatever).

> There's also pg_stat_statements, which seems lik eit should be included?
> Any security issues with that one would be the same as with
> pg_stat_activity.

Agreed.

I do see two issues to be addressed with such a role:

#1- We shouldn't just shove everything into one role.  Where
functionality can't be GRANT'd independently of the role, we should have
another default role.  For example, "Read all GUCs" is not something
that can currently be GRANT'd.  I'm sure there are cases where $admin
wants a given role to be able to read all GUCs, but not execute
pg_ls_logdir(), for example.  If we start writing code that refers
explicitly to pg_monitor then we will end up in an "all-or-nothing" kind
of situation (not unlike the superuser case) instead of allowing users a
fine-grained set of options.

That isn't to say that we shouldn't have a pg_monitor role, I'd really
like to have one, actually, but that role should only have rights which
can be GRANT'd to it (either by GRANT'ing other default roles to it, or
by GRANT'ing regular object-level ACLs to it).  What I'm getting at is
that we should have a 'pg_read_all_gucs' default role for the right and
then GRANT that role to pg_monitor.

#2- We need to define very carefully, up-front, how we will deal with
new privileges/capabilities/features down the road.  A very specific
default role like 'pg_read_all_gucs' is quite clear about what's allowed
by it and I don't think we'd get any push-back from adding new GUCs that
such a default role could read, but some new view pg_stat_X view that
would be really useful to monitoring tools might also allow more access
than the pg_monitor has or that some admins would be comfortable with-
how do we handle such a case?  I see a few options:
 - Define up-front that pg_monitor has rights on all pg_stat_X views,which then requires we provide a definition and
clarityon what"pg_stat_X" *is* and provides.  We can then later add such views andGRANT access to them to pg_monitor. 
 - Create new versions of pg_monitor in the future that cover everincreasing sets of privileges
("pg_monitor_with_pg_stat_X"or"pg_monitor_v11" for PG11 or something). 
 - Do not create our own pg_monitor but instead providedocumentation/scripts for users to create their own "monitor"
roles.

It seems at least unlikely that we'll never have another pg_stat_X view
that we want to give pg_monitor access to, so I don't really see "Fix
what pg_monitor can read forever based on what's in PG10" as being a
solution.

Thanks!

Stephen

Hi

On Wed, Feb 22, 2017 at 4:52 PM, Stephen Frost <sfrost@snowman.net> wrote:
>> > > 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.
>>
>> I think basically pg_stat_* should be readable by this role.
>
> Agreed.  I would explicitly point out that we do *not* want to include
> 'pg_statistic' in this as that would include actual data from the
> tables.

Right.

>> > > 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.
>>
>> I'll start by saying "why not cover contrib"?
>
> +1.

I'm not convinced we should include it, for the reason I gave above.
However, I don't feel that strongly.

What modules should be included?

>> Then I'll say *absolutely* not pageinspect. That is a diagnostics tool and
>> not a monitoring tool. And also, if you give me pageinspect I will happily
>> open up your pg_authid and hack your database. This needs to be superuser
>> only.
>
> Agreed.

+1

>> pgstattuple can be discussed. It doesn't leak anything dangerous. But it
>> does have views that are quite expensive.

I don't think expense should be a concern. It's not like a regular
user cannot run something expensive already, so why stop a user
specifically setup to monitor something?

> For my 2c, I think pgstattuple should be included.  It wouldn't be
> difficult to just have a GRANT at the end of the extension creation
> script to provide the appropriate rights to pg_monitor (or whatever).
>
>> There's also pg_stat_statements, which seems lik eit should be included?
>> Any security issues with that one would be the same as with
>> pg_stat_activity.
>
> Agreed.

OK.

> I do see two issues to be addressed with such a role:
>
> #1- We shouldn't just shove everything into one role.  Where
> functionality can't be GRANT'd independently of the role, we should have
> another default role.  For example, "Read all GUCs" is not something
> that can currently be GRANT'd.  I'm sure there are cases where $admin
> wants a given role to be able to read all GUCs, but not execute
> pg_ls_logdir(), for example.  If we start writing code that refers
> explicitly to pg_monitor then we will end up in an "all-or-nothing" kind
> of situation (not unlike the superuser case) instead of allowing users a
> fine-grained set of options.

I'm fine with having pg_read_all_gucs - it's a trivial change.  I
wouldn't want us to go too far and end up with separate roles for
everything under the sun though.

> That isn't to say that we shouldn't have a pg_monitor role, I'd really
> like to have one, actually, but that role should only have rights which
> can be GRANT'd to it (either by GRANT'ing other default roles to it, or
> by GRANT'ing regular object-level ACLs to it).  What I'm getting at is
> that we should have a 'pg_read_all_gucs' default role for the right and
> then GRANT that role to pg_monitor.

OK.

> #2- We need to define very carefully, up-front, how we will deal with
> new privileges/capabilities/features down the road.  A very specific
> default role like 'pg_read_all_gucs' is quite clear about what's allowed
> by it and I don't think we'd get any push-back from adding new GUCs that
> such a default role could read, but some new view pg_stat_X view that
> would be really useful to monitoring tools might also allow more access
> than the pg_monitor has or that some admins would be comfortable with-
> how do we handle such a case?  I see a few options:
>
>   - Define up-front that pg_monitor has rights on all pg_stat_X views,
>         which then requires we provide a definition and clarity on what
>         "pg_stat_X" *is* and provides.  We can then later add such views and
>         GRANT access to them to pg_monitor.
>
>   - Create new versions of pg_monitor in the future that cover ever
>         increasing sets of privileges ("pg_monitor_with_pg_stat_X" or
>         "pg_monitor_v11" for PG11 or something).

I prefer the first option. In my experience, users don't much care
about the rights their monitoring user has, as long as it's not full
superuser. The only case where I think there are legitimate concerns
are where you can read arbitrary data (I do not consider query strings
to be in that class for the record). That said, if we ever do add
something like that then there's nothing stopping us from explicitly
documenting that it's excluded from pg_monitor for that reason, and if
desired the user can grant on it as needed.

Using a scheme like that would also mean that the user is more likely
to need to manually update the role their monitoring system uses
following an upgrade.

>   - Do not create our own pg_monitor but instead provide
>         documentation/scripts for users to create their own "monitor" roles.

The whole point here is to minimise the requirements on the user, and
have a good set of default roles.

> It seems at least unlikely that we'll never have another pg_stat_X view
> that we want to give pg_monitor access to, so I don't really see "Fix
> what pg_monitor can read forever based on what's in PG10" as being a
> solution.

No, but similarly I don't see any reason why we cannot add new views
by default, and exclude by exception when there's a compelling reason.

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

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

Dave,

* Dave Page (dpage@pgadmin.org) wrote:
> On Wed, Feb 22, 2017 at 4:52 PM, Stephen Frost <sfrost@snowman.net> wrote:
> >> > > 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.
> >>
> >> I'll start by saying "why not cover contrib"?
> >
> > +1.
>
> I'm not convinced we should include it, for the reason I gave above.
> However, I don't feel that strongly.
>
> What modules should be included?

On a quick review of all of the modules, excluding those that are just
testing or examples or which can already be used by non-superusers by
default, and excluding those which can be used to trivially gain
superuser access (adminpack and pageinspect), I came up with:

pg_buffercache
pg_freespacemap
pgrowlocks
pg_stat_statements
pgstattuple
pg_visibility

Reviewing this list, they all seem like things a monitoring user could
have a use for and none of them allow direct access to table data from
what I could tell on a quick review.  Obviously, a more detailed review
of each should be done to make sure I didn't miss something.

One interesting thing that comes up from this list is that there's a
number of things which are "look at something about a row" or "look at
something about a block" (pg_freespacemap, pgrowlocks, pgstattuple,
pg_visibility all fall into those, and to some extent pg_buffercache
too).  I'm tempted to suggest that we have a role which covers that
theme (and is then GRANT'd to pg_monitor).

> >> pgstattuple can be discussed. It doesn't leak anything dangerous. But it
> >> does have views that are quite expensive.
>
> I don't think expense should be a concern. It's not like a regular
> user cannot run something expensive already, so why stop a user
> specifically setup to monitor something?

I tend to agree with this.

> > I do see two issues to be addressed with such a role:
> >
> > #1- We shouldn't just shove everything into one role.  Where
> > functionality can't be GRANT'd independently of the role, we should have
> > another default role.  For example, "Read all GUCs" is not something
> > that can currently be GRANT'd.  I'm sure there are cases where $admin
> > wants a given role to be able to read all GUCs, but not execute
> > pg_ls_logdir(), for example.  If we start writing code that refers
> > explicitly to pg_monitor then we will end up in an "all-or-nothing" kind
> > of situation (not unlike the superuser case) instead of allowing users a
> > fine-grained set of options.
>
> I'm fine with having pg_read_all_gucs - it's a trivial change.  I
> wouldn't want us to go too far and end up with separate roles for
> everything under the sun though.

I agree with you there- having too many default roles would lead to
things getting messy, without there really being a need for it.  Users
can always create their own roles for the specific set of capabilities
that they want to provide.  The main thing I want to avoid is having a
situation where a user *can't* create a role that has only a subset of
what "pg_monitor" has because there's some code somewhere that
explicitly allows the "pg_monitor" role to do something.

> > #2- We need to define very carefully, up-front, how we will deal with
> > new privileges/capabilities/features down the road.  A very specific
> > default role like 'pg_read_all_gucs' is quite clear about what's allowed
> > by it and I don't think we'd get any push-back from adding new GUCs that
> > such a default role could read, but some new view pg_stat_X view that
> > would be really useful to monitoring tools might also allow more access
> > than the pg_monitor has or that some admins would be comfortable with-
> > how do we handle such a case?  I see a few options:
> >
> >   - Define up-front that pg_monitor has rights on all pg_stat_X views,
> >         which then requires we provide a definition and clarity on what
> >         "pg_stat_X" *is* and provides.  We can then later add such views and
> >         GRANT access to them to pg_monitor.
> >
> >   - Create new versions of pg_monitor in the future that cover ever
> >         increasing sets of privileges ("pg_monitor_with_pg_stat_X" or
> >         "pg_monitor_v11" for PG11 or something).
>
> I prefer the first option. In my experience, users don't much care
> about the rights their monitoring user has, as long as it's not full
> superuser. The only case where I think there are legitimate concerns
> are where you can read arbitrary data (I do not consider query strings
> to be in that class for the record). That said, if we ever do add
> something like that then there's nothing stopping us from explicitly
> documenting that it's excluded from pg_monitor for that reason, and if
> desired the user can grant on it as needed.
>
> Using a scheme like that would also mean that the user is more likely
> to need to manually update the role their monitoring system uses
> following an upgrade.

I prefer the first option too, but that means you have work to do to
define what "pg_stat_X" means/covers in a way that, hopefully, future
hackers will remember and adhere to. ;)

I'd rather we try to avoid having exceptions as it can lead to confusion
and adds complexity that security folks would almost certainly prefer to
not have to deal with.

> > It seems at least unlikely that we'll never have another pg_stat_X view
> > that we want to give pg_monitor access to, so I don't really see "Fix
> > what pg_monitor can read forever based on what's in PG10" as being a
> > solution.
>
> No, but similarly I don't see any reason why we cannot add new views
> by default, and exclude by exception when there's a compelling reason.

I'd rather simply name whatever that exception is 'pg_somethingelse'.
There would have to be a really, really good reason to have a
'pg_stat_X' that isn't something that pg_monitor can have access to.

Now, perhaps what that means is that we should really name these things
something else or have aliases to them that is what pg_monitor is given,
so we get away from the issue that "statistics" stuff might have
something sensitive included that we don't want pg_monitor to have
access to (eg: pg_mon_statements, pg_mon_activity, pg_mon_whatever ...).

I guess that ends up depending on what we can come up with to define
what "pg_stat_X" is and if everyone can more-or-less agree to that (and,
subsequently, agree that pg_monitor should have access to all of that).

Thanks!

Stephen

Dave, all,

* Stephen Frost (sfrost@snowman.net) wrote:
> * Dave Page (dpage@pgadmin.org) wrote:
> > What modules should be included?
>
> On a quick review of all of the modules, excluding those that are just
> testing or examples or which can already be used by non-superusers by
> default, and excluding those which can be used to trivially gain
> superuser access (adminpack and pageinspect), I came up with:
>
> pg_buffercache
> pg_freespacemap
> pgrowlocks
> pg_stat_statements
> pgstattuple
> pg_visibility
>
> Reviewing this list, they all seem like things a monitoring user could
> have a use for and none of them allow direct access to table data from
> what I could tell on a quick review.  Obviously, a more detailed review
> of each should be done to make sure I didn't miss something.

Also, not everything in those modules should be allowed to the
pg_monitor role, I don't think.  For example, I don't think pg_monitor
should be given access to pg_truncate_visibility_map(), particularly
since there's zero ACL checks inside of pg_visibility, meaning that
having EXECUTE rights on that function would allow you to truncate the
visibility map of anything in the database, from what I can tell in a
quick review.

The other functions look like they would be 'ok' for the pg_monitor user
to have access to though.  To be clear, I don't think it would make
sense to add ACL checks into those other functions either, unless we
came up with a new type of ACL for just this type of meta-data access.
I'm not really a fan of that either though, because you would then have
to figure out how to give that access to every object in the system,
which isn't something we handle very well today.

Perhaps when we get around to creating default roles that have other
privileges by default (like a 'pg_read_only' role that automatically has
SELECT rights to every table in the database...) we could have a role
like "pg_read_metadata" that automatically had that right everywhere,
but I don't think we need to have that before adding pg_monitor.

Thanks!

Stephen