+7(495) 374-84-96
Связаться с нами
Telegram
 Mail
 WhatsApp

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

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

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

Существует два основных способа создавать пользователей PostgreSQL: из консоли с помощью команды CREATE USER или из графического интерфейса pgAdmin с помощью SQL-запроса. Ниже мы рассмотрим подробнее каждый из вариантов.
Однако, чтобы не выдать никому лишних прав доступа и не поставить бизнес под угрозу, для создания пользователей мы советуем обращаться в «ДБ-сервис»: наши инженеры специализируются на работе с PostgreSQL и могу гарантировать защиту ваших данных.

CREATE USER

В PostgreSQL с помощью терминальной программы psql откройте командную строку. В ней выполните одну из следующих команд, где name и pass меняются на нужные значения:
  • Для создания суперпользователя: 
CREATE USER name WITH PASSWORD 'pass' SUPERUSER;
  • Для создания обычного пользователя: 
CREATE USER name WITH PASSWORD 'pass';
  • Для создания пользователя с дополнительными параметрами (например, лимит на число одновременных подключений к БД): 
CREATE USER name WITH PASSWORD 'pass' CONNECTION LIMIT 5;

В графическом интерфейсе

Существует много программ с графическим интерфейсом для работы с PostgreSQL, однако наиболее распространенной считается pgAdmin. Алгоритм создания пользователя здесь следующий:
  1. В pgAdmin подключитесь к БД, зайдите в раздел Login / Group Roles, вызовите контекстное меню и выберете Create — Login / Group Role.
  2. В открывшемся окне во вкладке General введите имя пользователя и, по желанию, его описание в поле Comments.
  3. Во вкладке Definition задайте пароль и, по желанию, срок его действия.
  4. Во вкладке Privileges выставите с помощью тумблеров выдайте пользователю необходимый набор прав: возможность логиниться, создавать другие роли и базы и т. д.
  5. Во вкладке Membership присвойте пользователю нужные роли, например — дефолтную для PostgreSQL роль pg_monitor, позволяющую просматривать разные таблицы и представления, связанные с мониторингом БД.
  6. Во вкладке SQL приводятся SQL-команды, отвечающие за применение настроек, выполненных в предыдущих вкладках. От вас требуется лишь нажать Save, после чего пользователь будет создан и доступен в разделе Login / Group Roles.

Роли: пользователи и их группы в PostgreSQL

В современных версиях PostgreSQL понятие роли включает в себя и пользователей и их группы. Для создания ролей может использоваться как команда CREATE ROLE, так и CREATE USER.
CREATE ROLE как правило выполняется, когда нужно создать роль с правами Read Only — без возможности подключения к серверу PostgreSQL и внесения изменений в БД.
CREATE USER же чаще выполняется, когда создается роль с возможностью входа в систему и выполнения операций в БД.

Как изменить пользователя в PostgreSQL?

Изменение пользователей в PostgreSQL включает в себя различные действия, такие как изменение пароля, удаление пользователей и предоставление им дополнительных привилегий.
Смена пароля
Пароль может меняться с помощью одного из трех вариантов:
  • Командой =# ALTER USER postgres PASSWORD 'password'
  • Запросом ввода пароля: =# \password postgres
  • Из консоли Linux: sudo -u postgres psql -U postgres -d postgres -c "ALTER USER postgres PASSWORD 'password'"
Удаление пользователей PostgreSQL
Пользователь PostgreSQL может быть удален и лишен прав с помощью выполнения определенных команд.
  • Удаление: =# DROP USER username;
  • Лишение прав: database1=# REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM "username";
Выдача особых прав
Кроме выдачи суперпользовательских прав ALL PRIVILEGES в PostgreSQL можно выдавать правда для конкретных операций и таблиц.
  • Чтобы выдать разрешение на получение, обновление и добавление данных, нужно выполнить: =# GRANT SELECT, UPDATE, INSERT ON ALL TABLES IN SCHEMA public TO "username";
  • Чтобы выдать набор прав для конкретной таблицы нужно выполнить: "database_name" =# GRANT ALL PRIVILEGES ON "table_nsme" TO "username";

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

Одно из правил резервного копирования заключается в подключении к БД с пользователя с минимальным уровнем доступа.
  1. Создайте для этого специальную роль командой: =# CREATE USER "username" WITH PASSWORD "passwd";
  2. Затем выдайте права на подключение к базе: =# GRANT CONNECT ON DATABASE database TO username;
  3. Подключитесь к БД: =# \c database
  4. Выдайте права public пользователю bkpuser, используя команду =# GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO bkpuser; По дефолту в PostgreSQL выставлена схема public; если вы планируете задействовать другую, измените в команде это значение.

Резюме

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

Эксперт ДБ-сервис

Список литературы:
1. Как создать пользователя в PostgreSQL. – Текст: электронный // selectel.ru: интернет-ресурс.
2. Как создать пользователя PostgreSQL: инструкция. – Текст: электронный // timeweb.cloud: интернет-ресурс.
3. PostgreSQL Basics: Roles and Privileges. – Текст: электронный // Redgate Hyb: интернет-ресурс.
4. PostgreSQL 16.1 Documentation. – Текст: электронный // The PostgreSQL Global Development Group: интернет-ресурс.