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

PostgreSQL – система управления базами данных (СУБД) с открытым исходным кодом. Ее работа основана на стандартном языке запросов SQL. Системные администраторы выбирают этот инструмент по нескольким причинам: бесплатное использование, высокая производительность практически на любой аппаратной платформе. В нашем случае достаточно арендовать один из тарифов облачных баз данных у провайдера Timeweb Cloud.

В этом материале мы разберем, как создать и удалить пользователя в PostgreSQL, настроить права доступа, как использовать учетную запись на практике (на примере создания резервных копий). Отметим, что приведенные в статье процедуры выполняются в оболочке PostgreSQL. Ее можно запустить от имени аккаунта postgres:

При выдаче ошибки о недостаточных правах – повысьте их командой sudo su или su. Теперь можно стартовать саму командную оболочку:

где template1 – это шаблонный пример БД, в своем случае укажите любой другой. Работа будет идти под учеткой postgres. Перед дальнейшими действиями просмотрим список уже существующих пользователей СУБД:

Создадим новый аккаунт

Первое, что нам понадобится сделать – создать пользователя с паролем. Также надо назначить ему определенные привилегии через настройку в файле pg_hba.conf.

Шаг 1. Создадим пользователя

Зададим роль пользователя, пример команды из оболочки SQL:

То же, но при помощи командной строки Linux:

Шаг 2. Назначим права для операций с БД

Права зададим командой:

Теперь можно активировать подключение к базе:

Зададим права на работу с таблицами в рассматриваемой нами БД database1 и учетке user123:

Отметим, что по умолчанию система настроена на схему public, но пользователю доступно ее изменение и выбор новой.

При назначении прав есть возможность указать определенную таблицу:

Пользователь PostgreSQL создан, можно закрывать оболочку и работать дальше:

Шаг 3. Настроим файл pg_hba.conf

Проверим, какие привилегии заданы без изменения настроек. Они записаны в файле pg_hba.conf. Откроем его на редактирование:

Следующий шаг управления пользователем PostgreSQL – добавление прав новому аккаунту:

Команда позволяет пользователю с именем user123 подключаться к любым базам, размещенным на сервере. Важно внести указанную информацию выше строки, имеющейся по умолчанию:

Чтобы настройки применились, перезапустим службу:

В этом примере речь идет о СУБД PostgreSQL12.

Шаг 4. Протестируем работоспособность БД

Проверим подключение только что созданного пользователя:

Настроим права доступа к БД через группу

Первое действие – создадим групповую роль:

Следующим шагом внесем в нее нашего пользователя user123:

Теперь можно подключаться к БД:

И настроить привилегии для группы myRole:

Отредактируем пользователя

Перенастроить систему так же просто, как создать пользователя администратора. Для примера изменим пароль ранее созданного нами аккаунта. Выполняется это одной командой:

Та же операция доступна и в командной строке Linux:

Удалим аккаунт и группу

Команда для удаления пользователя выглядит так:

Вместо удаления можно ограничить аккаунт в правах:

Назначим особые привилегии существующему аккаунту

При управлении пользователями и полномочиями в СУБД PostgreSQL можно задавать как «полные» права ALL PRIVILEGES, так и особые:

Приведенный пример разрешает пользователю user123 считывать, редактировать и добавлять новые данные. Но удалять ничего нельзя, такие операции команда запрещает. Если требуется назначить права для работы с конкретной таблицей, команда примет такой вид:

Создадим учетку для резервирования БД

Создавать резервные копии рекомендуем с минимальными правами. Создадим роль пользователя на чтение PostgreSQL для выполнения процедуры:

Здесь мы создали учетку bkpuser и задали ей пароль bkppasswd, их можно заменить на свои. Следом активируем возможность подключаться к базе:

Можно подключиться к БД:

И предоставлять требуемые права:

В этом примере использована схема public, ее можно заменить на другую.

Подробнее о резервном копировании PostgreSQL мы писали в статье Дампы в PostgreSQL: резервное копирование и восстановление.

Выводы

Мы завершили рассмотрение методики, как создавать пользователей PostgreSQL через консоль и настраивать их права. Иногда удобнее работать через приложения с графическим интерфейсом вроде pgAdmin, это на выбор пользователя. Поэкспериментировать без риска повредить рабочие БД проще на арендованных мощностях провайдера Timeweb Cloud.

Кстати, в официальном канале Timeweb Cloud собрали комьюнити из специалистов, которые говорят про IT-тренды, делятся полезными инструкциями и даже приглашают к себе работать.