From: Noah Misch <noah@leadboat.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: Pavel Stehule <pavel.stehule@gmail.com>
Cc: pgsql-hackers@lists.postgresql.org
Subject: Re: Wrong security context for deferred triggers?
Date: Thu, 5 Jun 2025 09:34:41 -0700
Message-ID: <20250605163441.2f.nmisch@google.com> (raw)
In-Reply-To: <1283937.1749136204@sss.pgh.pa.us>
References: <6bca669972ef0d4a665fa83c5b751ab116324f8f.camel@cybertec.at>
<CAFj8pRDcy2OaPS7OtceFRhTRq6VKWbdnuawgaX3EXMQVwTR4VA@mail.gmail.com>
<2653121.1730737337@sss.pgh.pa.us>
<51386e72c47d922ef68eaa67451a52dc3f569843.camel@cybertec.at>
<3944969.1737570355@sss.pgh.pa.us>
<3e4c8fe7adce2e3956e01bae8f1754830bd2d2bf.camel@cybertec.at>
<201480.1737653448@sss.pgh.pa.us>
<67338f5d321ae6a4bae646e04b9fe0a1f27dd27c.camel@cybertec.at>
<20250415155850.9b.nmisch@google.com>
<1283937.1749136204@sss.pgh.pa.us>
On Thu, Jun 05, 2025 at 11:10:04AM -0400, Tom Lane wrote:
> Noah Misch <noah@leadboat.com> writes:
> > Phrase "the role that executed the statement" doesn't match what happens if
> > the role changes mid-statement. Example of a statement that does so:
>
> > select set_config('role', rolname, true), current_user from pg_authid;
>
> > The term "security context" doesn't otherwise appear in doc/. I would just
> > change "run in the security context of the role" to "run as the role". That's
> > simpler and less likely to create an impression that this stops attacks.
>
> I don't mind s/in the security context of/as/, but not sure if we
> need to do more here. Changing role mid-query seems sufficiently
> unusual that we'd probably just confuse people by mentioning the
> case. Unless you have a proposed wording you think is better?
In postgr.es/m/1071973.1749075038@sss.pgh.pa.us of yesterday's release notes
discussion, you wrote "Execute AFTER triggers as the role that was active at
the moment the trigger event was queued." That's a good direction, since it's
correct for the mid-query case without raising it explicitly. Maybe this way:
inline patch 1: doc/src/sgml/trigger.sgml (+3/-4 lines) download inline-1.patchdiff --git a/doc/src/sgml/trigger.sgml b/doc/src/sgml/trigger.sgmlindex e9214dc..4361cdd 100644--- a/doc/src/sgml/trigger.sgml+++ b/doc/src/sgml/trigger.sgml@@ -125,18 +125,17 @@
<para>
The execution of an <literal>AFTER</literal> trigger can be deferred
to the end of the transaction, rather than the end of the statement,
if it was defined as a <firstterm>constraint trigger</firstterm>.
In all cases, a trigger is executed as part of the same transaction as
the statement that triggered it, so if either the statement or the
trigger causes an error, the effects of both will be rolled back.
- Also, the trigger will always run in the security context of the role- that executed the statement that caused the trigger to fire, unless- the trigger function is defined as <literal>SECURITY DEFINER</literal>,- in which case it will run as the function owner.+ Also, the trigger will always run as the role that queued the trigger+ event, unless the trigger function is defined as <literal>SECURITY+ DEFINER</literal>, in which case it will run as the function owner.
</para>
<para>
If an <command>INSERT</command> contains an <literal>ON CONFLICT
DO UPDATE</literal> clause, it is possible for row-level
<literal>BEFORE</literal> <command>INSERT</command> and then
<literal>BEFORE</literal> <command>UPDATE</command> triggers
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: noah@leadboat.com, tgl@sss.pgh.pa.us, laurenz.albe@cybertec.at, pavel.stehule@gmail.com, pgsql-hackers@lists.postgresql.org
Subject: Re: Wrong security context for deferred triggers?
In-Reply-To: <20250605163441.2f.nmisch@google.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox