REVOKE в SQL: как отзывать права без сюрпризов от PUBLIC и ролей

В SQL командой GRANT мы выдаём права, а командой REVOKE — забираем их обратно.

На первый взгляд всё просто:

REVOKE SELECT ON orders FROM analyst;

Кажется, после этого роль analyst больше не сможет читать таблицу orders.

Но в реальной базе всё может оказаться хитрее.

Доступ к таблице может приходить не только напрямую. Он может быть получен:

• через другую роль;
• через псевдороль PUBLIC;
• через владельца объекта;
• через суперпользователя;
• через ранее выданные права с WITH GRANT OPTION.

Поэтому REVOKE — это не просто «написал команду и забыл». Это маленький аудит доступа: сначала нужно понять, откуда право пришло, а потом отозвать его именно на этом уровне.

Разберёмся по шагам.

Что делает REVOKE

REVOKE отзывает ранее выданные права.

Например, у нас есть роль аналитика:

CREATE ROLE analyst NOLOGIN;

И ей когда-то выдали право вставлять заказы:

GRANT INSERT ON orders TO analyst;

Теперь мы хотим это право забрать:

REVOKE INSERT ON orders FROM analyst;

После этого роль analyst больше не сможет выполнять:

INSERT INTO orders (user_id, amount, status)
VALUES (42, 1000, 'pending');

если у неё нет этого права через другой источник.

Вот это последняя часть очень важна:

REVOKE забирает конкретный путь доступа. Если такое же право пришло через другую роль или через PUBLIC, эффективный доступ может остаться.

Базовый синтаксис REVOKE

Общая форма выглядит так:

REVOKE privilege
ON object
FROM role;

Например, забрать право читать таблицу:

REVOKE SELECT ON orders FROM analyst;

Забрать право вставлять строки:

REVOKE INSERT ON orders FROM analyst;

Забрать право обновлять таблицу:

REVOKE UPDATE ON orders FROM analyst;

Можно отзывать несколько прав сразу:

REVOKE SELECT, INSERT, UPDATE ON orders FROM analyst;

Можно забрать все права на конкретный объект:

REVOKE ALL PRIVILEGES ON orders FROM analyst;

Но важно понимать: ALL PRIVILEGES здесь означает «все привилегии на этот объект», а не «вообще всё, что есть у роли в базе».

Если роль получает доступ через членство в другой роли, такой доступ этим запросом не исчезнет.

Пример: отозвать права на таблицу

Допустим, роль analyst раньше могла читать и добавлять заказы:

GRANT SELECT, INSERT ON orders TO analyst;

Теперь мы решили оставить только чтение, а вставку запретить.

Пишем:

REVOKE INSERT ON orders FROM analyst;

Теперь такой запрос разрешён:

SELECT *
FROM orders;

А такой уже нет:

INSERT INTO orders (user_id, amount, status)
VALUES (42, 1000, 'pending');

Это хороший пример минимального отзыва: мы не забрали всё подряд, а убрали только лишнее право.

REVOKE на отдельные колонки

Права можно выдавать и отзывать не только на всю таблицу, но и на отдельные колонки.

Например, есть таблица сотрудников employees. В ней есть чувствительная колонка salary.

Раньше роли analyst разрешили обновлять зарплату:

GRANT UPDATE (salary) ON employees TO analyst;

Теперь это право нужно забрать:

REVOKE UPDATE (salary) ON employees FROM analyst;

После этого роль analyst не сможет выполнить:

UPDATE employees
SET salary = 300000
WHERE id = 1;

Поколоночные права полезны там, где нельзя просто сказать «можно обновлять всю таблицу». Например, поддержке можно разрешить менять статус заказа, но нельзя менять сумму заказа.

REVOKE на схему

В PostgreSQL таблицы находятся внутри схем.

Например, public.orders.

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

GRANT USAGE ON SCHEMA public TO analyst;

Если вы хотите забрать доступ к самой схеме, можно сделать:

REVOKE USAGE ON SCHEMA public FROM analyst;

После этого роль может потерять возможность обращаться к объектам внутри схемы, даже если на сами таблицы у неё ещё где-то остались права.

Это частая причина путаницы:

• право на таблицу есть;
• права на схему нет;
• запрос всё равно падает.

И наоборот:

• право на схему есть;
• права на таблицу нет;
• читать таблицу всё равно нельзя.

Для доступа обычно нужны оба уровня:

USAGE ON SCHEMA
SELECT ON TABLE

REVOKE на все таблицы в схеме

Если нужно отозвать право сразу на все существующие таблицы в схеме, можно написать:

REVOKE SELECT ON ALL TABLES IN SCHEMA public FROM analyst;

Это заберёт SELECT со всех таблиц, которые уже существуют в схеме public.

Но есть важная ловушка.

Эта команда не меняет правила для будущих таблиц.

Если у вас настроены default privileges, то новые таблицы всё равно могут автоматически получать права для analyst.

Например, раньше было настроено:

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

Тогда для будущих таблиц нужно отдельно отозвать default privileges:

ALTER DEFAULT PRIVILEGES IN SCHEMA public
REVOKE SELECT ON TABLES FROM analyst;

Запомните:

REVOKE ... ON ALL TABLES работает с уже существующими таблицами. ALTER DEFAULT PRIVILEGES ... REVOKE влияет на будущие объекты.

Самая частая ловушка: PUBLIC

В PostgreSQL есть специальная псевдороль PUBLIC.

Она означает «все роли».

Если право выдано PUBLIC, его получают все пользователи и роли в базе.

Например:

GRANT SELECT ON users TO PUBLIC;

После этого читать таблицу users сможет любая роль, если у неё есть доступ к схеме.

Теперь представим, что мы хотим забрать доступ у analyst:

REVOKE SELECT ON users FROM analyst;

Команда выполнится, но analyst всё равно может продолжить читать users.

Почему?

Потому что право пришло не напрямую от analyst, а через PUBLIC.

Чтобы действительно забрать общий доступ, нужно отзывать его у PUBLIC:

REVOKE SELECT ON users FROM PUBLIC;

Это одна из самых важных вещей в теме REVOKE.

Если вы отозвали право у пользователя, а доступ остался, проверьте PUBLIC.

Почему отзыв у пользователя может ничего не изменить

Допустим, есть таблица orders. И есть групповая роль analytics_team.

Ей выдали чтение заказов:

GRANT SELECT ON orders TO analytics_team;

Потом пользователя или роль analyst добавили в эту группу:

GRANT analytics_team TO analyst;

Теперь analyst может читать orders через членство в analytics_team.

Если вы выполните:

REVOKE SELECT ON orders FROM analyst;

это может ничего не изменить.

Почему?

Потому что analyst не имел прямого права SELECT ON orders. Он получал его через роль analytics_team.

Чтобы убрать доступ, есть два варианта.

Вариант 1. Отозвать право у групповой роли

REVOKE SELECT ON orders FROM analytics_team;

Но это повлияет на всех участников роли analytics_team.

Вариант 2. Убрать конкретного пользователя из роли

REVOKE analytics_team FROM analyst;

Тогда analyst перестанет получать все права этой групповой роли.

Правильный выбор зависит от задачи.

Если больше никто из аналитиков не должен читать orders, отзываем право у analytics_team.

Если только один человек больше не должен иметь доступ, убираем его из роли.

Привилегия на объект и членство в роли — разные вещи

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

1. Отозвать право на объект

REVOKE SELECT ON orders FROM analyst;

Это означает:

забрать у analyst право читать таблицу orders.

2. Отозвать членство в роли

REVOKE analytics_team FROM analyst;

Это означает:

убрать analyst из роли analytics_team.

В первом случае мы работаем с доступом к конкретной таблице.

Во втором — с участием одной роли в другой.

Эти вещи нельзя путать. Если доступ пришёл через роль, отзыв прямой привилегии на таблицу может не помочь.

Как проверить, есть ли у роли доступ

В PostgreSQL можно проверить эффективное право через функцию:

SELECT has_table_privilege('analyst', 'public.orders', 'SELECT');

Если вернётся:

true

значит роль analyst может читать таблицу public.orders.

Если:

false

значит такого доступа нет.

Это удобно, потому что функция учитывает не только прямые права, но и права через роли.

Можно проверить разные действия:

SELECT has_table_privilege('analyst', 'public.orders', 'INSERT');
SELECT has_table_privilege('analyst', 'public.orders', 'UPDATE');
SELECT has_table_privilege('analyst', 'public.orders', 'DELETE');

Для схемы есть похожая проверка:

SELECT has_schema_privilege('analyst', 'public', 'USAGE');

Перед и после REVOKE такие проверки помогают понять, действительно ли доступ исчез.

Как посмотреть выданные права

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

\dp orders

Или на все таблицы:

\dp

Также можно использовать information_schema.

Например:

SELECT
    grantor,
    grantee,
    privilege_type,
    is_grantable
FROM information_schema.role_table_grants
WHERE table_schema = 'public'
  AND table_name = 'orders';

Так можно увидеть:

• кто выдал право;
• кому выдали право;
• какая именно привилегия выдана;
• можно ли передавать её дальше.

Для членства в ролях полезно смотреть системный каталог:

SELECT
    roleid::regrole  AS role_name,
    member::regrole  AS member_name,
    grantor::regrole AS grantor_name
FROM pg_auth_members;

Так можно понять, через какие групповые роли пользователь может получать доступ.

WITH GRANT OPTION: когда право передали дальше

Иногда право выдают с возможностью передавать его другим:

GRANT SELECT ON orders TO manager WITH GRANT OPTION;

Это значит, что manager может не только читать orders, но и выдавать это право дальше.

Например:

GRANT SELECT ON orders TO analyst;

Теперь analyst получил доступ, потому что manager передал ему право.

Если потом мы захотим отозвать право у manager, PostgreSQL должен понять, что делать с теми правами, которые manager уже успел раздать.

Для этого есть два режима: RESTRICT и CASCADE.

RESTRICT: не отзывать, если есть зависимые права

RESTRICT — это осторожный вариант.

Он говорит:

«Не выполняй отзыв, если от этого права зависят другие выданные права».

Например:

REVOKE SELECT ON orders FROM manager RESTRICT;

Если manager уже передал это право кому-то ещё, команда может завершиться ошибкой.

Это хорошо: PostgreSQL не даст вам случайно сломать цепочку доступов, о которой вы могли не знать.

По умолчанию REVOKE работает осторожно. Поэтому если вы не уверены, лучше не использовать CASCADE сразу.

CASCADE: отозвать по цепочке

CASCADE говорит:

«Отзови это право и всё, что от него зависит».

Например:

REVOKE SELECT ON orders FROM manager CASCADE;

Если manager выдал SELECT аналитику, это зависимое право тоже может быть отозвано.

Это удобно, когда вы точно хотите убрать всю цепочку.

Но это опасно, если вы не проверили зависимости заранее.

Можно случайно забрать доступ у отчёта, ETL-процесса или пользователя, про которого вы вообще не думали.

Поэтому перед CASCADE лучше посмотреть гранты:

SELECT
    grantor,
    grantee,
    privilege_type,
    is_grantable
FROM information_schema.role_table_grants
WHERE table_schema = 'public'
  AND table_name = 'orders';

Короткое правило:

RESTRICT безопаснее для первого шага. CASCADE используйте только когда понимаете, какие зависимые права будут отозваны.

Как отозвать только право передавать доступ дальше

Иногда нужно оставить человеку доступ, но запретить раздавать его другим.

Для этого используют REVOKE GRANT OPTION FOR.

Например:

REVOKE GRANT OPTION FOR SELECT ON orders FROM manager CASCADE;

После этого manager может продолжить читать orders, но больше не сможет выдавать SELECT другим ролям.

Почему здесь может понадобиться CASCADE?

Потому что если manager уже успел передать права дальше, эти зависимые гранты тоже нужно обработать.

Владелец таблицы и суперпользователь

REVOKE не работает как универсальная кнопка «запретить всем».

У объекта есть владелец.

Например, таблицу orders создала роль app_owner.

Владелец объекта обычно сохраняет полный контроль над ним. Обычный REVOKE не забирает права владельца так же, как у обычного пользователя.

Суперпользователь PostgreSQL тоже имеет особые полномочия.

Поэтому если вы проверяете REVOKE под владельцем таблицы или суперпользователем, результат может сбивать с толку:

REVOKE SELECT ON orders FROM app_owner;

Это не превратит владельца в обычного пользователя без доступа.

Для реальной проверки доступа используйте обычную роль, не владельца и не суперпользователя.

REVOKE и sequences

В PostgreSQL часто забывают не только про таблицы, но и про sequences.

Например, роль могла иметь право использовать sequence для генерации id:

GRANT USAGE ON SEQUENCE orders_id_seq TO app_write;

Если нужно забрать это право:

REVOKE USAGE ON SEQUENCE orders_id_seq FROM app_write;

Или сразу на все sequences в схеме:

REVOKE USAGE ON ALL SEQUENCES IN SCHEMA public FROM app_write;

Если роль потеряет право на sequence, вставка в таблицу с автоинкрементным id может начать падать, даже если INSERT на саму таблицу у неё остался.

Поэтому при отзыве прав для приложения проверяйте не только таблицы, но и sequences.

Безопасный сценарий отзыва прав

Допустим, нужно убрать у analyst возможность читать таблицу orders.

Не стоит сразу писать одну команду и считать задачу закрытой.

Лучше пройти по шагам.

Шаг 1. Проверить эффективный доступ

SELECT has_table_privilege('analyst', 'public.orders', 'SELECT');

Если результат false, доступа уже нет.

Если true, нужно понять, откуда он приходит.

Шаг 2. Посмотреть прямые гранты

SELECT
    grantor,
    grantee,
    privilege_type,
    is_grantable
FROM information_schema.role_table_grants
WHERE table_schema = 'public'
  AND table_name = 'orders'
  AND privilege_type = 'SELECT';

Ищем, есть ли там analyst, PUBLIC или роли, в которые входит analyst.

Шаг 3. Проверить членство в ролях

SELECT
    roleid::regrole AS role_name,
    member::regrole AS member_name
FROM pg_auth_members
WHERE member = 'analyst'::regrole;

Если analyst входит в analytics_team, а analytics_team имеет SELECT ON orders, то отзыв нужно делать на уровне роли или убрать analyst из роли.

Шаг 4. Отозвать право на правильном уровне

Если доступ выдан напрямую:

REVOKE SELECT ON orders FROM analyst;

Если доступ пришёл через роль:

REVOKE analytics_team FROM analyst;

или:

REVOKE SELECT ON orders FROM analytics_team;

Если доступ пришёл через PUBLIC:

REVOKE SELECT ON orders FROM PUBLIC;

Шаг 5. Проверить результат

SELECT has_table_privilege('analyst', 'public.orders', 'SELECT');

Если теперь false, доступ действительно исчез.

Если всё ещё true, значит остался другой путь доступа.

Можно ли делать REVOKE в транзакции

Да, серию REVOKE обычно можно выполнять внутри транзакции.

Например:

BEGIN;

REVOKE SELECT ON orders FROM analyst;
REVOKE INSERT ON orders FROM analyst;

SELECT has_table_privilege('analyst', 'public.orders', 'SELECT');

ROLLBACK;

Так можно проверить изменения и откатить их, если что-то пошло не так.

Когда уверены:

BEGIN;

REVOKE SELECT ON orders FROM analyst;
REVOKE INSERT ON orders FROM analyst;

COMMIT;

Для чувствительных изменений это хорошая привычка.

Частые ошибки с REVOKE

Ошибка 1. Отозвать право у пользователя, хотя доступ пришёл через роль

Не сработает как ожидается:

REVOKE SELECT ON orders FROM analyst;

если analyst получает доступ через:

GRANT analytics_team TO analyst;

Нужно либо убрать пользователя из роли:

REVOKE analytics_team FROM analyst;

либо отозвать право у самой роли:

REVOKE SELECT ON orders FROM analytics_team;

Ошибка 2. Забыть про PUBLIC

Проблема:

GRANT SELECT ON users TO PUBLIC;
REVOKE SELECT ON users FROM analyst;

analyst всё ещё может читать users, потому что право есть у PUBLIC.

Правильно:

REVOKE SELECT ON users FROM PUBLIC;

Ошибка 3. Использовать CASCADE без проверки

Опасно:

REVOKE SELECT ON orders FROM manager CASCADE;

Если manager раздавал права другим ролям, они тоже могут потерять доступ.

Перед этим лучше посмотреть гранты:

SELECT
    grantor,
    grantee,
    privilege_type,
    is_grantable
FROM information_schema.role_table_grants
WHERE table_schema = 'public'
  AND table_name = 'orders';

Ошибка 4. Отозвать права на существующие таблицы, но забыть default privileges

Есть:

REVOKE SELECT ON ALL TABLES IN SCHEMA public FROM analyst;

Но если осталось:

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

то будущие таблицы снова будут получать доступ.

Нужно отдельно:

ALTER DEFAULT PRIVILEGES IN SCHEMA public
REVOKE SELECT ON TABLES FROM analyst;

Ошибка 5. Проверять доступ под владельцем таблицы

Если вы тестируете права под владельцем объекта или суперпользователем, REVOKE может казаться «неработающим».

Проверяйте доступ под обычной ролью или через:

SELECT has_table_privilege('analyst', 'public.orders', 'SELECT');

MySQL: похожая идея, но меньше CASCADE-логики

В MySQL тоже есть REVOKE.

Например:

REVOKE SELECT ON mydb.orders FROM 'analyst'@'%';

Или отозвать несколько прав:

REVOKE SELECT, INSERT ON mydb.orders FROM 'analyst'@'%';

В MySQL 8 есть роли, поэтому доступ тоже может приходить через роль.

Например:

REVOKE analytics_team FROM 'analyst'@'%';

Но механика CASCADE и RESTRICT для REVOKE не такая, как в PostgreSQL. Поэтому при переносе примеров между СУБД нельзя копировать команды один в один.

Идея остаётся прежней:

если доступ не исчез после REVOKE, значит он приходит другим путём.

ClickHouse: REVOKE и роли тоже есть

В ClickHouse тоже можно отзывать права:

REVOKE SELECT ON analytics.orders FROM analyst;

И отзывать роли:

REVOKE analytics_team FROM analyst;

Но модель прав и синтаксис отличаются от PostgreSQL.

Например, в ClickHouse нет такой же истории с USAGE ON SCHEMA public, потому что базы и таблицы устроены иначе.

Поэтому общее правило переносится, а детали нужно проверять под конкретную СУБД.

Короткая шпаргалка

Отозвать чтение таблицы:

REVOKE SELECT ON orders FROM analyst;

Отозвать несколько прав:

REVOKE SELECT, INSERT, UPDATE ON orders FROM analyst;

Отозвать все права на таблицу:

REVOKE ALL PRIVILEGES ON orders FROM analyst;

Отозвать право на колонку:

REVOKE UPDATE (salary) ON employees FROM analyst;

Отозвать доступ к схеме:

REVOKE USAGE ON SCHEMA public FROM analyst;

Отозвать чтение всех существующих таблиц схемы:

REVOKE SELECT ON ALL TABLES IN SCHEMA public FROM analyst;

Отозвать default privileges для будущих таблиц:

ALTER DEFAULT PRIVILEGES IN SCHEMA public
REVOKE SELECT ON TABLES FROM analyst;

Отозвать доступ у всех через PUBLIC:

REVOKE SELECT ON users FROM PUBLIC;

Убрать пользователя из роли:

REVOKE analytics_team FROM analyst;

Осторожный отзыв без удаления зависимых грантов:

REVOKE SELECT ON orders FROM manager RESTRICT;

Отзыв вместе с зависимыми грантами:

REVOKE SELECT ON orders FROM manager CASCADE;

Отозвать только возможность передавать право дальше:

REVOKE GRANT OPTION FOR SELECT ON orders FROM manager CASCADE;

Проверить эффективный доступ:

SELECT has_table_privilege('analyst', 'public.orders', 'SELECT');

Главное правило

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

Если доступ пришёл напрямую, отзывайте прямой грант:

REVOKE SELECT ON orders FROM analyst;

Если доступ пришёл через роль, работайте с ролью:

REVOKE analytics_team FROM analyst;

или:

REVOKE SELECT ON orders FROM analytics_team;

Если доступ пришёл через PUBLIC, отзывайте у PUBLIC:

REVOKE SELECT ON orders FROM PUBLIC;

Если право было передано дальше через WITH GRANT OPTION, осторожно выбирайте между RESTRICT и CASCADE.

Запомните коротко:

перед REVOKE сначала найдите маршрут доступа. Потом отзывайте право именно там, где оно было выдано. После этого проверяйте эффективный доступ глазами нужной роли.

Так вы не будете удивляться ситуации, когда «право отозвали», а пользователь всё равно продолжает читать таблицу.