Как работать с пользователями в PostgreSQL: создать, добавить и удалить

В PostgreSQL управление пользователями осуществляется с помощью ролей (Roles). Используется команда CREATE USER, которая является сокращенной формой команды CREATE ROLE с преднастроенными правами. 

Использование команды CREATE USER

Команда CREATE USER позволяет добавить нового пользователя базы данных и задать ему релевантные права. Выполняйте команду в терминале через psql или в любом SQL-клиенте.

Как работать:

1. Подключитесь к PostgreSQL:

2. Создайте пользователя: 

3. Назначьте права (например, разрешите создавать базы): 

4. При необходимости добавьте роль SUPERUSER:

5. Назначьте доступ к базе: 

Создание пользователя через графический интерфейс (pgAdmin)

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

1. Запустите pgAdmin и подключитесь к вашему PostgreSQL-серверу.

2. Перейдите в раздел ролей, в дереве объектов разверните Servers → Login/Group Roles → Create → Login/Group Role.

3. Нажмите на Login/Group Roles и выберите Create → Login/Group Role.

4. В строке General укажите Role Name (имя роли).

5. В Definition задайте пароль для пользователя. 

6. В Privileges установите необходимые права. 

7. В Role Membership добавьте пользователя в нужные группы.

8. Нажмите Save и сохраните роль. 

Для управления доступом в PostgreSQL используются команды GRANT и REVOKE (отзыв прав). С их помощью можно предоставлять права ролям в отношении конкретных объектов базы данных.

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

Права назначаются на различных уровнях — на уровне кластера/базы данных, таблиц, схем и объектов. 

Основные права на уровне базы данных:

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

На схеме — процесс назначения прав. 

Использование групп и ролей

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

С помощью ролей удобно объединять пользователей по задачам. Например, можно добавить сотрудника в команду разработки — new_dev и сделать членом групповой роли — dev_team. Тогда сотрудник автоматически наследует все необходимые права.

Создайте роль-группу с помощью команды:

Назначьте привилегии группе:

Включите пользователя в роль:

Для модификации существующих пользователей и их атрибутов в PostgreSQL используется команда ALTER ROLE. Изменения можно выполнять из командной строки либо через pgAdmin.

Смена пароля

Пароль можно поменять с помощью команды: ALTER USER username WITH PASSWORD 'new_password'. Задавая комбинацию, учитывайте следующие моменты: 

• используйте длинные пароли — от 12 символов для корпоративных систем (некоторые стандарты требуют 15+ символов);

• комбинируйте буквы в разных регистрах, цифры и символы;

• откажитесь от банальных паролей вроде даты рождения, имен и фамилий, admin и т.д;

• не используйте один и тот же пароль для разных пользователей;

• меняйте пароли хотя бы раз в 3 месяца, а лучше — чаще. 

Дополнительные привилегии для пользователей

При необходимости можно выдать пользователю новые права или изменить существующие с помощью команды ALTER USER. 

Доступ к таблицам или базе данных можно дать через GRANT:

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

Удаление пользователя через DROP USER

Проверьте, какими объектами владеет пользователь:

Передайте владение другому пользователю или удалите объекты. Для передачи владения воспользуйтесь командой:

Проверьте роли и членство в группах:

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

Временное отключение пользователя без удаления

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

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

• ALTER USER username NOLOGIN; (требует явного LOGIN для разблокировки);

• REVOKE CONNECT ON DATABASE mydb FROM username; (только для конкретной БД).

При этом, если в pg_hba.conf используется метод "trust", пользователь может подключиться даже с NOLOGIN.

Чтобы было понятнее, как использовать команды, приведем примеры распространенных действий с ролями. 

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

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

Теперь удалим пользователя temp_tester, предварительно проверив, не владеет ли он критическими объектами:

Работа с конфигурациями через pg_hba.conf

Файл pg_hba.conf — это основной файл конфигурации безопасности в PostgreSQL. Он определяет, кто может подключаться к серверу, к какой базе данных, с какого хоста (IP), какой метод аутентификации будет использован.

Например, нам нужно разрешить пользователю analytics_reader подключаться к базе data_warehouse с любого хоста в локальной подсети 192.168.1.0/24, используя пароль, зашифрованный по методу scram-sha-256. Делаем следующие записи в файле:

Также обновите postgresql.conf:

После изменения pg_hba.conf необходимо применить конфигурацию одним из способов:

- Выполнить: 

- Перезагрузить сервер:

- Отправить сигнал SIGHUP: 

Грамотное управление пользователями в PostgreSQL — фундамент безопасности и стабильности вашей IT-инфраструктуры. Применяйте команды CREATE USER, GRANT и REVOKE, придерживайтесь принципа минимальных привилегий, устанавливайте сложные пароли и регулярно пересматривайте права доступа.​

Для упрощения администрирования рекомендуем использовать Evolution Managed PostgreSQL от Cloud.ru. Сервис берет на себя рутинные задачи: автоматическое резервное копирование, мониторинг производительности, обновления безопасности и масштабирование ресурсов, что позволяет сосредоточиться на разработке и бизнес-логике, не беспокоясь об инфраструктуре базы данных.