Re: Strange permission effect depending on DEFERRABILITY

Achilleas Mantzios - cloud <a(dot)mantzios(at)cloud(dot)gatewaynet(dot)com> 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