Re: Wrong security context for deferred triggers?

On Sat, Jun 22, 2024 at 7:21 PM Joseph Koshakow <koshy44(at)gmail(dot)com> wrote:

> On Sat, Jun 22, 2024 at 6:23 PM David G. Johnston <
> david(dot)g(dot)johnston(at)gmail(dot)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.