В этом уроке разбираемся, кто и что имеет право делать с объектами в postgresql. Аутентификация отвечает на вопрос "кто ты" (логин, пароль, SCRAM), а привилегии - на вопрос "что тебе можно". Это две разные стены, и путать их нельзя: успешный вход в базу еще не дает доступа ни к одной таблице. Научимся раздавать права командой GRANT, отбирать их через REVOKE, понимать роль владельца объекта, настраивать права по умолчанию через ALTER DEFAULT PRIVILEGES и строить схему доступа по принципу наименьших прав.
Как это работает
У каждого объекта в базе (таблица, последовательность, функция, схема, сама база данных) есть владелец - роль, которая его создала или которой его передали через ALTER ... OWNER TO. Владелец и суперпользователь могут с объектом все, и им не нужно отдельно выдавать права. Всем остальным ролям доступ выдается явно. Если роли ничего не выдали - она объект как бы не видит и получает ошибку permission denied при первой же попытке прочитать или изменить данные.
Привилегии бывают разные под разные типы объектов. Для таблиц это SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES (на создание внешних ключей), TRIGGER. Для последовательностей - USAGE, SELECT, UPDATE. Для функций - EXECUTE. Для схем - USAGE (право заглянуть внутрь и обращаться к объектам по имени) и CREATE (право создавать в схеме новые объекты). Для базы данных - CONNECT, TEMP, CREATE. Отдельно стоит USAGE на пользовательских типах и доменах. Запомните пару USAGE плюс что-то еще: чтобы прочитать таблицу в схеме app, роли нужны и USAGE на схему app, и SELECT на саму таблицу. Нет USAGE на схему - и SELECT на таблицу бесполезен.
GRANT добавляет привилегию роли, REVOKE забирает. Особый получатель - PUBLIC, это псевдороль "все роли вообще, включая будущие". Если выдать SELECT для PUBLIC, читать сможет любой, кто залогинился. Есть и опция WITH GRANT OPTION: она разрешает роли не только пользоваться привилегией, но и передавать ее дальше другим. Именно из-за таких цепочек передачи у REVOKE появляются режимы CASCADE и RESTRICT, о которых ниже.
Важное изменение начиная с PostgreSQL 15: схема public больше не дает CREATE для PUBLIC автоматически. Раньше любой пользователь мог насоздавать таблиц в public, теперь по умолчанию нет - это закрыло целый класс проблем безопасности. Также владельцем схемы public теперь считается псевдороль pg_database_owner. Если у вас старые скрипты или интеграция вроде postgresql для 1с, которые рассчитывали на свободную запись в public, права придется выдать руками.
SQL и примеры
Возьмем демобазу "Авиаперевозки" (схема bookings). Заведем роль для аналитика, которому нужно только читать.
Код: Выделить всё
CREATE ROLE analytic LOGIN PASSWORD 'secret';
GRANT USAGE ON SCHEMA bookings TO analytic;
GRANT SELECT ON bookings.flights, bookings.tickets TO analytic;
Код: Выделить всё
GRANT SELECT ON ALL TABLES IN SCHEMA bookings TO analytic;
Код: Выделить всё
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA bookings
GRANT SELECT ON TABLES TO analytic;
Проверим, что реально выдано. Привилегии на таблицу удобно смотреть командой \dp:
Код: Выделить всё
\dp bookings.flights
Заберем лишнее. Допустим, мы случайно дали UPDATE - отзываем:
Код: Выделить всё
REVOKE UPDATE ON bookings.flights FROM analytic;
Код: Выделить всё
REVOKE SELECT ON bookings.tickets FROM lead CASCADE;
Частые грабли
- Выдали SELECT на таблицу, но забыли USAGE на схему - роль все равно получает permission denied for schema.
- Думают, что GRANT ... ON ALL TABLES охватит и будущие таблицы. Нет, только существующие на момент команды. Будущие - это ALTER DEFAULT PRIVILEGES.
- ALTER DEFAULT PRIVILEGES настроили под одну роль-создателя, а таблицы создает другая роль - умолчания молча не применяются.
- REVOKE без CASCADE падает с ошибкой dependent privileges exist, и человек считает, что команда вообще не работает. Нужен CASCADE.
- Доступ к последовательности забыт: дали INSERT на таблицу с колонкой GENERATED ... AS IDENTITY или serial, но не дали USAGE на саму последовательность - вставка падает на nextval.
- Рассчитывают на старое поведение public после миграции на PG 15+: CREATE в public для PUBLIC больше не выдается по умолчанию.
- Путают права на функцию: по умолчанию EXECUTE есть у PUBLIC, и иногда это надо явно отозвать у чувствительных функций.
- Создайте роль reporter с LOGIN и паролем, и убедитесь, что без выданных прав она не может прочитать bookings.flights (войдите под ней через psql -U reporter).
- Выдайте reporter USAGE на схему bookings и SELECT на flights и airports. Проверьте, что чтение заработало, а INSERT по-прежнему запрещен.
- Командой \dp bookings.flights разберите строку Access privileges по буквам.
- Настройте ALTER DEFAULT PRIVILEGES так, чтобы будущие таблицы, создаваемые владельцем схемы, сразу давали reporter SELECT. Создайте тестовую таблицу и проверьте.
- Выдайте SELECT с WITH GRANT OPTION другой роли, передайте право от нее третьей роли, затем отзовите через REVOKE ... CASCADE и убедитесь, что у третьей роли права тоже пропали.
- Отзовите у PUBLIC лишние права на схему public (REVOKE CREATE ON SCHEMA public FROM PUBLIC) и зафиксируйте принцип наименьших прав.
Контрольные вопросы
- Чем владелец объекта отличается от роли, которой выдали все привилегии через GRANT?
- Почему SELECT на таблицу не работает без USAGE на схему, в которой эта таблица лежит?
- В чем разница между GRANT ON ALL TABLES IN SCHEMA и ALTER DEFAULT PRIVILEGES?
- Когда REVOKE требует ключевого слова CASCADE и что произойдет без него?
- Что изменилось в правах на схему public начиная с PostgreSQL 15 и почему это важно для безопасности?
- Как сформулировать принцип наименьших прав применительно к роли только для чтения отчетов?
