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.
Re: Triggers on underlying tables of updatable views
On Mon, 2021-11-22 at 12:06 +, 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
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 +, 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
ORDER BY in materialized view example?
An example in the materialized view documentation [1] includes an ORDER BY clause without a clear reason. Does it help build the index more efficiently? I suppose it's also sort of like a CLUSTER? But it seems like the ORDER BY should either be explained or dropped: as is, this gives the impression that the ORDER BY can be "embedded" into the resulting relation and persist to other queries that do not include an explicit ORDER BY. (I recently ran across this belief, though not sure if this was due to this example.) Thoughts? [1]: https://www.postgresql.org/docs/current/rules-materializedviews.html
max_slot_wal_keep_size unit is not specified
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/14/runtime-config-replication.html Description: The unit (I assume it's MB) of max_slot_wal_keep_size is not explicitly specified in the docs. If it's intentional then please, disregards, but I've not been able to deduct that from this page alone. Thanks!
Re: Triggers on underlying tables of updatable views
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 +, 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 >
