From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Max Ziermann <max.ziermann@htw-dresden.de>
To: pgsql-docs@lists.postgresql.org
Subject: Re: Triggers on underlying tables of updatable views
Date: Mon, 22 Nov 2021 21:02:16 +0100
Message-ID: <cde3a45c440fd64884565725fc6f98b6dae8446d.camel@cybertec.at> (raw)
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>
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
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-docs@postgresql.org
Cc: laurenz.albe@cybertec.at, max.ziermann@htw-dresden.de, pgsql-docs@lists.postgresql.org
Subject: Re: Triggers on underlying tables of updatable views
In-Reply-To: <cde3a45c440fd64884565725fc6f98b6dae8446d.camel@cybertec.at>
* 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