Message-ID: <793a7752e64b95d267f73e1e4ee6c12c620da5dd.camel@cybertec.at>
Subject: Re: Yet more ROLE changes in v18 beta1???
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Dominique Devienne <ddevienne@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>
Cc: Robert Haas <robertmhaas@gmail.com>, pgsql-general@postgresql.org
Date: Wed, 04 Jun 2025 19:52:01 -0500
In-Reply-To: <CAFCRh--PmwmqC4cUmGCPTVwzJnxoP3v=WbvyH=4z5ZpSqDRBDA@mail.gmail.com>
References: 
	<CAFCRh-_gUzbsUcr0LAazQZKYn4Mp-HrbgJXiybokM-GVTSZ=pg@mail.gmail.com>
	 <CA+TgmoYEVWid5Mor2img6sQOD7NK+gv71MGBxig7-RqGp1+B4Q@mail.gmail.com>
	 <CAFCRh-_bT_05c2=WySpKmzvf0KA_rLtdc1LOcjTZDZFmSUsW9Q@mail.gmail.com>
	 <1026415.1749051290@sss.pgh.pa.us>
	 <CAFCRh--PmwmqC4cUmGCPTVwzJnxoP3v=WbvyH=4z5ZpSqDRBDA@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.56.2 (3.56.2-1.fc42) 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/793a7752e64b95d267f73e1e4ee6c12c620da5dd.camel%40cybertec.at>
Precedence: bulk

On Wed, 2025-06-04 at 18:42 +0200, Dominique Devienne wrote:
> On Wed, Jun 4, 2025 at 5:34=E2=80=AFPM Tom Lane <tgl@sss.pgh.pa.us> wrote=
:
> > Dominique Devienne <ddevienne@gmail.com> writes:
> > > In any case, if anyone else knows about changes in this area, I'm int=
erested.
> >=20
> > Digging through the commit log didn't find much, but conceivably
> >=20
> > https://git.postgresql.org/gitweb/?p=3Dpostgresql.git&a=3Dcommitdiff&h=
=3D01463e1cc
> > Ensure that AFTER triggers run as the instigating user.
>=20
> Thanks Tom. We don't drop ROLEs in trigger (yet, we'll get there).
> So that particular commit is probably not the reason.
> Thanks a bunch for looking into it. --DD

That change is not about dropping roles, but:

- before, if you had a deferred constraint trigger that was triggered while
  you temporarily assumed a different role (e.g., the DML statement is exec=
uted
  in a SECURITY DEFINER function), the trigger was executed as the current =
user
  at commit time

- from v18 on, the trigger gets executed as the user that ran the DML state=
ment

I would be somewhat surprised if you hit this rather exotic case that went
unnoticed for many years, but it is possible.
Do you have deferred constraint triggers?

Yours,
Laurenz Albe