public inbox for [email protected]
help / color / mirror / Atom feedFrom: Tom Lane <[email protected]>
To: Achilleas Mantzios - cloud <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: Strange permission effect depending on DEFERRABILITY
Date: Mon, 09 Sep 2024 10:21:40 -0400
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
Achilleas Mantzios - cloud <[email protected]> writes:
> 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 strippedof 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.
AFAIR the trigger mechanisms do not change the execution environment.
If they did, then for example a trigger that stuffs CURRENT_USER into
a last_updated_by column would not give the desired results.
I'd suggest marking the problem trigger function as SECURITY DEFINER
if you want it to run as its owner.
regards, tom lane
view thread (2+ messages)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: Strange permission effect depending on DEFERRABILITY
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox