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

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

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

su – postgres

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

$ psql -U postgres template1

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

select * from pg_user;

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

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

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

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

CREATE USER user123 WITH PASSWORD 'myPassword';

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

createuser -P user123

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

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

GRANT ALL PRIVILEGES ON DATABASE "database1" to user123;

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

\c database1

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

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO "user123";

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

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

GRANT ALL PRIVILEGES ON TABLE table1 IN SCHEMA public TO "user123";

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

\q

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

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

vi /var/lib/pgsql/12/data/pg_hba.conf

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

# IPv4 local connections:
host    all             user123           127.0.0.1/32            md5

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

host    all             all             127.0.0.1/32            ident

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

systemctl restart postgresql

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

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

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

psql -U user123 template1 -h 127.0.0.1

дбаас

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

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

CREATE ROLE "myRole" NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;

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

GRANT "myRole" TO user123;

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

\c database1

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

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO GROUP "myRole";

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

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

ALTER USER postgres PASSWORD 'password'

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

sudo -u postgres psql -U postgres -d postgres -c "ALTER USER postgres PASSWORD 'password'"

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

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

DROP USER user123;

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

REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM "user123";

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

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

GRANT SELECT, UPDATE, INSERT ON ALL TABLES IN SCHEMA public TO "user123";

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

GRANT ALL PRIVILEGES ON table_users TO "user123";

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

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

CREATE USER bkpuser WITH PASSWORD 'bkppasswd';

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

GRANT CONNECT ON DATABASE database TO bkpuser;

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

\c database

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

GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO bkpuser;

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

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

Запустите облачную базу в один клик

Выводы

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

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