Как добавить нового пользователя в PostgreSQL

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

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

Основные способы создания пользователя

1. Команда CREATE ROLE (стандартный подход)

-- Создание пользователя с паролем и правом на подключение
CREATE ROLE username WITH LOGIN PASSWORD 'secure_password_123';

-- Альтернативная форма с теми же параметрами
CREATE USER username WITH PASSWORD 'secure_password_123';

Важно: CREATE USER - это просто алиас для CREATE ROLE WITH LOGIN, поэтому оба варианта рабочие.

2. Расширенное создание с дополнительными правами

-- Создание пользователя с расширенными атрибутами
CREATE ROLE developer WITH
  LOGIN
  PASSWORD 'DevPass2024!'
  CREATEDB      -- право создавать базы данных
  CREATEROLE    -- право создавать другие роли (осторожно!)
  VALID UNTIL '2025-01-01' -- срок действия учетной записи
  CONNECTION LIMIT 10;     -- ограничение одновременных подключений

Практические примеры из реальной эксплуатации

Пример для разработчика:

-- Пользователь для разработки с ограниченными правами
CREATE ROLE dev_user WITH
  LOGIN
  PASSWORD '${DEVELOPER_PASSWORD}'  -- на практике пароль передается переменной
  NOSUPERUSER
  INHERIT
  NOCREATEDB
  NOCREATEROLE
  NOREPLICATION
  CONNECTION LIMIT 5;

Пример для приложения:

-- Пользователь для приложения (обычно без права создания объектов)
CREATE ROLE app_user WITH
  LOGIN
  PASSWORD 'App$3cretPass'
  NOINHERIT;  -- не наследует права групп

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

После создания пользователя почти всегда нужно настроить его права:

-- Дать права на конкретную базу данных
GRANT CONNECT ON DATABASE production_db TO app_user;

-- Дать права на схему
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;

-- Дать права на выполнение функций
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO app_user;

Работа через командную строку (psql)

Для автоматизации в скриптах и CI/CD пайплайнах:

# Создание пользователя через psql с одной командой
psql -U postgres -c "CREATE USER deploy_user WITH PASSWORD '${DEPLOY_PASS}';"

# Создание с запросом пароля (более безопасно)
psql -U postgres -c "\password new_user"

Безопасность и best practices

1. Никогда не храните пароли в скриптах - используйте переменные окружения или системы управления секретами:

export PG_PASSWORD=$(aws secretsmanager get-secret-value --secret-id prod/pg/user-pass --query SecretString --output text)
psql -c "CREATE USER app_user WITH PASSWORD '$PG_PASSWORD';"

1. Используйте SSL соединения для защиты учетных данных в сети.

2. Ограничивайте привилегии по принципу минимальных необходимых прав:

-- Плохо: давать все права
GRANT ALL PRIVILEGES ON DATABASE mydb TO user;

-- Правильно: давать только необходимые
GRANT CONNECT, TEMPORARY ON DATABASE mydb TO user;
GRANT SELECT ON TABLE logs TO readonly_user;

1. Регулярно ротируйте пароли и используйте сложные политики паролей.

Проверка и управление существующими пользователями

-- Посмотреть всех пользователей
\du  -- в psql

SELECT usename, usesuper, usecreatedb, valuntil 
FROM pg_user 
ORDER BY usename;

-- Изменить пароль существующему пользователю
ALTER ROLE username WITH PASSWORD 'new_secure_password';

-- Удалить пользователя (осторожно!)
DROP ROLE IF EXISTS username;

Интеграция с системами аутентификации

PostgreSQL поддерживает различные методы аутентификации через pg_hba.conf:

# В pg_hba.conf
host    all             app_user     10.0.0.0/8          md5
hostssl production_db   dev_user     0.0.0.0/0           scram-sha-256
local   replication     backup       127.0.0.1/32        peer

Автоматизация в DevOps-практиках

В инфраструктуре как код (Terraform, Ansible):

# Пример Terraform для PostgreSQL пользователя
resource "postgresql_role" "app_user" {
  name     = "application_user"
  password = random_password.db_password.result
  login    = true
  
  lifecycle {
    ignore_changes = [password]
  }
}

При добавлении нового пользователя в продакшн-окружении всегда:

1. Создавайте через миграции базы данных
2. Логируйте операцию для аудита
3. Интегрируйте с системой мониторинга
4. Тестируйте подключение перед использованием в приложении

Помните, что в PostgreSQL управление доступом - это многоуровневая система (роли → базы данных → схемы → таблицы/функции), и правильная настройка каждого уровня критически важна для безопасности.