public inbox for [email protected]help / color / mirror / Atom feed
Re: Strange permission effect depending on DEFERRABILITY 3+ messages / 3 participants [nested] [flat]
* Re: Strange permission effect depending on DEFERRABILITY @ 2024-09-10 09:20 Achilleas Mantzios - cloud <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Achilleas Mantzios - cloud @ 2024-09-10 09:20 UTC (permalink / raw) To: Laurenz Albe <[email protected]>; [email protected] <[email protected]> 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 : https://www.postgresql.org/message-id/flat/77b89e609f21380785865542609fbc14010021c8.camel%40cybertec... ? 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 ? 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) just my 2cents > Yours, > Laurenz Albe ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Strange permission effect depending on DEFERRABILITY @ 2024-09-10 17:22 Laurenz Albe <[email protected]> parent: Achilleas Mantzios - cloud <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Laurenz Albe @ 2024-09-10 17:22 UTC (permalink / raw) To: Achilleas Mantzios - cloud <[email protected]>; [email protected] <[email protected]> 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. > 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. I had the impression that that is what you are looking for. Executing as table owner can easily be done with a SECURITY DEFINER function. Yours, Laurenz Albe ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Strange permission effect depending on DEFERRABILITY @ 2024-09-10 17:41 Achilleas Mantzios <[email protected]> parent: Laurenz Albe <[email protected]> 0 siblings, 0 replies; 3+ messages in thread From: Achilleas Mantzios @ 2024-09-10 17:41 UTC (permalink / raw) To: Laurenz Albe <[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) ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2024-09-10 17:41 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2024-09-10 09:20 Re: Strange permission effect depending on DEFERRABILITY Achilleas Mantzios - cloud <[email protected]> 2024-09-10 17:22 ` Laurenz Albe <[email protected]> 2024-09-10 17:41 ` Achilleas Mantzios <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox