Как работать с пользователями в PostgreSQL
Обновлено:
Опубликовано:
Что такое PostgreSQL простыми словами.
Часть нижеописанных операций нужно выполнять в командной оболочке PostgreSQL. Она может быть запущена командой:
$ psql -Upostgres template1
* в данном примере, вход выполняется от учетной записи postgres к шаблонной базе template1.
Для просмотра всех пользователей СУБД:
=# select * from pg_user;
Создание нового пользователя
Для того, чтобы была возможность подключения к СУБД PostgreSQL от нового пользователя, необходимо создать данного пользователя, назначить ему права, выполнить настройку файла pg_hba.conf.
1. Создание пользователя
Добавление новой роли (пользователя):
=# CREATE USER dmosk WITH PASSWORD 'myPassword';
* в примере создана роль dmosk с паролем myPassword.
2. Назначение прав на использование базы данных
Сначала необходимо подключиться к базе, с которой хотим работать:
=# \c database1
* в примере подсоединимся к базе с названием database1.
Так мы добавим все права на использование базы database1 учетной записи dmosk:
database1=# GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO "dmosk";
* в большинстве случаев, используется схема по умолчанию public. Но администратор может создать новую схему. Это нужно учитывать при назначении прав.
Выходим из SQL-оболочки:
database1=# \q
3. Настройка файла pg_hba.conf
Для возможности подключиться к СУБД от созданного пользователя, необходимо проверить настройки прав в конфигурационном файле pg_hba.conf.
Для начала смотрим путь расположения данных для PostgreSQL:
ps aux | grep postgres | grep -- -D
В ответ мы получим, что-то на подобие:
postgres 30728 0.0 1.5 359120 15676 ? Ss 14:49 0:00 /usr/pgsql-9.6/bin/postmaster -D /var/lib/pgsql/9.6/data/
* в данном примере последняя /var/lib/pgsql/9.6/data/ — путь расположения конфигурационных файлов.
Открываем pg_hba.conf:
vi /var/lib/pgsql/9.6/data/pg_hba.conf
Добавляем права на подключение нашему созданному пользователю:
...
# IPv4 local connections:
host all dmosk 127.0.0.1/32 md5
...
* в данном примере мы разрешили подключаться пользователю dmosk ко всем базам на сервере (all) от узла 127.0.0.1 (localhost) с требованием пароля (md5).
* необходимо, чтобы данная строка была выше строки, которая прописана по умолчанию
host all all 127.0.0.1/32 ident.
После перезапускаем службу:
systemctl restart postgresql-9.6
* в данном примере установлен postgresql версии 9.6, для разных версий на разных операционных системах команды для перезапуска сервиса могут быть разные.
4. Проверка
Для теста пробуем подключиться к Postgre с помощью созданного пользователя:
psql -Udmosk template1 -h127.0.0.1
Настройка прав доступа к базе с помощью групп
Сначала создадим групповую роль:
=# CREATE ROLE "myRole" NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
* данной командой создана группа myRole с минимальными правами.
Теперь добавим ранее созданного пользователя dmosk в эту группу:
=# GRANT "myRole" TO dmosk;
Подключимся к базе данных, для которой хотим настроить права
=# \c database1
и предоставим все права для группы myRole всем таблицам базы database1
database1=# GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO GROUP "myRole";
Удаление пользователей и групп
Удаление пользователя выполняется следующей командой:
=# DROP USER dmosk;
Забрать права:
database1=# REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM "dmosk";
* обратите внимание, данный запрос отличается от предоставления прав двумя моментами: 1) вместо GRANT пишем REVOKE; 2) вместо TO "dmosk" пишем FROM "dmosk";
Назначение особых прав пользователям PostgreSQL
Помимо ALL PRIVILEGES можно выдавать права на особые операции, например:
=# GRANT SELECT, UPDATE, INSERT ON ALL TABLES IN SCHEMA public TO "dmosk";
* команда позволит выдать права на получение данных, их обновление и добавление. Другие операции, например, удаление будут запрещены для пользователя dmosk.
Назначение прав для определенной таблицы:
database1=# GRANT ALL PRIVILEGES ON table_users TO "dmosk";
* в данном примере мы предоставим все права на таблицу table_users в базе данных database1;
Графический интерфейс
Иногда проще воспользоваться программой для выставления прав и работы с PostgreSQL. Могу посоветовать приложение pgAdmin. Оно позволит в оконном режиме не только создать и удалить пользователей, но и полноценно работать с СУБД.
