Horton

Re: Wrong security context for deferred triggers?

David G. Johnston <david.g.johnston@gmail.com>

View thread
From: "David G. Johnston" <david.g.johnston@gmail.com>
To: Joseph Koshakow <koshy44@gmail.com>
Cc: Laurenz Albe <laurenz.albe@cybertec.at>, Tomas Vondra <tomas.vondra@enterprisedb.com>, pgsql-hackers@lists.postgresql.org
Date: 2024-06-23T03:13:09Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Doc: improve description of which role runs a trigger.

  2. Change role names used in trigger test.

  3. Ensure that AFTER triggers run as the instigating user.

  4. Reverse the search order in afterTriggerAddEvent().

On Sat, Jun 22, 2024 at 7:21 PM Joseph Koshakow <koshy44@gmail.com> wrote:

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