From: Tom Lane <tgl@sss.pgh.pa.us>
To: Achilleas Mantzios - cloud <a.mantzios@cloud.gatewaynet.com>
cc: "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
Subject: Re: Strange permission effect depending on DEFERRABILITY
In-reply-to: <89e33a53-909c-6a02-bfc6-2578ba974e16@cloud.gatewaynet.com>
References: <89e33a53-909c-6a02-bfc6-2578ba974e16@cloud.gatewaynet.com>
Comments: In-reply-to Achilleas Mantzios - cloud <a.mantzios@cloud.gatewaynet.com>
	message dated "Mon, 09 Sep 2024 16:14:02 +0300"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <3143127.1725891700.1@sss.pgh.pa.us>
Date: Mon, 09 Sep 2024 10:21:40 -0400
Message-ID: <3143128.1725891700@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/3143128.1725891700%40sss.pgh.pa.us>
Precedence: bulk

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