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

• Роли и привилегии;
• Безопасность на уровне строк (мы сейчас здесь);
• Производительность безопасности на уровне строк (coming soon!);

В первой статье мы рассмотрели, как роли и предоставленные привилегии влияют на действия (запросы SELECT, INSERT, UPDATE и DELETE) в отношении объектов БД (таблиц, представлений и функций). Та статья закончилась небольшим клиффхэнгером: если вы создадите многопользовательское приложение, используя только роли и привилегии для авторизации, то ваши пользователи смогут удалять данные друг друга, а может и вообще друг друга. Необходим другой механизм, позволяющий ограничить пользователей чтением и изменением только собственных данных — механизм безопасности на уровне строк (RLS).

Практика

Лучший способ понять RLS — опробовать его. Будем использовать схему базы данных, роли и привилегии из предыдущей статьи (с добавлением новой таблицы «songs») — мы моделируем пример приложения, похожего на Bandcamp, где музыканты могут публиковать альбомы и песни, а слушатели могут находить исполнителей и следить за ними.


Пример схемы нашей БД. Вы можете загрузить эту схему по этой ссылке и использовать её с помощью Docker.

В Docker вы можете выполнить приведенную ниже команду, которая использует официальный образ Postgres Docker для локального запуска базы данных PostgreSQL. При первом подключении тома будет загружен файл schema.sql, который заполнит базу данных таблицами, показанными на схеме выше.

docker run --name=postgres \
    --rm \
    --volume=$(pwd)/schema.sql:/docker-entrypoint-initdb.d/schema.sql \
    --volume=$(pwd):/repo \
    --env=PSQLRC=/repo/.psqlrc \
    --env=POSTGRES_PASSWORD=foo \
    postgres:latest -c log_statement=all

Чтобы открыть консоль psql, выполните эту команду в другом терминале:

docker exec --interactive --tty postgres \
    psql --username=postgres

RLS

Что такое безопасность на уровне строк? Это способ ограничить количество видимых для запроса строк таблицы. Обычно, если вы выполните запрос SELECT * FROM mytable, то PostgreSQL вернет все столбцы и строки из этой таблицы. Но если в таблице включена RLS, то PostgreSQL не вернет никаких строк (в случае, если запрос выполнен не суперпользователем, владельцем таблицы или обладателем опции BYPASSRLS).

Основные политики RLS

Для того чтобы возвращать строки из таблицы с RLS, необходимо написать соответствующую политику безопасности. В политику входит выражение SQL (с логическим значением на выходе), которое вычисляется для каждой строки в таблице. По нему определяется, какие именно строки доступны пользователю, выполнившему запрос. Политики могут применяться как к определенным ролям, так и к определенным командам (как SELECT или INSERT, …).

Давайте для примера добавим некоторые данные в нашу БД и включим RLS в таблице:

-- Add 3 musical artists
=# INSERT INTO artists (name)
     VALUES ('Tupper Ware Remix Party'), ('Steely Dan'), ('Missy Elliott');

-- Switch to the artist role (so we're not querying from a superuser role, which
-- bypasses RLS)
=# SET ROLE artist;

=> SELECT * FROM artists;
--  artist_id |     name      
-- -----------+---------------
--          1 | Tupper Ware Remix Party
--          2 | Steely Dan
--          3 | Missy Elliott
-- (3 rows)

-- Switch to the postgres superuser to enable RLS on the artists table
=> RESET ROLE;
=# ALTER TABLE artists ENABLE ROW LEVEL SECURITY;

-- Now we don't see any rows! RLS hides all rows if no policies are declared on
-- the table.
=# SET ROLE artist;
=> SELECT * FROM artists;
--  artist_id | name 
-- -----------+------
-- (0 rows)

Теперь, добавим пару основных политик RLS:

-- Let's create a simple RLS policy that applies to all roles and commands and
-- allows access to all rows.
=> RESET ROLE;
=# CREATE POLICY testing ON artists
    USING (true);

-- The expression "true" is true for all rows, so all rows are visible.
=# SET ROLE artist;
=> SELECT * FROM artists;
--  artist_id |          name
-- -----------+-------------------------
--          1 | Tupper Ware Remix Party
--          2 | Steely Dan
--          3 | Missy Elliott
-- (3 rows)

-- Let's change the policy to use an expression that depends on a value in the
-- row.
=> RESET ROLE;
=# ALTER POLICY testing ON artists
    USING (name = 'Steely Dan');

-- Now, we see that only 1 row passes the policy's test.
=# SET ROLE artist;
=> SELECT * FROM artists;
--  artist_id |    name
-- -----------+------------
--          2 | Steely Dan
-- (1 row)

Политики, основанные на пользователе

Предположим довольно реалистичную ситуацию: мы хотим, чтобы исполнители могли изменять свои имена, но не могли менять чужие. Для этого нам нужно знать идентификатор исполнителя, который делает запрос — выдача результата исходя из общей роли «artist» (как в примерах выше) не даёт нам достаточное количество информации. Один из способов идентифицировать определенную личность в роли/группе «artist» — создать другую роль БД и сделать её членом роли «artist»:

=> RESET ROLE;
-- Create a login/role for a specific artist. We'll design the role name to be
-- "artist:N" where N is the artist_id. So, "artist:1" will be the account for
-- Tupper Ware Remix Party.
-- NOTE: We have to quote the role name because it contains a colon.
=# CREATE ROLE "artist:1" LOGIN;
=# GRANT artist TO "artist:1";

Теперь, если вы войдете в БД как «artist:1» то у вас будут те же привилегии, что и у роли «artist». Применяя определенные single-user логины (и соответствующие им имена пользователей), мы можем написать политику, которая использует имя пользователя для определения того, какие именно строки в БД принадлежат ему.

-- Let's make all artists visible to all users again
=# DROP POLICY testing;
=# CREATE POLICY viewable_by_all ON artists
    FOR SELECT
    USING (true);

-- We create an RLS policy specific to the "artist" role/group and the UPDATE
-- command. The policy makes rows from the "artists" table available if the
-- row's artist_id matches the number in the current user's name (i.e.
-- a db role name of "artist:1" makes the row with artist_id=1 available).
=# CREATE POLICY update_self ON artists
    FOR UPDATE
    TO artist
    USING (artist_id = substr(current_user, 8)::int);

=# SET ROLE "artist:1";
-- Even though we try to update the name for all artists in the table, the RLS
-- policy limits our update to only the row we "own" (i.e. that has an artist_id
-- matching our db role name).
=> UPDATE artists SET name = 'TWRP';
-- UPDATE 1
=> SELECT * FROM artists;
--  artist_id |     name
-- -----------+---------------
--          2 | Steely Dan
--          3 | Missy Elliott
--          1 | TWRP
-- (3 rows)

-- Trying to update a row that no policy gives us access to simply results in no
-- rows updating.
=> UPDATE artists SET name = 'Ella Fitzgerald' WHERE name = 'Steely Dan';
-- UPDATE 0

Мы успешно внедрили разрешения, позволяющие исполнителю обновлять только своё имя. В этом примере также использовались:

• ограничение политики для конкретной команды (например, SELECT, UPDATE);
• ограничение политики для определенной роли (например, artist);

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

???? Если вы заметили выражение artist_id = substr(current_user, 8)::int и нахмурились, то это хорошо! Этот пример был написан для более простого понимания, однако в реальном приложении вам вряд ли бы захотелось использовать имена пользователей, представляющие собой объединение имени роли/группы в строку с идентификатором. Из-за тесно связанных фрагментов данных потребуются дополнительные операции со строками для их извлечения! Существуют различные способы разработки имен пользователей БД и привязки их к политикам RLS, которые, возможно, будут описаны в будущих статьях.

Политики и таблицы

Теперь давайте сделаем так, чтобы исполнители могли создавать/редактировать/удалять свои альбомы и песни в этих альбомах. Выражение USING в политиках RLS может содержать любое выражение SQL, поэтому мы можем использовать отношения foreign-key для решения этой задачи.

=> RESET ROLE;
-- Enable RLS on albums and songs, and make them viewable by everyone.
=# ALTER TABLE albums ENABLE ROW LEVEL SECURITY;
=# ALTER TABLE songs ENABLE ROW LEVEL SECURITY;
=# CREATE POLICY viewable_by_all ON albums
    FOR SELECT
    USING (true);
=# CREATE POLICY viewable_by_all ON songs
    FOR SELECT
    USING (true);

-- Limit create/edit/delete of albums to the "owning" artist.
=# CREATE POLICY affect_own_albums ON albums
    FOR ALL
    TO artist
    USING (artist_id = substr(current_user, 8)::int);
-- Limit create/edit/delete of songs to the "owning" artist of the album.
=# CREATE POLICY affect_own_songs ON songs
    FOR ALL
    TO artist
    USING (
                EXISTS (
            SELECT 1 FROM albums
            WHERE albums.album_id = songs.album_id
        )
        );

-- Add a Missy Elliott (artist_id=3) album (album_id=1) for testing below
=# INSERT INTO albums (artist_id, title, released)
    VALUES (3, 'Under Construction', '2002-11-12');

-- Change to the user account corresponding to the artist TWRP (artist_id=1)
=# SET ROLE "artist:1";
-- Add an album and a song to that album
=> INSERT INTO albums (artist_id, title, released)
    VALUES (1, 'Return to Wherever', '2019-07-11');
=> INSERT INTO songs (album_id, title)
    VALUES (2, 'Hidden Potential');

-- Trying to add an album to another artist fails the RLS policy
=> INSERT INTO albums (artist_id, title, released)
    VALUES (2, 'Pretzel Logic', '1974-02-20');
-- ERROR:  42501: new row violates row-level security policy for table "albums"
-- LOCATION:  ExecWithCheckOptions, execMain.c:2058

-- Trying to add a song to Missy Elliott's album fails the RLS policy
=> INSERT INTO songs (album_id, title)
    VALUES (1, 'Work It');
-- ERROR:  42501: new row violates row-level security policy for table "songs"
-- LOCATION:  ExecWithCheckOptions, execMain.c:2058

Примечательной частью нашего примера является политики RLS в отношении таблицы «songs». Когда мы выполняем команду INSERT, UPDATE или DELETE для этой таблицы, политика RLS гарантирует, что мы можем вставлять, изменять или удалять песни только в альбоме исполнителя, отправившего запрос.

Мы использовали подзапрос EXISTS для выражения USING в приведенной выше политике таблицы «songs», но существует множество способов реализации этого разрешения. Какой способ наиболее эффективен? С этим вопросом мы разберемся в следующей статье «Производительность RLS».

Взаимодействие нескольких политик

Мы уже знаем, что политика может применяться:

• ко всем или конкретным командам;
• ко всем или конкретным пользователям;

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

• таблица может иметь несколько политик;
• политика может обращаться к другой таблице с собственной политикой:

Таблицы с несколькими политиками

Если таблица имеет несколько политик, то необходимо обратить внимание, объявлены ли эти политики как PERMISSIVE (по умолчанию) или RESTRICTIVE. Для доступа к любым строкам в таблице должна существовать PERMISSIVE политика. Если существует несколько PERMISSIVE политик, строка доступна, если какая-либо PERMISSIVE политика имеет значение true. С другой стороны, все RESTRICTIVE политики должны иметь значение true, чтобы строка была доступна. Изучим это, добавив новую функцию в наше приложение — мы хотим разрешить исполнителям создавать альбомы с датой выпуска в будущем, но только исполнитель должен видеть свои еще не выпущенные альбомы.

=> RESET ROLE;
-- Reminder: We previously created a viewable_by_all policy on albums that shows
-- all rows to SELECT queries issued by all roles. We re-create that policy here
-- for reference:
=# DROP POLICY viewable_by_all ON albums;
=# CREATE POLICY viewable_by_all ON albums
    FOR SELECT
    USING (true);

-- For fans: restrict visibility to albums with a release date in the past.
=# CREATE POLICY hide_unreleased_from_fans ON albums
    AS RESTRICTIVE
    FOR SELECT
    TO fan
    USING (released <= now());

-- For artists: restrict visibility to albums with a release date in the past,
-- unless the role issuing the query is the owning artist.
=# CREATE POLICY hide_unreleased_from_other_artists ON albums
    AS RESTRICTIVE
    FOR SELECT
    TO artist
    USING (released <= now() or (artist_id = substr(current_user, 8)::int);

Комбинируя PERMISSIVE и RESTRICTIVE политики, ориентированные на разные роли (fans и artists), мы сделали альбомы будущих релизов видимыми только для их исполнителя. Возможно, лучший способ продемонстрировать эту логику — это использовать только PERMISSIVE политики следующим образом:

-- Alternate implementation using only PERMISSIVE (rather than RESTRICTIVE)
-- policies.
=# DROP POLICY viewable_by_all ON albums;
=# DROP POLICY hide_unreleased_from_fans ON albums;
=# DROP POLICY hide_unreleased_from_other_artists ON albums;
=# CREATE POLICY viewable_by_all ON albums
    FOR SELECT
    USING (released <= now());

-- Reminder: We previously created an affect_own_albums policy on albums that
-- already allows the artist to see their own albums. We re-create that policy
-- here for reference:
=# DROP POLICY affect_own_albums ON albums;
=# CREATE POLICY affect_own_albums ON albums
    -- FOR ALL
    TO artist
    USING (artist_id = substr(current_user, 8)::int);

Теперь политика viewable_by_all позволяет всем пользователям видеть уже выпущенные альбомы, а политика affect_own_albums позволяет исполнителям делать что угодно (SELECT, INSERT и т.д.) с альбомами, которыми они владеют.

Запросы к другим таблицам

Другой способ взаимодействия нескольких политик — использование в разделе USING запроса к другой таблицы с собственной политикой. В нашем примере мы можем использовать политику в таблице «albums», чтобы определить, должны ли песни в этом альбоме быть видимыми:

-- To control visibility of songs, we simply query for the corresponding album
-- and the RLS policy on the albums table will determine if we can see the
-- album. If we see the album, we'll see the songs.
=# DROP POLICY viewable_by_all ON songs;
=# CREATE POLICY viewable_by_all ON songs
    FOR SELECT
    USING (
        EXISTS (
            SELECT 1 FROM albums
            WHERE albums.album_id = songs.album_id
        )
    );

Теперь давайте протестируем наши политики RLS, чтобы убедиться, что корректные роли/группы видят (или не могут видеть) ещё не выпущенные альбомы и их песни.

-- Create another artist role for testing
=# CREATE ROLE "artist:2";
=# GRANT artist TO "artist:2";

-- Test that the owning artist (artist:1) can see future albums and songs, but
-- other artists and fans cannot see them.
=# SET ROLE "artist:1";
=> SELECT * FROM albums;
--  album_id | artist_id |       title        |  released  
-- ----------+-----------+--------------------+------------
--         1 |         3 | Under Construction | 2002-11-12
--         2 |         1 | Return to Wherever | 2019-07-11
--         4 |         1 | Future Album       | 2050-01-01
-- (3 rows)

=> SELECT * FROM songs;
--  song_id | album_id |      title       
-- ---------+----------+------------------
--        1 |        2 | Hidden Potential
--        3 |        4 | Future Song 1
-- (2 rows)

=> SET ROLE fan;
=> SELECT * FROM albums;
--  album_id | artist_id |       title        |  released
-- ----------+-----------+--------------------+------------
--         1 |         3 | Under Construction | 2002-11-12
--         2 |         1 | Return to Wherever | 2019-07-11
-- (2 rows)

=> SELECT * FROM songs;
--  song_id | album_id |      title
-- ---------+----------+------------------
--        1 |        2 | Hidden Potential
-- (1 row)

=> SET ROLE "artist:2";
=> SELECT * FROM albums;
--  album_id | artist_id |       title        |  released
-- ----------+-----------+--------------------+------------
--         1 |         3 | Under Construction | 2002-11-12
--         2 |         1 | Return to Wherever | 2019-07-11
-- (2 rows)

=> SELECT * FROM songs;
--  song_id | album_id |      title
-- ---------+----------+------------------
--        1 |        2 | Hidden Potential
-- (1 row)

Успех! На этом моменте мы завершим обзор RLS в PostgreSQL; еще много чего нужно рассказать (смотрите документацию по политикам RLS и команде CREATE POLICY), но основные моменты безопасности на уровне строк были освещены. До сих пор я оставлял без внимания важный вопрос: каким образом использование политик RLS влияет на производительность, особенно при наличии сложных выражений USING (например, запросы к другим таблицам, использование объединений, вызов функций)? Мы углубимся в этот вопрос в следующей статье!