Thread: Triggers on underlying tables of updatable views

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.

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.

Yours,
Laurenz Albe

Thanks for your reply.

Maybe I am missing an obvious point, but I don't think that's the case.
SQL example:


CREATE ROLE view_access;
CREATE TABLE data (a integer primary key);
CREATE VIEW data_view AS SELECT * FROM data;
GRANT SELECT, INSERT ON data_view TO view_access;

CREATE FUNCTION trig() RETURNS trigger AS $$
BEGIN
    RAISE NOTICE 'trig() executed as user=%', current_user;
    PERFORM COUNT(*) FROM data;
    RETURN new;
END
$$ LANGUAGE plpgsql;
CREATE TRIGGER trig AFTER INSERT ON data FOR EACH ROW EXECUTE FUNCTION
trig();


-- Executed by owner of view, data and trigger: (as expected) all ok
INSERT INTO data_view VALUES (1);

SET ROLE TO view_access;
-- Executed by view_access: fails
INSERT INTO data_view VALUES (2);


For the second INSERT, the trigger is called by the user performing the
insert instead of the user owning the table or the view.


Best Regards,

Max Ziermann

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.
>
> Yours,
> Laurenz Albe
>

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