Re: Monitoring roles patch

OK, so before I start hacking again, here's a proposal based on my
understanding of folks comments, and so open questions. If I can get
agreement and answers, I'll be able to break out vi again without
(hopefully) too many more revisions:

pg_read_all_stats: Will have C-coded access to pg_stats views and
pg_*_size that are currently hard-coded to superuser

pg_read_all_settings: Will have C-coded access to read GUCs that are
currently hard-coded to the superuser

pg_monitor: Will include pg_read_all_stats and pg_read_all_settings,
and all explicitly GRANTable rights, e.g. in contrib modules.

Patch to be rebased on Simon's updated version.

Questions:

- pg_stat_statements has a hard-coded superuser check. Shall I remove
that, and include REVOKE ALL FROM ALL and then GRANT to pg_monitor?

- pgrowlocks has hard-coded access to superuser and users with SELECT
on the table being examined. How should this be handled?

- Stephen suggested a separate role for functions that can lock
tables. Is this still desired, or shall we just grant access to
pg_monitor (I think the latter is fine)?

- Based on Peter's concerns, is pg_read_all_stats the right name?
Maybe pg_read_monitoring_stats?

Thanks!

On Tue, Mar 28, 2017 at 1:37 PM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> Greetings,
>
> * Robert Haas (robertmhaas(at)gmail(dot)com) wrote:
>> On Tue, Mar 28, 2017 at 12:47 PM, Dave Page <dpage(at)pgadmin(dot)org> wrote:
>> >> I don't see any precedent in the code for having a hardcoded role, other than
>> >> superuser, and allowing privileges based on a hardcoded test for membership
>> >> in that role. I'm struggling to think of all the security implications of that.
>> >
>> > This would be the first.
>>
>> Isn't pg_signal_backend an existing precedent?
>
> Yes, it is.
>
> Thanks!
>
> Stephen

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

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