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

Greetings,

* Isaac Morland (isaac(dot)morland(at)gmail(dot)com) wrote:
> On Thu, 14 Oct 2021 at 09:11, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> > According to https://www.postgresql.org/docs/current/sql-createview.html
> > it always works like that: "Access to tables referenced in the view is
> > determined by permissions of the view owner. In some cases, this can
> > be used to provide secure but restricted access to the underlying
> > tables."
> >
> > Hmm, unless that rule is only being applied for *tables* and not for
> > *functions*? I guess that could be true, but if so, it sure seems
> > inconsistent.

I'm not sure that it's really inconsistent- if you want the function to
run as someone else, define it as SECURITY DEFINER and it will. If the
function is defined as SECURITY INVOKER then it'll run with the
privileges of the user invoking the function- which can be pretty handy
if, say, the function references CURRENT_USER. Note that RLS policies
work in the same way.

> Yes, I think this has come up before. It seems obvious to me that a view
> should execute entirely in the context of its owner. I should be able to
> use functions to define view columns without requiring that access to those
> functions be handed out to users of the view.

I don't know that it's all that obvious, particularly when you consider
that the function owner has the option of having the function run as the
invoker of the function or as the owner of the function.

> I feel this might relate to the discussion of triggers, which I claim
> should execute in the context of the table owner (or maybe the trigger
> owner, if that were a separate concept). There are lots of triggers one
> might want to write that cannot be written because they execute in the
> context of the user of the table; my recollection is that it is harder to
> find examples of non-malware triggers that depend on executing in the
> context of the user of the table.

Triggers can call security definer functions, so I'm not quite sure I
understand what the issue here is.

Thanks,

Stephen