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

Опубликовано admin в 13.07.202213.07.2022

Часть нижеописанных операций нужно выполнять в командной оболочке PostgreSQL. Она может быть запущена от пользователя postgres — чтобы войти в систему от данного пользователя, вводим:

su - postgres

* если система выдаст ошибку, связанную с нехваткой прав, сначала повышаем привилегии командой sudo su или su.

или

sudo -i -u postgres

Теперь запускаем командную оболочку PostgreSQL:

$ psql -Upostgres template1

* в данном примере, вход выполняется от учетной записи postgres к шаблонной базе template1.

Для просмотра всех пользователей СУБД:

=# select * from pg_user;

Команды для просмотра информации о объктах

Отобразить все БД

\l

Для того чтоб подключиться к БД необходимо

\с <имя БД>

Для следующих команд необходимо подключить к БД

\z  - посмотреть все сущности

\dt - посмотреть таблицы

\ds - Постмотреть автоинкременты

\dn - Показать схемы

Создание нового пользователя

Для того, чтобы была возможность подключения к СУБД PostgreSQL от нового пользователя, необходимо создать данного пользователя, назначить ему права, выполнить настройку файла pg_hba.conf.

1. Создание пользователя

а) Добавление новой роли (пользователя) из оболочки SQL:

=# CREATE USER user_proger WITH PASSWORD 'myPassword';

* в примере создана роль user_proger с паролем myPassword.

б) Добавление новой роли (пользователя) из командной строки Linux:

createuser -P user_proger

Документаци по доступным опциям ссылка

2. Создание новой БД

Создаем БД и выдаем права для пользователя

CREATE DATABASE new_database2;
GRANT ALL PRIVILEGES ON DATABASE new_database2 TO user_proger;
ALTER DATABASE new_database2 OWNER TO user_proger;

Делаем владельцем всех сущностей созданного пользователя, так как при создании БД владельцем будет польщователь из под которого све создается и как правило это superuser postgres.

Для этого переходим в созданную БД при помощи команды

\c new_database2

И делаем владельцем сущностей нашего нового пользователя

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO user_proger;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO user_proger;
GRANT ALL ON SCHEMA public TO user_proger;

3. Назначение прав на использование базы данных

Даем права на базу командой:

=# GRANT ALL PRIVILEGES ON DATABASE "database1" to user_proger;

C 15 версии необходимо делать владельцем данного пользователя

=# ALTER DATABASE database1 OWNER TO user_proger;

Теперь подключаемся к базе, к которой хотим дать доступ:

=# \c database1

* в примере подсоединимся к базе с названием database1.

а) Так мы добавим все права на использование всех таблиц в базе database1 учетной записи user_proger:

database1=# GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO "user_proger";

* в большинстве случаев, используется схема по умолчанию public. Но администратор может создать новую схему. Это нужно учитывать при назначении прав.

даем права на SEQUENCES — это по сути индексы, в postgress они как отдельные сущности

database1=# GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO user_proger;

C 15 версии, если пользователь не владелец БД, необходимо дать права на на все операции (SELECT, INSERT, UPDATE, DELETE, REFERENCES, TRIGGER) в схеме public

database1=# GRANT ALL ON SCHEMA public TO user_proger;

б) Также можно дать доступ к базе для определенных таблиц:

database1=# GRANT ALL PRIVILEGES ON TABLE table1 IN SCHEMA public TO "user_proger";

* в данном примере мы даем права на таблицу table1.

Выходим из SQL-оболочки:

database1=# \q

4. Настройка файла pg_hba.conf

Для возможности подключиться к СУБД от созданного пользователя, необходимо проверить настройки прав в конфигурационном файле pg_hba.conf.

Для начала смотрим путь расположения данных для PostgreSQL:

=# SHOW config_file;

В ответ мы получим, что-то на подобие:

——————————————
/var/lib/pgsql/14/data/postgresql.conf
(1 row)

* в данном примере /var/lib/pgsql/14/data/ — путь расположения конфигурационных файлов.

Открываем pg_hba.conf:

sudo nano /var/lib/pgsql/14/data/pg_hba.conf

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

…
# IPv4 local connections:
host all user_proger 127.0.0.1/32 md5
…

* в данном примере мы разрешили подключаться пользователю user_proger ко всем базам на сервере (all) от узла 127.0.0.1 (localhost) с требованием пароля (md5).
* необходимо, чтобы данная строка была выше строки, которая прописана по умолчанию

host all all 127.0.0.1/32 ident.

После перезапускаем службу:

systemctl restart postgresql-9.6

* в данном примере установлен postgresql версии 9.6, для разных версий на разных операционных системах команды для перезапуска сервиса могут быть разные.

5. Проверка

Для теста пробуем подключиться к Postgre с помощью созданного пользователя:

psql -Uuser_proger template1 -h127.0.0.1

Настройка прав доступа к базе с помощью групп

Сначала создадим групповую роль:

=# CREATE ROLE "myRole" NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;

* данной командой создана группа myRole с минимальными правами.

Теперь добавим ранее созданного пользователя user_proger в эту группу:

=# GRANT "myRole" TO user_proger;

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

=# \c database1

и предоставим все права для группы myRole всем таблицам базы database1

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

Редактирование пользователя

1. Смена пароля

Рассмотрим несколько примеров смены пароля пользователя.

Одной командой:

=# ALTER USER postgres PASSWORD 'password'

* в данном примере мы зададим пароль password для пользователя postgres.

С запросов ввода пароля:

=# \password postgres

* после ввода данной команды система потребует дважды ввести пароль для пользователя (в нашем примере, postgres).

Из командной строки Linux:

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

* по сути, мы выполняем также запрос в оболочке sql.

Удаление пользователей и групп

Удаление пользователя выполняется следующей командой:

=# DROP USER user_proger;

Забрать права:

database1=# REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM "user_proger";

* обратите внимание, данный запрос отличается от предоставления прав двумя моментами: 1) вместо GRANT пишем REVOKE; 2) вместо TO «user_proger» пишем FROM «user_proger»;

Назначение особых прав пользователям PostgreSQL

Помимо ALL PRIVILEGES можно выдавать права на особые операции, например:

=# GRANT SELECT, UPDATE, INSERT ON ALL TABLES IN SCHEMA public TO "user_proger";

* команда позволит выдать права на получение данных, их обновление и добавление. Другие операции, например, удаление будут запрещены для пользователя user_proger.

Назначение прав для определенной таблицы:

database1=# GRANT ALL PRIVILEGES ON table_users TO "user_proger";

* в данном примере мы предоставим все права на таблицу table_users в базе данных database1;

Учетная запись для резервного копирования

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

Сначала создаем роль, которую будем использовать для выполнения резервного копирования:

=# CREATE USER bkpuser WITH PASSWORD 'bkppasswd';

* мы создадим учетную запись bkpuser с паролем bkppasswd.

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

=# GRANT CONNECT ON DATABASE database TO bkpuser;

* в данном примере к базе database.

Подключаемся к базе (в нашем примере database):

=# \c database

Даем права на все последовательности в схеме:

=# GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO bkpuser;

* мы дали права для схемы public. Это схема является схемой по умолчанию, но в вашем случае она может быть другой. В таком случае, подставляем свое значение.

Графический интерфейс

Иногда проще воспользоваться программой для выставления прав и работы с PostgreSQL. Могу посоветовать приложение pgAdmin. Оно позволит в оконном режиме не только создать и удалить пользователей, но и полноценно работать с СУБД.

Источник