[Перевод] Авторизация в PostgreSQL. Часть 1 — Роли и Привилегии
Никто не будет спорить с тем, как важно понимать механизмы прав доступа и безопасности в базах данных. Если вы не продумываете логику авторизации в вашей БД, то, вероятно, вы не следуете принципу наименьших привилегий — к вашей базе данных могут получить доступ коллеги (например, разработчики, аналитики данных, маркетологи, бухгалтеры), подрядчики, процессы непрерывной интеграции или развернутые службы, которые имеют больше привилегий, чем должны. Это увеличивает риск утечек, неправомерного доступа к данным (например, личной информации), а также случайного или злонамеренного повреждения и потери данных.
Несмотря на важность темы, авторизация в базе данных являлась моим слабым местом в начале карьеры. NoSQL был самым крутым парнем на районе, а мир веб-разработки соблазняли фреймворки (например Rails), которые давали более приятный опыт разработки, нежели сложные SQL-скрипты. Но мир меняется. SQL и реляционные базы данных снова оказались в центре внимания, поэтому важно научиться пользоваться ими безопасно и эффективно. В этой серии статей я раскрою основные области авторизации в базах данных с акцентом на PostgreSQL, поскольку это одна из самых зрелых и функциональных СУБД с открытым исходным кодом. Мы рассмотрим следующие темы:
- Роли и Привилегии (в этой статье).
- Безопасность на уровне строк.
- Производительность безопасности на уровне строк.
Почти вся информация из этой статьи содержится в документации PostgreSQL. Однако она может немного смутить своей ошеломляющей детализацией и справочной организацией. Я постараюсь сделать более упрощенное руководство, чтобы вы смогли быстро получить знания, необходимые для создания проекта с использованием авторизации PostgreSQL.
Зачем нужна авторизация в PostgreSQL
Прежде чем углубиться в вопрос как, сделаем шаг назад и зададимся вопросом, зачем использовать авторизацию в PostgreSQL.
Для любого приложения или веб-сайта, где пользователи проходят аутентификацию и имеют доступ к различному контенту или могут выполнять отличные друг от друга действия, вам необходима авторизация. При использовании фреймворков, таких, как Rails и Django, документация и комьюнити, как правило, советуют следующее: использовать одного пользователя БД с правами суперпользователя (или правами произвольного чтения/записи). Затем авторизация реализуется в виде логики и правил в кодовой базе Rails/Django. Если вы добавляете смежные службы, которые работают с одними и теми же данными (например, очереди, background workers, cronjobs, хранилища данных), вам может потребоваться дублировать некоторую логику авторизации в этих службах, или эти службы могут использовать логику авторизации через разделяемые библиотеки или прямое включение в их код (что усложняет разработку, развертывание, продумывание архитектуры и безопасности). Кроме того, если различные службы получают доступ к БД через учетную запись суперпользователя, поле для атак и ошибок, повреждающих данные, значительно шире.
Реализация авторизации в PostgreSQL позволяет определять правила доступа с одном месте (в базе данных), и эти правила буду последовательно применяться ко всем службам и приложениям, которые обращаются к данным в БД. Изучение и использование инструментов авторизации в базе данных естественным образом побуждает использовать отдельные роли с минимальными привилегиями, что ограничивает масштабы и серьезность атак и ошибок.
Еще одно преимущество использования PostgreSQL для авторизации: это мощный, хорошо протестированный инструмент, которые вы уже используете. Вам не нужно самостоятельно внедрять механизмы авторизации (подверженные ошибкам и отнимающие много времени) или заморачиваться с аудитом, интеграцией и обновлениями сторонних библиотек. Все эти проблемы справедливы и для PostgreSQL, однако PostgreSQL превосходит любую библиотеку авторизации с точки зрения стабильности, поддержки и безопасности.
Несмотря на приведенные выше аргументы, использование авторизации PostgreSQL подходит не для всех и каждого проекта! Если у вас простое приложение с ограниченной областью действия или у вас аллергия на SQL или его (относительно примитивный) инструментарий, реализовать авторизацию в одном месте на удобном для вас языке, скорее всего, будет быстрее и проще. Если вам нужна авторизация, которая охватывает множество источников данных и сервисов, вам может понадобиться что-то вроде Zanzibar. Создание авторизации с помощью PostgreSQL — это не панацея, но подход, который стоит рассмотреть в контексте вашего конкретного проекта и команды.
Тестовая схема
Теперь, когда мы понимаем, почему мы можем захотеть использовать PostgreSQL для авторизации, рассмотрим, как нам это реализовать. В качестве примера возьмем приложение, похожие на Bandcamp, где музыкальные исполнители могут публиковать альбомы, а фанаты — находить исполнителей и следить за ними. Возьмем схему «музыкальные исполнители и альбомы» из предыдущей статьи о загрузке текстовых данных в PostgreSQL и немного скорректируем ее, добавив другой тип пользователя (фанаты) и удалив таблицы с «жанрами», чтобы сохранить схему простой и доступной.
Пример схемы с музыкальным исполнителям, альбомам и подписчикам (фанатам).
Вы можете повторять действия, используя Doker и схему: https://gitlab.com/tangram-vision/oss/tangram-visions-blog/-/tree/main/2021.12.13_PostgreSQLAuthorizationRolesAndGrants
Как написано в README репозитория, вы можете выполнить приведенную ниже команду, которая использует официальный образ 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
Роли
Первый уровень любого проекта авторизации в PostgreSQL — это роли. Роли базы данных могут представлять пользователей и/или группы. Поначалу в PostgreSQL обычно одна роль суперпользователя с именем «postgres».
Давайте запустим обе docker-команды из предыдущей части в отдельных окнах терминала, чтобы запустить базу данных PostgreSQL в контейнере и подключиться к ней с помощью psql. Обратите внимание, что в команде «docker exec» мы подключаемся с юзернеймом «postgres». Чтобы посмотреть роли, существующие в бд, запустите \du:
-- SQL comments (like this one) start with 2 hyphens (--).
-- I'll represent the psql prompt as
-- =# (when the current role is a superuser, i.e. «postgres») or
-- => (when the current role is not a superuser).
-- This corresponds to a psql PROMPT1 setting of '%R%# '. For more info about
-- psql prompts, see https://www.postgresql.org/docs/current/app-psql.html.
=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
Увидеть роль, которую мы используем:
=# SELECT current_user, session_user;
current_user | session_user
--------------+--------------
postgres | postgres
Когда вы тестируете привилегии и политики, вы, вероятно, будете часто менять роли. К сожалению, насколько я знаю, в приглашении командной строки psql нельзя настроить показ «current_user» (только «session_user»). Вы можете упростить выполнение приведенного выше запроса, установив псевдоним в файле .psqlrc, например \set whoami 'SELECT current_user, session_user;'. Затем вам нужно всего лишь ввести :whoami в psql, чтобы выполнить запрос. Спасибо этому ответу с StackOverflow от wjv за идею. Дополнительные советы по psqlrc можно найти в этой статье.
«session_user» обычно будет пользователем, от имени которого вы подключились к базе данных (хотя его можно изменить, установив SET SESSION AUTHORIZATION). «current_user» — это пользователь, от имени которого вы действуете, — это пользователь, который будет проверяться при оценке привилегий и политик. current_user изменяется с помощью SET ROLE и RESET ROLE (или запуска функций SECURITY DEFINER, но об этом в другой раз).
Давайте создадим роли «artist» и «fan» для нашего примера и попрактикуемся в смене ролей:
=# CREATE ROLE fan LOGIN;
=# CREATE ROLE artist LOGIN;
-- Running \du will now show 3 users
=# SET ROLE fan;
=> SELECT current_user, session_user;
current_user | session_user
--------------+--------------
fan | postgres
=> RESET ROLE;
=# SELECT current_user, session_user;
current_user | session_user
--------------+--------------
postgres | postgres
-- We can run the psql connect command (\c) to connect as a different user
=# \c — artist
You are now connected to database «postgres» as user «artist».
=> SELECT current_user, session_user;
current_user | session_user
--------------+--------------
artist | artist
=> SET ROLE fan;
ERROR: 42501: permission denied to set role «fan»
Из приведенного выше видно, что не суперпользователи («artist») не могут изменять свою роль. Исключением является случай, когда роль предоставляется другой роли, и именно так роли в БД могут действовать как группы — роли «пользователя» может быть предоставлена роль «группы», тем самым пользователь получает привилегии группы. Мы не будем углубляться в использование ролей в качестве групп, вся необходимая информация есть в документации PostgreSQL.
Из-под роли «artist» посмотрим, какие существуют исполнители:
=> SET ROLE artist;
=> SELECT * from artists;
ERROR: 42501: permission denied for table artists
Хотя у нас роль «исполнитель», мы еще ничего не сделали, чтобы предоставить нашей роли доступ к таблице исполнителей в базе данных. Для этого нужны…
Привилегии
Права доступа на работу с объектами базы данных контролируются привилегиями, управляемыми с помощью команд GRANT и REVOKE. Мы можем проверить привилегии в psql с помощью команды \dp:
=> \dp
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+-----------------------+----------+-------------------+-------------------+----------
public | albums | table | | |
public | albums_album_id_seq | sequence | | |
public | artists | table | | |
public | artists_artist_id_seq | sequence | | |
public | fan_follows | table | | |
public | fans | table | | |
public | fans_fan_id_seq | sequence | | |
В столбцах привилегий ничего нет, поэтому над этими объектами базы данных (таблицами и последовательностями) не допускаются никакие действия, если они не выполняются суперпользователем или владельцем объекта базы данных. Перечисляя таблицы с помощью \dt, мы видим, что «postgres» является владельцем всех таблиц:
=> \dt
List of relations
Schema | Name | Type | Owner
--------+-------------+-------+----------
public | albums | table | postgres
public | artists | table | postgres
public | fan_follows | table | postgres
public | fans | table | postgres
Давайте разрешим роли «artist» делать выборку из таблицы с исполнителями, а затем снова проверим привилегии:
-- Reconnect as the postgres superuser
=> \c — postgres
You are now connected to database «postgres» as user «postgres».
=# GRANT SELECT ON artists TO artist;
=# SET ROLE artist;
=> SELECT * FROM artists;
artist_id | name
-----------+------
(0 rows)
=> \dp
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+-----------------------+----------+---------------------------+-------------------+----------
public | albums | table | | |
public | albums_album_id_seq | sequence | | |
public | artists | table | postgres=arwdDxt/postgres+| |
| | | artist=r/postgres | |
public | artists_artist_id_seq | sequence | | |
public | fan_follows | table | | |
public | fans | table | | |
public | fans_fan_id_seq | sequence | | |
(7 rows)
Теперь мы видим некоторые привилегии. Привилегия select (read), которую мы предоставили роли «artist» из роли «postgres», появляется вместе с полным набором возможных привилегий, которые имплицитно предоставляются владельцу таблицы (postgres). Что означают все эти буквы? В документации PostgreSQL есть наглядная таблица:
Первые 7 строк — это привилегии, применимые к объектам базы данных «табличного» типа.
Давайте продолжим добавлять остальные привилегии в нашем примере. На естественном языке эти привилегии звучат так:
- Мы хотим, чтобы фанаты могли видеть свои данные и удалять свою учетную запись.
- Мы хотим, чтобы фанаты могли видеть, на каких артистов они подписаны, а также подписываться на артистов и отписываться от них.
- Мы хотим, чтобы фанаты могли видеть исполнителей и альбомы.
- Мы хотим, чтобы исполнители могли видеть свои собственные данные и редактировать свое имя.
- Мы хотим, чтобы исполнители могли создавать, редактировать и удалять альбомы.
Теперь то же самое на SQL:
=> RESET ROLE;
=# GRANT SELECT, DELETE ON fans to fan;
=# GRANT SELECT, INSERT, DELETE ON fan_follows TO fan;
=# GRANT SELECT ON artists TO fan;
=# GRANT SELECT ON albums TO fan;
=# GRANT SELECT, UPDATE (name), DELETE ON artists to artist;
=# GRANT SELECT, INSERT, UPDATE (title, released), DELETE ON albums to artist;
-- I add the *s pattern to only match database objects with names ending in s,
-- so it'll show our tables (which have plural names) and hide the sequence
-- database objects that appeared in the output last time we ran \dp.
=# \dp *s
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+-------------+-------+---------------------------+---------------------+----------
public | albums | table | postgres=arwdDxt/postgres+| title: +|
| | | fan=r/postgres +| artist=w/postgres+|
| | | artist=ard/postgres | released: +|
| | | | artist=w/postgres |
public | artists | table | postgres=arwdDxt/postgres+| name: +|
| | | artist=rd/postgres +| artist=w/postgres |
| | | fan=r/postgres | |
public | fan_follows | table | postgres=arwdDxt/postgres+| |
| | | fan=ard/postgres | |
public | fans | table | postgres=arwdDxt/postgres+| |
| | | fan=rd/postgres | |
(4 rows)
Вы можете заметить, что мы не предоставляем права на обновление столбцов с ID. Если бы вместо этого мы разрешили пользователям приложения редактировать идентификаторы, то они могли бы делать то, что нам не нужно, например изменять идентификатор строки или изменять отношения между строками (например, исполнитель мог бы назначить созданный им альбом другому исполнителю). Используя привилегии для конкретных столбцов, мы можем гарантировать, что пользователи смогут изменять только разрешенные нами значения. Еще один способ защиты от изменения пользователями идентификаторов, используемых в отношениях (внешних ключах), — это политики безопасности на уровне строк, которые мы рассмотрим в следующей статье.
Мы также могли бы опустить привилегии выбора в столбцах идентификаторов, которые вы, возможно, захотите сделать, чтобы скрыть внутреннюю информацию, такую как суррогатные ключи или бизнес-информацию, например скорость, с которой исполнители создают альбомы на вашей платформе (если вы используете автоинкрементные целочисленные идентификаторы). Однако, если пользователям недоступны идентификаторы, они не могут объединять таблицы (придется делать отдельные представления для объединений, которые вы хотите предоставить пользователям) и делать SELECT * (им нужно явно указывать столбцы для выборки).
Теперь, когда мы установили некоторые привилегии, давайте добавим данные и проверим, правильно ли все работает.
- First, we insert one fan and 3 artists as the postgres superuser
=# INSERT INTO fans DEFAULT VALUES;
=# INSERT INTO artists (name)
VALUES ('DJ Okawari'), ('Steely Dan'), ('Missy Elliott');
-- We change role to «fan» and follow some artists (DJ Okawari and Steely Dan)
=# SET ROLE fan;
=> INSERT INTO fan_follows (fan_id, artist_id)
VALUES (1, 1), (1, 2);
-- We unfollow DJ Okawari
=> DELETE FROM fan_follows WHERE artist_id = 1;
-- Let's list what artists we're still following
=> SELECT * FROM fans
INNER JOIN fan_follows USING (fan_id)
INNER JOIN artists USING (artist_id);
artist_id | fan_id | name
-----------+--------+------------
2 | 1 | Steely Dan
-- Try to change an artist's name, which doesn't work from the «fan» role
=> UPDATE artists SET name = 'TWRP' WHERE artist_id = 2;
ERROR: 42501: permission denied for table artists
-- Change roles to «artist» and change an artist's name
=> SET ROLE artist;
=> UPDATE artists SET name = 'TWRP' WHERE artist_id = 2;
-- Add a new album
=> INSERT INTO albums (artist_id, title, released)
VALUES (3, 'Under Construction', '2002-11-12');
-- Try to assign the album to a different artist, which doesn't work
=> UPDATE albums SET artist_id = 2;
ERROR: 42501: permission denied for table albums
=> DELETE FROM artists;
-- Deleting all artists in the database executes without error! *gulp*
Наши привилегии в целом выглядят неплохо… за исключением того, что пользователь, вошедший в систему как «artist», может удалить всех исполнителей из базы данных! Мы бы предпочли, чтобы артисты могли удалять только свои собственные учетные записи, но такая логика авторизации не выражается с точки зрения привилегий, GRANT и REVOKE. Это связано с тем, что решение об авторизации («должно ли быть разрешено это действие?») зависит от значений в конкретной строке базы данных. Как вы уже догадались, нам нужны политики безопасности на уровне строк для принятия детальных решений о том, какие строки в базе данных могут обрабатываться конкретными пользователями. Мы рассмотрим это в следующей статье.
