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.
>
