Авторизация в PostgreSQL. Часть 2. Безопасность на уровне строк

Приветствую вас в очередном разборе инструментов авторизации 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 не применяются. В этом случае никакие строки не будут видны и затронуты запросом.