Re: Strange permission effect depending on DEFERRABILITY

Achilleas Mantzios - cloud <a.mantzios@cloud.gatewaynet.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