Блог sql-ex

Для выполнения операций с базами данных в PostgreSQL создаются пользователи с различными правами доступа. В статье расскажем о том, как создать пользователя в PostgreSQL разными способами. Чтобы выполнить приведенные ниже рекомендации, сначала установите эту СУБД на сервер. Порядок установки на Ubuntu (Linux) и Windowsв этой статье

Почему вам следует использовать psql

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

Однако спустя некоторое время я понял, что зачастую много проще запрыгнуть в базу данных с помощью psql, когда мне просто необходимо подключиться, взглянуть на схему и выполнить простые запросы. Использование мета-команд становится второй натурой при получении подробной информации об объектах базы данных. Это подобно инструменту с сотнями эффективных примочек для быстрой работы с базой данных.

Более привлекательно то, что вы можете так сконфигурировать psql, что наряду с результатами будет возвращаться SQL, который она выполняет для каждой команды. Это может стать окном в изучение многих лежащих в основе каталога PostgreSQL таблиц, которые управляют тем, как используется база данных.

И последнее, важно, что во многих (многих!) статьях, руководствах, книгах и видео, которые показывают, как использовать функции PostgreSQL, демонстрация понятий будет происходить с помощью psql. Знание того, как подключиться с помощью этого инструмента и выполнить основные мета-команды, быстро помогут вам двигаться вперед.

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

Чтобы установить и настроить PostgreSQL:

Загрузите и установите PostgreSQL.
Зайдите на сайт https://www.enterprisedb.com/downloads/postgres-postgresql-downloads, чтобы просмотреть список поддерживаемых операционных систем и загрузить инсталлятор.
Откройте конфигурационный файл postgresql.conf. Этот файл находится в папке %postgresql_dir%\data. Здесь %postgresql_dir% (например, C:\Program Files\PostgreSQL\11) это папка, в которую был установлен PostgreSQL.
Укажите IP-адрес, который решение Kaspersky Scan Engine должно использовать для соединения с PostgreSQL, в настройке listen_addresses конфигурационного файла postgresql.conf.
Укажите порт, который СУБД PostgreSQL должна прослушивать в ожидании соединений от Kaspersky Scan Engine, в настройке port конфигурационного файла postgresql.conf.
Для PostgreSQL версии 13 или более поздней найдите следующую строку в postgresql.conf:
password_encryption = md5 # md5 или scram-sha-256

Если метод шифрования пароля, указанный в этой строке, отличается от md5, замените его на md5.
Сохраните и закройте postgresql.conf.
Откройте конфигурационный файл pg_hba.conf на редактирование. Этот файл расположен в той же папке, что и файл postgresql.conf.
Убедитесь, что для PostgreSQL требуются пароли, зашифрованные по алгоритму MD5, для аутентификации всех ее клиентов. Найдите следующую строку в файле pg_hba.conf:
host all all 127.0.0.1/32 md5
Если метод аутентификации, указанный в этой строке, отличается от md5, замените его на md5.
Если PostgreSQL и Kaspersky Scan Engine установлены на разных компьютерах, добавьте следующую строку в файл pg_hba.conf:
host all all %IP%/32 md5
Сохраните и закройте файл pg_hba.conf.
Перезапустите PostgreSQL, выполнив следующие команды:
sc stop postgresql-x64-11
sc start postgresql-x64-11
Если был выполнен шаг 5, вам необходимо обновить пароль суперпользователя на компьютере, на котором установлен PostgreSQL. Пользователь по умолчанию – postgres. Вы можете использовать утилиту psql или pgAdmin для выполнения этого действия. Если вы используете утилиту psql, укажите пробел и имя пользователя после параметра -U, как показано ниже: psql -U postgres
Обновите пароль суперпользователя:

Теперь вы можете установить Kaspersky Scan Engine GUI.

Чтобы установить Kaspersky Scan Engine GUI, вам нужен пользователь PostgreSQL с правами на создание новых баз данных и пользователей. Для этого вы можете использовать пользователя postgres или создать нового.

Все данные хранятся в базе данных kavebase. Kaspersky Scan Engine не использует другие базы данных.

RU

EN

RU EN

В приложении со встраиваемым SQL можно запустить любую команду SQL. Ниже приведены несколько примеров, показывающих как это делать.

36.3.1. Выполнение SQL-операторов #

EXEC SQL CREATE TABLE foo (number integer, ascii char(16));
EXEC SQL CREATE UNIQUE INDEX num1 ON foo(number);
EXEC SQL COMMIT;

EXEC SQL INSERT INTO foo (number, ascii) VALUES (9999, 'doodad');
EXEC SQL COMMIT;

EXEC SQL DELETE FROM foo WHERE number = 9999;
EXEC SQL COMMIT;

EXEC SQL UPDATE foo SET ascii = 'foobar' WHERE number = 9999;
EXEC SQL COMMIT;

Операторы SELECT, возвращающие одну строку результата, также могут выполняться непосредственно командой EXEC SQL. Чтобы обработать наборы результатов с несколькими строками, приложение должно использовать курсоры; см. Подраздел 36.3.2 ниже. (В отдельных случаях приложение может выбрать сразу несколько строк в переменную массива; см. Подраздел 36.4.4.3.1.)

:/> Вирус для Windows. Создаем простейшую вредоносную программу на ассемблере

Выборка одной строки:

EXEC SQL SELECT foo INTO :FooBar FROM table1 WHERE ascii = 'doodad';

Так же можно получить параметр конфигурации командой SHOW:

EXEC SQL SHOW search_path INTO :var;

Идентификаторы вида :имя воспринимаются как переменные среды, то есть они ссылаются на переменные программы C. Они рассматриваются в Разделе 36.4.

36.3.2. Использование курсоров #

Чтобы получить набор результатов, содержащий несколько строк, приложение должно объявить курсор и выбирать каждую строку через него. Использование курсора подразумевает следующие шаги: объявление курсора, открытие его, выборку строки через курсор, повторение предыдущего шага, и наконец, закрытие курсора.

Выборка с использованием курсоров:

EXEC SQL DECLARE foo_bar CURSOR FOR SELECT number, ascii FROM foo ORDER BY ascii;
EXEC SQL OPEN foo_bar;
EXEC SQL FETCH foo_bar INTO :FooBar, DooDad;
...
EXEC SQL CLOSE foo_bar;
EXEC SQL COMMIT;

Более подробно объявление курсора описывается в DECLARE, а выборка строк через курсор — в .

Примечание

Команда DECLARE в ECPG на самом деле не передаёт этот оператор серверу PostgreSQL. Курсор открывается на сервере (командой сервера DECLARE) в момент, когда выполняется команда OPEN.

36.3.3. Управление транзакциями #

В режиме по умолчанию операторы фиксируются только когда выполняется EXEC SQL COMMIT. Интерфейс встраиваемого SQL также поддерживает автофиксацию транзакций (так работает по умолчанию); она включается аргументом командной строки -t программы ecpg (см. ) либо оператором EXEC SQL SET AUTOCOMMIT TO ON. В режиме автофиксации каждая команда фиксируется автоматически, если только она не помещена в явный блок транзакции. Этот режим можно выключить явным образом, выполнив EXEC SQL SET AUTOCOMMIT TO OFF.

Поддерживаются следующие команды управления транзакциями:

EXEC SQL COMMIT #: Зафиксировать текущую транзакцию.
EXEC SQL ROLLBACK #: Откатить текущую транзакцию.
EXEC SQL PREPARE TRANSACTIONид_транзакции #: Подготовить текущую транзакцию для двухфазной фиксации.
EXEC SQL COMMIT PREPAREDид_транзакции #: Зафиксировать транзакцию в подготовленном состоянии.
EXEC SQL ROLLBACK PREPAREDид_транзакции #: Откатить транзакцию в подготовленном состоянии.
EXEC SQL SET AUTOCOMMIT TO ON #: Включить режим автофиксации.
EXEC SQL SET AUTOCOMMIT TO OFF #: Отключить режим автофиксации. По умолчанию он отключён.

36.3.4. Подготовленные операторы #

Когда значения, передаваемые SQL-оператору, неизвестны во время компиляции, или один и тот же оператор будет использоваться многократно, могут быть полезны подготовленные операторы.

Оператор подготавливается командой PREPARE. Вместо значений, которые ещё неизвестны, вставляются местозаполнители :

EXEC SQL PREPARE stmt1 FROM "SELECT oid, datname FROM pg_database WHERE oid = ?";

Если оператор возвращает одну строку, приложение может вызвать EXECUTE после PREPARE для выполнения этого оператора, указав фактические значения для местозаполнителей в предложении USING:

EXEC SQL EXECUTE stmt1 INTO :dboid, :dbname USING 1;

Если оператор возвращает несколько строк, приложение может использовать курсор, объявленный на базе подготовленного оператора. Чтобы привязать входные параметры, курсор нужно открыть с предложением USING:

EXEC SQL PREPARE stmt1 FROM "SELECT oid,datname FROM pg_database WHERE oid > ?";
EXEC SQL DECLARE foo_bar CURSOR FOR stmt1;
/* по достижении конца набора результатов прервать цикл while */
EXEC SQL WHENEVER NOT FOUND DO BREAK;
EXEC SQL OPEN foo_bar USING 100;
...
while (1)
{ EXEC SQL FETCH NEXT FROM foo_bar INTO :dboid, :dbname; ...
}
EXEC SQL CLOSE foo_bar;

Когда подготовленный оператор больше не нужен, его следует освободить:

EXEC SQL DEALLOCATE PREPARE имя;

Подробнее оператор PREPARE описан в PREPARE. Также обратитесь к Разделу 36.5 за дополнительными сведениями о местозаполнителях и входных параметрах.

Подключение к PostgreSQL

После установки psql имеется два способа указать основные параметры подключения для целевой базы данных. Вам потребуется следующая информация для подключения к PostgreSQL.

Имя хоста

Порт (по умолчанию 5432)

Имя пользователя

Пароль

Имя базы данных

Задание отдельных параметров

Подключитесь к базе данных, используя описанные выше параметры. Если сервер PostgreSQL запущен на порте по умолчанию 5432, вы можете опустить переключатель -p и psql попытается подключиться к этому порту автоматически.

psql -h [hostname] -p [port] -U [username] -d [database name]

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

:/> Как узнать версию DirectX на компьютере — 4 способа

Использование URI для подключения к PostgreSQL

Как альтернатива, вы можете использовать параметры подключения для создания URI подключения к базе данных PostgreSQL.

psql postgresql://[имя пользователя]:[пароль]@[имя хоста]:[порт]/[имя базы данных]

При использовании этой формы вы можете указать пароль в строке подключения, если он не содержит точки с запятой (;) или амперсанда (&), т.к. они помешают парсингу URI-подключения. Если опустить пароль, то PostgreSQL запросит его.

Установка на Windows

Для Windows у вас имеется два варианта.

Если вы хотите использовать psql в командной строке Windows, вам необходмо установить PostgreSQL и все инструменты, используя пакет установки на PostreSQL.org. В процессе установки вы сможете решить, какие компоненты сервера и инструментов устанавливать.

На Windows 10 и выше есть возможность установки инструментов в среде Windows Subsystem для Linux 2 (WSL2) и использовать обычное управление пакетами Linux для установки компонентов, как показано выше. Опять, в зависимости от используемого дистрибутива для хоста WSL, команды пакета могут несколько отличаться.

Краткая история psql

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

Установка на MacOS

Есть множество способов установки PostgreSQL на MacOS, многие из которых включают полный сервер и связанные с ним инструменты.

Самый быстрый способ установки PostgreSQL на MacOS – использование Postgres.app. Он обеспечивает полную установку сервера PostgreSQL, которая может быть запущена и остановлена по желанию, а простые инструкции на их домашней странице покажут вам, как убедиться, что инструменты командной строки PostgreSQL готовы к использованию из терминала.

Как альтернативу, вы можете просто установить PostgreSQL, используя Homebrew. Пакет libpq устанавливает только инструментарий (не сервер).

ryan@mba-laptop % brew doctor
ryan@mba-laptop % brew update
ryan@mba-laptop % brew install libpq

Поскольку это не полный сервер PostgreSQL, вам также потребуется корректно установить пути, чтобы вы могли использовать psql из терминала. Наиболее простой способ это сделать – позволить Homebrew обновить связи следующим образом.

ryan@mba-laptop % brew link --force libpq

Установка на Linux

Все основные дистрибутивы Linux должны иметь пакет для postgresql-client. Он может использоваться только для установки инструментов PostgreSQL, а не самого сервера, включая psql, pg_dump, pg_restore и других.

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

Из строки терминала выполните следующие команды:

$> sudo apt-get update
$> sudo apt-get install postgresql-client

По завершению проверьте установку с помощью получения версии psql, как было показано ранее.

Проверка наличия Psql

Поскольку psql поступает вместе с сервером PostgreSQL, возможно, эта утилита уже имеется на вашем компьютере, если вы устанавливали PostgreSQL. Из терминала или командной строки Windows наберите следующее:

$> psql --version
psql (PostgreSQL) 15.0 (Ubuntu 15.0-1.pgdg20.04+1)

Если в ответ вы увидите версию psql, то psql у вас уже установлена. Если версия устарела, рассмотрите возможность ее обновления.

Хорошим правилом является постараться иметь такую версию psql, которая соответствует самой новой версии PostgreSQL в вашем рабочем окружении. Для основных команд, которые мы рассмотрим ниже, весьма вероятно, что будет работать любая ваша версия, но хорошей практикой является обновление psql.

CREATE USER vs CREATE ROLE

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

А на этом всё: теперь вы знаете, как создавать пользователей различными способами через консоль и графический интерфейс и наделять их различными правами. Успешной работы!

:/> Как удалить метаданные из PDF-файла

Создаем пользователей через CREATE USER

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

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

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

Опция CONNECTION LIMITуказывает максимальное количество одновременных подключений, которые может установить этот пользователь. В данном случае, пользователь будет ограничен до 5 одновременных подключений к базе данных.

Создаем пользователя PostgreSQL через pgAdmin

В левой панели навигации выберите БД, в которой вы хотите создать пользователя.
Щелкните правой кнопкой на «Login/Group Roles» и выберите «Create» — «Login/Group Role»
В появившемся окне заполните следующую информацию:

- NameGeneral введите имя пользователя.
- PasswordDefinition введите пароль новой учетной записи. Также на этой вкладке можно настроить дополнительные параметры, такие как ограничение количества одновременных подключений ( — см. пример выше) и дату и время истечения прав учетной записи. Однако если для пользователя задан вход без пароля, то значение, указанное в Account expires, применяться не будет. Если же вы хотите установить неограниченное количество одновременных подключений, установите значение CONNECTION LIMIT
- Privileges выберите необходимые привилегии. Например, можно установить опцию Can login? в активную позицию (нажав на переключатель справа), чтобы разрешить пользователю вход в систему. Там же можно выдать учетной записи права суперпользователя (опция Superuser?
- Membership можно задавать пользователям специфические роли, которые предлагаются системой. Например, для отслеживания и диагностики БД предусмотрена роль

Проверьте созданный код SQL на крайней правой вкладке и затем нажмите кнопку «Save», чтобы создать пользователя с выбранными параметрами.
Теперь пользователь будет создан и отображен в списке «Login/Group Roles»

Установка psql

Инструмент командной строки (CLI) psql имеется для Linux, MacOS и Windows. Он идет вместе с установочным пакетом сервера PostgreSQL или может быть установлен отдельно как автономное приложение CLI.

Кроме того, версия psql совместима с версией PostgreSQL, поскольку запросы, которые она выполняет при использовании мета-команд, должна работать с более новыми схемами каталога. Было предпринято много усилий для поддержания обратной совместимости psql с поддерживаемыми версиями PostgreSQL, поэтому самая новая версия psql должна работать, по крайней мере, с пятью последними главными релизами.

Docker

Наконец, это будет упущением, если я не упомяну альтернативный вариант – Docker. Может быть использован любой из официальных контейнеров PostgreSQL через интерактивную оболочку Docker для подключения и использования psql. Пытаетесь ли вы подключиться к PostgreSQL на самом экземпляре Docker или удаленно, может использоваться установленное приложение psql (при корректно установленной сети).
Как пример, следующие команды могут использоваться для загрузки и запуска контейнера PostgreSQL Docker, подключения к выполняющейся оболочке и использования psql внутри контейнера.

ryan@redgate-laptop:~$ docker run --name pg15 -p 5432:5432 -e POSTGRES_PASSWORD=password -d postgres
ryan@redgate-laptop:~$ docker exec -it pg15 bash
root@80049acea1c0:/# psql -h localhost -U postgres

Заключение

Приложение командной строки psql является полезным инструментом для каждого, кто работает с PostgreSQL. За десятилетия разработки появились сотни встроенных мета-команд, помогающих разработчикам и администраторам работать с PostgreSQL быстро и эффективно. Знания о том, как установить и использовать его для подключения к базам данных, является важным навыком для пользователей PostgreSQL.Дополнительным преимуществом является то, что последние версии psql могут использоваться, по крайней мере, с пятью последними основными релизами PostgreSQL из-за неустанной работы сообщества по поддержанию обратной совместимости.

Конечно, эти знания являются только частью пазла. В следующей статье мы проведем экскурс по основным мета-командам, которые вам следует знать, чтобы ускорить разработку баз данных PostgreSQL.