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
DBaaS
оптимизируйте процессы DevOps и CI/CD.
Настроим права доступа к БД через группу
Первое действие – создадим групповую роль:
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: резервное копирование и восстановление.
Запустите облачную базу в один клик
447 ₽/мес
711 ₽/мес
Выводы
Мы завершили рассмотрение методики, как создавать пользователей PostgreSQL через консоль и настраивать их права. Иногда удобнее работать через приложения с графическим интерфейсом вроде pgAdmin, это на выбор пользователя. Поэкспериментировать без риска повредить рабочие БД проще на арендованных мощностях провайдера Timeweb Cloud.
Кстати, в официальном канале Timeweb Cloud собрали комьюнити из специалистов, которые говорят про IT-тренды, делятся полезными инструкциями и даже приглашают к себе работать.
