Date: Thu, 5 Jun 2025 09:34:41 -0700
From: Noah Misch <noah@leadboat.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Laurenz Albe <laurenz.albe@cybertec.at>,
	Pavel Stehule <pavel.stehule@gmail.com>,
	pgsql-hackers@lists.postgresql.org
Subject: Re: Wrong security context for deferred triggers?
Message-ID: <20250605163441.2f.nmisch@google.com>
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>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <1283937.1749136204@sss.pgh.pa.us>
User-Agent: Mutt/2.2.12 (2023-09-09)
Archived-At: 
 <https://www.postgresql.org/message-id/20250605163441.2f.nmisch%40google.com>
Precedence: bulk

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:

diff --git a/doc/src/sgml/trigger.sgml b/doc/src/sgml/trigger.sgml
index 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