Re: Wrong security context for deferred triggers?

On Mon, Jul 1, 2024 at 11:45 AM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
wrote:

> I asked them for a statement, and they were nice enough to write up
> https://postgr.es/m/e89e8dd9-7143-4db8-ac19-b2951cb0c0da%40gmail.com

> They have a workaround, so the patch is not absolutely necessary for them.

It sounds like the issue is that there is there is a constraint
trigger to check a table constraint that must be executed at commit
time, and we'd like to guarantee that if the triggering action was
successful, then the constraint check is also successful. This is an
even bigger issue for transactions that have multiple of these
constraint checks where there may be no single role that has the
privileges required to execute all checks.

Your patch would fix the issue in a majority of cases, but not all.
Since INSERT, UPDATE, DELETE privileges don't necessarily imply SELECT
privileges, the role that modifies a table doesn't necessarily have the
privileges required to check the constraints. It sounds like creating
the constraint check triggers as a security definer function, with a
role that has SELECT privileges, is the more complete solution rather
than a workaround.

Given the above and the fact that the patch is a breaking change, my
vote would still be to keep the current behavior and update the
documentation. Though I'd be happy to be overruled by someone with more
knowledge of triggers.

Thanks,
Joe Koshakow