Message-ID: <363f99f669d19bbb2776aa0b7f6b0d991d11d323.camel@cybertec.at>
Subject: Re: Strange permission effect depending on DEFERRABILITY
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Achilleas Mantzios - cloud <a.mantzios@cloud.gatewaynet.com>, 
 "pgsql-general@lists.postgresql.org"
	 <pgsql-general@lists.postgresql.org>
Date: Tue, 10 Sep 2024 19:22:00 +0200
In-Reply-To: <40558bdd-d641-feac-84fe-65b3e87ec085@cloud.gatewaynet.com>
References: <89e33a53-909c-6a02-bfc6-2578ba974e16@cloud.gatewaynet.com>
	 <a3559b3c11330e59a4757b97d741c4cd6005d659.camel@cybertec.at>
	 <40558bdd-d641-feac-84fe-65b3e87ec085@cloud.gatewaynet.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.52.4 (3.52.4-1.fc40) 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/363f99f669d19bbb2776aa0b7f6b0d991d11d323.camel%40cybertec.at>
Precedence: bulk

On Tue, 2024-09-10 at 12:20 +0300, Achilleas Mantzios - cloud wrote:
> On 9/10/24 00:09, Laurenz Albe wrote:
> > On Mon, 2024-09-09 at 16:14 +0300, Achilleas Mantzios - cloud wrote:
> > > The below runs on PostgreSQL 16.4
> > >=20
> > > We are trying to implement a certain operation based on a security de=
finer
> > > function : mariner_update_availability_date
> > >=20
> > > This is supposed to update a table : mariner , which has several othe=
r triggers :
> > >=20
> > >    [...]
> > >  =C2=A0=C2=A0zzzmariner_dmq_tg AFTER INSERT OR DELETE OR UPDATE ON ma=
riner DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE FUNCTION export_dm=
q()
> > >=20
> > > As you noticed the last trigger is a CONSTRAINT DEFERRABLE trigger.
> > > This function mariner_update_availability_date is supposed to be run =
by a user :
> > > cbt_results_import stripped of any privileges to the rest of the syst=
em. Here is
> > > what we get : when we SET the constraint of the last trigger to IMMED=
IATE, the
> > > function runs on behalf of its owner (postgres) who has all needed pr=
ivileges
> > > (as superuser) to run the update on mariner table and also run the tr=
iggers .
> > > However, when we run with this CONSTRAINT as DEFERRED then it seems t=
o NOT run
> > > the last deferrable trigger as postgres.
> > >=20
> > I have proposed a patch that fixes exactly that case:
> > https://commitfest.postgresql.org/49/4888/
> >=20
> > So far, the feedback seems to be that it is not considered a bug.
> > But that doesn't mean that we cannot change the behavior.
>=20
> Nice work! However I am not sure. What's a trigger owner btw in the=20
> thread :
> ? Do they mean the table owner? is the trigger creator / owner stored=20
> somewhere ? I dont see it in system tables or the schema dump. Or do=20
> they imply the trigger function owner ?

The owner of a trigger is always the owner of the table.

> Maybe controlling the queued and later executed trigger invocations=20
> security context via a new special GUC? such as :
>=20
> trigger_security_ctx =3D current_user (default) | table/trigger_owner |=
=20
> execution_triggered_user
>=20
> (in every case a SECURITY DEFINER function would override the above setti=
ng)

The PostgreSQL project has made bad experiences with parameters that change
the semantics of SQL statements, so I think that idea will meet resistance.

Besides, what I am proposing in the patch is not to use the owner of the
table, but the current_user at the time that the trigger is queued.

I had the impression that that is what you are looking for.
Executing as table owner can easily be done with a SECURITY DEFINER functio=
n.

Yours,
Laurenz Albe