hi. the following setup is extract from src/test/regress/sql/updatable_views.sql you can search keywords: "-- ordinary view on top of security invoker view permissions" ------------------------------------------------------------------------------------ CREATE USER regress_view_user1; CREATE USER regress_view_user2; CREATE USER regress_view_user3; drop table if exists base_tbl cascade; -- ordinary view on top of security invoker view permissions CREATE TABLE base_tbl(a int, b text, c float); INSERT INTO base_tbl VALUES (1, 'Row 1', 1.0); SET SESSION AUTHORIZATION regress_view_user1; CREATE VIEW rw_view1 AS SELECT b AS bb, c AS cc, a AS aa FROM base_tbl; ALTER VIEW rw_view1 SET (security_invoker = true); SELECT * FROM rw_view1; -- not allowed UPDATE rw_view1 SET aa=aa; -- not allowed MERGE INTO rw_view1 t USING (VALUES (2, 'Row 2', 2.0)) AS v(a,b,c) ON t.aa = v.a WHEN NOT MATCHED THEN INSERT VALUES (v.b, v.c, v.a); -- not allowed SET SESSION AUTHORIZATION regress_view_user2; CREATE VIEW rw_view2 AS SELECT cc AS ccc, aa AS aaa, bb AS bbb FROM rw_view1; GRANT SELECT, UPDATE ON rw_view2 TO regress_view_user3; SELECT * FROM rw_view2; -- not allowed UPDATE rw_view2 SET aaa=aaa; -- not allowed MERGE INTO rw_view2 t USING (VALUES (2, 'Row 2', 2.0)) AS v(a,b,c) ON t.aaa = v.a WHEN NOT MATCHED THEN INSERT VALUES (v.c, v.a, v.b); -- not allowed RESET SESSION AUTHORIZATION; GRANT SELECT(a,b) ON base_tbl TO regress_view_user1; GRANT UPDATE (a, b) ON base_tbl TO regress_view_user1; ------------------------------------------------------------------------------------ SET SESSION AUTHORIZATION regress_view_user1; SELECT a, b FROM base_tbl; -- ok SELECT aa, bb FROM rw_view1; -- ok UPDATE rw_view1 SET aa=11, bb=22; --ok UPDATE rw_view1 SET aa=11, bb=22 returning aa,bb; --not ok. Should the last query be allowed? The (Updatable Views section in create_view.sgml) didn't explain it or I didn't fully understand it. <<QUOTE from manual [1] 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, by default, the view's owner must have the relevant privileges on the underlying base relations, whereas the user performing the update does not need any permissions on the underlying base relations (see Section 41.5). However, if the view has security_invoker set to true, the user performing the update, rather than the view owner, must have the relevant privileges on the underlying base relations. <<end of the QUOTE [1] https://www.postgresql.org/docs/current/sql-createview.html
