On Mon, 2024-09-09 at 16:14 +0300, Achilleas Mantzios - cloud wrote: > The below runs on PostgreSQL 16.4 > > We are trying to implement a certain operation based on a security definer > function : mariner_update_availability_date > > This is supposed to update a table : mariner , which has several other > triggers : > > [...] > zzzmariner_dmq_tg AFTER INSERT OR DELETE OR UPDATE ON mariner DEFERRABLE > INITIALLY DEFERRED FOR EACH ROW EXECUTE FUNCTION export_dmq() > > As you noticed the last trigger is a CONSTRAINT DEFERRABLE trigger. > This function mariner_update_availability_date is supposed to be run by a > user : > cbt_results_import stripped of any privileges to the rest of the system. Here > is > what we get : when we SET the constraint of the last trigger to IMMEDIATE, the > function runs on behalf of its owner (postgres) who has all needed privileges > (as superuser) to run the update on mariner table and also run the triggers . > However, when we run with this CONSTRAINT as DEFERRED then it seems to NOT run > the last deferrable trigger as postgres.
I have proposed a patch that fixes exactly that case: https://commitfest.postgresql.org/49/4888/ So far, the feedback seems to be that it is not considered a bug. But that doesn't mean that we cannot change the behavior. Yours, Laurenz Albe
