From: Max Ziermann <max.ziermann@htw-dresden.de>
To: Laurenz Albe <laurenz.albe@cybertec.at>
To: pgsql-docs@lists.postgresql.org
Subject: Re: Triggers on underlying tables of updatable views
Date: Wed, 1 Dec 2021 21:34:44 +0100
Message-ID: <c81b4639-1f77-3454-401b-eed00b02efb6@htw-dresden.de> (raw)
In-Reply-To: <c36ec1311c8ded32bef4dd95dda6dba4f5ca65ff.camel@cybertec.at>
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>
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.
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-docs@postgresql.org
Cc: max.ziermann@htw-dresden.de, laurenz.albe@cybertec.at, pgsql-docs@lists.postgresql.org
Subject: Re: Triggers on underlying tables of updatable views
In-Reply-To: <c81b4639-1f77-3454-401b-eed00b02efb6@htw-dresden.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox