Блог

Ниже вы можете найти код для минимального тестового примера для проблемы, с которой я сталкиваюсь в системе, где RLS используется для управления доступом к иерархической структуре данных. Я использую Postgres v11.

В коде, который у меня есть units , который является объектом верхнего уровня. units иметь subunits отношение 1-n.

Существуют также users случаи, когда a user может иметь доступ к ряду units через unit_owner таблицу.

Политики RLS предназначены для того, чтобы позволить user вставить новое subunits в units принадлежащее ему.

Все это работает нормально, как видно вплоть до 2-й последней строки в коде.

НО вот моя проблема: эта база данных предоставляется через промежуточное программное обеспечение GraphQL (Postgraphile), которому требуется результат вставки обратно с помощью INSERT ... RETURNING функции.

И, как видно из последнего оператора insert, это не работает, появляется сообщение «ОШИБКА: новая строка нарушает политику безопасности на уровне строк«.

Проблема, по-видимому, возникает из-за того, что для возврата требуются права выбора, а функция политики выбора оценивается с использованием набора subunit идентификаторов, доступных до вставки, а не после.

Любые советы о том, как я могу позволить своим пользователям вставлять подразделения в свои модули, будут оценены!

 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

--
  

Вы правильно проанализировали проблему: вставленная строка недоступна для подзапроса в FOR SELECT политике on subunit .

Нет способа «заставить это работать» таким образом. Вам нужно будет найти другой тест для политики, который не ожидает найти новую строку в таблице. В соответствии с тем, как написан ваш случай, вы могли бы напрямую использовать unit_id новую строку для более простого теста, но вы уверяете нас, что это не сработает в вашем реальном случае использования…

Вы не можете выбрать новую строку, но вы можете использовать все атрибуты новой строки. Поэтому попробуйте написать условие, используя выражение SQL, которое не включает подвыборку в самой таблице.

Чтобы заставить его работать (и вы не видите способа изменить базовый RLS), вы можете создать пользовательскую функцию мутации, которую вы можете пометить как SECURITY DEFINER .

В этой функции мутации вам нужно будет выполнить проверку самостоятельно.

Это не отвечает на ваш вопрос относительно RLS, к которому, я думаю, уже был получен правильный доступ в другом ответе на этот вопрос. Скорее всего, это совет от другого пользователя Postgraphile.

Также:

• По моему опыту, использование функций в RLS почти всегда приводит к снижению производительности. Особенно когда они не встроены. В вашем случае VOLATILE и уже SECURITY DEFINER должно предотвращать встраивание.

• Почти всегда быстрее использовать EXISTS вместо IN в определениях RLS. Ваш опыт может отличаться.