> 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.