On Sat, Jun 22, 2024 at 7:21 PM Joseph Koshakow <kosh...@gmail.com> wrote:
> On Sat, Jun 22, 2024 at 6:23 PM David G. Johnston < > david.g.johns...@gmail.com> wrote: > > > except invoker and triggerer are the same entity > > Maybe "executor" would have been a better term than 'invoker". In this > specific example they are not the same entity. The trigger is > triggered and queued by one role and executed by a different role, > hence the confusion. > No matter what we label the keyword it would be represent the default and existing behavior whereby the environment at trigger resolution time, not trigger enqueue time, is used. I suppose there is an argument for capturing and reapplying the trigger enqueue time environment and giving that a keyword as well. But fewer options has value and security definer seems like the strictly superior option. > Though I agree with Laurenz, special SQL syntax > for this exotic corner case is a little too much. > It doesn't seem like a corner case if we want to institute a new recommended practice that all triggers should be created with security definer. We seem to be discussing that without giving the dba a choice in the matter - but IMO we do want to provide the choice and leave the default. > > Security definer on the function would take precedence as would its set > clause. > > These trigger options seem a bit redundant with the equivalent options > on the function that is executed by the trigger. What would be the > advantages or differences of setting these options on the trigger > versus the function? > > At least security definer needs to take precedence as the function owner is fully expecting their role to be the one executing the function, not whomever the trigger owner might be. If putting a set clause on the trigger is a thing then the same thing goes - the function author, if they also did that, expects their settings to be in place. Whether it really makes sense to have trigger owner set configuration when they attach the function is arguable but also the most flexible option. David J.
