SQL GRANT: Privileges on Tables, Schemas and Columns with Least Privilege

GRANT выдаёт право что-то делать: читать таблицу, вставлять строки, использовать схему. В PostgreSQL по умолчанию обычная роль не видит ничего за пределами того, что ей явно разрешили, поэтому грамотный набор GRANT — это и есть ваша модель доступа. Сделаете его слишком широким — аналитик случайно перепишет боевые данные.

Привилегии на таблицу

Базовая форма перечисляет действия и объект, а затем роль-получателя. Возьмём знакомые users, orders и employees.

CREATE ROLE analyst NOLOGIN;

GRANT SELECT, INSERT ON orders TO analyst;
GRANT SELECT ON users TO analyst;

Привилегии для таблиц — это SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER. Слово ALL выдаёт сразу все:

GRANT ALL PRIVILEGES ON employees TO analyst;

Грабли: выдать SELECT на таблицу мало, если аналитик не может войти в схему. Чтобы добраться до объекта, нужна привилегия USAGE на содержащую его схему:

GRANT USAGE ON SCHEMA public TO analyst;

Без USAGE запрос упадёт с permission denied for schema, хотя право на таблицу формально есть.

Колонки и схемы целиком

Привилегии можно сузить до отдельных колонок. Это удобно, когда employees.salary — чувствительные данные, а имя и отдел читать можно всем.

GRANT SELECT (id, name, dept) ON employees TO analyst;

Теперь SELECT id, name FROM employees работает, а SELECT salary FROM employees отклоняется. UPDATE тоже бывает поколоночным: GRANT UPDATE (status) ON orders TO analyst разрешит менять только статус заказа.

В обратную сторону можно выдать права на все таблицы схемы одной командой:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO analyst;

Важные грабли: ALL TABLES IN SCHEMA действует только на таблицы, существующие в момент выполнения. Новая таблица, созданная завтра, прав не получит. Чтобы права раздавались автоматически, настройте ALTER DEFAULT PRIVILEGES:

ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT ON TABLES TO analyst;

WITH GRANT OPTION и передача прав

По умолчанию получатель права не может передать его дальше. WITH GRANT OPTION это меняет:

GRANT SELECT ON orders TO team_lead WITH GRANT OPTION;

Теперь team_lead может сам выполнить GRANT SELECT ON orders TO .... Это удобно для делегирования, но опасно: вы теряете контроль над тем, кто в итоге получит доступ. Ещё одна ловушка — при REVOKE придётся добавить CASCADE, иначе отзыв заблокируется зависимыми правами:

REVOKE SELECT ON orders FROM team_lead CASCADE;

CASCADE отзовёт и все права, которые team_lead успел раздать на основе своего grant option.

Права на объект против членства в роли

Это два разных механизма, которые легко спутать.

• Привилегия на объект — право вроде SELECT ON orders, привязанное к конкретному объекту.
• Членство в роли — GRANT role_a TO role_b делает role_b членом role_a и даёт ему все привилегии role_a скопом.

Группирующие роли — основа удобного управления доступом:

CREATE ROLE readonly NOLOGIN;
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

CREATE ROLE alice LOGIN PASSWORD 'secret';
GRANT readonly TO alice;

Теперь alice читает всё через членство в readonly. Поменяете права у readonly — и они применятся ко всем участникам сразу, никого не трогая поимённо.

Проектирование по принципу наименьших прав

Принцип least privilege прост: роль получает ровно то, что нужно для работы, и ни байтом больше.

• Не раздавайте права напрямую пользователям — собирайте их в группирующие роли (readonly, app_write, reporting).
• Закрывайте чувствительные колонки поколоночными грантами вместо SELECT на всю таблицу.
• Помните, что владелец таблицы и суперпользователь имеют полный доступ в обход GRANT — REVOKE на них не действует.

Различия движков:

• MySQL не знает ALL TABLES IN SCHEMA; используйте GRANT SELECT ON mydb.* для всей базы. Поколоночные гранты есть: GRANT SELECT (name) ON employees TO 'analyst'@'%'.
• ClickHouse поддерживает GRANT, роли и WITH GRANT OPTION, но USAGE на схему ему не нужен; права выдают на базу и таблицу напрямую.

Базовое правило: давайте USAGE на схему, минимально необходимые привилегии на объекты, группируйте через роли и держите WITH GRANT OPTION под жёстким контролем.