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
