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.
