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/7/21, 10:42 AM, "Bharath Rupireddy" <bharath(dot)rupireddyforpostgres(at)gmail(dot)com> wrote:
> In a typical production environment, the user (not necessarily a
> superuser) sometimes wants to analyze the memory usage via
> pg_backend_memory_contexts view or pg_log_backend_memory_contexts
> function which are accessible to only superusers. Isn't it better to
> allow non-superusers with an appropriate predefined role (I'm thinking
> of pg_monitor) to access them?

It looks like this was discussed previously [0]. From the description
of pg_monitor [1], I think it's definitely arguable that this view and
function should be accessible by roles that are members of pg_monitor.

The pg_monitor, pg_read_all_settings, pg_read_all_stats and
pg_stat_scan_tables roles are intended to allow administrators
to easily configure a role for the purpose of monitoring the
database server. They grant a set of common privileges
allowing the role to read various useful configuration
settings, statistics and other system information normally
restricted to superusers.

AFAICT the current permissions were chosen as a safe default, but
maybe it can be revisited. The view and function appear to only
reveal high level information about the memory contexts in use (e.g.,
name, size, amount used), so I'm not seeing any obvious reason why
they should remain superuser-only. pg_log_backend_memory_contexts()
directly affects the server log, which might be a bit beyond what
pg_monitor should be able to do. My currently thinking is that we
should give pg_monitor access to pg_backend_memory_contexts (and maybe
even pg_shmem_allocations). However, one interesting thing I see is
that there is no mention of any predefined roles in system_views.sql.
Instead, the convention seems to be to add hard-coded checks for
predefined roles in the backing functions. I don't know if that's a
hard and fast rule, but I do see that predefined roles are given
special privileges in system_functions.sql.

Nathan

[0] https://www.postgresql.org/message-id/flat/a99bdd0e-7271-8176-f700-2553a51d4a27%40oss.nttdata.com#0f79f7cf6a6c3b3e3ccb4570870b3bd4
[1] https://www.postgresql.org/docs/devel/predefined-roles.html