Пользователи и права в PostgreSQL — CREATE USER, GRANT, REVOKE

В PostgreSQL нет разделения «пользователь» и «роль» — это одно и то же. CREATE USER — синоним CREATE ROLE WITH LOGIN. Разбираем создание пользователей, выдачу прав через GRANT, группировку через роли и минимальные привилегии для продакшн-приложений.

Пользователи и роли в PostgreSQL

-- Роль = пользователь + набор прав
-- USER = ROLE WITH LOGIN (может подключаться)
-- ROLE без LOGIN = группа прав (нельзя подключиться напрямую)

-- Создать пользователя (роль с возможностью входа):
CREATE USER appuser WITH PASSWORD 'секретный_пароль';
-- Эквивалентно:
CREATE ROLE appuser WITH LOGIN PASSWORD 'секретный_пароль';

-- Создать с дополнительными атрибутами:
CREATE USER admin_user WITH
  PASSWORD 'пароль'
  SUPERUSER           -- полный доступ ко всему
  CREATEDB            -- может создавать базы данных
  CREATEROLE          -- может создавать роли
  CONNECTION LIMIT 5  -- максимум 5 одновременных подключений
  VALID UNTIL '2025-12-31';  -- пароль действует до даты

-- Создать роль-группу (без LOGIN — нельзя подключиться):
CREATE ROLE readonly_role;
CREATE ROLE readwrite_role;
CREATE ROLE admin_role;

-- Просмотреть всех пользователей и роли:
\du
-- Или через SQL:
SELECT rolname, rolsuper, rolcreatedb, rolcanlogin, rolconnlimit
FROM pg_roles
ORDER BY rolname;

GRANT — выдача прав

-- Подключиться к нужной базе данных перед выдачей прав:
\c mydb

-- Права на базу данных:
GRANT CONNECT ON DATABASE mydb TO appuser;

-- Права на схему:
GRANT USAGE ON SCHEMA public TO appuser;

-- Права на конкретную таблицу:
GRANT SELECT ON users TO appuser;
GRANT SELECT, INSERT, UPDATE ON orders TO appuser;
GRANT ALL PRIVILEGES ON products TO appuser;

-- Права на все таблицы в схеме:
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO readwrite_role;

-- Права на будущие таблицы (которые создадут потом):
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO readonly_role;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO readwrite_role;

-- Права на sequences (нужны для INSERT с SERIAL):
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO appuser;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT USAGE, SELECT ON SEQUENCES TO appuser;

-- Назначить роль пользователю (пользователь наследует права роли):
GRANT readonly_role TO appuser;
GRANT readwrite_role TO appuser2;

REVOKE — отзыв прав

-- Отозвать конкретное право:
REVOKE INSERT ON orders FROM appuser;

-- Отозвать все права на таблицу:
REVOKE ALL PRIVILEGES ON users FROM appuser;

-- Отозвать права на все таблицы схемы:
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM appuser;

-- Убрать пользователя из роли:
REVOKE readonly_role FROM appuser;

-- Отозвать право подключения к базе:
REVOKE CONNECT ON DATABASE mydb FROM appuser;

-- Проверить текущие права:
\dp users            -- права на таблицу users в psql
\dp                  -- права на все таблицы

-- Через SQL — права на таблицы:
SELECT grantee, privilege_type, table_name
FROM information_schema.role_table_grants
WHERE table_schema = 'public'
ORDER BY grantee, table_name;

Минимальные права для продакшн-приложения

-- Создать пользователя приложения с минимальными правами:
CREATE USER app WITH PASSWORD 'strong_password';

-- Разрешить подключение к базе:
GRANT CONNECT ON DATABASE mydb TO app;

-- Разрешить использование схемы:
GRANT USAGE ON SCHEMA public TO app;

-- Права на таблицы (без DELETE и DROP):
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO app;

-- Права на sequences (для SERIAL/IDENTITY):
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app;

-- Применить к будущим таблицам:
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE ON TABLES TO app;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT USAGE, SELECT ON SEQUENCES TO app;

-- Для приложения только на чтение (реплика, аналитика):
CREATE USER app_readonly WITH PASSWORD 'readonly_password';
GRANT CONNECT ON DATABASE mydb TO app_readonly;
GRANT USAGE ON SCHEMA public TO app_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO app_readonly;

Управление пользователями

-- Изменить пароль:
ALTER USER appuser WITH PASSWORD 'новый_пароль';

-- Заблокировать пользователя (запретить вход):
ALTER USER appuser NOLOGIN;
-- Разблокировать:
ALTER USER appuser LOGIN;

-- Изменить атрибуты:
ALTER USER appuser CONNECTION LIMIT 10;
ALTER USER appuser VALID UNTIL 'infinity';  -- снять ограничение по дате

-- Переименовать:
ALTER USER appuser RENAME TO newname;

-- Удалить пользователя:
DROP USER appuser;
-- Если у пользователя есть объекты — сначала переназначить владение:
REASSIGN OWNED BY appuser TO postgres;
DROP OWNED BY appuser;  -- удалить оставшиеся права
DROP USER appuser;

-- Отключить активные сессии пользователя:
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE usename = 'appuser' AND pid != pg_backend_pid();

Часто задаваемые вопросы

В чём разница между CREATE USER и CREATE ROLE в PostgreSQL?

Только в одном атрибуте: CREATE USER создаёт роль с LOGIN по умолчанию — пользователь может подключиться к базе. CREATE ROLE создаёт роль с NOLOGIN по умолчанию — нельзя подключиться напрямую, используется как группа прав. На практике для приложений используют CREATE USER, для группировки прав — CREATE ROLE. Дать роль пользователю: GRANT readonly_role TO appuser; — пользователь наследует все права роли.

Как дать пользователю права только на чтение в PostgreSQL?

Три команды: GRANT CONNECT ON DATABASE mydb TO user;, GRANT USAGE ON SCHEMA public TO user;, GRANT SELECT ON ALL TABLES IN SCHEMA public TO user;. Дополнительно для будущих таблиц: ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO user;. Без последней команды пользователь не увидит новые таблицы, созданные после выдачи прав. Важно: GRANT нужно выполнять будучи подключённым к нужной базе данных.

Как посмотреть права пользователя в PostgreSQL?

В psql: \du — список ролей и их атрибуты (SUPERUSER, CREATEDB и т.д.); \dp tablename или \z tablename — права на конкретную таблицу. Через SQL: SELECT * FROM information_schema.role_table_grants WHERE grantee = 'appuser'; — все права на таблицы. Права роли на схемы: SELECT * FROM information_schema.role_usage_grants WHERE grantee = 'appuser';. Членство в ролях: SELECT rolname FROM pg_roles JOIN pg_auth_members ON pg_roles.oid = pg_auth_members.roleid WHERE member = (SELECT oid FROM pg_roles WHERE rolname = 'appuser');.