Re: Triggers on underlying tables of updatable views

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

I think I know the source of the confusion. "current_user" will always
return the current user, even if you are running under the security
context of a different user.

So the function will run with the permissions of the owner of "data",
but "current_user" will return "view_access".

Yours,
Laurenz Albe