On Thu, 14 Oct 2021 at 13:43, Stephen Frost <sfr...@snowman.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.
