From: PG Doc comments form <noreply@postgresql.org>
To: pgsql-docs@lists.postgresql.org
Cc: max.ziermann@htw-dresden.de
Subject: Triggers on underlying tables of updatable views
Date: Mon, 22 Nov 2021 12:06:59 +0000
Message-ID: <163758281947.26190.10251137266467665332@wrigleys.postgresql.org> (raw)
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/14/sql-createview.html
Description:
Hi all,
I'm using an updatable view with an underlying table. The underlying table
has a trigger attached to it that performs a select statement on the
underlying table. When attempting an insert/update to the view, the
operation on the underlying table triggers the trigger. The trigger is
executed with the permissions of the user performing the insert/update on
the view. Since in my setup the user inserting/updating the view has no
permissions on the underlying table, the trigger fails. (When I remove the
trigger, the insert/update is possible.)
I would have expected that the trigger is executed with permissions of the
user owning the view, rather than the user executing insert/update on the
view. To me, that would seem a reasonable expectation based on what the
CREATE VIEW docs state on updatable views and the required permissions
(especially the last half-sentence):
"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?
Thanks.
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: noreply@postgresql.org, pgsql-docs@lists.postgresql.org, max.ziermann@htw-dresden.de
Subject: Re: Triggers on underlying tables of updatable views
In-Reply-To: <163758281947.26190.10251137266467665332@wrigleys.postgresql.org>
* 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