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

Greetings,

* Jeff Davis (pgsql(at)j-davis(dot)com) wrote:
> On Wed, 2021-10-13 at 10:03 -0400, Robert Haas wrote:
> > Yeah. I think we should really only use predefined roles where it's
> > not practical to have people use GRANT/REVOKE.
>
> That sounds like a good rule.
>
> A minor complaint though: to grant on pg_backend_memory_contexts, you
> need two grant statements:
>
> grant select on pg_backend_memory_contexts to foo;
> grant execute on function pg_get_backend_memory_contexts() to foo;
>
> The second is more of an internal detail, and we don't really want
> users to be relying on that undocumented function. Is there a good way
> to define a view kind of like a SECURITY DEFINER function so that the
> superuser would only need to issue a GRANT statement on the view?

Erm, surely the function should be documented...

Other than that, grouping of privileges is generally done using roles.
We could possibly create a predefined role to assist with this but I
don't think it's a huge issue for users to do that themselves.,
particularly since they're likely to grant other accesses to that role
too. In some instances, it might make sense to grant such access to
other predefined roles too (pg_monitor or the other ones), of course.

I don't think we really want to be doing privilege checks with one role
(view owner) for who is allowed to run the function, and then actually
running the function with some other role when it's a security invoker
function..

Thanks,

Stephen