Re: Wrong security context for deferred triggers?

Laurenz Albe <laurenz.albe@cybertec.at>

From: Laurenz Albe <laurenz.albe@cybertec.at>

To: "David G. Johnston" <david.g.johnston@gmail.com>, Joseph Koshakow <koshy44@gmail.com>

Cc: Tomas Vondra <tomas.vondra@enterprisedb.com>, pgsql-hackers@lists.postgresql.org

Date: 2024-06-26T09:02:30Z

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 →

Doc: improve description of which role runs a trigger.
- c37be39a74b2 18.0 landed
Change role names used in trigger test.
- 4b05ebf0957b 18.0 landed
Ensure that AFTER triggers run as the instigating user.
- 01463e1cccd3 18.0 landed
Reverse the search order in afterTriggerAddEvent().
- 7921927bbb9d 18.0 landed

On Sat, 2024-06-22 at 20:13 -0700, David G. Johnston wrote:
> [bikeshedding discussion about SQL syntax]

Sure, something like CREATE TRIGGER ... USING {INVOKER|CURRENT} ROLE
orsimilar would work, but think that this discussion is premature
at this point.  If we have syntax to specify the behavior
of deferred triggers, that needs a new column in "pg_trigger", support
in pg_get_triggerdef(), pg_dump, pg_upgrade etc.

All that is possible, but keep in mind that we are talking about corner
case behavior.  To the best of my knowledge, nobody has even noticed the
difference in behavior up to now.

I think that we should have some consensus about the following before
we discuss syntax:

- Does anybody depend on the current behavior and would be hurt if
  my current patch went in as it is?

- Is this worth changing at all or had we better document the current
  behavior and leave it as it is?

Concerning the latter, I am hoping for a detailed description of our
customer's use case some time soon.

Yours,
Laurenz Albe