Mam zgłoszenie do Pacjentów i Terapeutów. Wszystkie są w tej samej tabeli users. Pacjenci powinni widzieć swoich terapeutów, a terapeuci powinni widzieć swoich pacjentów.

Skonfigurowałem zmaterializowany widok (user_access_pairs) z parami identyfikatorów użytkowników, jeśli dwóch użytkowników ma wiersz w widoku, oznacza to, że hej powinien mieć do siebie dostęp.

database> \d user_access_pairs
+----------+---------+-------------+
| Column   | Type    | Modifiers   |
|----------+---------+-------------|
| id1      | integer |             |
| id2      | integer |             |
+----------+---------+-------------+
Indexes:
    "index_user_access_pairs" UNIQUE, btree (id1, id2)

Oto definicja tabeli users, zawiera kilka dodatkowych kolumn, które nie powinny być odpowiednie dla tego pytania.

database> \d users
+-----------------------------+-----------------------------+-----------------------------------------------------+
| Column                      | Type                        | Modifiers                                           |
|-----------------------------+-----------------------------+-----------------------------------------------------|
| id                          | integer                     |  not null default nextval('users_id_seq'::regclass) |
| first_name                  | character varying(255)      |                                                     |
| last_name                   | character varying(255)      |                                                     |
+-----------------------------+-----------------------------+-----------------------------------------------------+
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)

Stworzyłem zasadę RLS, która ogranicza to, które users może odczytać kto używa tokena jwt.

create policy select_users_policy
  on public.users
  for select using (
    (current_setting('jwt.claims.user_id'::text, true)::integer, id) in (
      select id1, id2 from user_access_pairs
    )
  );

Wydaje się, że działa to logicznie, ale mam okropną wydajność. Planer zapytań wykonuje sekwencyjne skanowanie user_access_pairs pomimo istnienia tam indeksu.

database> set jwt.claims.user_id to '2222';
database> explain analyze verbose
    select first_name, last_name
    from users
+------------------------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN                                                                                                                         |
|------------------------------------------------------------------------------------------------------------------------------------|
| Seq Scan on public.users  (cost=231.84..547.19 rows=2386 width=14) (actual time=5.481..6.418 rows=2 loops=1)                       |
|   Output: users.first_name, users.last_name                                                                                        |
|   Filter: (hashed SubPlan 1)                                                                                                       |
|   Rows Removed by Filter: 4769                                                                                                     |
|   SubPlan 1                                                                                                                        |
|     ->  Seq Scan on public.user_access_pairs  (cost=0.00..197.67 rows=13667 width=8) (actual time=0.005..1.107 rows=13667 loops=1) |
|           Output: user_access_pairs.id1, user_access_pairs.id2                                                                     |
| Planning Time: 0.072 ms                                                                                                            |
| Execution Time: 6.521 ms                                                                                                           |
+------------------------------------------------------------------------------------------------------------------------------------+

Jeśli jednak przełączę się na rolę super użytkownika, która omija RLS i ręcznie zastosuję ten sam filtr, uzyskam znacznie lepszą wydajność. Czy nie powinno to być to samo?

database> set jwt.claims.user_id to '2222';
database> explain analyze verbose
   select first_name, last_name
   from users
   where (current_setting('jwt.claims.user_id'::text, true)::integer, id) in (
     select id1, id2 from user_access_pairs
   )
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| QUERY PLAN
|---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Nested Loop  (cost=4.59..27.86 rows=2 width=14) (actual time=0.041..0.057 rows=2 loops=1)
|   Output: users.first_name, users.last_name
|   Inner Unique: true
|   ->  Bitmap Heap Scan on public.user_access_pairs  (cost=4.31..11.26 rows=2 width=4) (actual time=0.029..0.036 rows=2 loops=1)
|         Output: user_access_pairs.id1, user_access_pairs.id2
|         Filter: ((current_setting('jwt.claims.user_id'::text, true))::integer = user_access_pairs.id1)
|         Heap Blocks: exact=2
|         ->  Bitmap Index Scan on index_user_access_pairs  (cost=0.00..4.31 rows=2 width=0) (actual time=0.018..0.018 rows=2 loops=1)
|               Index Cond: (user_access_pairs.id1 = (current_setting('jwt.claims.user_id'::text, true))::integer)
|   ->  Index Scan using users_pkey on public.users  (cost=0.28..8.30 rows=1 width=18) (actual time=0.008..0.008 rows=1 loops=2)
|         Output: users.id, users.email, users.encrypted_password, users.first_name, users.last_name, users.roles_mask, users.reset_password_token, users.reset_password_sent_at, users.remember_created_at, users.sign_in_count, users.current_sign_in_at, users.last_sign_in_at,
|         Index Cond: (users.id = user_access_pairs.id2)
| Planning Time: 0.526 ms
| Execution Time: 0.116 ms
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Dlaczego RLS nie używa indeksu podczas tworzenia zapytania?

PS Używam PostgreSQL w wersji 12.4

database> select version()
+-------------------------------------------------------------------------------------------------------------------------------+
| version                                                                                                                       |
|-------------------------------------------------------------------------------------------------------------------------------|
| PostgreSQL 12.4 (Ubuntu 12.4-0ubuntu0.20.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-10ubuntu2) 9.3.0, 64-bit |
+-------------------------------------------------------------------------------------------------------------------------------+

EDYTOWAĆ

Dzięki za odpowiedź Laurenz. To znacznie poprawiło wydajność. Ale nadal otrzymuję kilka skanów sekwencyjnych.

Oto zaktualizowane zasady, zgodnie z sugestią Laurenz.

create policy select_users_policy
  on public.users
  for select using (
    exists (
      select 1
      from user_access_pairs
      where
        id1 = current_setting('jwt.claims.user_id'::text, true)::integer
        and id2 = users.id
    )
  );

Zapytanie o tę tabelę za pomocą RLS nadal daje mi skanowanie sekwencyjne w tabeli users, mimo że zapytanie exists w zasadach używa indeksów.

database> set jwt.claims.user_id to '2222';
database> explain analyze verbose
  select first_name, last_name
  from users
+-------------------------------------------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN                                                                                                                                            |
|-------------------------------------------------------------------------------------------------------------------------------------------------------|
| Seq Scan on public.users  (cost=0.00..40048.81 rows=2394 width=14) (actual time=0.637..1.216 rows=2 loops=1)                                          |
|   Output: users.first_name, users.last_name                                                                                                           |
|   Filter: (alternatives: SubPlan 1 or hashed SubPlan 2)                                                                                               |
|   Rows Removed by Filter: 4785                                                                                                                        |
|   SubPlan 1                                                                                                                                           |
|     ->  Index Only Scan using index_user_access_pairs on public.user_access_pairs  (cost=0.29..8.31 rows=1 width=0) (never executed)                  |
|           Index Cond: ((user_access_pairs.id1 = (current_setting('jwt.claims.user_id'::text, true))::integer) AND (user_access_pairs.id2 = users.id)) |
|           Heap Fetches: 0                                                                                                                             |
|   SubPlan 2                                                                                                                                           |
|     ->  Bitmap Heap Scan on public.user_access_pairs user_access_pairs_1  (cost=4.31..11.26 rows=2 width=4) (actual time=0.075..0.083 rows=2 loops=1) |
|           Output: user_access_pairs_1.id2                                                                                                             |
|           Recheck Cond: (user_access_pairs_1.id1 = (current_setting('jwt.claims.user_id'::text, true))::integer)                                      |
|           Heap Blocks: exact=2                                                                                                                        |
|           ->  Bitmap Index Scan on index_user_access_pairs_on_id1  (cost=0.00..4.31 rows=2 width=0) (actual time=0.064..0.064 rows=2 loops=1)         |
|                 Index Cond: (user_access_pairs_1.id1 = (current_setting('jwt.claims.user_id'::text, true))::integer)                                  |
| Planning Time: 0.572 ms                                                                                                                               |
| Execution Time: 1.295 ms                                                                                                                              |
+-------------------------------------------------------------------------------------------------------------------------------------------------------+

Oto to samo zapytanie wykonane „ręcznie” bez RLS dla porównania. Tym razem nie ma skanowania sekwencyjnego, a wydajność jest znacznie lepsza (szczególnie w przypadku uruchamiania na większych zbiorach danych)

database> set jwt.claims.user_id to '2222';
database> explain analyze verbose
    select first_name, last_name
    from users
    where exists (
       select 1
       from user_access_pairs
       where
         id1 = current_setting('jwt.claims.user_id'::text, true)::integer
         and id2 = users.id
     )

+---------------------------------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN                                                                                                                                  |
|---------------------------------------------------------------------------------------------------------------------------------------------|
| Nested Loop  (cost=4.59..27.86 rows=2 width=14) (actual time=0.020..0.033 rows=2 loops=1)                                                   |
|   Output: users.first_name, users.last_name                                                                                                 |
|   Inner Unique: true                                                                                                                        |
|   ->  Bitmap Heap Scan on public.user_access_pairs  (cost=4.31..11.26 rows=2 width=4) (actual time=0.013..0.016 rows=2 loops=1)             |
|         Output: user_access_pairs.id1, user_access_pairs.id2                                                                                |
|         Recheck Cond: (user_access_pairs.id1 = (current_setting('jwt.claims.user_id'::text, true))::integer)                                |
|         Heap Blocks: exact=2                                                                                                                |
|         ->  Bitmap Index Scan on index_user_access_pairs_on_id1  (cost=0.00..4.31 rows=2 width=0) (actual time=0.010..0.010 rows=2 loops=1) |
|               Index Cond: (user_access_pairs.id1 = (current_setting('jwt.claims.user_id'::text, true))::integer)                            |
|   ->  Index Scan using users_pkey on public.users  (cost=0.28..8.30 rows=1 width=18) (actual time=0.006..0.006 rows=1 loops=2)              |
|         Output: users.id, users.email, users.encrypted_password, users.first_name, users.last_name, users.roles_mask                        |
|         Index Cond: (users.id = user_access_pairs.id2)                                                                                      |
| Planning Time: 0.464 ms                                                                                                                     |
| Execution Time: 0.075 ms                                                                                                                    |
+---------------------------------------------------------------------------------------------------------------------------------------------+

Domyśliłbym się, że planer zapytań potraktuje te dwa zapytania tak samo. Dlaczego się różnią i co można zrobić, aby uniknąć skanowania sekwencyjnego?

5
The Hoff 27 sierpień 2020, 17:07

6 odpowiedzi

Najlepsza odpowiedź

Powodem, dla którego nie widzisz tego samego planu, co pozornie równoważne zapytanie bez zasad RLS, jest to, że pobieranie podzapytań ma miejsce przed uwzględnieniem zasad RLS. To dziwactwo planisty.

Podsumowując, zasady RLS w połączeniu z podzapytaniami niestety nie są ze sobą przyjaciółmi pod względem wydajności.

Dla Twojej informacji podobną manifestację można zobaczyć, porównując następujące dwa zapytania:

SELECT ... FROM my_table WHERE                     EXISTS(SELECT ...);
SELECT ... FROM my_table WHERE CASE WHEN true THEN EXISTS(SELECT ...) END;

W tym przypadku, mimo że oba zapytania są równoważne, drugie zapytanie skutkuje (zahaszowanym) planem podrzędnym podzapytania, ponieważ składanie niepotrzebnego CASE WHEN true jest wykonywane po podciągnięciu podzapytania.

Zastrzeżenie: otrzymałem te informacje od RhodiumToad na IRC #postgresql, ale wyjaśniłem / uprościłem je własnymi słowami.

4
pbillen 8 wrzesień 2020, 17:45

Nie mogę wskazać różnicy, ale myślę, że powinieneś uzyskać lepszy plan z mądrzejszą polityką:

CREATE POLICY select_users_policy ON public.users
  FOR SELECT
  USING (
     EXISTS (SELECT 1 FROM user_access_pairs
             WHERE id1 = current_setting('jwt.claims.user_id'::text, true)
               AND id2 = users.id)
  );

Chciałbym wspomnieć, że opieranie zabezpieczeń na poziomie wiersza na zmiennej zastępczej, którą użytkownik może zmienić w dowolnym momencie, jest wątpliwe.

2
Laurenz Albe 27 sierpień 2020, 14:25

Jedno rozwiązanie (na podstawie w tym poście, który zawiera kilka innych dobrych sugestii i testów porównawczych), aby w ogóle nie używać RLS, ale wbudować filtrowanie w widok:

create view api.allowed_users
with (security_barrier)
as
  select id, first_name, last_name, favorite_color
  from public.users
  join user_access_pairs uap
    on uap.id1 = current_setting('jwt.claims.user_id'::text, true)::integer

Przedstawiłeś już swoją politykę dostępu w widoku user_access_pairs, więc prawdopodobnie reguła RLS tak naprawdę niczego nie dodaje.

(security_barrier ma zapobiec potencjalnemu wyciekowi informacji, ale wiąże się z kosztem wydajności, więc sprawdź, czy jest to konieczne w Twoim przypadku).

1
Felixyz 6 październik 2020, 13:47

Inny użytkownik subZero Slack podzielił się rozwiązaniem opartym na zawinięciu wyszukiwania uprawnień aktualnego użytkownika w funkcję. W twoim przypadku coś takiego:

create policy select_users_policy
  on public.users
  for select using (
    id IN (
      select * from current_user_read_users()
   )
  );

I stworzyłbyś funkcję current_user_read_users(), która wyszukuje user_id z jwt i zwraca zestaw użytkowników, których aktualny użytkownik może przeczytać, na podstawie user_access_pairs.

Może być ważne, ale nie musi, to, że ta funkcja ma tego samego właściciela co widok user_access_pairs lub że funkcja jest zadeklarowana z SECURITY DEFINER (tak, że omija RLS). Może się zdarzyć, że ważną częścią jest po prostu wyciągnięcie podzapytania do funkcji (w jakiś sposób pomagając optymalizatorowi), ale inne rzeczy są zgłaszane jako pomoc w innych problemach z wydajnością.

Na koniec możesz poeksperymentować z umieszczeniem tego w widoku api, tak jak w innym rozwiązaniu I zgłoszone.

Jedno zastrzeżenie:

istnieje cykliczny problem z zależnościami w samej tabeli uprawnień, więc musiałem zrobić jedną zasadę dotyczącą specjalnego przypadku. Ten nie miał jednak żadnych problemów z wydajnością, więc było w porządku.

(Zauważ, że w ich przypadku uprawnienia były przechowywane w tabeli , edytowalnej przez administratorów, a nie generowane jak w twoim przypadku).

1
Felixyz 6 październik 2020, 13:45

Nie podano tego w pytaniu, ale zakładam, że odczyt z public.users jest wyzwalany z innego schematu z interfejsem API (nazwijmy go api).

Jedna osoba na subZero Slack udostępniła:

Napotkałem ten sam problem i zdefiniowałem RLS na podstawie moich widoków api, które rozwiązały problem skanowania sekwencyjnego. Jednak wprowadzanie zmian w tych widokach jest trochę uciążliwe, ponieważ podczas migracji muszę najpierw porzucić zasady RLS, zmienić widok, a następnie ponownie utworzyć zasady. ... Kiedy w RLS są zaangażowane podzapytania, używam widoków api.

Tak więc używają dokładnie tej samej reguły, ale odwołują się do tabel api.foo i api.bar views instead of public.foo and public.bar`.

W Twoim przypadku możesz spróbować:

create policy select_users_policy
  on public.users
  for select using (
    exists (
      select 1
      from api.user_access_pairs
      where
        id1 = current_setting('jwt.claims.user_id'::text, true)::integer
        and id2 = api.users.id
    )
  );

Zakładamy więc, że masz users widok w schemacie api dublujący schemat public.users i przenosisz user_access_pairs również do api (lub utwórz widok odwołujący się do niego ).

Nie jest dla mnie jasne, czy to działa, ponieważ zapytanie jest wyzwalane z widoku / funkcji w schemacie api w pierwszej kolejności, a więc odwoływanie się do widoków w tym schemacie jest mniej mylące dla optymalizatora zapytań, czy też to tylko sztuczka, która powoduje uruchomienie optymalizatora, niezależnie od pochodzenia zapytania. (Moim zdaniem to drugie wydaje się nieco bardziej prawdopodobne, ale kto wie.)

1
Felixyz 6 październik 2020, 13:18

Autor ten komentarz pojawił się (metodą prób i błędów) z rozwiązaniem rzutowania podzapytania do ARRAY. Wcale nie jestem pewien, czy ma to zastosowanie w twoim przypadku, ale po prostu pokazuje, że całkiem nieoczekiwane sztuczki mogą najwyraźniej przestraszyć optymalizator do wykonania swojej pracy.

Więc możesz spróbować:

create policy select_users_policy
on public.users
for select using (
  users.id = any (
    array(
        select id1
        from user_access_pairs
        where 
            id1 = current_setting('jwt.claims.user_id'::text, true)::integer
            and id2 = users.id
        )
    )
);

Dość niezręcznie, ale kto wie ...

1
Felixyz 28 wrzesień 2020, 19:58