Re: pg_monitor role

Dave, all,

* Stephen Frost (sfrost(at)snowman(dot)net) wrote:
> * Dave Page (dpage(at)pgadmin(dot)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