From: Max Ziermann <max.ziermann@htw-dresden.de>
Subject: Re: Triggers on underlying tables of updatable views
To: Laurenz Albe <laurenz.albe@cybertec.at>, <pgsql-docs@lists.postgresql.org>
References: <163758281947.26190.10251137266467665332@wrigleys.postgresql.org>
 <2f54dbb7d59aacacbc4654b0bc56faaebe993e09.camel@cybertec.at>
 <3e14b477-40ce-4b6a-047a-9fea3843427c@htw-dresden.de>
 <cde3a45c440fd64884565725fc6f98b6dae8446d.camel@cybertec.at>
 <c36ec1311c8ded32bef4dd95dda6dba4f5ca65ff.camel@cybertec.at>
Message-ID: <c81b4639-1f77-3454-401b-eed00b02efb6@htw-dresden.de>
Date: Wed, 1 Dec 2021 21:34:44 +0100
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101
 Thunderbird/78.14.0
MIME-Version: 1.0
In-Reply-To: <c36ec1311c8ded32bef4dd95dda6dba4f5ca65ff.camel@cybertec.at>
Content-Type: text/plain; charset="utf-8"
Content-Transfer-Encoding: 7bit
Content-Language: en-GB
Archived-At: 
 <https://www.postgresql.org/message-id/c81b4639-1f77-3454-401b-eed00b02efb6%40htw-dresden.de>
Precedence: bulk

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