On Sat, Jun 8, 2024 at 2:37 PM Joseph Koshakow <koshy44@gmail.com> wrote:
Something like `SECURITY INVOKER | SECURITY TRIGGERER` (modeled after the modifiers in `CREATE FUNCTION`) that control which role is used.
I'm inclined toward this option (except invoker and triggerer are the same entity, we need owner|definer). I'm having trouble accepting changing the existing behavior here but agree that having a mode whereby the owner of the trigger/table executes the trigger function in an initially clean environment (server/database defaults; the owner role isn't considered as having logged in so their personalized configurations do not take effect) (maybe add a SET clause to create trigger too). Security invoker would be the default, retaining current behavior for upgrade/dump+restore.
Security definer on the function would take precedence as would its set clause.