Как создать пользователя в PostgreSQL

Несколько способов создания пользователя в PostgreSQL.

Введение

В этой статье покажем несколько способов, как создать пользователя в PostgreSQL, включить его в группы и назначить права напрямую, а также поменяем пароль. У вас должен быть сервер с установленным Postgres, если его нет — почитайте нашу статью про установку PostgreSQL на примере Ubuntu.

Создание пользователя PostgreSQL с помощью CREATE USER

Перед тем, как рассказывать о создании пользователей, мы объясним, как устроены роли в Postgres. В современных версиях этой СУБД нет понятий «пользователь» или «группа», они объединены в одну сущность — «роль». И хотя в Postgres есть отдельные команды для создания пользователя или роли, на самом деле они создают одну и ту же сущность. Роль может иметь возможность входа в систему (то есть подключаться к БД и выполнять запросы) или не иметь ее.

Для удобства во многих руководствах и инструментах роль с возможностью входа называется «пользователь», а роль без этой возможности — «группа» или «групповая роль». В этой статье мы тоже будем придерживаться этих наименований.

Итак, существует несколько способов создания пользователя в командной строке:

CREATE ROLE — SQL-команда, которая выполняется на сервере базы данных. По умолчанию создается роль с параметром NOLOGIN, который означает, что эта роль не может залогиниться в СУБД. Но если указать явно параметр LOGIN, тогда эта команда будет эквивалентна созданию пользователя.

Синтаксис:

CREATE ROLE <название> [

Примеры использования:

CREATE ROLE selectel_user LOGIN PASSWORD ‘bestpass’; — создать пользователя и установить ему пароль.
CREATE ROLE selectel_user WITH LOGIN PASSWORD ‘bestpass’ VALID UNTIL ‘2022-12-31’; — создать пользователя, установить ему пароль и срок действия учетной записи до конца 2022 года.

CREATE USER — тоже SQL-команда, которая выполняется на сервере. Это алиас к предыдущей команде CREATE ROLE. Единственно отличие в том, что эта команда по умолчанию использует параметр LOGIN, который разрешает логиниться в базу данных.

Синтаксис и примеры использования аналогичны команде CREATE ROLE.

createuser — утилита операционной системы, которая поставляется вместе с сервером. Это тоже не самостоятельно решение, а просто обертка над SQL-командой CREATE ROLE.

Синтаксис:

createuser [параметр-подключения...] [параметр...] [имя-пользователя]

Примеры использования:

createuser selectel_user -P; — создать нового пользователя и установить ему пароль (терминал запросит пароль после ввода команды).
createuser selectel_user -P -g selectel_group; — создать пользователя, установить ему пароль и добавить его в группу selectel_group.

Как создать пользователя PostgreSQL в графическом интерфейсе

Кроме командной строки, существует множество утилит для работы с Postgres в графическом окружении. Мы будем пользоваться pgAdmin, но аналогичные действия можно выполнить и в любой другой подобной программе.

С помощью pgAdmin подключимся к базе данных и в разделе Login/Group Roles вызовем контекстное меню и выберем Create — Login/Group Role.

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

На вкладке Definition зададим пароль. Тут же можно указать дату истечения, после которого пользователю обязательно нужно будет сменить пароль:

На вкладке Privileges можно указать, какими полномочиями будет обладать пользователь: логиниться, создавать другие роли или базы данных, будет ли это суперпользователь и т.п. Мы выберем лишь опцию Login, остальное оставим по умолчанию:

На вкладке Membership можно присвоить пользователю роли. Для примера мы присвоим ему встроенную в Postgres роль pg_monitor, которая дает права на просмотр различных таблиц и представлений, связанных с мониторингом и диагностикой базы данных:

На последней вкладке SQL можно увидеть SQL-команды, с помощью которых будет создан пользователь. Запоминать и копировать их не обязательно, pgAdmin выполнит их за нас. Эта вкладка нужна скорее для финальной проверки.

Нажимаем на кнопку Save, и пользователь создается. Мы можем увидеть его в разделе Login/Group Roles:

Добавление и удаление прав с помощью групп в базе данных PostgreSQL

В Postgres роли могут наследоваться от других ролей. А так как роль — это либо «пользователь», либо «группа», получается, что пользователи могут наследовать привилегии от групп или других пользователей. Мы рассмотрим настройку с наследованием от групп. Создадим две группы: одну с правами только на чтение данных из таблицы, а другую — с правами на чтение и запись. По очереди назначим пользователю эти группы и посмотрим на результат.

Создаем группы:

CREATE ROLE "read_only_role";
CREATE ROLE "read_write_role";

Мы создали группы, но не дали им никаких привилегий. В нашей БД есть таблица first_table, на которой мы будем проверять работу назначения привилегий. Группе read_only_role дадим права на чтение этой таблицы, а группе read_write_role — на чтение и запись:

GRANT SELECT ON TABLE first_table TO read_only_role;
GRANT SELECT, INSERT, UPDATE, DELETE on table first_table TO read_write_role;

Группы созданы, но наш пользователь еще не унаследовал их привилегии. Для начала убедимся, что у него нет прав на чтение данных из таблицы. Залогинимся в БД под пользователем selectel_user и выполним команду:

SELECT * FROM first_table;

Получим ошибку:

ERROR: permission denied for table first_table

Теперь из под учетной записи владельца присвоим пользователю роль на чтение:

GRANT read_only_role TO selectel_user;

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

id | message  
----+---------
 1 | Привет!
(1 row)

Теперь попробуем вставить строку в таблицу:

INSERT INTO first_table VALUES (2, 'Hello!');

Получим ошибку:

ERROR: permission denied for table first_table

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

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

REVOKE read_write_role FROM selectel_user;
GRANT read_write_role TO selectel_user;

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

id | message  
----+---------
 1 | Привет!
 2 | Hello!
(2 rows)

Успешно — у пользователя появились права на запись в таблицу.

Изменение пользователя: добавление привилегий напрямую и смена пароля

Теперь попробуем присвоить привилегии пользователю напрямую, а не через группы. В нашей схеме есть еще одна таблица, на которую у пользователя пока что нет прав: second_table. Убедимся в этом:

SELECT * FROM second_table;

Результат:

ERROR: permission denied for table second_table

Добавим пользователю привилегии на эту таблицу напрямую. Это делается аналогично назначению привилегий на группу, только вместо группы укажем имя пользователя. В этот раз вместо указания конкретных привилегий мы воспользуемся конструкцией ALL PRIVILEGES, которая сразу дает все полномочия на таблицу:

GRANT ALL PRIVILEGES ON TABLE second_table TO selectel_user;

Снова проверим содержимое этой таблицы и увидим результат:

id | message
----+-----------
 1 | Hello again!
(1 row)

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

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

ALTER USER selectel_user PASSWORD 'newpass';

Заключение

Мы узнали, что между группой и пользователем в PostgreSQL практически нет никакой разницы, рассмотрели несколько способов создания пользователя и добавления ему групп, поработали в консоли и воспользовались графической утилитой pgAdmin.

Базы данных