Message-ID: <cde3a45c440fd64884565725fc6f98b6dae8446d.camel@cybertec.at>
Subject: Re: Triggers on underlying tables of updatable views
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Max Ziermann <max.ziermann@htw-dresden.de>,
	pgsql-docs@lists.postgresql.org
Date: Mon, 22 Nov 2021 21:02:16 +0100
In-Reply-To: <3e14b477-40ce-4b6a-047a-9fea3843427c@htw-dresden.de>
References: 
	<163758281947.26190.10251137266467665332@wrigleys.postgresql.org>
	 <2f54dbb7d59aacacbc4654b0bc56faaebe993e09.camel@cybertec.at>
	 <3e14b477-40ce-4b6a-047a-9fea3843427c@htw-dresden.de>
Content-Type: text/plain; charset="UTF-8"
User-Agent: Evolution 3.40.4 (3.40.4-2.fc34) 
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/cde3a45c440fd64884565725fc6f98b6dae8446d.camel%40cybertec.at>
Precedence: bulk

On Mon, 2021-11-22 at 19:54 +0100, Max Ziermann wrote:
> Am 22.11.21 um 16:41 schrieb Laurenz Albe:
> > On Mon, 2021-11-22 at 12:06 +0000, PG Doc comments form wrote:
> > > "Note that the user performing the insert, update or delete on the view must
> > > have the corresponding insert, update or delete privilege on the view. In
> > > addition the view's owner must have the relevant privileges on the
> > > underlying base relations, but the user performing the update does not need
> > > any permissions on the underlying base relations (see Section 41.5)."
> > > 
> > > Could it be made more clear that triggers on a underlying table of an
> > > updatable view are still executed with the permissions of the user
> > > performing an insert/update/delete on the view?
> >
> > But that is not the case: that trigger will be executed with the permissions
> > of the owner of the underlying table.
>
> Maybe I am missing an obvious point, but I don't think that's the case.
> SQL example:
> 
> 
> CREATE ROLE view_access;
> CREATE TABLE data (a integer primary key);
> CREATE VIEW data_view AS SELECT * FROM data;
> GRANT SELECT, INSERT ON data_view TO view_access;
> 
> CREATE FUNCTION trig() RETURNS trigger AS $$
> BEGIN
>     RAISE NOTICE 'trig() executed as user=%', current_user;
>     PERFORM COUNT(*) FROM data;
>     RETURN new;
> END
> $$ LANGUAGE plpgsql;
> CREATE TRIGGER trig AFTER INSERT ON data FOR EACH ROW EXECUTE FUNCTION
> trig();
> 
> 
> -- Executed by owner of view, data and trigger: (as expected) all ok
> INSERT INTO data_view VALUES (1);
> 
> SET ROLE TO view_access;
> -- Executed by view_access: fails
> INSERT INTO data_view VALUES (2);
> 
> 
> For the second INSERT, the trigger is called by the user performing the
> insert instead of the user owning the table or the view.

I think I know the source of the confusion.  "current_user" will always
return the current user, even if you are running under the security
context of a different user.

So the function will run with the permissions of the owner of "data",
but "current_user" will return "view_access".

Yours,
Laurenz Albe