У меня следующая проблема с PostgreSQL 9.3.
Есть представление, инкапсулирующее нетривиальный запрос к некоторым ресурсам (например, документам). Давайте проиллюстрируем это так просто, как
CREATE VIEW vw_resources AS
SELECT * FROM documents; -- there are several joined tables in fact...
Клиентское приложение обычно использует представление с некоторыми условиями WHERE
для нескольких полей, а также может использовать разбиение результатов на страницы, поэтому также могут применяться OFFSET
и LIMIT
.
Теперь, поверх фактического списка ресурсов, вычисленного vw_resources
, я хочу отображать только те ресурсы, которые разрешены текущему пользователю. Существует довольно сложный набор правил, касающихся привилегий (они зависят от нескольких атрибутов рассматриваемых ресурсов, явных ACL, неявных правил, основанных на ролях пользователей или отношениях с другими пользователями...), поэтому я хотел инкапсулировать их все в единая функция. Чтобы предотвратить повторяющиеся дорогостоящие запросы для каждого ресурса, функция берет список идентификаторов ресурсов, оценивает привилегии сразу для всех из них и возвращает набор запрошенных идентификаторов ресурсов вместе с соответствующими привилегиями (чтение/запись различаются). Это выглядит примерно так:
CREATE FUNCTION list_privileges(resource_ids BIGINT[])
RETURNS TABLE (resource_id BIGINT, privilege TEXT)
AS $function$
BEGIN
-- the function lists privileges for a user that would get passed in an argument - omitting that for simplicity
RAISE NOTICE 'list_privileges called'; -- for diagnostic purposes
-- for illustration, let's simply grant write privileges for any odd resource:
RETURN QUERY SELECT id, (CASE WHEN id % 2 = 1 THEN 'write' ELSE 'none' END)
FROM unnest(resource_ids) id;
END;
$function$ LANGUAGE plpgsql STABLE;
Вопрос в том, как интегрировать такую функцию в представление vw_resources
, чтобы она отдавала только те ресурсы, для которых пользователь имеет привилегию (т. е. имеет привилегию 'read'
или 'write'
).
Тривиальное решение будет использовать CTE:
CREATE VIEW vw_resources AS
WITH base_data AS (
SELECT * FROM documents
)
SELECT base_data.*, priv.privilege
FROM base_data
JOIN list_privileges((SELECT array_agg(resource_id) FROM base_data)) AS priv USING (resource_id)
WHERE privilege IN ('read', 'write');
Проблема в том, что само представление дает слишком много строк - некоторые условия WHERE
и предложения OFFSET
/LIMIT
применяются только к самому представлению, например SELECT * FROM vw_resources WHERE id IN (1,2,3) LIMIT 10
(любая сложная фильтрация может быть запрошена клиентским приложением). А поскольку PostgreSQL не может протолкнуть условия вниз по CTE, функция list_privileges(BIGINT[])
заканчивается оценкой привилегий для всех ресурсов в базе данных, что эффективно снижает производительность.
Поэтому я попытался использовать оконную функцию, которая собирала бы идентификаторы ресурсов из всего набора результатов и присоединялась к функции list_privileges(BIGINT[])
во внешнем запросе, как показано ниже, но функция list_privileges(BIGINT[])
в конечном итоге вызывается повторно для каждой строки (о чем свидетельствует 'list_privivations called' уведомления), что как бы портит предыдущее усилие:
CREATE VIEW vw_resources AS
SELECT d.*, priv.privilege
FROM (
SELECT *, array_agg(resource_id) OVER () AS collected
FROM documents
) AS d
JOIN list_privileges(d.collected) AS priv USING (resource_id)
WHERE privilege IN ('read', 'write');
Я бы прибегнул к принуждению клиентов к двум отдельным запросам: первый берет vw_resources
без примененных привилегий, второй вызывает функцию list_privileges(BIGINT[])
, передавая ей список идентификаторов ресурсов, полученных первым запросом, и фильтрует запрещенные ресурсы на стороне клиента. Однако это довольно неуклюже для клиента, и получение, например. первые 20 разрешенных ресурсов были бы практически невозможны, так как ограничение первого запроса просто не дает его - если некоторые ресурсы отфильтровываются из-за привилегий, то у нас просто нет 20 строк в общем результате...
Приветствуется любая помощь!
P.S. Для полноты я добавляю образец таблицы documents
:
CREATE TABLE documents (resource_id BIGINT, content TEXT);
INSERT INTO documents VALUES (1,'a'),(2,'b'),(3,'c');
select version();
- person Clodoaldo Neto   schedule 27.08.2014