Главная
→
Блог
→
Как работать с пользователями в PostgreSQL?

Как работать с пользователями в 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. Алгоритм создания пользователя здесь следующий:

В pgAdmin подключитесь к БД, зайдите в раздел Login / Group Roles, вызовите контекстное меню и выберете Create — Login / Group Role.
В открывшемся окне во вкладке General введите имя пользователя и, по желанию, его описание в поле Comments.
Во вкладке Definition задайте пароль и, по желанию, срок его действия.
Во вкладке Privileges выставите с помощью тумблеров выдайте пользователю необходимый набор прав: возможность логиниться, создавать другие роли и базы и т. д.
Во вкладке Membership присвойте пользователю нужные роли, например — дефолтную для PostgreSQL роль pg_monitor, позволяющую просматривать разные таблицы и представления, связанные с мониторингом БД.
Во вкладке 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";

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

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

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

Краткие выводы

Создать пользователя в PostgreSQL можно двумя способами: командой CREATE USER в консоли или через графический интерфейс pgAdmin, где права настраиваются с помощью переключателей.
Понятие «роль» в современных версиях PostgreSQL объединяет и пользователей, и группы. Команда CREATE USER по умолчанию создает роль с правом на подключение к БД (LOGIN).
Чтобы дать права пользователю в PostgreSQL, используется команда GRANT. Можно выдать как полные привилегии (ALL PRIVILEGES), так и разрешения на конкретные операции, например, SELECT или UPDATE.
Управлять пользователями в PostgreSQL можно с помощью команд ALTER USER для смены пароля и других атрибутов, а также DROP USER для полного удаления учетной записи.
Для задач резервного копирования рекомендуется создавать отдельную учетную запись с минимальными правами — только CONNECT на базу данных и SELECT для таблиц.
Суперпользователь создается с опцией SUPERUSER, но для повседневных задач и повышения безопасности следует использовать роли с ограниченным набором привилегий.

Частые вопросы по теме

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

Опыт работы: 13 лет опыта работы с базами данных, более 6 лет опыта работы архитектором БД и DBA. Опыт построения отказоустойчивых кластеров на базе СУБД PostgreSQL и GreenPlum 6x. Постоянный докладчик на Российских и международных IT конференциях.

Иван Чувашов

Ведущий инженер в Data Driven Lab / Сертифицированный администратор PostgreSQL (PostgresPro, 10 уровень «Эксперт»)

Нужна поддержка или планируете изменения в инфраструктуре?

Проблемы с производительностью, переход на PostgreSQL, нестабильная БД — у нас есть опыт, чтобы это исправить. Оставьте заявку — обсудим, чем можем помочь именно вам.

Оставить заявку

Наши топ-3 стратегии надежности

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

Поддержка 24/7 и аудит БД

Глубокий технический анализ производительности, безопасности и архитектуры. Выявляем узкие места, даём чёткие рекомендации и план оптимизации.

Подробнее
Мониторинг баз данных

Круглосуточный контроль за состоянием вашей базы данных.
Уведомления в случае отклонений, отчёты и превентивные меры. Обеспечиваем стабильность и безопасность.

Подробнее
Миграция на PostgreSQL

Мы поможем вам не просто "перейти" с Oracle или MSSQL, а модернизировать инфраструктуру и выйти на новый уровень надёжности.

Подробнее

Еще статьи по теме

Статья

Импорт и экспорт в PostgreSQL

Импорт данных в PostgreSQL, как выполнить? Как сделать экспорт базы данных PostgreSQL — читайте подробную инструкцию по импорту и экспорту бд