public inbox for [email protected]  

help / color / mirror / Atom feed

From: Achilleas Mantzios <[email protected]>
To: Laurenz Albe <[email protected]>
To: [email protected] <[email protected]>
Subject: Re: Strange permission effect depending on DEFERRABILITY
Date: Tue, 10 Sep 2024 20:41:32 +0300
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>

Στις 10/9/24 20:22, ο/η Laurenz Albe έγραψε:
> On Tue, 2024-09-10 at 12:20 +0300, Achilleas Mantzios - cloud wrote:
>> On 9/10/24 00:09, Laurenz Albe wrote:
>>> On Mon, 2024-09-09 at 16:14 +0300, Achilleas Mantzios - cloud wrote:
>>>> The below runs on PostgreSQL 16.4
>>>>
>>>> We are trying to implement a certain operation based on a security definer
>>>> function : mariner_update_availability_date
>>>>
>>>> This is supposed to update a table : mariner , which has several other triggers :
>>>>
>>>>     [...]
>>>>     zzzmariner_dmq_tg AFTER INSERT OR DELETE OR UPDATE ON mariner DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE FUNCTION export_dmq()
>>>>
>>>> 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 stripped of 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.
>>>>
>>> I have proposed a patch that fixes exactly that case:
>>> https://commitfest.postgresql.org/49/4888/
>>>
>>> So far, the feedback seems to be that it is not considered a bug.
>>> But that doesn't mean that we cannot change the behavior.
>> Nice work! However I am not sure. What's a trigger owner btw in the
>> thread :
>> ? Do they mean the table owner? is the trigger creator / owner stored
>> somewhere ? I dont see it in system tables or the schema dump. Or do
>> they imply the trigger function owner ?
> The owner of a trigger is always the owner of the table.
Thank you.
>
>> Maybe controlling the queued and later executed trigger invocations
>> security context via a new special GUC? such as :
>>
>> trigger_security_ctx = current_user (default) | table/trigger_owner |
>> execution_triggered_user
>>
>> (in every case a SECURITY DEFINER function would override the above setting)
> The PostgreSQL project has made bad experiences with parameters that change
> the semantics of SQL statements, so I think that idea will meet resistance.
>
> Besides, what I am proposing in the patch is not to use the owner of the
> table, but the current_user at the time that the trigger is queued.
Yes, your patch "current_user at the time that the trigger is queued " 
is my execution_triggered_user from above. Bad naming from my part.
>
> I had the impression that that is what you are looking for.
To be frank, the current behavior albeit confusing to noobs, (and I have 
no excuses here using PostgreSQL since 2001 in this same job) , seems 
right to me. So I would welcome some kind of GUC/session-level or 
DDL/object-level control but still the current behavior is fine to live 
with.
> Executing as table owner can easily be done with a SECURITY DEFINER function.
>
> Yours,
> Laurenz Albe

-- 
Achilleas Mantzios
  IT DEV - HEAD
  IT DEPT
  Dynacom Tankers Mgmt (as agents only)

view thread (3+ messages)

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