I'm not sure where in the docs a clarification could best be placed. For me, the confusion arises from the fact that the updatable views section on the CREATE VIEW docs aren't very clear what *actually* happens when performing insert/update etc. through a view. It seems like the distinction between "security context" and the actual user might be helpful to understand the behaviour.
With this background in mind, I still think that the wording "the user performing the update does not need ANY permissions on the underlying base relations" (from CREATE VIEW; emphasis mine) is misleading. However, to me, it would be perfectly fine if this statement was scoped to the actual insert/update on the base relation, thus excluding triggered functions (unless SECURITY DEFINERs). Best Regards, Max Ziermann Am 23.11.21 um 11:28 schrieb Laurenz Albe: > On Mon, 2021-11-22 at 21:02 +0100, Laurenz Albe wrote: >> 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: > After some more thinking and experimenting, I realize that was wrong. > > The trigger will always execute with the permissions of the user > running the query. > Only the permissions on the underlying tables are checked for the > view owner, the actual query is executed in the security context of > the user that queries the view. > > I don't think that requires special mention on the CREATE VIEW page, > since it is no different when views are not involved. > It might be worth mentioning on > https://www.postgresql.org/docs/current/trigger-definition.html > that triggers (unless the function is SECURITY DEFINER) are executed > under the security context of the user that runs the query, rather > than under the security context of the table owner. >
