Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1snfrs-005HLL-SA for pgsql-general@arkaria.postgresql.org; Mon, 09 Sep 2024 14:59:54 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1snfrs-00DW2q-9s for pgsql-general@arkaria.postgresql.org; Mon, 09 Sep 2024 14:59:52 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1snfrr-00DW27-TY for pgsql-general@lists.postgresql.org; Mon, 09 Sep 2024 14:59:51 +0000 Received: from cloud.gatewaynet.com ([185.90.37.94]) by magus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1snfro-000JkD-9e for pgsql-general@lists.postgresql.org; Mon, 09 Sep 2024 14:59:51 +0000 Content-Type: multipart/alternative; boundary="------------0uQU8MozS1McTmBZc0oGnO9V" Message-ID: Date: Mon, 9 Sep 2024 17:59:46 +0300 MIME-Version: 1.0 Subject: Re: Strange permission effect depending on DEFERRABILITY Content-Language: en-US To: Tom Lane Cc: "pgsql-general@lists.postgresql.org" References: <89e33a53-909c-6a02-bfc6-2578ba974e16@cloud.gatewaynet.com> <3143128.1725891700@sss.pgh.pa.us> From: Achilleas Mantzios - cloud In-Reply-To: <3143128.1725891700@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------0uQU8MozS1McTmBZc0oGnO9V Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 9/9/24 17:21, Tom Lane wrote: > Achilleas Mantzios - cloud 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. Thank you Tom.  With a little bit of debugging is obvious that the last deferred trigger function runs outside the security environment of the top SECURITY DEFINER function (naturally), however current_user seems to be on par with the security definer owner, cbt_results_import@[local]/dynacom=> begin ; BEGIN cbt_results_import@[local]/dynacom=*> SET CONSTRAINTS zzzmariner_dmq_tg IMMEDIATE; SET CONSTRAINTS cbt_results_import@[local]/dynacom=*> select mariner_update_availability_date(13916, '2020-02-28'); NOTICE:  manage past : the current_user is postgres, session_user is cbt_results_import, system_user id md5:cbt_results_import NOTICE:  manage past : the current_user is postgres, session_user is cbt_results_import, system_user id md5:cbt_results_import mariner_update_availability_date ---------------------------------- (1 row) cbt_results_import@[local]/dynacom=*> commit ; COMMIT cbt_results_import@[local]/dynacom=> begin ; BEGIN cbt_results_import@[local]/dynacom=*> SET CONSTRAINTS zzzmariner_dmq_tg DEFERRED; SET CONSTRAINTS cbt_results_import@[local]/dynacom=*> select mariner_update_availability_date(13916, '2020-02-28'); NOTICE:  manage past : the current_user is postgres, session_user is cbt_results_import, system_user id md5:cbt_results_import mariner_update_availability_date ---------------------------------- (1 row) cbt_results_import@[local]/dynacom=*> commit ; NOTICE:  manage past : the current_user is cbt_results_import, session_user is cbt_results_import, system_user id md5:cbt_results_import ERROR:  permission denied for table export_dmq CONTEXT:  SQL statement "DELETE FROM export_dmq where id=($1).id and op='U' and tbl='mariner'" PL/pgSQL function export_dmq() line 18 at EXECUTE Thank you and sorry for missing something so obvious. > > regards, tom lane --------------0uQU8MozS1McTmBZc0oGnO9V Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit


On 9/9/24 17:21, Tom Lane wrote:
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.

Thank you Tom.  With a little bit of debugging is obvious that the last deferred trigger function runs outside the security environment of the top SECURITY DEFINER function (naturally), however current_user seems to be on par with the security definer owner,

cbt_results_import@[local]/dynacom=> begin ;
BEGIN
cbt_results_import@[local]/dynacom=*> SET CONSTRAINTS zzzmariner_dmq_tg IMMEDIATE;
SET CONSTRAINTS
cbt_results_import@[local]/dynacom=*> select mariner_update_availability_date(13916, '2020-02-28');
NOTICE:  manage past : the current_user is postgres, session_user is cbt_results_import, system_user id md5:cbt_results_import  
NOTICE:  manage past : the current_user is postgres, session_user is cbt_results_import, system_user id md5:cbt_results_import  
mariner_update_availability_date  
----------------------------------
 
(1 row)

cbt_results_import@[local]/dynacom=*> commit ;
COMMIT
cbt_results_import@[local]/dynacom=> begin ;
BEGIN
cbt_results_import@[local]/dynacom=*> SET CONSTRAINTS zzzmariner_dmq_tg DEFERRED;
SET CONSTRAINTS
cbt_results_import@[local]/dynacom=*> select mariner_update_availability_date(13916, '2020-02-28');
NOTICE:  manage past : the current_user is postgres, session_user is cbt_results_import, system_user id md5:cbt_results_import  
mariner_update_availability_date  
----------------------------------
 
(1 row)

cbt_results_import@[local]/dynacom=*> commit ;
NOTICE:  manage past : the current_user is cbt_results_import, session_user is cbt_results_import, system_user id md5:cbt_results_import  
ERROR:  permission denied for table export_dmq
CONTEXT:  SQL statement "DELETE FROM export_dmq where id=($1).id and op='U' and tbl='mariner'"
PL/pgSQL function export_dmq() line 18 at EXECUTE

Thank you and sorry for missing something so obvious.


			regards, tom lane
--------------0uQU8MozS1McTmBZc0oGnO9V--