Re: should we allow users with a predefined role to access pg_backend_memory_contexts view and pg_log_backend_memory_contexts function?

On 10/8/21, 12:01 AM, "Bharath Rupireddy" <bharath(dot)rupireddyforpostgres(at)gmail(dot)com> wrote:
> I think we can remove the below revoke statements from
> system_views.sql and place the checks shown at (2) in the underlying
> functions pg_get_shmem_allocations, pg_get_backend_memory_contexts,
> also in pg_log_backend_memory_contexts.
>
> REVOKE ALL ON pg_shmem_allocations FROM PUBLIC;
> REVOKE EXECUTE ON FUNCTION pg_get_shmem_allocations() FROM PUBLIC;
> REVOKE ALL ON pg_backend_memory_contexts FROM PUBLIC;
> REVOKE EXECUTE ON FUNCTION pg_get_backend_memory_contexts() FROM PUBLIC;
>
> Thoughts?

This approach would add a restriction that a role must have SUPERUSER
or be a member of pg_monitor to use the views/functions. I think
there is value in allowing any role to use them (if granted the proper
privileges). In any case, users may already depend on being able to
do that.

Instead, I think we should just grant privileges to pg_monitor. I've
attached a (basically untested) patch to demonstrate what I'm
thinking.

Nathan