Без объектов

Здесь начинается граница между разработчиками и системными администраторами. Кто-то скажет, что администратор PostgreSQL, не знающий объекты, это не администратор, и ему обязательно нужно уметь работать со всеми объектами БД, таблицами, и в идеале знать SQL полностью. Я с таким подходом в корне не согласен именно для системных администраторов широкого профиля, либо для начинающих, поскольку выучить все это – процесс крайне долгий, а других сервисов, помимо PostgreSQL, может быть еще несколько десятков, и они также требуют времени на изучение. В этом разделе мы научимся администрировать только БД и учетные записи (они не являются объектами).

Важный момент для всего, что будет дальше:

Не называйте роли, базы и т.п. с приставки «pg_», потому что с нее принято называть системные сервисы, утилиты и т.п. Для вашего же удобства рекомендую ВСЕГДА давать имена в нижнем регистре. Учтите также, что все имена регистрозависимы (в отличие от языка SQL, который является регистронезависимым).

База данных

По умолчанию в каждом новом кластере есть три БД: template0, template1 и postgres.

template0 – это первичный шаблон, из которого была создана БД template1.

template1 – это вторичный шаблон, из которого по умолчанию создаются все новые БД.

postgres – это БД, созданная по умолчанию, она как раз была создана из шаблона template1.

template0 и template1 – это именно шаблоны баз данных, template0 защищен от удаления, а вот template1 нет. Однако ни в коем случае его не удаляйте. Шаблоны – это весьма специфичная вещь, так что просто имейте ввиду, что все БД по умолчанию создаются из шаблона template1.

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

Как вы уже знаете, все эти три БД находятся в одном кластере. Но выше я уже несколько раз упоминал, что для каждого кластера лучше определять по одной БД. Так просто удобнее их администрировать и обслуживать. Но все зависит также и от приложения. Каждому приложению (ПО, которое интегрируется с PostgreSQL), необходимо назначать отдельный кластер. Если приложение работает с несколькими БД, то их можно определить как в один кластер (если они достаточно маленькие), так и в несколько различных кластеров. Приложение работает именно с БД, а не с кластером, поэтому БД может быть на любом кластере. У вас даже может быть в одном кластере две БД для двух разных приложений. Однако это потом выльется в административный ад. В общем, распределяйте БД так, как надо, и оставляйте три БД, которые были созданы по умолчанию, они все равно пустые и практически ничего не весят.

Приложение подключается к БД по имени и по порту кластера, на котором эта БД расположена.

Создание БД

https://www.postgresql.org/docs/current/app-createdb.html

Чтобы создать новую БД, необходимо как минимум задать ей имя. Называйте БД по-нормальному, не надо называть их в честь политиков, марок автомобилей и пород кошек. Это в целом касается всего, не только БД. Название БД должно отражать спецификацию самой БД и приложения, которое ее использует, например:

db_number_appname (db_1_myapp).

Необходимые параметры:

-O – задает владельца БД. По умолчанию владелец тот, кто создал БД.

-T – позволяет создать БД из шаблона (по умолчанию это template1). Если вы разработчик или просто хотите новую и чистую базу – то создавайте из шаблона template0.

-l – задает локализацию для БД. Лезть в устройство не буду, просто поймите, что все опять зависит от вашего приложения и от того, какие локали у этого приложения. В целом, все есть в документации. И не переживайте: если у вас установлена не та локаль, то приложение просто не запустится. Так что испортить вы ничего не сможете. Используйте только UTF8. Изменить локаль на существующей БД можно, просто придется ее восстанавливать из дампа. Также локаль PostgreSQL зависит от локали хостовой ОС. Насчет конкретных параметров локализации (-l отвечает сразу за все), вам все равно придется менять сразу их все, так что используйте -l, только если в документации не сказано другое. При обновлении кластера, у нового должны быть те же локали (если делаете через pg_dump).

-e – отдельно задает кодировку.

Не забывайте про параметры подключений (-p и -h). Остальные же параметры вторичны и не играют роли.

https://wiki.debian.org/Locale

Удаление БД

https://www.postgresql.org/docs/current/app-dropdb.html

Для удаления чего-либо всегда используется выражение drop, а не delete или remove, имейте это ввиду.

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

В общем, это все. Просто, не правда ли? Если вас интересует вопрос, к примеру, как посмотреть все БД кластера – всему свое время. Сначала разберем учетные записи.

Учетные записи (роли)

С учетными записями все немного сложнее. Учетные записи в PostgreSQL называются ролями. Каждая роль работает только в рамках одного кластера. Роли СУБД никак не связаны с учетными записями ОС (но могут называться одинаково, чего я вам советую избегать, чтобы не запутаться).

В PostgreSQL понятия группа и учетная запись перемешаны. Поэтому рекомендую работать с отдельными ролями, без наследования. Так проще, да и все равно у вас вряд ли будет больше 5-8 ролей на кластер, и все они будут разные.

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

Создание роли

https://www.postgresql.org/docs/current/app-createuser.html

https://www.postgresql.org/docs/current/role-attributes.html

Для создания новой роли вам нужны права на создание ролей или права суперпользователя СУБД. Имя создаваемой роли также должно быть адекватным и информативным.

Полезные параметры команды createuser:

-c <number> – задает максимальное количество одновременных подключений. По умолчанию без ограничений (-1).

-d – дать роли право на создание БД.

-D – запретить роли создавать БД. Это значение по умолчанию.

-l – разрешить роли подключение к базам (любым).

-L – запретить роли подключение к базам.

-P – во время создания роли вам необходимо будет ввести для нее пароль.

-r – разрешить роли добавление, удаление и изменение других ролей (в общем, все операции, связанные с ролями).

-R – то же, но запретить. Это значение по умолчанию.

-s – дать роли права суперпользователя СУБД. Суперпользователь по умолчанию обладает всеми остальными ролями, кроме подключения к базам (ее обычно отключают в целях безопасности). Чтобы создать роль с правами суперпользователя, надо самому быть суперпользователем.

-S – НЕ давать создаваемой роли права суперпользователя. Это значение по умолчанию.

-v <timestamp> – задает дату и время, когда пароль роли станет недействительным. По умолчанию ограничений нет.

--replication – просто заранее отмечаю, пригодится для репликации. Должно идти вместе с правом на подключение к БД.

Удаление роли

https://www.postgresql.org/docs/current/app-dropuser.html

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

Подход к распределению ролей

Подход к администрированию ролей должен быть примерно следующий:

Суперпользователя postgres оставляем, но запрещаем все удаленные подключения. Можно также запретить локальные подключения к базам.

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

Еще в дополнение к этому можно ограничить кол-во одновременных подключений к базам, а также создать отдельные дополнительные роли, которые будут держать репликацию.

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

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

https://www.postgresql.org/docs/current/predefined-roles.html

Они пригодятся вам, к примеру, когда вы будете учиться работать с кластеризацией серверов или сторонней системой резервного копирования. Однако, это еще не все. Вам также пригодятся функции. Просто прочитайте их названия, и вы поймете, почему мы так усердно настраивали параметры конфига postgresql.conf. Запускать их вручную не рекомендую, этим уже занимаются сторонние сервисы.

https://www.postgresql.org/docs/current/functions-admin.html

Консоль psql

Все, что мы делали выше – это обертки над SQL-командами. SQL-команды вводятся напрямую в консоль psql (посмотрите параметр -e у команд выше). К сожалению, без psql не обойтись никак, и нам придется научиться с ней работать, потому что оболочек над другими SQL-командами пока что не добавили.

https://www.postgresql.org/docs/current/app-psql.html

Консоль psql входит в состав пакета postgresql-client. Разработчики рекомендуют, чтобы версия клиента совпадала с версией сервера, к которому мы подключаемся.

Для подключения к консоли, мы просто вводим команду psql в оболочку ОС.

Необходимые параметры psql:

-? – показывает информацию обо всех параметрах.

-h – задает хост, к которому мы подключаемся. По умолчанию локальный. Полезно и удобно. Если отдельно установить клиент PostgreSQL (postgresql-client), то можно работать удаленно.

-p – задает номер порта, к кластеру которого мы будем подключаться. Это вторая ступень, поскольку порты всегда у всех разные.

-d – задает имя БД, к которой мы подключаемся. По умолчанию мы подключаемся к БД postgres. Если ее нет, то подключаемся к другой БД, к примеру, template1. Именно поэтому я не рекомендую удалять БД postgres. Консолью psql мы подключаемся именно к конкретной БД кластера, но при этом можем работать со всеми БД в нем (именно с БД, а не с их содержимым). Так уж устроена система подключений.

-l – показывает все БД кластера без подключения к нему.

-c – в кавычках задает SQL-запрос, который будет введен в консоль, а затем возвращает результат.

-f – то же самое, только считывает запросы из заданного файла. Полезно, если у вас уже записано много готовых запросов.

-L – выводит результат выполнения запроса в заданный файл. Имеет смысл только с параметрами -c или -f.

-o – то же, что и -L, только перезаписывает файл.

-q – ничего не выводит обратно в консоль. Полезно, если мы получаем информацию и записываем ее в файл, но глаза уже и так болят.

-U – задает имя роли, от имени которой будет произведено подключение.

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

Про интерфейс

Первое слово – это имя БД, к которой мы подключены. Если вы подключались к БД postgres, то у вас будет написано postgres. Сразу скажу, есть способ сделать так, чтобы в psql показывалось не только имя БД, но еще и имя текущей роли. Однако это небезопасно (имя БД не так важно, как имя роли, которая имеет к ней доступ), и заниматься подобными нестандартными вещами я не рекомендую, тем более что у нас есть способ вывести информацию о текущем подключении.

Символ после имени БД. Символ равно означает, что система ждет ввода SQL-запроса (или мета-команды). Символ минус означает, что уже был введен SQL-запрос, но не до конца. Символ кавычек означает, что вы задали лишнюю кавычку (одинарную или двойную). Каждый SQL-запрос должен оканчиваться точкой с запятой.

Последний символ – тип роли, от имени которой вы сейчас работаете. Символ «#» – вы суперпользователь СУБД. Символ «>» – нет.

Мета-команды

Консоль psql принимает два вида вводимых данных: мета-команды и SQL-запросы. Синтаксис SQL вы уже точно где-либо видели. Мета команды отличаются. Они все начинаются с символа «\» (обратный слэш). Полезных для администратора мета-команд не так уж и много. В основном, все они будут полезны программистам, которые разрабатывают приложение под PostgreSQL и работают с объектами внутри БД.

https://www.postgresql.org/docs/current/app-psql.html

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

\q – выйти из psql (самая полезная команда).

\? – показывает все команды.

\h – другая вещь, показывает все SQL-команды, но можно задать конкретную.

\l – показывает все БД кластера. Можно задать имя конкретной БД.

\l+ – то же самое, но с дополнениями (к примеру, сколько весит каждая БД).

\du – показывает все роли кластера (аналог \dg, поскольку пользователи и группы объединены: d – display, u – users, g – groups). Также показывает атрибуты ролей. Можно задать конкретную роль.

\password – меняет пароль заданной роли. По умолчанию у той роли, которая подключена в данный момент.

\s <filename> – записывает историю всех введенных мета-команд и SQL-запросов в файл. Не перезаписывает файл, а просто добавляет их построчно. Очень полезно. Приучайте себя использовать \s всегда.

\dx – показывает установленные расширения (модули).

\dconfig – выводит значения заданных параметров из конфига postgresql.conf. Можно задать имя параметра или сразу все через *.

\! – тоже полезно, к примеру, если вы работаете с файлами. Позволяет запускать команды оболочки ОС в psql. Удобно, чтобы по 10 раз не перезаходить в psql.

\conninfo – выводит информацию о текущем подключении.

\c – позволяет подключиться к другой БД на том же или другом хосте.

\x – используйте с какой-либо другой мета-командой для вывода информации, если у вас экран с небольшим разрешением. Выводит все списком по вертикали (к примеру, при \l это очень хорошо видно). Принимает значения on и off.

SQL

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

Также прошу прощения за то, что на протяжении всей статьи я путаю понятия SQL запросов и команд. Целенаправленно SQL никогда не изучал.

Работа с базами

Пример создания одной и той же базы двумя методами:

createdb db1 -O user1 -T template0 -l ru_RU.UTF-8

CREATE DATABASE "db1" WITH OWNER user1 TEMPLATE template0 LOCALE 'ru_RU.UTF-8';

https://www.postgresql.org/docs/current/sql-createdatabase.html

https://www.postgresql.org/docs/current/sql-dropdatabase.html

Работа с ролями

Пример создания одной и той же роли двумя методами:

createuser user1 -c 3 -d -l --replication

CREATE ROLE "user1" WITH CONNECTION LIMIT 3 CREATEDB LOGIN REPLICATION;

Также через SQL при помощи ALTER не забывайте менять пароль роли, например:

ALTER ROLE "user1" WITH PASSWORD 'new_password_12354';

https://www.postgresql.org/docs/current/sql-createrole.html

https://www.postgresql.org/docs/current/sql-droprole.html

SQL в целом, несмотря на наличие готовых команд и объем вводимого текста, предоставляет значительно больше возможностей при работе с ролями и базами. К примеру, мы можем сразу ввести пароль для роли в SQL, а не два раза его подтверждать, как в createuser. Или через тот же SQL задать максимальное количество подключений к базе, чего createdb сделать не может. Не сказал бы, что без SQL можно жить, но все же большинство начинающих системных администраторов могут без SQL команд обойтись, если надо реализовать что-то простое и примитивное без дальнейших изменений. Но вам все равно придется с ним работать, и это касается любых СУБД, не только PostgreSQL.

Основным преимуществом SQL здесь является возможность изменения параметров существующих ролей или баз. Например, вы можете изменить имя роли и ее атрибуты. Или вы можете ограничить количество подключений к БД или полностью их запретить, изменить ее имя или назначить ей другого владельца. При изменении параметров роли или БД не обязательно перезапускать сервер. Достаточно лишь отрубить все их подключения. У роли – к базам, у БД – со стороны клиентов (ролей). Не путайте это с изменением параметров экземпляра в postgresql.conf.

https://www.postgresql.org/docs/current/sql-alterrole.html

https://www.postgresql.org/docs/current/sql-alterdatabase.html

Для чего я тогда тратил ваше время на createdb, dropdb, createuser, dropuser? Чтобы вы разобрались сами, стоит ли вам учиться работать с SQL дальше или нет. Если вас напрягает постоянно входить в консоль, то вспомните про psql -c.

Если захотите учить SQL, то учтите, что, с одной стороны, синтаксис SQL очень строгий и требует конкретной последовательности команд, а с другой стороны эти самые команды не обязательно прописывать заглавными буквами, и есть у нас «специалисты», которые превращают код непонятно во что:

create database "db1" with owner user1 template template0 locale 'ru_RU.UTF-8';

Не делайте так. Пишите нормально. Да, придется насиловать CAPS, однако потом вы будете тратить значительно больше времени на разбор своего же кода.

Права на БД для роли

Недостаточно просто создать БД и роль с правом LOGIN. Необходимо именно назначить для роли права на базу, чтобы эта роль могла без препятствий работать с содержимым БД. Делается это при помощи SQL команды GRANT. Назначайте права сразу на всю БД для какой-либо из ролей, потому что именно от имени этой роли приложение будет работать с содержимым базы. Владелец базы по умолчанию имеет все права на нее (createdb -O), так что ему не обязательно повторно назначать права через GRANT.

https://www.postgresql.org/docs/current/sql-grant.html

К примеру, выдадим все права роли user1 на базу db1:

GRANT ALL PRIVILEGES ON DATABASE "db1" TO "user1";

Отбираются права командой REVOKE. Отобрать права может только тот, кто их выдал.

https://www.postgresql.org/docs/current/sql-revoke.html

Предопределенные роли

https://www.postgresql.org/docs/current/predefined-roles.html

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

Отдельно про псевдороль public:

Не трогайте ее. Это очередные качели между безопасностью и удобством.

Создание чекпоинта вручную

В psql мы можем вручную создать чекпоинт командой CHECKPOINT. Вам также нужны права pg_checkpoint. Создание чекпоинта вручную создает большую нагрузку на дисковую подсистему, потому что он записывается сразу, в отличие от процесса автоматического создания чекпоинта, который старается распределить нагрузку равномерно.

https://www.postgresql.org/docs/current/sql-checkpoint.html

Выполнение очистки

Мы также можем запустить SQL командами процесс очистки, аналогично команде vacuumdb (собственно, vacuumdb это просто надстройка над SQL). Используйте любой удобный для вас вариант. Ниже оставил ссылку на документацию к команде, внимательно ознакомьтесь с параметрами.

https://www.postgresql.org/docs/current/sql-vacuum.html

Перестроение индексов

Тут все аналогично, читайте документацию. Команда REINDEX перестраивает индексы полностью. При выполнении VACUUM FULL по сути дела выполняется и REINDEX.

https://www.postgresql.org/docs/current/sql-reindex.html

Перестроение таблиц

А вот здесь уже немного интереснее. Команда CLUSTER работает как VACUUM FULL и REINDEX, но еще и перестраивает таблицу со всеми индексами. Смысла в этом особого нет. Просто незначительно увеличивает именно скорость чтения.

https://www.postgresql.org/docs/current/sql-cluster.html

В целом, про процесс очистки и перестроения индексов я уже все сказал выше: используйте модуль pg_repack, потому что все эти команды блокируют таблицу для изменений. То есть на рабочей системе вы их выполнить не сможете (точнее, сможете, но у вас все подключения послетают). Что такое индексы, таблицы, строки, как это все дело взаимосвязано между собой, вам знать пока что не обязательно. Просто имейте ввиду, что есть конкретный набор операций обслуживания, который необходимо периодически выполнять для снижения объема баз и повышения производительности.

Конфиг postgresql.auto.conf

Думаю, сейчас самое время начать и сразу закрыть тему с конфигом postgresql.auto.conf.

postgresql.auto.conf – это файл, в который записываются изменения конфигурационных параметров при помощи SQL-команд (ALTER SYSTEM). Расположен он там же, где и кластер. Смысла изменять этот файл через SQL нет никакого, только если основной конфигурационный файл не расположен где-то, куда вы не имеете доступ. Если все же с ним работаете, то ни в коем случае не редактируйте его вручную, только через SQL. В остальном же всегда используйте pg_conftool или редактируйте параметры конфига postgresql.conf вручную.

Подключения

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

Перед тем, как начать, еще раз ознакомьтесь с параметрами, отвечающими за подключения в postgresql.conf.

За политику подключений отвечает конфиг pg_hba.conf. Перед тем, как его трогать, сделайте его копию и защитите ее от записи, как было с postgresql.conf.

Но для начала немного терминологии:

Идентификация – есть ли пользователь с таким логином. Пока что сопоставляется именно логин.

Аутентификация – правильный ли он ввел пароль (или можно использовать не пароль, а какой-либо другой метод аутентификации).

Авторизация – имеет ли этот пользователь право на подключение. Первые две проверки должны быть пройдены.

Каждая из политик в pg_hba.conf начинается с новой строки. Сама политика задается набором полей, которые разделяются между собой как минимум одним пробелом или знаком табуляции.

Порядок строк тоже важен. Все политики применяются друг за другом сверху вниз. Так что не следует разводить непойми что из десятков и сотен правил. Файл один для всех баз кластера. Если у вас в одном кластере тридцать баз для десяти приложений – то сами этот бардак и расхлебывайте.

Если строка пустая или начинается с символа «#», то она просто игнорируется.

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

Тип подключения
БД
Роль
Адрес клиента
Метод аутентификации

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

Чтобы изменения в файле pg_hba.conf были применены, необходимо перезагрузить сервер (reload).

Имейте ввиду, что значения по умолчанию зависят от дистрибутива и даже от его версии.

Параметры подключений

Тип подключения – определяет, откуда и куда идет подключение. Если задано local – то это локальные подключения. Если host – то удаленные по TCP/IP. Учтите также, что при изменении параметра host надо менять параметр listen_addresses в postgresql.conf. Есть еще несколько типов, но нам они пока что не нужны.

БД – задает имя БД, к которой разрешается подключение. Можно задать all сразу для всех баз кластера. Не рекомендую использовать samerole, а то запутаетесь. Никогда не давайте ролям и базам одни и те же имена. Никогда не давайте базам имена, совпадающие с ключевыми словами (если у вас с этим какие-то проблемы, то задавайте имя БД в кавычках, вдруг вы назвали базу именем all). Есть еще параметр replication – он разрешает подключение по протоколу репликации сразу для всех баз. Можно задать несколько баз через запятую (без пробелов, потому что пробел обозначает переход к след. параметру).

Роль – задает логин роли, которой разрешено подключение к базам, указанным в предыдущем параметре. Аналогично, all задает все роли, и вы также можете указать конкретную роль или их список через запятую (без пробелов). Задавайте всегда конкретные роли.

Адрес клиента – его еще называют адресом узла. Задает IP-адреса, с которых разрешено подключение к этому кластеру. При локальных подключениях (local) задавать не надо. Принимает как адреса, так и имена хостов. Значение all разрешает подключения с любых адресов. Можете задать конкретную подсеть, к примеру: 10.5.100.0/24. Значения samehost и samenet не используйте просто так. Можете еще указать доменное имя, но делать так я не рекомендую, потому что DNS сервер может оказаться недоступен. Также не забываем про параметр listen_addresses в postgresql.conf. Нельзя задать IPv4 и IPv6 в одном правиле.

Метод аутентификации

В первую очередь, мы имеем два метода, которые ничего не проверяют, а просто все пропускают или отклоняют – это trust и reject соответственно. Метод trust пропускает все подключения без ввода пароля и т.п. А вот reject просто отклоняет все подряд.

Метод password требует ввода пароля, который передается в незашифрованном виде (ни в коем случае его не используйте).

Методы scram-sha-256 и md5 также требуют пароль, но шифруют его при отправке. И опять же, все зависит от того, как в вашей организации подходят к вопросу информационной безопасности. Смотрите также, чтобы метод аутентификации (если используется именно scram-sha-256 или md5) совпадал с параметром password_encryption в файле postgresql.conf.

Если для роли не задан пароль – то аутентификация с использованием пароля не пройдет, в таком случае (без пароля) подключится можно только через метод trust.

Про остальные методы, такие как ldap, radius, peer, cert и др. – решайте сами, нужны они вам или нет.

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

К примеру, разрешим удаленные подключения для роли user1 ко всем БД с адреса 10.1.1.100/24, и запретим для всех остальных в сети IPv4:

host all user1 10.1.1.100/24 scram-sha-256

host all all 0.0.0.0/0 reject

Если бы мы поменяли эти правила местами, то user1 не смог бы подключиться ни при каком раскладе. В общем, попробуйте поэкспериментировать, и вы сами все поймете. Если у вас только локальная система – то учитесь на примере локальных подключений. Работать с базами и ролями вы уже умеете. Если вы вдруг не знаете IP-адресацию, то подтягивайте теорию. Это не стыдно, что вы чего-то не знаете. Стыдно, когда люди этого не признают и продолжают считать себя самыми умными. Самое главное – не разрешайте суперпользователям удаленные подключения. Локальные к базам тоже желательно запретить.

К примеру, запретим все удаленные подключения для роли postgres:

host all postgres 0.0.0.0/0 reject

host all postgres ::1/128 reject

Каждое правило может держать только конкретную версию IP. Так что не забывайте добавлять для каждой из них по правилу отдельно.

Если правило не задано, то система автоматически считывает это как запрет. Но лучше все задавать в явном виде и подписывать комментариями.

https://www.postgresql.org/docs/current/auth-pg-hba-conf.html

Пул подключений

Пул подключений – это некий буфер между приложением, которое плодит подключения, и СУБД. Зачем оно надо – для наиболее эффективной обработки подключений, потому что встроенные в PostgreSQL средства такие себе (это касается многих вещей на самом деле, ну вы уже знаете, к примеру, про vacuum и reindex). В целом для клиентов сам процесс подключения становится быстрее, как и время ответа со стороны сервера СУБД. Но самая важная вещь: это кеширование запросов. Для каждого запроса сервер должен начать и завершить подключение. Чтобы не делать этого по стопицот раз, пул просто кеширует запрос для экономии ресурсов и времени.

Пул подключений (любой) не бесполезен. От него есть польза даже если у вас одновременно обрабатывается 10 подключений. Другой вопрос, насколько значительная эта польза. Значительный прирост вы заметите только при большом объеме транзакций и количестве подключений. Пул подключений уже, скорее всего, встроен в приложение (клиенты работают с приложением, а приложение – с СУБД через подключения). И нет, встроенный в приложение пул подключений не замена стороннему решению. Даже если их использовать вместе, польза определенно будет. Когда начнете работать с кластеризацией, облаками, большими системами с большим количеством подключений, только тогда вам действительно будет нужен пул. Самым известным из них является, наверное, PgBouncer. Но есть у него одна «особенность» – он не распараллеливается, т.е. использует только одно ядро CPU. Помните, я говорил в самом начале, что производительность на ядро очень важна? Вот здесь это и выстрелило.

На самом деле, выстрелило уже пару раз, и дальше выстрелит еще. Но я об этом не писал, потому что либо там нет нагрузок, либо мы используем что-то стороннее.

При использовании PgBouncer, примерно после 500-1000 подключений (в зависимости от размера пула и производительности на ядро) скорость обработки начнет с ускоренными темпами падать. Решение простое – найти альтернативу. К примеру, PgCat.

https://github.com/postgresml/pgcat

https://tembo.io/blog/postgres-connection-poolers

Но, очевидно также, что все пулы разные. И прирост производительности будет также отличаться, как и потребление ресурсов (пул тоже жрет ресурсы, так что вспоминаем еще раз про оборудование). В общем, пока что вам просто надо знать, что есть такая вещь как пул. Изучать вам его или нет – решайте сами по ситуации. Есть время, есть большая система – однозначно да. А если нет – то нет. В первую очередь рекомендую рассмотреть именно PgCat. Он довольно сложен в настройке (на самом деле, это касается всех пулов), и по незнанию вы, наоборот, можете все испортить.

Четвертая часть:
https://habr.com/ru/articles/843264/

Администрирование PostgreSQL для начинающих (часть 3)