postgresql - 如何使 INSERT ... RETURNING 语句在使用行级安全性 (RLS) 时起作用？

Question

您可以在下面找到我在使用 RLS 管理对分层数据结构的访问的系统中遇到的问题的最小测试用例的代码。我正在使用 Postgres v11。

在代码中我有units，这是一个顶级对象。 units 在 1-n 关系中有 subunits。

还有用户，其中用户可以通过unit_owner访问多个单元表。

RLS 策略旨在让 用户 将新的 subunits 插入到他拥有的 units 中。

直到代码中的倒数第二行，所有这些都工作正常。

但这是我的问题:这个数据库是通过 GraphQL 中间件 (Postgraphile) 公开的，它需要通过 INSERT ... RETURNING 插入的结果> 功能。

从最后一个插入语句中可以看出，这不起作用，它得到“错误:新行违反行级安全策略”。

问题似乎是因为 RETURNING 需要选择权限，并且选择策略函数是使用 插入之前可用的 subunit id 集来评估的，不是之后。

任何关于如何让我的用户将子单元插入他们的单元的提示都将不胜感激!

CREATE SCHEMA insert_returning;
CREATE ROLE users;

GRANT USAGE ON SCHEMA insert_returning TO users;

DROP TABLE IF EXISTS insert_returning.unit;
DROP TABLE IF EXISTS insert_returning.subunit;
DROP TABLE IF EXISTS insert_returning.unit_owner;

CREATE TABLE insert_returning.unit (
    id integer NOT NULL,
    description varchar NULL,
    CONSTRAINT unit_pk PRIMARY KEY (id)
);

CREATE TABLE insert_returning.subunit (
    id integer NOT NULL,
    unit_id integer NOT NULL,
    description varchar NULL,
    CONSTRAINT subunit_pk PRIMARY KEY (id)
);

CREATE TABLE insert_returning.unit_owner (
    user_id integer NOT NULL,
    unit_id integer NOT NULL
);

GRANT SELECT,INSERT,UPDATE ON TABLE insert_returning.unit TO users;
GRANT SELECT,INSERT,UPDATE ON TABLE insert_returning.subunit TO users;

GRANT SELECT ON TABLE insert_returning.unit_owner TO users;

CREATE OR REPLACE FUNCTION insert_returning.get_users_units()
RETURNS SETOF integer
LANGUAGE sql VOLATILE SECURITY DEFINER AS
$$
  SELECT uo.unit_id FROM insert_returning.unit_owner uo
    WHERE uo.user_id = 17;
$$;


CREATE OR REPLACE FUNCTION insert_returning.get_users_subunits()
RETURNS SETOF integer
LANGUAGE sql VOLATILE SECURITY DEFINER AS
$$
  SELECT s.id FROM insert_returning.subunit s
    JOIN insert_returning.unit_owner uo ON uo.unit_id = s.unit_id
    WHERE uo.user_id = 17;
$$;


ALTER TABLE insert_returning.unit ENABLE ROW LEVEL SECURITY;
ALTER TABLE insert_returning.subunit ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS select_unit ON insert_returning.unit;
DROP POLICY IF EXISTS select_subunit ON insert_returning.subunit;
DROP POLICY IF EXISTS insert_subunit ON insert_returning.subunit;

CREATE POLICY select_unit ON insert_returning.unit FOR SELECT TO PUBLIC USING ((
    SELECT (id IN ( SELECT unit_id FROM insert_returning.unit_owner WHERE user_id = 17))
));

CREATE POLICY select_subunit ON insert_returning.subunit FOR SELECT TO PUBLIC USING ((
    SELECT (id IN (SELECT insert_returning.get_users_subunits()) )
));

CREATE POLICY insert_subunit ON insert_returning.subunit FOR INSERT TO PUBLIC WITH CHECK ((
    SELECT (unit_id IN (SELECT insert_returning.get_users_units()) )
));


INSERT INTO insert_returning.unit (id, description) VALUES (1, 'I am visible');
INSERT INTO insert_returning.unit (id, description) VALUES (2, 'I am hidden');

INSERT INTO insert_returning.subunit (id, unit_id, description) VALUES (1, 1, 'I belong to a visible unit');
INSERT INTO insert_returning.subunit (id, unit_id, description) VALUES (2, 2, 'I belong to a hidden unit');
INSERT INTO insert_returning.subunit (id, unit_id, description) VALUES (3, 1, 'I too belong to a visible unit');

INSERT INTO insert_returning.unit_owner (user_id,unit_id) VALUES (17,1);

SET ROLE users;

SELECT * FROM insert_returning.subunit; -- works

INSERT INTO insert_returning.subunit VALUES (4, 1, 'I am a new subunit'); -- works

INSERT INTO insert_returning.subunit VALUES (5, 1, 'I am another new subunit') RETURNING *; -- FAILS

--

Answer 1

您分析的问题是正确的:插入的行对于 subunit 上的 FOR SELECT 策略中的子查询不可用。

没有办法像这样“让它工作”。您将不得不为该策略找到一个不同的测试，一个不希望在表中找到新行的测试。按照您的案例编写方式，您可以直接使用新行的 unit_id 进行更简单的测试，但您向我们保证这在您的实际用例中不起作用......

您不能选择新行，但可以使用新行的所有属性。因此，请尝试使用不涉及表本身的子查询的 SQL 表达式来编写条件。