On Mon, Jul 1, 2024 at 11:45 AM Laurenz Albe <laurenz.a...@cybertec.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
