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

On Thu, 14 Oct 2021 at 13:43, Stephen Frost <sfrost(at)snowman(dot)net> wrote:

> 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.
>

Even something as simple as a "log all table updates" cannot be implemented
as far as I can tell.

So you have table T and T_log. Trigger on T causes all INSERT/UPDATE/DELETE
actions to be logged to T_log. The only changes to T_log should be inserts
resulting from the trigger. But now in order to make changes to T the user
also needs INSERT on T_log. OK, so use a security definer function. That
doesn't help; now instead of needing INSERT on T_log they need EXECUTE on
the function. Either way, two privilege grants are required, and one of
them allows the user to make spurious entries in T_log.

But the desired behaviour is that the user has access *only* to T, and no
access whatsoever to T_log other than indirect changes by causing the
trigger to execute.