可更新视图可以更新基表，但不能更新视图列

Question

BEGIN;
SET search_path TO test;
CREATE USER regress_view_user1;
CREATE USER regress_view_user2;
GRANT CREATE, usage ON SCHEMA test TO regress_view_user1;
GRANT CREATE, usage ON SCHEMA test TO regress_view_user2;
SET session AUTHORIZATION regress_view_user1;
CREATE TABLE test.base_tbl (
a int UNIQUE,
b text,
c float
);
INSERT INTO test.base_tbl
VALUES (1, 'xxx', 1.0);
GRANT SELECT (a, b) ON test.base_tbl TO regress_view_user2;
GRANT INSERT (a, b) ON test.base_tbl TO regress_view_user2;
GRANT UPDATE (a, b) ON test.base_tbl TO regress_view_user2;
COMMIT;
SET session AUTHORIZATION regress_view_user2;
CREATE VIEW rw_view2 AS
SELECT
b AS bb,
c AS cc,
a AS aa
FROM
base_tbl;
INSERT INTO rw_view2 (aa, bb)
VALUES (1, 'xxx')
ON CONFLICT (aa)
DO UPDATE SET
bb = excluded.bb;-- Not allowed

ERROR: 42501: permission denied for table base_tbl我不知道为什么会被拒绝。

select aa, bb from rw_view2;--permission denied
select a,b from base_tbl; --permission ok.

Answer 1

编辑:用更详细和更有用的解决方案重写了这个

视图在Postgres中被实现为在一个空表上运行SELECT语句来定义视图的Select重写规则。这些重写规则与新创建表的所有者具有相同的权限。

当你执行

select aa, bb from rw_view2

您可能期望查询规划器将认识到不需要rw_view2中的列cc，因此对列c缺乏读权限是无关的-实际上查询规划器将在base_tbl上执行表扫描;然而，Postgres会在查询规划器进行优化之前检查权限。重写规则将生成一个等价的解析树:

SELECT aa,bb
FROM (
SELECT
b AS bb,
c AS cc,
a AS aa
FROM
test.base_tbl
) as rw_view2

，其中regress_user2没有执行的权限，导致您遇到的错误。

同样

UPDATE test.rw_view2 SET bb = 'xxx' WHERE aa = 1;

失败是因为查询规划器看到的内容(出于权限的目的)类似于:

UPDATE test.base_tbl SET b = 'xxx'
FROM (
SELECT
b AS bb,
c AS cc,
a AS aa
FROM
test.base_tbl
) as rw_view2
WHERE base_tbl.a = rw_view2.aa;

您不会希望操作授权的成功或失败取决于查询规划器使用的优化，因为这将导致不一致的行为，从而在执行语句时产生难以调试的错误。

所以从根本上说，用户需要SELECT权限对他们在视图中定义的所有列进行任何不重要的INSERT/UPDATE操作。

但是，您可以通过在regress_user1下创建视图，然后将SELECT,UPDATE,INSERT权限授予regress_view_user2来绕过此限制。这个设置允许您直接定义您希望regress_view_user2对rw_view2而不是base_tbl:

的权限。

DROP SCHEMA IF EXISTS test;
CREATE SCHEMA test;
CREATE USER regress_view_user1;
CREATE USER regress_view_user2;
GRANT CREATE, usage ON SCHEMA test TO regress_view_user1;
GRANT CREATE, usage ON SCHEMA test TO regress_view_user2;
SET session AUTHORIZATION regress_view_user1;
CREATE TABLE test.base_tbl (
a int UNIQUE,
b text,
c float
);
INSERT INTO test.base_tbl
VALUES (1, 'xxx', 1.0);
CREATE VIEW test.rw_view2 AS
SELECT
b AS bb,
c AS cc,
a AS aa
FROM
test.base_tbl;
GRANT SELECT (aa,bb) ON test.rw_view2 TO regress_view_user2;
GRANT INSERT (aa,bb) ON test.rw_view2 TO regress_view_user2;
GRANT UPDATE (aa,bb) ON test.rw_view2 TO regress_view_user2;
SET session authorization regress_view_user2;
INSERT INTO test.rw_view2 (aa, bb)
VALUES (1, 'xxx')
ON CONFLICT (aa)
DO UPDATE SET
bb = excluded.bb; --succeeds

在本例中，regress_view_user1对base_tbl的SELECT权限允许对视图进行计算，并且由于regress_user2只使用他们被明确授予访问权限的视图中的列，因此允许继续执行INSERT ON CONFLICT操作。

如果视图有WHERE子句，那么在定义视图时可能需要考虑一些额外的安全问题，例如使用security_barriers以及在视图上为INSERTS/UPDATES设置适当的CHECK OPTION。更多关于使用规则的信息在这里