MIME-Version: 1.0
From: jian he <jian.universality@gmail.com>
Date: Wed, 21 Aug 2024 17:08:25 +0800
Message-ID: <CACJufxG31b+LwmxHz6xw9Qr+qc3VgQaRdpiaGUoZFrf=NBu48g@mail.gmail.com>
Subject: security invoker review need full select (all columns) to do DML?
To: pgsql-general list <pgsql-general@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Archived-At: <https://www.postgresql.org/message-id/CACJufxG31b%2BLwmxHz6xw9Qr%2Bqc3VgQaRdpiaGUoZFrf%3DNBu48g%40mail.gmail.com>
Precedence: bulk

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