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.