public inbox for [email protected]
help / color / mirror / Atom feedFrom: Achilleas Mantzios - cloud <[email protected]>
To: Tom Lane <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: Strange permission effect depending on DEFERRABILITY
Date: Mon, 9 Sep 2024 17:59:46 +0300
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<[email protected]>
On 9/9/24 17:21, Tom Lane wrote:
> 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.
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
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