PostgreSQL игнорирует индексы GIN pg_trgm, если включена защита на уровне строк и используется оператор NOT LEAKPROOF

Question

Прежде всего, этот вопрос SO описывает аналогичную проблему: запрос PostgreSQL не использует INDEX, когда RLS (Row Level Security) включен, но я не смог успешно использовать его предложения, а также хотел бы посмотреть, есть ли способ улучшить вещи в контексте Postgraphile.

Этапы воспроизведения:

Как суперпользователь создайте простую таблицу и заполните ее случайными данными:

CREATE TABLE public.videos AS SELECT id, md5(random()::text) AS title from generate_Series(1,1000000) id;

Выполните запрос ILIKE (далее в этом посте он называется "запрос ILIKE" и используется для многократного тестирования производительности):

EXPLAIN ANALYSE SELECT COUNT(*) FROM public.videos WHERE title ILIKE '%test%';

Как и ожидалось, он выполняет Seq Scan со временем выполнения около 194,823 мс.

Установите расширение gp_trgm и добавьте индекс джина:

CREATE EXTENSION IF NOT EXISTS pg_trgm WITH SCHEMA public;
CREATE INDEX trgm_idx_videos_title ON public.videos USING gin (title gin_trgm_ops);

Поскольку таблица уже заполнена данными, для создания индекса потребуется некоторое время (~10 секунд). Теперь при выполнении того же запроса ILIKE будет использоваться "сканирование растрового индекса на trgm_idx_videos_title" со временем выполнения 0,036 мс.

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

Давайте настроим дополнительного пользователя и предоставим ему доступ для подключения к нашей базе данных (с именем gin_rls_test)

CREATE ROLE db_login WITH LOGIN PASSWORD 'db_login_pwd' NOINHERIT;
GRANT CONNECT ON DATABASE gin_rls_test TO db_login;

Нам также необходимо предоставить права SELECT для ранее созданной таблицы для указанного пользователя.

GRANT SELECT ON public.videos TO db_login;

Чтобы убедиться, что наш пользователь может запрашивать данные так же, как мы это делали до сих пор, подключитесь к серверу базы данных с помощью db_login. (В pgAdmin вы просто создаете новый сервер, указываете другое имя, то же имя хоста, но используете db_login/db_login_pwd в качестве имени пользователя и пароля)

Если вы перейдете к нашей таблице, используя недавно добавленное соединение db_login, откройте инструмент запроса и выполните тот же запрос ILIKE - результаты должны быть такими же, индекс будет применен.

Что ломает это, так это RLS (Row Level Security). Давайте вернемся к редактору запросов для суперпользователя и настроим его для нашей таблицы:

ALTER TABLE public.videos ENABLE ROW LEVEL SECURITY;

CREATE OR REPLACE FUNCTION public.user_has_permission() returns boolean LANGUAGE plpgsql as $$
BEGIN
   return true;
END;
$$;

CREATE POLICY videos_authorization ON public.videos FOR SELECT USING (public.user_has_permission());

Для простоты функция public.user_has_permission() просто возвращает true. (в моем случае это функция plpgsql, которая проверяет разрешения для настроек, хранящихся в pg_catalog.current_setting, и не выполняет никаких дополнительных явных запросов.)

Теперь, если вы запустите запрос ILIKE из редактора запросов суперпользователя, он по-прежнему будет супербыстрым, как и раньше, потому что суперпользователь избегает rls. Если вы запустите его из редактора запросов db_login, индекс больше не будет попадать, будет использоваться Seq Scan и время выполнения будет около 1013,485 мс.

После прочтения этой ветки ( https://www.postgresql.org/message-id/CAGrP7a3PwDYJhPe53yE6pBPPNxk2Ve4n%2BdPQMS1HcBU6swXYfA%40mail.gmail.com), похоже, проблема возникает из-за того, что основная функция защиты от утечек для ILIKE. EXPLAIN ANALYZE сообщает нам, что мы используем оператор ~~* для сравнения текста, выполнение этого запроса позволит вам узнать имена основных функций для этого оператора:

SELECT * FROM pg_operator WHERE oprname = '~~*';

Фактически есть 3 результата, в нашем случае имя базовой функции - "texticlike". Что вы можете сделать, так это переключиться в редактор запросов суперпользователя и сделать эту функцию герметичной:

ALTER FUNCTION texticlike LEAKPROOF;

Теперь, если вы снова запустите запрос ILIKE из редактора запросов db_login, произойдет срабатывание индекса и время выполнения вернется к 0,040 мс.

Проблема:

Возможно, сделать определенные операторы явной защитой от утечек приемлемо, но настоящая проблема (помимо случайной утечки чего-либо, например, при возникновении исключения) состоит в том, что только суперпользователь может сделать функцию защищенной от утечек. Если у вас есть база данных, размещенная в Azure или AWE, у вас не будет доступа суперпользователя, и вы получите следующую ошибку при попытке сделать функцию защищенной от утечек:

ERROR:  only superuser can define a leakproof function
SQL state: 42501

Поэтому для меня все еще остается без ответа вопрос о том, как заставить индекс GIN работать для запросов ILIKE в таблицах с включенным RLS. Или что было бы альтернативой для достижения тех же результатов производительности, сохраняя при этом свойство title в качестве текстового типа?

Я работаю с Postgraphile, и меня интересует улучшение производительности фильтров "includesInsensitive" и "startWithInsensitive", и герметичность операторов ILIKE также влияет на эти фильтры.

Дополнительная информация:

Если вы хотите переключить функцию обратно без УТЕЧКИ:

ALTER FUNCTION texticlike NOT LEAKPROOF;

Я попытался использовать этот запрос, чтобы найти все доступные герметичные операторы, но не нашел среди них каких-либо жизнеспособных альтернатив (ближайшая функция - "start_with" для оператора ^@, которая чувствительна к регистру):

select pg_proc.proname, pg_operator.oprname, pg_operator.oprcode, pg_proc.proleakproof from pg_proc
join pg_operator ON pg_proc.proname::text = pg_operator.oprcode::text
where pg_proc.proleakproof;

Оригинальная идея использования индекса gp_trgm gin пришла из этой статьи: https://niallburkley.com/blog/index-columns-for-like-in-postgres/

Версия PostgreSQL (с использованием SELECT version();) - "PostgreSQL 12.2 на x86_64-pc-linux-musl, скомпилирован gcc (Alpine 9.2.0) 9.2.0, 64-бит"

Answer 1

Вы можете попробовать использовать представления без барьера безопасности и реплицировать предикат RLS непосредственно в представление:

create view view_video as
select * from videos
 where user_has_permission(); -- a predicate from RLS

И запрашивать данные в представлении без ущерба для скорости. Поскольку запрос представления преобразуется в запрос к обычной таблице, будут использоваться все индексы.