Postgresql как создать пользователя windows

Введение

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

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

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

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

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

1. 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 года.

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

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

1. 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.

PostgreSQL — это бесплатная объектно-реляционная СУБД с мощным функционалом, который позволяет конкурировать с платными базами данных, такими как Microsoft SQL, Oracle. PostgreSQL поддерживает пользовательские данные, функции, операции, домены и индексы. В данной статье мы рассмотрим установку и краткий обзор по управлению базой данных PostgreSQL. Мы установим СУБД PostgreSQL в Windows 10, создадим новую базу, добавим в неё таблицы и настроим доступа для пользователей. Также мы рассмотрим основы управления PostgreSQL с помощью SQL shell и визуальной системы управления PgAdmin. Надеюсь эта статья станет хорошей отправной точкой для обучения работы с PostgreSQL и использованию ее в разработке и тестовых проектах.

Установка PostgreSQL 11 в Windows 10

Для установки PostgreSQL перейдите на сайт https://www.postgresql.org и скачайте последнюю версию дистрибутива для Windows, на сегодняшний день это версия PostgreSQL 11 (в 11 версии PostgreSQL поддерживаются только 64-х битные редакции Windows). После загрузки запустите инсталлятор.

В процессе установки установите галочки на пунктах:

• PostgreSQL Server – сам сервер СУБД
• PgAdmin 4 – визуальный редактор SQL
• Stack Builder – дополнительные инструменты для разработки (возможно вам они понадобятся в будущем)
• Command Line Tools – инструменты командной строки

Установите пароль для пользователя postgres (он создается по умолчанию и имеет права суперпользователя).

По умолчание СУБД слушает на порту 5432, который нужно будет добавить в исключения в правилах фаерволла.

Нажимаете Далее, Далее, на этом установка PostgreSQL завершена.

Доступ к PostgreSQL по сети, правила файерволла

Чтобы разрешить сетевой доступ к вашему экземпляру PostgreSQL с других компьютеров, вам нужно создать правила в файерволе. Вы можете создать правило через командную строку или PowerShell.

Запустите командную строку от имени администратора. Введите команду:

netsh advfirewall firewall add rule name="Postgre Port" dir=in action=allow protocol=TCP localport=5432

• Где rule name – имя правила
• Localport – разрешенный порт

Либо вы можете создать правило, разрешающее TCP/IP доступ к экземпляру PostgreSQL на порту 5432 с помощью PowerShell:

New-NetFirewallRule -Name 'POSTGRESQL-In-TCP' -DisplayName 'PostgreSQL (TCP-In)' -Direction Inbound -Enabled True -Protocol TCP -LocalPort 5432

После применения команды в брандмауэре Windows появится новое разрешающее правило для порта Postgres.

Утилиты управления PostgreSQL через командную строку

Рассмотрим управление и основные операции, которые можно выполнять с PostgreSQL через командную строку с помощью нескольких утилит. Основные инструменты управления PostgreSQL находятся в папке bin, потому все команды будем выполнять из данного каталога.

• Запустите командную строку.

  Совет. Перед запуском СУБД, смените кодировку для нормального отображения в русской Windows 10. В командной строке выполните:
  chcp 1251

• Перейдите в каталог bin выполнив команду:
  CD C:\Program Files\PostgreSQL\11\bin

Основные команды PostgreSQL:

PgAdmin: Визуальный редактор для PostgresSQL

Редактор PgAdmin служит для упрощения управления базой данных PostgresSQL в понятном визуальном режиме.

По умолчанию все созданные базы хранятся в каталоге base по пути C:\Program Files\PostgreSQL\11\data\base.

Для каждой БД существует подкаталог внутри PGDATA/base, названный по OID базы данных в pg_database. Этот подкаталог по умолчанию является местом хранения файлов базы данных; в частности, там хранятся её системные каталоги. Каждая таблица и индекс хранятся в отдельном файле.

Для резервного копирования и восстановления лучше использовать инструмент Backup в панели инструментов Tools. Для автоматизации бэкапа PostgreSQL из командной строки используйте утилиту pg_dump.exe.

Query Tool: использование SQL запросов в PostgreSQL

Для написания SQL запросов в удобном графическом редакторе используется встроенный в pgAdmin инструмент Query Tool. Например, вы хотите создать новую таблицу в базе данных через инструмент Query Tool.

• Выберите базу данных, в панели Tools откройте Query Tool
• Создадим таблицу сотрудников:

CREATE TABLE employee
(
Id SERIAL PRIMARY KEY,
FirstName CHARACTER VARYING(30),
LastName CHARACTER VARYING(30),
Email CHARACTER VARYING(30),
Age INTEGER
);

Id — номер сотрудника, которому присвоен ключ SERIAL. Данная строка будет хранить числовое значение 1, 2, 3 и т.д., которое для каждой новой строки будет автоматически увеличиваться на единицу. В следующих строках записаны имя, фамилия сотрудника и его электронный адрес, которые имеют тип CHARACTER VARYING(30), то есть представляют строку длиной не более 30 символов. В строке — Age записан возраст, имеет тип INTEGER, т.к. хранит числа.

После того, как написали код SQL запроса в Query Tool, нажмите клавишу F5 и в базе будет создана новая таблица employee.

Для заполнения полей в свойствах таблицы выберите таблицу employee в разделе Schemas -> Tables. Откройте меню Object инструмент View/Edit Data.

Здесь вы можете заполнить данные в таблице.

После заполнения данных выполним инструментом Query простой запрос на выборку:
select Age from employee;

Обновлено: 22.03.2023
Опубликовано: 20.07.2016

Что такое PostgreSQL простыми словами.

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

su — postgres

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

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

$ psql -Upostgres template1

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

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

=# select * from pg_user;

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

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

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

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

=# CREATE USER dmosk WITH PASSWORD ‘myPassword’;

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

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

createuser -P dmosk

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

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

=# GRANT ALL PRIVILEGES ON DATABASE «database1» to dmosk;

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

=# \c database1

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

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

database1=# GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO «dmosk»;

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

Посмотреть список всех схем можно командой:

database1=# \dn

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

database1=# GRANT ALL PRIVILEGES ON TABLE table1 IN SCHEMA public TO «dmosk»;

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

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

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

Для начала смотрим путь до него:

=# SELECT current_setting(‘hba_file’);

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

—————————————— 
/var/lib/pgsql/9.6/data/pg_hba.conf
(1 row)

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

=# \q

Возвращается в командную строку суперпользователя:

$ exit

Открываем 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. Проверка

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

psql -U dmosk -d database1 -h 127.0.0.1

* в данном примере мы подключается к локальному серверу 127.0.0.1 под пользователем dmosk и базе database1, на которую мы дали ему права.

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

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

=# 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»;

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

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 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;

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

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

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

=# 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. Оно позволит в оконном режиме не только создать и удалить пользователей, но и полноценно работать с СУБД.