Запрос PostgreSQL не использует INDEX, когда включен RLS (Row Level Security)

Я использую PostgreSQL 10.1, иду прямо к делу...

Допустим, у меня есть ТАБЛИЦА:

CREATE TABLE public.document (
    id uuid PRIMARY KEY,

    title   text,
    content text NOT NULL
);

Вместе с индексом джина на нем:

CREATE INDEX document_idx ON public.document USING GIN(
    to_tsvector(
        'english',
        content || ' ' || COALESCE(title, '')
    )
);

И основной полнотекстовый поисковый запрос:

SELECT * FROM public.document WHERE (
    to_tsvector(
        'english',
        content || ' ' || COALESCE(title, '')
    ) @@ plainto_tsquery('english', fulltext_search_documents.search_text)
)

Независимо от размера таблицы public.document, запрос (вы уже знаете это) очень быстрый! Планировщик использует INDEX, и все работает отлично.

Теперь я познакомлю вас с базовым контролем доступа через RLS (Row Level Security), во-первых, я его включу:

ALTER TABLE public.document ENABLE ROW LEVEL SECURITY;

и затем я добавляю политику:

CREATE POLICY document_policy ON public.document FOR SELECT
    USING (EXISTS (
        SELECT 1 FROM public.user WHERE (is_current_user) AND ('r' = ANY(privileges))
    ));

Для простоты is_current_user - это еще один запрос, который проверяет именно это.

Теперь запрос полнотекстового поиска сглаживается запросом document_policy, и планировщик выполняет сканирование Seq вместо сканирования индекса, что приводит к увеличению скорости запроса в 300 раз!

Я думаю, что вопрос довольно очевиден, как я могу это исправить, чтобы полнотекстовый поисковый запрос оставался быстрым?

Заранее спасибо!

Источник

2 ответа

Решение

Я решил это с момента публикации... Любой, кто сталкивался с этой проблемой, вот как я это сделал:

Моим решением было иметь личное SECURITY DEFINER функция-обертка, содержащая запрос propper и другую публичную функцию, которая вызывает приватную и INNER JOINS таблица, которая требует контроля доступа.

Так что в конкретном случае выше, это будет что-то вроде этого:

CREATE FUNCTION private.filter_document() RETURNS SETOF public.document AS
$$
    SELECT * FROM public.document WHERE (
        to_tsvector(
            'english',
            content || ' ' || COALESCE(title, '')
        ) @@ plainto_tsquery('english', fulltext_search_documents.search_text)
    )
$$
LANGUAGE SQL STABLE SECURITY DEFINER;
----
CREATE FUNCTION public.filter_document() RETURNS SETOF public.document AS
$$
    SELECT filtered_d.* FROM private.filter_documents() AS filtered_d
        INNER JOIN public.document AS d ON (d.id = filtered_d.id)
$$
LANGUAGE SQL STABLE;

Так как я использовал Postgraphile (что очень круто!), Я смог опустить самоанализ частной схемы, сделав недоступной "опасную" функцию! При правильной реализации мер безопасности конечный пользователь увидит только окончательную схему GraphQL, изрядно удалив Postgres из внешнего мира.

Это сработало красиво! До недавнего времени, когда Postgres 10.3 был выпущен и исправлен, отпадает необходимость в этом хакере.

С другой стороны, мои политики RLS очень сложны, вложены и углубляются. Таблицы, к которым они запускаются, также достаточно велики (всего около 50000 записей для запуска RLS). Даже с очень сложными и вложенными политиками мне удалось сохранить производительность в разумных пределах.

При работе с RLS имейте в виду следующее:

  1. Создать правильное INDEXES
  2. Предпочитайте встроенные запросы везде! (Даже если это означает переписывание одного и того же запроса N раз)
  3. Избегайте функций в политике всеми средствами! Если вы обязательно должны иметь их внутри, убедитесь, что они STABLE и иметь высокий COST (как указал @mkurtz); или IMMUTABLE
  4. Извлеките запрос из политики, запустите его напрямую EXPLAIN ANALYZE и попробуйте оптимизировать его как можно больше

Надеюсь, вы, ребята, нашли эту информацию полезной так же, как и я!

Источник

Попробуйте следующее: вместо записи запроса в USING(...) предложение поместить запрос в STABLE функция с очень высокой стоимостью. При этом функция не должна вызываться очень часто сейчас - в идеале, только один раз за время существования запроса, потому что стоимость вызова функции сейчас очень высока для Postgres. Маркировка функции как STABLE сообщает Postgres, что результат функции не изменяется в течение времени жизни одного запроса. Я думаю, что это правильно для вашего запроса, не так ли? Подробнее об этих двух параметрах читайте здесь.

Как это:

CREATE OR REPLACE FUNCTION check_permission () RETURNS BOOLEAN AS $$
    SELECT EXISTS (
        SELECT 1 FROM public.user WHERE (is_current_user) AND ('r' = ANY(privileges))
    )
$$ LANGUAGE SQL STABLE COST 100000;

И политика сейчас:

CREATE POLICY document_policy ON public.document FOR SELECT
    USING (check_permission());

Надеюсь, это даст вам лучшую производительность. Но имейте в виду, это работает правильно, только если все в порядке, чтобы пометить функцию как STABLE, Если ваша функция может возвращать разные результаты в течение одного времени жизни запроса, это не будет работать правильно, и вы получите странные результаты.

Источник
Другие вопросы по тегам