Re: Strange permission effect depending on DEFERRABILITY

Στις 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)