PostgreSQL настройка пользователей: создание, права, безопасность | AdminWiki
Logo

PostgreSQL настройка пользователей: полное руководство для администраторов

17 декабря 2025 8 мин. чтения #postgresql #администрирование #базы данных #безопасность #пользователи #права доступа #роли
Содержание статьи

Представь, что твоя PostgreSQL база — это многоэтажное здание с ценными данными. Пользователи — это ключи от разных комнат. Без правильной настройки кто угодно может попасть куда угодно. Давай разберем, как грамотно настроить пользователей в PostgreSQL, чтобы обеспечить безопасность и эффективное управление доступом.

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

В PostgreSQL понятия "пользователь" и "роль" практически идентичны. С версии 8.1 роль может быть как пользователем (с возможностью входа), так и группой. Это дает гибкость в управлении правами.

Важно: Все операции с пользователями требуют прав суперпользователя или роли с соответствующими привилегиями CREATEROLE.

Создание нового пользователя

Давай начнем с базовых команд. Создадим пользователя для веб-приложения:

sql
-- Создание пользователя с паролем
CREATE USER webapp_user WITH PASSWORD 'StrongPassword123!';

-- Альтернативный синтаксис (CREATE ROLE с LOGIN)
CREATE ROLE api_user WITH LOGIN PASSWORD 'AnotherStrongPass!';

-- Создание пользователя с дополнительными параметрами
CREATE USER analyst WITH 
  PASSWORD 'AnalystPass2024'
  VALID UNTIL '2025-12-31'
  CONNECTION LIMIT 10;

Просмотр существующих пользователей

sql
-- Просмотр всех ролей/пользователей
SELECT rolname, rolcanlogin, rolconnlimit, rolvaliduntil 
FROM pg_roles 
WHERE rolcanlogin = true;

-- Детальная информация
\du
\du+

Управление правами и привилегиями

Права доступа — сердце настройки пользователей. PostgreSQL использует систему привилегий GRANT/REVOKE.

Базовые привилегии на уровне базы данных

sql
-- Дать права на подключение к базе
GRANT CONNECT ON DATABASE mydb TO webapp_user;

-- Дать права на создание схем в базе
GRANT CREATE ON DATABASE mydb TO developer_role;

-- Запретить подключение
REVOKE CONNECT ON DATABASE sensitive_db FROM public;

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

sql
-- Дать SELECT, INSERT, UPDATE на конкретную таблицу
GRANT SELECT, INSERT, UPDATE ON users TO api_user;

-- Дать все права на таблицу
GRANT ALL PRIVILEGES ON orders TO manager_role;

-- Дать права на все таблицы в схеме
GRANT SELECT ON ALL TABLES IN SCHEMA public TO report_user;

-- Настроить права по умолчанию для будущих таблиц
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO webapp_user;
Внимание: Роль PUBLIC существует по умолчанию и включает всех пользователей. Будьте осторожны с привилегиями для PUBLIC!

Работа с ролями и группами

Групповые роли упрощают управление правами для категорий пользователей.

sql
-- Создание роли-группы (без LOGIN)
CREATE ROLE developers NOLOGIN;
CREATE ROLE analysts NOLOGIN;

-- Назначение привилегий группе
GRANT SELECT ON ALL TABLES IN SCHEMA analytics TO analysts;
GRANT USAGE ON SCHEMA analytics TO analysts;

-- Добавление пользователей в группы
GRANT developers TO john_doe;
GRANT analysts TO jane_smith;

-- Наследование прав (по умолчанию INHERIT = true)
ALTER ROLE john_doe NOINHERIT; -- пользователь не наследует права группы

Иерархия ролей

Уровень Роль Права Пример пользователей
Суперпользователь postgres Все права Администраторы БД
Группа developers CREATE, USAGE Разработчики приложений
Пользователь webapp_user SELECT, INSERT, UPDATE Веб-приложение
Read-only report_user SELECT Аналитики, BI-системы

Безопасность и аутентификация

Настройка методов аутентификации через pg_hba.conf — критически важный этап.

Конфигурация pg_hba.conf

config
# TYPE  DATABASE        USER            ADDRESS                 METHOD

# Локальные подключения (Unix socket)
local   all             postgres                                peer
local   mydb            webapp_user                            md5

# IPv4 подключения
host    all             all             127.0.0.1/32            md5
host    mydb            api_user       192.168.1.0/24          scram-sha-256

# SSL подключения
hostssl analytics       analysts       0.0.0.0/0               cert

# Репликация
host    replication     replica_user    10.0.0.0/8              md5

Методы аутентификации:

  • trust — без пароля (опасно для production)
  • md5 — хэш пароля (устаревший, но поддерживается)
  • scram-sha-256 — современный безопасный метод (рекомендуется)
  • peer — аутентификация через ОС (только local)
  • cert — SSL сертификаты клиента

Политики паролей

sql
-- Установка срока действия пароля
ALTER USER webapp_user VALID UNTIL '2024-12-31';

-- Принудительная смена пароля при следующем входе
ALTER USER john_doe PASSWORD 'TempPass123' VALID UNTIL '2024-01-15';

-- Проверка просроченных паролей
SELECT usename, valuntil 
FROM pg_user 
WHERE valuntil < CURRENT_TIMESTAMP;

Практический пример: настройка окружения

Давай настроим полное окружение для веб-приложения с разделением прав:

sql
-- 1. Создаем базу данных
CREATE DATABASE ecommerce;

-- 2. Создаем роли-группы
CREATE ROLE app_developers NOLOGIN;
CREATE ROLE app_users NOLOGIN;
CREATE ROLE app_admins NOLOGIN;

-- 3. Создаем конкретных пользователей
CREATE USER frontend_app WITH PASSWORD 'FrontendPass!2024';
CREATE USER backend_service WITH PASSWORD 'BackendPass!2024';
CREATE USER admin_user WITH PASSWORD 'AdminSuperPass!2024';

-- 4. Назначаем пользователей в группы
GRANT app_users TO frontend_app, backend_service;
GRANT app_admins TO admin_user;

-- 5. Даем права группам
GRANT CONNECT ON DATABASE ecommerce TO app_users, app_admins;
GRANT CREATE ON DATABASE ecommerce TO app_developers;

-- 6. Настраиваем права в схеме public
GRANT USAGE ON SCHEMA public TO app_users;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO app_users;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO app_admins;

-- 7. Настройка прав по умолчанию
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT SELECT, INSERT, UPDATE ON TABLES TO app_users;

ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT ALL PRIVILEGES ON TABLES TO app_admins;

Мониторинг и обслуживание

Полезные запросы для администрирования

sql
-- Активные подключения пользователя
SELECT pid, usename, application_name, client_addr, state 
FROM pg_stat_activity 
WHERE usename = 'webapp_user';

-- Права пользователя на таблицы
SELECT grantee, table_schema, table_name, privilege_type
FROM information_schema.table_privileges 
WHERE grantee = 'api_user';

-- Просмотр наследования ролей
SELECT rolname, memberof 
FROM pg_roles 
WHERE rolname = 'john_doe';

-- Поиск пользователей с истекающим паролем
SELECT usename, valuntil 
FROM pg_user 
WHERE valuntil IS NOT NULL 
ORDER BY valuntil ASC;

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

bash
# Подключение под конкретным пользователем
psql -U webapp_user -d mydb -h localhost

# Смена пароля из командной строки
psql -c "ALTER USER api_user WITH PASSWORD 'NewPass2024!';"

# Экспорт списка пользователей
pg_dumpall --roles-only > roles_backup.sql
Совет: Регулярно проводите аудит прав доступа. Используйте инструменты вроде pg_permissions или создавайте свои скрипты для проверки соответствия политикам безопасности.

Частые вопросы (FAQ)

Как сбросить забытый пароль суперпользователя?

Остановите PostgreSQL, запустите в single-user mode и выполните:

bash
# Остановка PostgreSQL
sudo systemctl stop postgresql

# Запуск в single-user mode
sudo -u postgres postgres --single -D /var/lib/postgresql/data

# В интерактивном режиме:
ALTER USER postgres WITH PASSWORD 'NewSuperSecretPass';

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

CREATE USER — это CREATE ROLE WITH LOGIN. Для создания роли без возможности входа используйте CREATE ROLE NOLOGIN.

Как ограничить ресурсы для пользователя?

Используйте connection_limit и настройки на уровне ОС. Для более тонкого контроля рассмотрите использование расширений вроде pg_qualstats или настройку resource groups.

Почему пользователь не видит таблицы после выдачи прав?

Проверьте:

  • Права USAGE на схему: GRANT USAGE ON SCHEMA schema_name TO user;
  • Права на конкретные таблицы
  • Наследование прав (NOINHERIT)
  • Права на последовательности для INSERT

Как безопасно удалить пользователя?

sql
-- Проверить активные сессии
SELECT pid, query FROM pg_stat_activity WHERE usename = 'user_to_delete';

-- Завершить сессии (если нужно)
SELECT pg_terminate_backend(pid) FROM pg_stat_activity 
WHERE usename = 'user_to_delete';

-- Передать объекты другому пользователю
REASSIGN OWNED BY old_user TO new_user;

-- Удалить привилегии
DROP OWNED BY old_user;

-- Удалить пользователя
DROP USER old_user;

Заключение

Грамотная настройка пользователей в PostgreSQL — это баланс между безопасностью и удобством. Начните с принципа минимальных привилегий, используйте групповые роли для упрощения управления, регулярно проводите аудит и обновляйте политики паролей. Помните, что хорошо настроенная система прав — это фундамент стабильной и безопасной работы вашей базы данных.

Ключевые рекомендации:
  • Всегда используйте сложные пароли и scram-sha-256 аутентификацию
  • Ограничивайте права роли PUBLIC
  • Ведите документацию по ролям и правам
  • Тестируйте настройки прав в staging-окружении
  • Настройте мониторинг подозрительной активности
Поделиться:
Сохранить гайд? В закладки браузера