Роли и привилегии
Предоставляйте и отзывайте доступ с помощью GRANT и REVOKE
«Роли и привилегии» — бесплатный урок SQL Academy на CoddyKit. Это урок 1 из 4. Ты можешь прочитать весь урок бесплатно ниже — а потом практиковать его прямо в браузере с встроенным редактором кода и ИИ-репетитором 24/7. Это часть пути обучения SQL Academy, и твой прогресс синхронизируется между веб-версией и приложением CoddyKit. Курс SQL Academy содержит 4 уроков всего.
Что такое роли и привилегии
В SQL роли — это именованные наборы привилегий, которые можно назначать пользователям. Привилегии определяют, какие действия пользователь или роль может выполнять с объектами базы данных, такими как таблицы, представления и функции.
Вместо того чтобы выдавать разрешения каждому пользователю отдельно, создают роль с необходимыми привилегиями, а затем назначают её сразу многим пользователям. Это значительно упрощает управление доступом в больших системах.
Создание роли
Используйте CREATE ROLE, чтобы определить новую роль в PostgreSQL. В зависимости от настроек роль может представлять одного пользователя или группу пользователей.
По умолчанию роли создаются без каких-либо привилегий — доступ к объектам необходимо выдавать явно.
CREATE ROLE readonly_user;
CREATE ROLE app_writer;
CREATE ROLE db_admin;Выдача привилегий для таблицы
Оператор GRANT разрешает роли или пользователю выполнять определённые операции с объектом базы данных. К распространённым привилегиям на уровне таблицы относятся SELECT, INSERT, UPDATE и DELETE.
Можно выдать одну или сразу несколько привилегий, разделив их запятыми.
-- Grant SELECT only (read-only role)
GRANT SELECT ON employees TO readonly_user;
-- Grant multiple privileges
GRANT SELECT, INSERT, UPDATE ON orders TO app_writer;Выдача всех привилегий
Если роли нужен полный доступ к таблице, можно использовать GRANT ALL PRIVILEGES вместо перечисления каждого разрешения по отдельности. Это выдаёт все применимые привилегии для указанного объекта.
Будьте осторожны с ALL PRIVILEGES — выдавайте их только ролям, которым действительно требуется полный контроль над объектом.
-- Grant full access to db_admin on a table
GRANT ALL PRIVILEGES ON employees TO db_admin;
-- Or the shorthand form
GRANT ALL ON orders TO db_admin;Отзыв привилегий
Оператор REVOKE удаляет ранее выданные привилегии у роли или пользователя. Так можно ужесточить доступ, когда требования меняются или роли больше не нужны определённые разрешения.
После выполнения REVOKE затронутая роль сразу теряет указанную привилегию для названного объекта.
-- Remove UPDATE access from app_writer
REVOKE UPDATE ON orders FROM app_writer;
-- Remove all privileges from a role
REVOKE ALL PRIVILEGES ON employees FROM readonly_user;Назначение роли пользователю
В PostgreSQL пользователи также являются ролями: роль с атрибутом LOGIN может подключаться к базе данных. Групповую роль назначают роли для входа с помощью GRANT role TO user.
После назначения пользователь наследует все привилегии этой роли, что упрощает управление разрешениями сразу для многих пользователей.
-- Create a login user
CREATE ROLE alice WITH LOGIN PASSWORD 'secret123';
-- Assign the readonly role to alice
GRANT readonly_user TO alice;
-- Alice can now SELECT on tables granted to readonly_userОтзыв роли у пользователя
Чтобы удалить назначенную пользователю роль, используйте REVOKE role FROM user. После этого пользователь перестаёт наследовать привилегии, связанные с этой ролью.
Это удобно, когда сотрудник меняет обязанности или покидает организацию: роль можно отозвать, не изменяя определения собственных привилегий этой роли.
-- Remove the readonly role from alice
REVOKE readonly_user FROM alice;
-- Alice no longer has SELECT on tables via that roleПривилегии на уровне схемы
Прежде чем роль сможет получить доступ к объектам внутри схемы, ей нужна привилегия USAGE для этой схемы. Без неё даже роль с привилегией SELECT для определённой таблицы не сможет прочитать данные, поскольку не сможет разрешить путь к схеме.
Всегда выдавайте USAGE для схемы вместе с привилегиями на уровне объектов.
-- Allow readonly_user to see inside the public schema
GRANT USAGE ON SCHEMA public TO readonly_user;
-- Then grant table-level privilege
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;Привилегии по умолчанию для будущих объектов
При создании новых таблиц в схеме существующие роли не получают к ним доступ автоматически. Используйте ALTER DEFAULT PRIVILEGES, чтобы объекты, создаваемые в будущем определённой ролью, автоматически становились доступными другой роли.
Это предотвращает распространённую проблему, когда вновь созданная таблица невидима для ролей приложения, пока кто-нибудь не вспомнит вручную выполнить GRANT.
-- Future tables created by the current user will be SELECTable by readonly_user
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO readonly_user;
-- Future tables will allow INSERT/UPDATE/DELETE for app_writer
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_writer;Параметр WITH GRANT OPTION
По умолчанию роль, получившая привилегию, не может передать её другим ролям. Добавление WITH GRANT OPTION позволяет получателю также выдавать эту привилегию другим пользователям.
Используйте этот параметр осторожно: роль становится доверенной точкой делегирования управления доступом, что при чрезмерном использовании может усложнить аудит.
-- app_writer can now grant SELECT on orders to other roles
GRANT SELECT ON orders TO app_writer WITH GRANT OPTION;
-- app_writer can then do:
-- GRANT SELECT ON orders TO reporting_role;Просмотр выданных привилегий
PostgreSQL хранит сведения о привилегиях в представлениях системного каталога. Можно выполнить запрос к information_schema.role_table_grants, чтобы узнать, каким ролям выданы какие привилегии для каких таблиц.
Команда обратной косой черты \dp tablename в psql также показывает список привилегий доступа к таблице в компактном формате.
-- List all table-level grants in the current database
SELECT grantee, table_schema, table_name, privilege_type
FROM information_schema.role_table_grants
WHERE table_schema = 'public'
ORDER BY grantee, table_name;Быстрая проверка
Проверьте, насколько хорошо Вы понимаете роли и привилегии в SQL.
Итоги: роли и привилегии
В этом уроке Вы узнали, как SQL управляет доступом с помощью ролей и привилегий:
- CREATE ROLE определяет новую роль или учётную запись пользователя;
- GRANT назначает роли привилегии (SELECT, INSERT, UPDATE, DELETE, ALL) для объектов;
- REVOKE удаляет эти привилегии;
- GRANT role TO user назначает роль группы пользователю для входа, чтобы он унаследовал её привилегии;
- ролям требуется USAGE для схемы, прежде чем они смогут получить доступ к объектам внутри неё;
- ALTER DEFAULT PRIVILEGES обеспечивает автоматический доступ к будущим объектам;
- WITH GRANT OPTION позволяет получателю передавать привилегию другим пользователям.
Минимальные привилегии, основанные на ролях, — один из основных принципов безопасного проектирования баз данных: выдавайте только необходимое и своевременно отзывайте доступ, когда он больше не требуется.
Часто задаваемые вопросы
Урок «Роли и привилегии» бесплатный?
Да — полный текст урока «Роли и привилегии» бесплатно доступен здесь в веб-версии. Чтобы практиковать его интерактивно (встроенный редактор кода и ИИ-репетитор 24/7) и разблокировать остальной курс SQL Academy, подпишись на CoddyKit PRO. Курс SQL Academy содержит 4 уроков всего.
Чему я научусь в уроке «Роли и привилегии»?
Предоставляйте и отзывайте доступ с помощью GRANT и REVOKE Ты практикуешь SQL Academy с помощью реального кода, который запускаешь прямо в браузере, и ИИ-репетитор 24/7 отвечает на твои вопросы во время урока.
Нужен ли мне опыт, чтобы начать SQL Academy?
Предыдущий опыт не требуется. SQL Academy на CoddyKit структурирован для всех уровней — от новичков до продвинутых, поэтому ты можешь начать отсюда или с самого начала и учиться в своем темпе. Это урок 1 из 4.
Сколько времени занимает урок «Роли и привилегии»?
Большинство уроков CoddyKit занимают около 5–10 минут. Каждый из них компактный и интерактивный, поэтому ты постоянно делаешь прогресс и продолжаешь с того же места в веб-версии и приложении.
Можно ли писать и запускать код в этом уроке SQL Academy?
Да. Каждый урок SQL Academy включает встроенный редактор кода, поэтому ты пишешь и запускаешь реальный код прямо в браузере и получаешь моментальную обратную связь от AI — локальная установка не требуется.