Сценарии администрирования#

Администрирование Platform V Pangolin осуществляется средствами, которые описаны в этом разделе.

Реализация АРМ администратора#

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

psql [параметр...] [имя_бд [имя_пользователя]]

Примечание:

Решение по обеспечению безопасности АРМ аминистратора должно исходить из окружения конечной АС.

Получение информации об используемой версии#

В данном разделе приведены примеры команд для получения используемой версии продукта Platform V Pangolin. Примеры команд не отличаются для ОС Альт 8 СП и Rad Hat.

Для клиентской части#

Чтобы получить название и версию клиентской части продукта Platform V Pangolin, запустите любую команду с ключом --product_version, например:

Команда:

pg_ctl --product_version

Результат выполнения команды:

Platform V Pangolin 5.2.0

где:

• Platform V Pangolin — наименование продукта;

• 5.2.0 — версия продукта.

Чтобы получить название и версию PostgreSQL, запустите любую команду с ключом --version, например:

Команда:

pg_ctl --version

Результат выполнения команды:

pg_ctl (PostgreSQL) 13.4

Для серверной части#

Для получения названия и версии продукта серверной части подключитесь к серверу Platform V Pangolin, чтобы:

• узнать версию Platform V Pangolin:

  Команда:

  SELECT product_version();
  

  Результат выполнения команды:

      product_version     
  ---------------------------
  Platform V Pangolin 5.1.0
  (1 row)
  

• узнать версию PostgreSQL:

  Команда:

  SELECT version();
  

  Результат выполнения команды:

                                                  version                                                
  ---------------------------------------------------------------------------------------------------------
  PostgreSQL 13.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
  (1 row)
  

  Примечание:

  Вывод может отличаться от приведенного в примере.

Получение информации о сборке продукта#

Чтобы получить информацию о сборке продукта Platform V Pangolin, запустите любую команду с ключом --product_build_info, например:

Команда:

pg_ctl --product_build_info

Результат выполнения команды:

build 54 (22:27:09 05.02.2022) commit cbb7a012923abe4143bc029c6ae7fe31be636ee5

где:

• 54 — порядковый номер сборки продукта;

• 22:27:09 05.02.2022 — время и дата сборки продукта;

• cbb7a012923abe4143bc029c6ae7fe31be636ee5 — хеш-сумма исходных кодов продукта (идентификатор коммита в git репозитории продукта).

Пример вывода информации о сборке продукта для psql:

Команда:

SELECT product_build_info();

Результат выполнения команды:

build 54 (22:27:09 05.02.2022) commit cbb7a012923abe4143bc029c6ae7fe31be636ee5

Примечание:

Вывод может отличаться, но будет сохранена структура и формат.

Получение хеш-суммы внутренней версии компонента продукта#

Для PostgreSQL можно получить хеш-сумму с помощью параметра --product_component_hash, например:

Команда:

pg_ctl --product_component_hash

Результат выполнения команды:

ae6aec146801794bb9d95c3821ca039bff6ea7d4

Интерфейс администратора безопасности Pangolin#

Интерфейс администратора безопасности включает в себя следующие функции:

• pm_get_protected_objects () — возвращает список объектов, находящихся под защитой;

• pm_protect_object (имя базы данных, типа объекта, наименование объекта) — помещает объект под защиту;

• pm_unprotect_object (имя базы данных, типа объекта, наименование объекта) — снимает защиту с объекта;

• pm_make_policy (имя политики) — создает политику;

• pm_grant_to_policy (имя политики, имя базы данных, типа объекта, наименование объекта, массив действий над объектом) — вносит в политику разрешение на действия над объектом;

• pm_revoke_from_policy (имя политики, имя базы данных, типа объекта, наименование объекта, массив действий над объектом) — исключает из политики разрешения на действия над объектом;

• pm_assign_policy_to_user (имя пользователя, имя политики) — назначает политику пользователю;

• pm_unassign_policy_from_user (имя пользователя, имя политики) — изымает политику у пользователя;

• pm_get_assigned_policies (имя пользователя) — получает список политик, назначенных пользователю;

• pm_get_policy_grants (имя политики) — получает список разрешений в составе политики;

• pm_get_policies () — получает список политик;

• pm_grant_security_admin (имя пользователя) — делает пользователя администратором безопасности;

• pm_revoke_security_admin (имя пользователя) — снимает с пользователя политики администратора безопасности.

Интерфейс управления парольными политиками: PL/pgSQL API#

Все запросы к базе выполняются через SPI интерфейс (то есть не через внутренний API АС Platform V Pangolin), так как вероятность изменения SQL интерфейса меньше.

Настройки механизма хранятся в файле postgresql.conf. Данный файл также содержит значения по умолчанию для настроек парольной политики. Настройки парольной политики хранятся в таблице pg_pp_policy.

При включенной защите от привилегированных пользователей, в режиме защищенного конфигурирования, параметры парольных политик конфигурируются администратором безопасности в хранилище секретов — HashiCorp Vault, в случае отсутствия KMS-hosts (использования эмулятора) для хранения параметров используются локальные конфигурационные файлы.

Параметры парольной политики хранятся в файле в зависимости от типа конфигурации сервера, если тип конфигурации:

• standalone — в файле $PGDATA/postgresql.conf;

• cluster — в файле /etc/patroni/postgres.yml.

Подробнее о параметрах файла postgresql.conf в разделе «Параметры в postgresql.conf» данного документа.

Сценарии работы с механизмом#

Большинство операций выполняется соответствующими функциями PL/pgSQL (см. «Документация на публичные API», раздел «Функции для работы с парольными политиками»):

• создание парольной политики;

• активация парольной политики;

• деактивация парольной политики;

• отображение парольных политик, примененных к роли;

• отображение всех активных политик;

• разблокировка роли.

Включение механизма (password_policies_enable)#

Параметр password_policies_enable включает (on) или выключает (off) механизм.

Статистика и история по паролю ведется в любом состоянии механизма.

Использование значений настроек парольной политики из файла postgresql.conf (password_policy.deny_default)#

Параметр password_policy.deny_default включает (on) или выключает (off) использование значений для настроек парольной политики из файла postgresql.conf.

Примечание:

Если выключить параметр password_policy.deny_default:

password_policy.deny_default=off

то для всех политик должны быть заданы все обязательные настройки (см. подраздел «Обязательные настройки парольной политики» данного документа).

Управление шифрованием пароля (password_policy.psql_encrypt_password)#

Параметр password_policy.psql_encrypt_password управляет шифрованием пароля при передаче от фронтенда (psql) к базе с помощью команды psql \password .

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

Задание значений по умолчанию для настроек парольной политики#

Все настройки для парольной политики имеют аналог в файле postgresql.conf. Это необходимо для возможности задания значения по умолчанию для настроек парольной политики.

Подробное описание настроек в разделе «Использование значений настроек парольной политики из файла postgresql.conf» данного документа.

Создать новую политику#

Примечание:

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

Если связанные параметры с основным не будут указаны, то новая политика не применится.

Для создания новой политики последовательно выполните функции:

1. Создания парольной политики.

   Примечание:

   Если какая-либо настройка политики не задана, то ее значение берется из конфигурационного файла.

2. Активации парольной политики.

Функции создания и активации парольной политики описаны в «Документация на публичные API», раздел «Функции для работы с парольными политиками».

Разблокировать роль#

Выполняется соответствующей SQL-функцией (см. «Документация на публичные API», раздел «Функции для работы с парольными политиками»).

Активировать или деактивировать политику#

Выполняется соответствующими SQL-функциями:

• активация парольной политики;

• деактивация парольной политики.

Подробнее о функциях см. «Документация на публичные API», раздел «Функции для работы с парольными политиками»

Изменить настройки политики#

Выполняется соответствующей SQL-функцией (см. «Документация на публичные API», раздел «Функции для работы с парольными политиками»).

При изменении параметров учитывайте зависимости между ними и изменяемую функциональность (см. ниже подраздел «Обязательные настройки парольной политики»). Нельзя изменить параметр roloid.

Сменить политику для роли#

Роли связаны с политиками по идентификатору роли. Изменить политику для роли можно следующими способами:

• удалить старую политику и создать новую политику;

• включить роль в другую роль с нужной политикой.

Подробнее о функциях см. «Документация на публичные API», раздел «Функции для работы с парольными политиками»

Обязательные настройки парольной политики#

Пользовательская функция проверки пароля#

Пользователь может создать PL/pgSQL функцию проверки пароля. Ниже описаны требования к пользовательской функции.

Примечание:

Зашифрованный пароль приходит, если Pangolin подключен к сторонней системе аутентификации.

Параметры для управления транспортными паролями#

Разблокировать и восстановить работу кластера:#

1. Определите, на каком хосте был последний активный лидер. Это можно проверить по логам patroni:

   host1$ sudo journalctl --since "5 hours ago" -u patroni | grep "i am " | tail -1
   Aug 04 22:38:17 <host> python3[14026]: 2020-08-04 22:38:17,824 INFO: no action. i am the leader with the lock
   host2$ sudo journalctl --since "5 hours ago" -u patroni | grep "i am " | tail -1
   Aug 04 22:38:07 <host> python3[21032]: 2020-08-04 22:38:07,814 INFO: no action. i am a secondary and i am following a leader
   

   В данном случае лидер был на хосте «host1».

2. Остановите patroni на хосте последнего лидера:

   host1$ sudo systemctl stop patroni
   

3. Запустите PostgreSQL в single user режиме:

   host1$ postgres --single
   

4. Если PostgreSQL не запускается даже на предположительном лидере и выводит ошибку:

   2020-08-04 23:08:34 MSK [20019]: [1-1] app=,user=,db=,client= LOG: database system was shut down in recovery at 2020-08-04 23:08:30 MSK
   2020-08-04 23:08:34 MSK [20019]: [2-1] app=,user=,db=,client= WARNING: recovery command file "recovery.conf" specified neither primary_conninfo nor restore_command
   2020-08-04 23:08:34 MSK [20019]: [3-1] app=,user=,db=,client= HINT: The database server will regularly poll the pg_wal subdirectory to check for files placed there.
   2020-08-04 23:08:34 MSK [20019]: [4-1] app=,user=,db=,client= FATAL: standby mode is not supported by single-user servers
   

   Уберите файл recovery.conf из $PGDATA и снова выполните запуск:

   host1$ mv $PGDATA/recovery.conf{,.back}
   host1$ postgres --single
   

5. В single user режиме выполните SQL-команды от лица пользователя postgres. Для выхода нажмите Ctrl/Cmd-D. Разблокируйте роль postgres:

   select unblock_role('postgres')
   

   Если при попытке разблокировать пользователя postgres выходит ошибка:

   backend> select unblock_role('postgres')
        1: unblock_role        (typeid = 16, len = 1, typmod = -1, byval = t)
       ----
   2022-07-05 11:58:17 MSK [19233]: [2-1] app=[unknown],user=postgres,db=postgres,client=[tty] ERROR:  Cant find role with Oid 10 in password policy cache
   2022-07-05 11:58:17 MSK [19233]: [3-1] app=[unknown],user=postgres,db=postgres,client=[tty] STATEMENT:  select unblock_role('postgres')
   

   Выполните команду для разблокировки пользователя postgres:

   backend> update pg_pp_policy set lockout='f' WHERE roloid = to_regrole('postgres');
   backend>
   

6. Запустите службу Patroni:

   host1$ sudo systemctl start patroni
   

7. Убедитесь, что кластер вернулся в стабильное состояние. В таблице вывода в столбце Role должен быть указан Leader:

   host1$ list
   + Cluster: clustername (6857170778029161231) ----------------------------------------+--------------+---------+----+-----------+
   |                 Member                |                    Host                    |     Role     |  State  | TL | Lag in MB |
   +---------------------------------------+--------------------------------------------+--------------+---------+----+-----------+
   | <host>                                | <host>:5433                                | Sync Standby | running |  4 |         0 |
   | <host>                                | <host>:5433                                |    Leader    | running |  4 |           |
   +---------------------------------------+--------------------------------------------+--------------+---------+----+-----------+
   

8. Стандартным образом разблокируйте остальных администраторов БД.

Справочник журнальных сообщений#

Параметры в postgresql.conf#

В данном разделе более подробно описаны параметры файла postgresql.conf.

password_policy.policy_enable (Состояние по умолчанию для парольной политики)#

Признак включенной парольной политики:

• on – политика включена;

• off – политика выключена.

password_policy.deny_default#

Запрет использования значений для настроек политик, указанных в файле postgresql.conf:

• on – включить использование значений настроенных политик, указанных в файле postgresql.conf;

• off – выключить использование значений настроенных политик, указанных в файле postgresql.conf.

Настройка хранения паролей#

password_policy.reuse_time#

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

password_policy.in_history#

Максимальное количество сохраненных старых паролей. При достижении максимума добавление еще одного старого пароля приводит к удалению наиболее старого (по pg_pp_history.createtime) из них.

Примечание:

Если задан параметр password_policy.reuse_time, то параметр password_policy.in_history не используется.

Время жизни пароля#

password_policy.max_age#

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

password_policy.min_age#

Время в секундах, которое должно пройти между двумя изменениями пароля.

password_policy.grace_login_limit#

Максимальное количество аутентификаций, доступных роли после истечения времени жизни пароля.

password_policy.grace_login_time_limit#

Время в секундах после окончания действия пароля, в течение которого он продолжает работать. Если вычисленное зачение graceloginlimit=0, то параметр не учитывается.

password_policy.expire_warning#

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

Поведение при неудачной аутентификации#

password_policy.lockout#

Блокировка аккаунта в результате достижения максимума попыток входа с неверным паролем:

• on – включить блокировку;

• off – выключить блокировку.

password_policy.max_failure#

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

password_policy.failure_count_interval#

Время в секундах, после которого обнуляется количество неверных вводов пароля.

password_policy.lockout_duration#

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

Синтаксические проверки пароля#

password_policy.check_syntax#

Признак включенных правил синтаксической проверки пароля:

• on – включить механизм;

• off – выключить механизм.

password_policy.alpha_numeric#

Минимальное количество цифр в пароле. Если вычисленное зачение checksyntax=false, то параметр не учитывается.

password_policy.min_length#

Минимальная длина пароля. Если вычисленное зачение checksyntax=false, то параметр не учитывается.

password_policy.min_alpha_chars#

Минимальное количество букв в пароле. Если вычисленное зачение checksyntax=false, то параметр не учитывается.

password_policy.min_special_chars#

Минимальное количество символов в пароле, не являющихся буквой или цифрой. Если вычисленное значение checksyntax=false, то параметр не учитывается.

password_policy.min_uppercase#

Минимальное количество прописных букв. Если вычисленное зачение checksyntax=false, то параметр не учитывается.

password_policy.min_lowercase#

Минимальное количество строчных букв. Если вычисленное зачение checksyntax=false, то параметр не учитывается.

password_policy.max_rpt_chars#

Максимальное количество повторяющихся символов. Если вычисленное зачение checksyntax=false, то параметр не учитывается.

Проверка максимального времени неактивности пользователя#

password_policy.track_login#

Запоминать ли время последней аутентификации:

• on – запоминать;

• off – не запоминать.

password_policy.max_inactivity#

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

Использование библиотеки zxcvbn#

password_policy.use_password_strength_estimator#

Включить или выключить использование библиотеки zxcvbn для проверки пароля:

• on – включить механизм;

• off – выключить механизм.

password_policy.password_strength_estimator_score#

Минимальная оценка сложности пароля, допустимая в системе. Если вычисленное зачение usepasswordstrengthestimator=false, то параметр не учитывается.

Использование пользовательской функции проверки пароля#

password_policy.custom_function#

Название пользовательской PL/pgSQL функции проверки пароля.

Использование библиотеки cracklib#

password_policy.illegal_values#

Использовать библиотеку cracklib для проверки пароля по списку часто используемых:

• on – включить проверку;

• off – выключить проверку.

Настройка кэширования#

password_policy.pp_cache_dump_interval#

Интервал сохранения данных кэша из памяти на диск (при наличии изменений).

password_policy.pp_cache_init_size#

Размер изначально инициализированного кэша парольных политик в пользователях.

password_policy.pp_cache_soft_max_size#

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

password_policy.pp_cache_max_size#

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

psql_encrypt_password#

Шифрование пароля при передаче от фронтенда (psql) к базе:

• on – включить шифрование;

• off – выключить шифрование.

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

password_policy.deduplicate_ssl_no_ssl_fail_auth_attepmts#

Включение механизма дедупликации повторных попыток подключения psql.

password_policy.allow_hashed_password#

Разрешить задание пароля в виде хеша.

Авторизация и аутентификация#

Pangolin поддерживает несколько типов авторизации и аутентификации пользователей и сервисов.

В данном разделе более подробно рассмотрены варианты сквозной и двухфакторной авторизации.

Сквозная аутентификация pgBouncer — Pangolin#

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

Количество итераций обмена данными аутентификации для конкретного пользователя зависит от установленных в файле pg_hba.conf методов аутентификации. Обмен данными аутентификации между pgBouncer и Pangolin выполняется по отдельным сетевым каналам. Количество сетевых каналов зависит от количества баз данных, к которым выполняется подключение пользователей.

Конфигурирование сквозной аутентификации в pgBouncer#

Настроить сквозную аутентификацию в pgBouncer можно с помощью конфигурационных параметров, описанных в данном разделе.

Параметры аутентификации:

• auth_proxy (string) — параметр включает/выключает режим сквозной аутентификации:

  • off — режим сквозной аутентификации выключен, выполняется локальная аутентификация пользователя (значение по умолчанию);

  • on — режим сквозной аутентификации включен, выполняется аутентификация пользователя только на Pangolin;

• auth_failure_threshold (integer) — параметр задает максимальное число НЕ аутентифицированного N раз подряд клиента с идентичными параметрами (тип соединения, адрес клиента, база данных и имя пользователя), при котором будет взведен таймер неактивности аутентификации для этого клиента. Значение по умолчанию 0 (выключено);

• auth_inactivity_period (integer) — параметр определяет период неактивности аутентификации (в секундах). Это время, в течение которого ранее НЕ аутентифицированному более N раз подряд клиенту при подключении с идентичными параметрами (тип соединения, адрес клиента, база данных и имя пользователя), pgBouncer откажет в обслуживании. Значение по умолчанию 0 (выключено);

• auth_lost_size (integer) — параметр задает максимальное число кэшируемых записей о последних аутентификациях пользователей. Значение по умолчанию 10. Информацию о последних аутентификациях пользователей можно получить с помощью команды show last (см. подробнее в подразделе «Команды вывода информации» текущего раздела);

• log_audit (integer) — включает/выключает аудит. Значение по умолчанию - 0 (выключено).

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

• auth_port (integer) — номер порта, к которому нужно подключиться для выполнения аутентификации пользователей. Параметр раздела базы данных [databases];

• auth_pool_size (integer) — параметр задает максимальное количество соединений для выполнения аутентификации пользователей. Значение по умолчанию 1. Параметр раздела базы данных [databases].

Пример конфигурации (содержит параметры, связанные со сквозной аутентификацией):

[databases]
* =м host=<ip_address> port=5433 auth_port=5434

[pgbouncer]
 listen_addr = *
 listen_port = 6544
; включена сквозная аутентификация
 auth_proxy = on
; включен audit
 log_audit = 1
; выставлено время выполнения аутентификации
 client_login_timeout = 10
; выставлен порог, по превышению которого пользователь временно блокируется
 auth_failure_threshold = 3
; выставлен период неактивности аутентификации
 auth_inactivity_period = 30
; выставлен размер кешируемых записей о последних аутентификациях пользователей
 auth_last_size = 20

; пользователи, прописанные в userlist, будут выполнять аутентификацию, используя данный метод
 auth_type = scram-sha-256
; в файле userlist содержатся пользователи, выполняющие действия администратора или мониторинг
 auth_file = ./userlist.txt
 admin_users = pgbouncer
 stats_users = stat

Внимание!

Сквозная аутентификация не работает для пользователей, которые указаны в секции [users] конфигурационного файла pgBouncer.

Раздел [users] содержит пары ключ=значение, где в качестве ключа принимается имя пользователя, а в качестве значения — переопределяемые для него параметры конфигурации (в формате строк подключения libpq):

• pool_mode – задает режим пула для всех подключений данного пользователя;

• max_user_connections – задает максимум подключений для пользователя.

Пользователь, для которого переопределен один или оба параметра:

• должен быть указан в параметре stats_users или admin_users;

• должен присутствовать в списке userlist.txt (параметр auth_file) в виде "имя_пользователя" "хеш_пароля_пользователя".

Пример конфигурации (содержит параметры, связанные с использованием секции [users] файла /etc/pgbouncer/pgbouncer.ini):

[pgbouncer]
; в файле userlist содержатся пользователи, выполняющие действия администратора или мониторинг
auth_file = ./userlist.txt
admin_users = pgbouncer
stats_users = user1
[users]
; раздел содержит имя пользователя и переопределяемые для него параметры конфигурации
user1 = pool_mode=session max_user_connections=1

Пользователю user1 при таких настройках будут применены персональные параметры pool_mode и max_user_connections, а аутентификация в базе данных будет выполняться только после аутентификации его в pgBouncer.

Конфигурирование сквозной аутентификации в Pangolin#

Настроить сквозную аутентификацию в Pangolin можно с помощью конфигурационных параметров, описанных в данном разделе.

Параметры аутентификации:

• authentication_proxy (integer) — параметр включает/выключает режим сквозной аутентификации:

  • 0 — режим сквозной аутентификации выключен, не позволяет выполнять аутентификацию пользователей конкретной БД в отдельном потоке (значение по умолчанию);

  • 1 — режим сквозной аутентификации включен, позволяет выполнять аутентификацию пользователей конкретной БД в отдельном потоке;

• auth_handshake_timeout (integer) — параметр определяет максимальное время, за которое должно произойти подтверждение аутентификации (в секундах). Значение по умолчанию 10 сек. Если потенциальный клиент не сможет выполнить подтверждение аутентификации (рукопожатие) за это время, сервер закроет соединение;

• auth_activity_period (integer) — параметр определяет период активности аутентификации (в секундах). Значение по умолчанию 60 сек. Это время, в течение которого ранее аутентифицированный клиент при подключении с идентичными параметрами (тип соединения, адрес клиента, база данных и имя пользователя), выполнит аутентификацию по token.

  Значение передается на pgBouncer и используется для проведения более быстрой аутентификации. Возможные значения:

  • -1 — не используется период активности аутентификации;

  • 0 — период активности аутентификации не имеет ограничений по времени;

  • > 0 — имеет ограничение по времени.

Примечание:

• не рекомендуется выставлять значение auth_activity_period = 0, так как его нельзя сбросить в pgBouncer без перезагрузки;

• выставляемого значения должно хватить, чтобы поднять пул соединений между pgBouncer и Pangolin.

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

authentication_port (integer) — TCP-порт, открываемый сервером для выполнения аутентификации пользователей (по умолчанию порт — 5433).

Примечание:

Параметр authentication_port (integer) можно задать только при запуске сервера.

Пример конфигурации (содержит параметры связанные со сквозной аутентификацией):

port = 5433

authentication_port = 5434
authentication_timeout 60 # sec
auth_activity_period = 60 # sec

Команды вывода информации#

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

• SHOW AUTHSERVERS — показывает информацию о соединениях с сервером аутентификации.

• SHOW AUTHPOOLS — показывает информацию по пулам аутентификации.

  Примечание:

  Новый пул аутентификации создается для каждой базы данных.

• SHOW AUTHUSERS — показывает информацию о пользователях.

• SHOW LAST — показывает информацию об аутентификации последних N пользователей.

  Примечание:

  auth_last_size — по умолчанию команда показывает 10 последних пользователей.

  При превышении этого значения первые записи удаляются, а новые добавляются в конец. Команда не хранит причину, по которой не прошла аутентификация. Ее требуется искать в логах Pangolin или pgBouncer. В каком логе и в какое время - зависит от значений параметров place и connect_time\auth_time.

• SHOW LOCKED_USERS — показывает информацию о заблокированных пользователях.

  Команда показывает временно заблокированных пользователей. Это пользователи с идентичными параметрами: тип соединения, адрес клиента, база данных и имя пользователя, которые не смогли аутентифицироваться N раз подряд (N определяет конфигурационный параметр auth_failure_threshold). Длительность блокировки пользователя определяется конфигурационным параметром auth_inactivity_period.

Подробное описание команд в «Документация на публичные API: PL/pgSQL», раздел «Описание API функциональности сквозной аутентификации».

В утилите psql имелся механизм запоминания ранее выполненных запросов. Этот механизм позволял просмотреть историю ранее выполненных команд и повторно вызвать ранее выполненные запросы.

История запросов хранилась в открытом виде в файле ~/.psql_history. При выполнении запросов, содержащих пароли это могло представлять угрозу безопасности, так как пароли хранились так же в незашифрованном виде.

В связи с новой функциональностью изменяется поведение утилиты psql по сравнению с имеющимся:

• создание и чтение файла ~/.psql_history не производится;

• влияние переменной окружения PSQL_HISTORY на работу утилиты psql отсутствует;

• установка переменной HISTFILE в утилите psql не дает эффекта.

Реализованная функциональность не приводит к удалению или очищению ранее созданных файлов истории запросов. После обновления продукта рекомендуется (если необходимо) удалить ранее созданные файлы истории, выполнив команду:

rm -f ~/.psql_history

Двухфакторная аутентификация#

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

Пользователь может подключаться к БД:

• непосредственно (или напрямую) к Pangolin;

• через pgBouncer к Pangolin, используя сквозную аутентификацию;

• через pgBouncer к Pangolin, используя базовые механизмы аутентификации.

Сертификат безопасности клиента должен содержать поле CN, содержащее логин клиента и, опционально, поле SubjectAltName, содержащее один или несколько IP-адресов (возможен вариант указания подсети) и (или) DNS-имен клиента.

Для подключения непосредственно к Pangolin, в файле pg_hba.conf необходимо указать метод аутентификации: 2f-scram-sha-256, 2f-md5, 2f-password или 2f-ldap.

Для подключения через pgBouncer к Pangolin, используя сквозную аутентификацию, необходимо:

• в конфигурационных файлах Pangolin:

  • в файле pg_hba.conf необходимо указать метод аутентификации: 2f-scram-sha-256, 2f-md5, 2f-password или 2f-ldap;

  • в файле postgresql.conf указать:

    • authentication_proxy = on;

    • authentication_port = {AUTHPORT};

• в конфигурационном файле pgBouncer указать:

  • auth_port={AUTHPORT};

  • auth_proxy = on;

  • auth_type = scram-sha-256 или md5.

Для подключения через pgBouncer к Pangolin, используя базовые механизмы аутентификации, необходимо в конфигурационном файле pgBouncer указать:

• в конфигурационных файлах Pangolin:

  • в файле pg_hba.conf необходимо указать метод аутентификации: 2f-scram-sha-256, 2f-md5, 2f-password или 2f-ldap;

  • в файле postgresql.conf указать authentication_proxy = off;

• в конфигурационном файле pgBouncer указать:

  • auth_proxy = off;

  • auth_type = 2f-scram-sha-256 (2f-md5, 2f-plain);

• Аутентификация LDAP не поддерживается pgBouncer, поэтому 2f-ldap так же не поддерживается.

Внимание!

Необходимо учитывать, что разрешенными по умолчанию механизмами двухфаторной аутентификации являются 2f-scram-sha-256 и 2f-ldap.

При необходимости использования методов аутентификации 2f-md5 и 2f-password, нужно добавить эти методы в параметр enabled_extra_auth_methods в конфигурационном файле postgresql.conf.

Например:

enabled_extra_auth_methods = '2f-md5,2f-password'

Настройка Pangolin для двухфакторной аутентификации#

Для выполнения двухфакторной аутентификации клиентов требуется:

• в файле postgresql.conf - включить SSL режим и прописать сертификаты:

  ssl = on
  ssl_ca_file = './root.crt'
  ssl_cert_file = './server.crt'
  ssl_key_file = './server.key'
  

• в файле pg_hba.conf указать:

  • тип сети - hostssl;

  • тип аутентификации: 2f-md5 или 2f-scram-sha-256;

  # TYPE  DATABASE        USER            ADDRESS                 METHOD
  hostssl    test           test1             127.0.0.1/32        2f-md5
  hostssl    test           test1             hostname            2f-scarm-sha-256
  

Настройка pgBouncer для двухфакторной аутентификации#

Для выполнения двухфакторной аутентификации клиентов требуется в файле конфигурации pgbouncer.ini (имя файла конфигурации может быть другим) указать:

• параметры подключения SSL/TLS;

• тип аутентификации: 2f-scram-sha-256 или 2f-md5;

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

[pgbouncer]
auth_type = 2f-scram-sha-256
 
; TLS settings
client_tls_protocols = all
client_tls_sslmode = verify-full
client_tls_ca_file = ./root.crt
client_tls_cert_file = ./server.crt
client_tls_key_file = ./server.key

Настройка сквозной двухфакторной аутентификации#

Все специальные настройки были описаны ранее — необходимо только включить сквозную аутентификацию на pgBouncer с Pangolin.

В файле pgbouncer.ini:

[pgbouncer]
 auth_proxy = on
 auth_type = scram-sha-256
 
 client_tls_sslmode = verify-ca
 client_tls_key_file = ./server.key
 client_tls_cert_file = ./server.crt
 client_tls_ca_file = ./root.crt
 
 server_tls_sslmode = verify-full
 server_tls_key_file = ./pgbouncer.key
 server_tls_cert_file = ./pgbouncer.crt
 server_tls_ca_file = ./root.crt
 server_tls_ciphers = normal
 
[databases]
* = host=localhost port=5432 auth_port=5433  auth_port=5444 auth_pool_size=1

В файле postgresql.conf:

port = 5432
authentication_port = 5433
authentication_timeout = 60 # sec
auth_activity_period = 10 # sec
 
ssl = on
ssl_key_file = ./server.key
ssl_cert_file = ./server.crt
ssl_ca_file = ./root.crt

Генерация сертификатов#

Для включения SSL между компонентами кластера необходимо подготовить сертификаты и пароль для сервиса статистики haproxy.

Подготовка сертификатов не выполняется инсталлятором Pangolin. Ожидается, что на хосте уже есть сертификаты, которые будут переданы на вход инсталлятору. Инсталлятор сам разместит их в нужных директориях и выдаст права для служебных пользователей (например, etcd).

• Серверный сертификат (необходимо создать для каждого хоста в кластере):

  1. Сгенерируйте ключ:

  openssl genrsa -out server.key 2048
  

2. Создайте файл конфигурации для создания запроса на подпись сертификата vim server.conf:

   [req]
   req_extensions = v3_req
   distinguished_name = req_distinguished_name
   [req_distinguished_name]
   [ v3_req ]
   basicConstraints = CA:FALSE
   keyUsage = nonRepudiation, digitalSignature, keyEncipherment
   subjectAltName = @alt_names
   [ ssl_client ]
   extendedKeyUsage = clientAuth, serverAuth
   basicConstraints = CA:FALSE
   subjectKeyIdentifier=hash
   authorityKeyIdentifier=keyid,issuer
   subjectAltName = @alt_names
   [ v3_ca ]
   basicConstraints = CA:TRUE
   keyUsage = nonRepudiation, digitalSignature, keyEncipherment
   subjectAltName = @alt_names
   authorityKeyIdentifier=keyid:always,issuer
   [alt_names]
   DNS.1 = <host> ## hostname with domain
   IP.1 = <IP-адрес> ## host ip address
   

3.  Экспортируйте файл конфигурации:
    
    ```
    CONFIG=`echo $PWD/server.conf`
    ```
    
4.  Создайте запрос на подпись сертификата. В `CN` необходимо указать полный `hostname`:

 ```
 openssl req -new -key server.key -out server.csr -subj "/CN=<host>" -config ${CONFIG}
 ```

• Клиентский сертификат:

  1. Сгенерируйте ключ:

     openssl genrsa -out postgres.key 2048
     

  2. Создайте файл конфигурации для создания запроса на подпись сертификата vim client.conf:

     [req]
     req_extensions = v3_req
     distinguished_name = req_distinguished_name
     [req_distinguished_name]
     [ v3_req ]
     basicConstraints = CA:FALSE
     keyUsage = nonRepudiation, digitalSignature, keyEncipherment
     [ ssl_client ]
     extendedKeyUsage = clientAuth
     basicConstraints = CA:FALSE
     subjectKeyIdentifier=hash
     authorityKeyIdentifier=keyid,issuer
     [ v3_ca ]
     basicConstraints = CA:TRUE
     keyUsage = nonRepudiation, digitalSignature, keyEncipherment
     authorityKeyIdentifier=keyid:always,issuer
     

  3. Экспортируйте файл конфигурации:

     CONFIG=`echo $PWD/client.conf`
     

4. Создайте запрос на подпись сертификата. В CN необходимо указать имя клиента/компонента (PostgreSQL, patroni, patronietcd, pgbouncer, haproxy):

   openssl req -new -key postgres.key -out postgres.csr -subj "/CN=postgres" -config ${CONFIG}
   

> **Примечание:**
>
> При создании клиентских сертификатов, в поле `subjectAltName` можно указать IP-адрес или(и) DNS-имя машины (список машин), на которой(ых) будет использоваться сертификат. Также в этом поле можно указать адрес подсети.

Сгенерированные запросы на подпись сертификатов (файлы в формате *.csr) необходимо подписать удостоверяющем центре.

Полученные сертификаты необходимо перевести в формат PEM (например, командой openssl x509 -inform DER -outform PEM -in ./certificate.cer -out ./certificate.crt), расположить в одинаковых директориях на каждом хосте и выдать права - 600 для ключей и 644 для сертификатов, владелец — УЗ ОС postgres.

Наименование сертификатов, с которыми будет работать инсталлятор:

Поскольку в PostgreSQL можно указать только один корневой сертификат, а в нашем случае их два (один — УЦ непосредственно выпустивший сертификат, второй — УЦ выпустивший сертификат для УЦ и имеющий признак CA), необходимо объединить сертификаты корневого и промежуточного УЦ в один:

cat root.crt intermediate.crt > rootCA.crt

Все корневые сертификаты необходимо скопировать в папку /etc/pki/ca-trust/source/anchors и выполнить команду обновления хранилища доверенных корневых сертификатов:

sudo update-ca-trust

Если используется компонент haproxy, то для него необходимо сертификат и приватный ключ объединить в один файл, например, командой:

cat server-key.pem server.pem >> haproxy.pem

Данный сертификат будет использоваться для подключения по HTTPS на сервер статистики.

Настройка компонентов#

Внимание!

В процессе работы инструмента развертывания производится валидация сертификатов на их соответствие требованиям указанным в разделе «Подготовка». После работы инструмента развертывания валидация сертификатов находится на стороне владельца стенда.

Произведите настройку компонентов на использование сертификатов (задача инструмента развертывания):

1. В файле postgres.conf (для стендов в конфигурации standalone-postgresql-pgbouncer). В данном случае необходимо указать путь к подписанным сертификатам для сервера БД:

   ssl = 'on'
   ssl_cert_file = '/home/postgres/*/server.crt'
   ssl_key_file = '/home/postgres/*/server.key'
   ssl_ca_file = '/home/postgres/*/rootCA.crt'
   ssl_crl_file = 'путь до файла со списком отозванных сертифкатов'
   

2. В pgbouncer.ini добавьте секцию TLS-настроек. Между pgbouncer и PostgreSQL настраивается обязательное SSL-соединение, между pgbouncer и клиентом — по требованию клиента. В обоих случаях минимальная версия TLS 1.2 и соответствующие ему шифры (Cipher suites (TLS 1.2): ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-RSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-CHACHA20-POLY1305:ECDHE-RSA-CHACHA20-POLY1305:DHE-RSA-AES128-GCM-SHA256:DHE-RSA-AES256-GCM-SHA384).

   #  TLS settings
   server_tls_protocols = secure
   server_tls_ciphers = secure
   server_tls_sslmode = verify-full
   server_tls_ca_file = /home/postgres/*/rootCA.crt
   server_tls_cert_file = /home/postgres/*/pgbouncer.crt
   server_tls_key_file = /home/postgres/*/pgbouncer.key
   client_tls_protocols = secure
   client_tls_ciphers = secure
   client_tls_sslmode = prefer
   client_tls_ca_file = /home/postgres/*/rootCA.crt
   client_tls_cert_file = /home/postgres/*/server.crt
   client_tls_key_file = /home/postgres/*/server.key
   

3. В etcd.conf значения http переведите в https и добавьте секцию настроек TLS. Аутентификация в БД etcd происходит по паролю, так же необходимо указать сертификат пользователя.

ETCD_NAME="tkles-core00133"
ETCD_LISTEN_CLIENT_URLS="<URL>:2379"
ETCD_ADVERTISE_CLIENT_URLS="<URL>:2379"
ETCD_LISTEN_PEER_URLS="https://0.0.0.0:2380"
ETCD_INITIAL_ADVERTISE_PEER_URLS="<URL>:2380"
ETCD_INITIAL_CLUSTER_TOKEN="test"
ETCD_INITIAL_CLUSTER="tkles-core00133=<URL>:2380,tkles-core00075=<URL>:2380,tkles-core00077=<URL>:2380"
ETCD_INITIAL_CLUSTER_STATE="new"
ETCD_DATA_DIR="/var/lib/etcd"
ETCD_ELECTION_TIMEOUT="5000"
ETCD_HEARTBEAT_INTERVAL="1000"
ETCD_ENABLE_V2="false"
# TLS settings
ETCD_CIPHER_SUITES="TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256,TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384"
ETCD_TRUSTED_CA_FILE="/home/postgres/*/rootCA.crt"
ETCD_CERT_FILE="/home/postgres/*/server.crt"
ETCD_KEY_FILE="/home/postgres/*/server.key"
ETCD_PEER_TRUSTED_CA_FILE="/home/postgres/*/rootCA.crt"
ETCD_PEER_CERT_FILE="/home/postgres/*/server.crt"
ETCD_PEER_KEY_FILE="/home/postgres/*/server.key"
ETCD_PEER_CLIENT_CERT_AUTH="true"
ETCD_PEER_CRL_FILE="путь до файла со списком отозванных сертификатов"

4. В файле конфигурации patroni postgres.yml внесите изменения в секции restapi, etcd3, postgres, pg_hba.

   1. restapi добавить параметры verify_client, cafile, certfile, keyfile - пути до сертификатов. Параметр verify_client: optional - означает, что все запросы «управления» PUT, POST, PATCH, DELETE требуют аутентификации по сертификатам. Параметры allowlist: [] allowlist_include_members: true - означают, что доступ к запросам «управления» есть только у членов кластера и хостов, которые перечислены в allowlist. Запросы GET (только получение сведений о кластере) будут возвращаться без аутентификации.

   restapi:
       listen: 0.0.0.0:8008
       connect_address: <host>:8008
       allowlist: []
       allowlist_include_members: true
       verify_client: optional
       cafile: /home/postgres/ca/rootCA.crt
       certfile: /home/postgres/ca/server.crt
       keyfile: /home/postgres/ca/server.key
       authentication:
           username: patroniyml
           password: $enc$gDr8F+Suzo3TToVigCBX2se9MJW3Ie0kqyJ+PClL5UA=
   

   2. В секции etcd добавьте параметры protocol, cacert, cert, key. Соединение с etcd будет осуществляться по протоколу HTTPS.

   etcd:
       hosts: <host>:2379,<host>:2379,<host>:2379
       protocol: https
       cacert: /home/postgres/*/rootCA.crt
       cert: /home/postgres/*/patronietcd.crt
       key: /home/postgres/*/patronietcd.key
       username: patronietcd
       password: $enc$cVrbd96FvJJGAY5dUq6M2Mta1SciGLDvMZ4kJzh831Y=
   

5. В секции postgresql в подразделе аутентификации пользователя patroni добавьте параметры sslmode, sslkey, sslcert, sslrootcert и в разделе parameters укажите путь к серверному сертификату. sslmode устанавливается в значение verify-ca из-за особенностей локального подключения.

   postgresql:
       listen: 0.0.0.0:5433
       bin_dir: /usr/pgsql-se-05/bin
       connect_address: <host>:5433
       data_dir: /pgdata/05/data/
       create_replica_methods:
           - basebackup
       basebackup:
           format: plain
           wal-method: fetch
       authentication:
           replication:
               username: patroni
               database: replication
               sslmode: verify-ca
               sslkey: /home/postgres/*/patroni.key
               sslcert: /home/postgres/*/patroni.crt
               sslrootcert: /home/postgres/*/rootCA.crt
               sslcrl: "путь до файла со списком отозванных сертификатов"
           superuser:
               username: patroni
               sslmode: verify-ca
               sslkey: /home/postgres/*/patroni.key
               sslcert: /home/postgres/*/patroni.crt
               sslrootcert: /home/postgres/*/rootCA.crt
               sslcrl: "путь до файла со списком отозванных сертификатов"
   
           ssl: 'on'
           ssl_cert_file: /home/postgres/*/server.crt
           ssl_key_file: /home/postgres/*/server.key
           ssl_ca_file: /home/postgres/*/rootCA.crt
           ssl_crl_file: "путь до файла со списком отозванных сертификатов"
   

6. В секции pg_hba смените метод подключения для УЗ patroni с host на hostssl:

   hostssl all patroni <IP-адрес>/32 scram-sha-256
   hostssl all patroni <IP-адрес>/32 scram-sha-256
   hostssl replication patroni <IP-адрес>/32 scram-sha-256
   hostssl replication patroni <IP-адрес>/32 scram-sha-256
   

7. haproxy.cfg - в haproxy активируется режим passthrough, добавляются проверки health-check по SSL (для patroni на порту 8008) без верификации. Доступ к странице статистики осуществляется по протоколу HTTPS с методом аутентификации «логин-пароль». Доступ к серверу статистики, порт 7000, haproxy осуществляется на основе access list, где по-умолчанию ставится localhost, а дополнительные хосты можно прописать в файле настраиваемого конфигурационного файла.

frontend fe_postgresql
    mode tcp
    option tcplog
    bind *: 5001
    default_backend be_postgres
  
backend be_postgres
   mode tcp
   option tcplog
   option httpchk OPTIONS /master #
   http-check expect status 200
   default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions #
   server <host> <host>:6544 verify none maxconn 100 check check-ssl port 8008
   server <host> <host>:6544 verify none maxconn 100 check check-ssl port 8008
  
listen stats
    mode http
    bind *:7000 ssl crt /home/postgres/*/haproxy.crt
    stats enable
    stats uri /
    stats auth login:password
    acl whitelist src <IP-адрес>
    tcp-request connection reject if ! whitelist

6. Если какой-либо сертификат был просрочен или отозван, необходимо выпустить новый, согласно инструкции описанной в разделе «Генерация сертификатов», и, в случае изменения наименования сертификата, произвести настройку компонента, где данный сертификат был задействован. В случае сохранения имени сертификата, необходимо перезапустить сервисы компонентов после замены файлов сертификатов.

Архивирование и восстановление#

Для архивирования и восстановления используется Подсистема резервного копирования и репликации. Она включает в себя утилиты для резервного копирования и восстановления, например, pg_probackup, а также для потоковой и логической репликации.

В системе реализована поддержка резервного копирования, которая настраивается в конфигурационном файле custom_file.yml через параметр SRC (резервная копия системы).

Примечание:

Подробное описание конфигурационного файла приведено в документе «Руководство по установке», раздел «Установка».

Ниже приведен пример шаблона параметров формирования SRC спецификаций для резервного копирования (файл datalist_serveraXserverb_RUN_PG_FULL.j2, где servera - имя мастера-сервера, serverb - имя реплицирующего сервера):

DATALIST "hostname-serveraXhostname-serverb_RUN_PG_FULL"`
GROUP "DININFRA"
DESCRIPTION "PostgreSQL_SE"
RECONNECT
DYNAMIC 1 1
POSTEXEC "patroni_session_run.sh" -on_host "{{ data_protector_host }}"
DEFAULTS
{
    FILESYSTEM
    {
        -vss    no_fallback
    } -protect days 3
    RAWDISK
    {

    }
}

DEVICE "{{ device }}"
{
}

FILESYSTEM "fqdn-servera" fqdn-serverb:"/"
{
    -trees
        "/etc/opt/omni/client/cell_server"
}

FILESYSTEM "fqdn-serverb" fqdn-serverb:"/"
{
    -trees
        "/etc/opt/omni/client/cell_server"
}

А так же шаблон формирования расписания создания резервной копии (файл schedule_serveraXserverb_RUN_PG_FULL.j2, где servera - имя мастер-сервера, serverb - имя реплицирующего сервера):

-full
-every
     -day
     -at {{ (23,0,1,2) |random }}:{{ '%02d' | format( 59 | random | int )}}

Мониторинг блокировок#

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

Механизм отслеживания блокировок представляет собой набор представлений, позволяющий проводить оперативный мониторинг блокировок в разрезе:

• заблокированных объектов;

• типов блокировок;

• параметров сессии, которая заблокировала объект (при наличии прав);

• длительности сессий, запросов, ожидания блокировок, смены статуса (при наличии прав);

• текста последнего запроса в сессии (при наличии прав);

• дерева блокировок, при наличии очереди заблокированных объектов.

Поставка механизма отслеживания блокировок производится в виде расширения psql_lockmon.

Предусмотрена автоматизация развертывания решения на уровне специализированного сценария (custom.yml). Решение по умолчанию устанавливатся в БД пользователя и в шаблонную БД.

Управление учетными записями#

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

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

Парольные политики описываются в документе «Документация на публичные API: PL/pgSQL» раздел «Функции для работы с парольными политиками».

Разграничение доступа к данным#

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

Управление доступом на уровне ролей (RBAC)#

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

Подробнее в разделе «Ролевая модель и права доступа».

Row level security#

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

Защита от привилегированных пользователей#

В Platform V Pangolin используется механизм защиты данных от привилегированных пользователей, построенный на принципе разделения ролей.

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

• администраторы БД имеют произвольный доступ к любым пользовательским данным, что может привести к утечкам конфиденциальной информации;

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

В Platform V Pangolin Администраторы БД и ОС теряют возможность самостоятельно управлять некоторыми параметрами и перестают иметь полный доступ ко всем объектам БД.

В дополнение к роли суперпользователя, которая есть в стандартной версии PostgreSQL, в Pangolin можно создать специальную роль Администратора безопасности (АБ).

АБ – независимый администратор, не обладающий особыми правами в операционной системе (в том числе не имеющий прав Linux-пользователя postgres) и не имеющий доступа к объектам БД. Внутри БД роль Администратора безопасности является особенной — она не может быть изменена суперпользователем. Таким образом, ни один из пользователей не может единолично получить доступ к конфиденциальным данным или изменить важные для безопасности настройки БД и права доступа.

Администратор безопасности создается при инициализации кластера, либо существующим администратором безопасности через функцию pm_grant_security_admin.

Примечание:

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

При защите схемы целиком права на доступ к объектам выдаются на все объекты заданного типа, находящиеся в защищенной схеме.

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

Ниже описаны основные функции, подробнее о функциях в «Документации на публичные API», раздел «Защита от привилегированных пользователей».

Поставить объект под защиту#

Для защиты пользовательских данных, хранимых в объектах базы, можно поставить объект под защиту с помощью функции pm_protect_object, например:

SELECT pm_protect_object('role', 'u1');

Снять защиту с объекта#

В случае, если защита более не требуется, она может быть снята вызовом функции pm_unprotect_object, например:

SELECT pm_unprotect_object('table','ext.t1');

Доступ к защищенному объекту#

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

Для осуществления доступа к таким объектам создаются политики доступа через функцию pm_make_policy, и наполняются правилами доступа к объекту (какие действия над объектом разрешены для выполнения).

Пример создания политики:

SELECT pm_make_policy('pol1');

Функция pm_grant_to_policy добавляет правило в политику, например:

SELECT pm_grant_to_policy('pol1', 'table', 'ext.t1', array['select','update']::name[]);

Назначить политику пользователю#

Политика может быть назначена пользователю с помощью функции pm_assign_policy_to_user. Он получит разрешение на выполнение действий над защищенными объектами согласно правилам назначенной политики.

Пример назначения политики пользователю:

SELECT pm_assign_policy_to_user('u1', 'pol1');

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

Запретить выданные права доступа#

Для запрета ранее выданных прав доступа:

• политика может быть отозвана целиком вызовом функции pm_unassign_policy_from_user, например:

  SELECT pm_unassign_policy_from_user('u1','pol1');
  

• или из назначенной политики могут удаляться конкретные правила — pm_revoke_from_policy, например:

  SELECT pm_revoke_from_policy('pol1', 'function', 'public.f1', array['call', 'drop', 'alter']::name[]);
  

Защита всех объектов в схеме#

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

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

SELECT pm_grant_to_policy('имя_политики',  'тип_объекта', 'имя_схемы.*', array['действие1', ..., 'действиеN']::name[]);

Примечание:

Символ * после имени схемы означает применимость этого правила ко всем объектам заданного типа в данной схеме, как существующим на момент определения правила, так и к вновь добавляемым в данную схему.

Пример обобщенного правила для действия SELECT над всеми таблицами в защищенной схеме myschema:

SELECT pm_grant_to_policy('schema_objects_policy', 'table', 'myschema.*', array['select']::name[]);

Объект в защищенной схеме может находиться под индивидуальной защитой. В этом случае настройка доступа к нему производится без учета защиты схемы в которой объект расположен.

Предоставление доступа ко всем объектам защищенной схемы#

Исходя из ранее созданных политик и логики приложения, определите необходимость отдельной политики для предоставления доступа к защищенной схеме. В случае необходимости создайте новые политики доступа к защищенным объектам (pm_make_policy). Затем наполните политику правилами доступа (pm_grant_to_policy). Для этого добавьте в политику правила доступа для всех таблиц (партиций, представлений, функций) схемы (см. пример и примечание выше в подразделе «Защита всех объектов в схеме»). С помощью команды pm_assign_policy_to_user, назначьте наполненную правилами доступа политику пользователям.

Предоставление доступа к объекту в защищенной схеме#

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

• объект под защитой — политики, разрешающие доступ к объекту, содержат правила доступа только к данному объекту БД;

• объект под защитой через схему — доступ к объекту предоставляется политиками, содержащими правила доступа ко всем объектам данного типа в схеме.

После определения типа защиты необходимо наполнить политику правилами, при этом, если:

• объект под защитой: наполните политику правилами доступа к конкретному объекту;

• объект под защитой через схему: наполните политику правилами доступа ко всем объектам данного типа, находящимся в защищенной схеме.

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

Перенос объекта между схемами#

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

Обращение к объекту БД#

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

Механизм защиты данных проверяет или:

• нахождение запрашиваемого объекта под защитой и наличие у субъекта (пользователя) разрешений на выполнение действия над объектом БД;

• нахождение схемы, содержащей запрашиваемый объект, под защитой и наличие у субъекта (пользователя) разрешений на выполнение действия над данным типом объектов БД.

Если проверка пройдена и есть права на доступ, запрашивающей стороне будет возвращен результат выполнения запроса. В случае отсутствия прав на доступ, запрос завершится ошибкой.

Настроечные параметры, управляемые администраторами безопасности через KMS в режиме защищенного конфигурирования, находятся в таблице документа «Параметры, настраиваемые через KMS».

Маскирование запросов#

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

Модель функционирования СУБД Pangolin строится на выполнении запросов со стороны аутентифицированных и авторизованных пользователей. В том числе запросов на:

• управление ролями и пользователями, включая задание параметров аутентификации и авторизации, в том числе паролей;

• управление структурой хранимых данных, включая БД, схемы, табличные пространства, объекты схем;

• выполнение действий над хранимыми данными, таких как выборка, вставка, изменение и удаление.

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

Известные для СУБД Pangolin точки, в которых может быть выполнен вывод пользовательских запросов, следующие:

Выполняемые запросы могут содержать следующую информацию, которая не должна быть доступна администраторам СУБД (superuser) или лицам, имеющим доступ к файлам логов, но не имеющим доступ к БД (администраторы ОС):

• значения паролей или хешей паролей в запросах на задание или изменение паролей пользователей;

• значения параметров-паролей в функциях API администраторов безопасности;

• явно заданные как константы значения параметров в функциях;

• явно заданные как константы значения, вставляемые (INSERT) в таблицы и представления, в том числе заданные через выражения SELECT или CTE;

• явно заданные как константы значения, задаваемые для изменения полей (UPDATE) в таблицах и представлениях, или используемые для фильтрации записей для изменения;

• явно заданные как константы значения, задаваемые как условия выражений SELECT, UPDATE и DELETE по полям таблиц и представлений.

Для поддержки возможности сокрытия такой информации реализована функциональность маскирования указанных выше значений для категорий запросов при:

• выводе запроса в лог по условию log_statement=условие, влияет на попадание в лог запросов определенных типов - либо dll (только запросы DDL), либо mod (запросы DDL, модификации данных и COPY FROM), либо all (все запросы);

• выводе запроса в лог по условию log_min_duration_statement=минимальная длительность, влияет на попадание в лог запросов с определенной длительностью;

• выводе запроса в лог при ошибке по условию log_min_error_statement=уровень логирования запросов, влияет на попадание в лог запросов определенных типов;

• вывод в лог информации об обрабатываемых запросах от расширения auto_explain;

• вывод в лог информации об обрабатываемых запросах от расширения pg_hint_plan;

• вывод в лог (аудита) в соответствии с критериями аудита, включая значения параметров подготовленных запросов при pgaudit.log_parameter = on;

• накопление, сохранение в файл pg_stat_tmp/pgss_query_texts.stat и получение запросов из представления pg_stat_statements;

• накопление и получение текстов запросов из представления pg_stat_activity.

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

Внимание!

Сохранение текста запроса в файлы ASH (performance insight) - регулируется собственными параметрами performance insight и performance_insights.masking.

При этом, при заданном маскировании запросов через параметр masking_mode со значением full, маскирование запросов в ASH (performance insight) будет выполняться независимо от значения параметра performance_insights.masking. Это связано с тем, что ASH пользуется текстами запросов, помещенных в представление pg_stat_activity, и при включенном маскировании тексты запросов в представлении находятся в уже замаскированном виде.

Особенности маскирования запросов:

• текст подготовленных запросов при их вызове через EXECUTE также обрабатывается по вышеуказанным правилам;

• в случае вывода ошибки, в которой отдельно выводится лексема, которую Pangolin считает ошибочной: она должна быть замаскирована по тем же правилам. То есть, если эта лексема была замаскирована в полном тексте запроса, то должна быть замаскирована и в ошибке. Исключением являются ошибки в лексемах, определяющих категорию запроса - SELECT/INSERT/UPDATE/DELETE и CREATE/ALTER ROLE/USER.

Внимание!

Маскирование параметров функций, принимающих на вход конфиденциальную информацию, таких как pm_create_security_admin, pm_set_security_admin_password и add_auth_record_to_storage, будет выполняться только при значении full параметра masking_mode.

Настройка маскирования#

Маскирование запросов настраивается новым конфигурационным параметром masking_mode, который может принимать два значения:

• disabled - механизм отключен, маскирование запросов не производится;

• full - механизм маскирования работает по всем обрабатываемым запросам SELECT/INSERT/UPDATE/DELETE и их параметрам, а также запросам CREATE/ALTER ROLE/USER и параметру пароля в их составе.

По умолчанию механизм выключен (disabled), параметр хранится в postgresql.conf, а при защищенном конфигурировании (настроенное соединение с KMS + значение параметра secure_config = on) берется из хранилища секретов (KMS), из параметра кластера с именем masking_mode.

Для изменения значения параметра masking_mode выполните перезапуск СУБД Pangolin.

Автоматическое завершение неактивных соединений#

Соединения, остающиеся неактивными определенное время, подлежат принудительному завершению. Для завершения таких соединений используется фоновый процесс, осуществляющий мониторинг и завершение бездействующих клиентских сеансов. Этот процесс запускается при старте сервера Pangolin и завершается вместе с остановкой сервера.

Процесс регулируется двумя параметрами:

• check_idle_time_delay - интервал мониторинга в миллисекундах;

• backend_idle_alive_time - допустимое время бездействия в секундах.

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

HashiCorp Vault#

Решение HashiCorp Vault используется в качестве защищенного хранилища ключей шифрования и настроек и управления ключами.

Средства криптографической защиты информации#

Для использования шифрования между клиентом и сервером необходимо настроить OpenSSL на клиенте и на сервере.

SSL включается с помощью параметра ssl = on в файле postgresql.conf.

SSL использует файлы сертификата и закрытого ключа сервера. По умолчанию они называются server.crt и server.key. Эти названия менять не рекомендуется.

Доступ к файлу server.key должен быть ограничен командой chmod 0600 server.key.

Чтобы клиенты могли подключаться к серверу с помощью сертификатов, поместите сертификаты корневых центров сертификации в каталог data, укажите имя файла с сертификатами в параметре ssl_ca_file в postgresql.conf.

После этого добавьте параметр clientcert=1 в соответствующие строки hostssl в файле pg_hba.conf.

Снятие резервной копии со standby-базы#

Функция резервного копирования позволяет снять с базы данных архивную копию, которую в дальнейшем можно использовать для восстановления. PostgreSQL поддерживает создание резервной копии как с Active, так и с Standby.

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

PGPASSWORD={backup_pass} pg_probackup backup -B {PGBACKUP} --instance {cluster_name} -b FULL

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

pg_probackup restore -B {PGBACKUP} --instance {cluster_name} --recovery-target='latest

Процедуры резервного копирования на главном сервере и на копии принципиально идентичны.

Оптимизация таблиц#

В процессе работы с Pangolin возникает table bloat — ситуация, при которой данные таблиц будут храниться неэффективно. Они фрагментируются, что приводит к ухудшению производительности и нерациональному использованию места на диске.

Механизм работы с данными в PostgreSQL#

При первом наполнении таблицы данные добавляются последовательно и равномерно занимают блоки. Пример наполнения записями таблицы bloated_table:

CREATE TABLE bloated_table(id integer, data integer);
INSERT INTO bloated_table SELECT i, random() FROM generate_series(1, 1000000) AS g(i);

Посмотреть статистику по таблице можно с помощью запроса к pg_stat_user_tables командой ANALYZE:

ANALYZE bloated_table;
SELECT n_tup_ins, n_tup_upd, n_tup_del, n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'bloated_table';

 n_tup_ins | n_tup_upd | n_tup_del | n_live_tup | n_dead_tup
-----------+-----------+-----------+------------+------------
   1000000 |         0 |         0 |    1000000 |          0

Полученные результаты:

• n_tup_ins, n_tup_upd, n_tup_del — количество вставок, изменений, удалений строк таблицы;

• n_live_tup — актуальные записи;

• n_dead_tup — «мертвые» записи, помеченные на удаление.

Команда pg_size_pretty покажет объем таблицы на диске:

SELECT pg_size_pretty(pg_table_size('bloated_table'));
 pg_size_pretty
----------------
 35 MB

Симуляция фрагментации методом удаления каждой второй строки:

DELETE FROM bloated_table WHERE (id % 2) = 0;

Статистика таблицы после фрагментации:

n_tup_ins | n_tup_upd | n_tup_del | n_live_tup | n_dead_tup
-----------+-----------+-----------+------------+------------
   1000000 |         0 |    500000 |     500000 |     500000

 pg_size_pretty
----------------
 35 MB

Теперь 500 000 записей считаются «мертвыми» (dead) и могут быть удалены сборщиком мусора (VACUUM). При штатной работе это сделает auto vacuum, а для таблицы из примера очистка запущена вручную:

VACUUM bloated_table;

...

 n_tup_ins | n_tup_upd | n_tup_del | n_live_tup | n_dead_tup
-----------+-----------+-----------+------------+------------
   1000000 |         0 |    500000 |     500000 |          0

 pg_size_pretty
----------------
 35 MB

«Мертвых» записей больше нет, но размер таблицы не изменился. VACUUM не возвращает место на диске кроме случаев, когда удаляет последний блок с данными. Свободное место будет переиспользовано Pangolin для новых записей.

Примечание:

Обновление существующих записей также может привести к фрагментации: UPDATE и DELETE не изменяют значение текущей строки (tuple), а создают ее новую версию.

Анализ фрагментации таблиц#

В PostgreSQL есть расширение pgstattuple, позволяющее анализировать состояние таблиц. Оно устанавливается вместе с Pangolin по умолчанию.

Пример использования:

test=> SELECT * FROM pgstattuple('bloated_table');
-[ RECORD 1 ]------+-------
table_len          | 458752
tuple_count        | 1470
tuple_len          | 438896
tuple_percent      | 95.67
dead_tuple_count   | 11
dead_tuple_len     | 3157
dead_tuple_percent | 0.69
free_space         | 8932
free_percent       | 1.95

Значение строк вывода:

• free_percent — процент свободных записей. Чем он выше, тем больше фрагментирована таблица. Нормальными считаются значения не более 20%;

• table_len — физическая длина отношения в байтах;

• tuple_count — количество «живых» записей;

• tuple_len — общая длина «живых» записей в байтах;

• tuple_percent — процент «живых» записей;

• dead_tuple_count — количество «мертвых» записей;

• dead_tuple_len — общая длина «мертвых» записей в байтах;

• dead_tuple_percent — процент «мертвых» записей;

• free_space — общий объем свободного пространства в байтах.

Примечание:

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

Стандартные утилиты PostgreSQL#

Вернуть освобожденное после VACUUM место на диске можно стандартными средствами PostgreSQL:

• VACUUM FULL полностью пересоберет таблицу, освободив все неиспользуемые строки:

  VACUUM FULL bloated_table;
  ...
   pg_size_pretty
  ----------------
   17 MB
  

• CLUSTER выполнит все операции VACUUM FULL и упорядочит строки по индексу, уменьшая количество обращений к диску на некоторых запросах:

  CLUSTER bloated_table USING <index_name>;
  

Использовать VACUUM FULL и CLUSTER на БД под нагрузкой не рекомендуется. Эти команды работают медленно и полностью блокируют обрабатываемую таблицу. Для решения table bloat без прибегания к блокировке в состав Pangolin входят утилиты по реорганизации данных: pg_repack и pgcompacttable.

Расширение pg_repack#

Иструмент для реорганизации таблиц без эксклюзивной блокировки. Позволяет реорганизовать таблицы и индексы к ним и переносить таблицы и индексы в другое табличное пространство.

Пример использования:

Реорганизовать таблицу «bloated_table»:

pg_repack -U <username> -d <dbname> -t bloated_table

Ключи:

• -U USERNAME — имя пользователя;

• -d DBNAME — имя базы данных;

• --table TABLE_NAME или -t SCHEME_NAME.TABLE_NAME — выбор таблицы для обработки;

• --schema SCHEME_NAME — выбор схемы для обработки;

• --index INDEX_NAME — выбор индекса для обработки;

• --jobs NUMBER_OF_PROCESSES — запустить несколько параллельных процессов для ускорения обработки.

Инструмент pgcompacttable#

Скрипт реорганизации данных в «раздутых» таблицах (bloated tables), восстановления индексов и возврата дискового пространства.

Запуск скрипта#

Для запуска скрипта требуются права superuser.

Рекомендуется запускать его от имени владельца кластера. В этом случае скрипт может использовать ionice в бэкенде PostrgreSQL для понижения приоритетов IO.

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

• общие ключи;

• ключи настройки соединения;

• ключи работы с БД;

• ключи настройки поведения инструмента.

Общие ключи:

• -?, --help — вывести короткую справку об инструменте;

• -m, --man — вывести полную справку об инструменте;

• -V, --version — вывести версию инструмента;

• -q, --quiet — включить тихий режим. В этом режиме выводятся только сообщения об ошибках и результирующее сообщение;

• -v, --verbose — включить режим протоколирования. В этом режиме выводятся все сообщения.

Ключи настройки соединения:

• -h HOST, --host HOST — имя или IP адрес сервера базы данных;

• -p PORT, --port PORT — порт для подключения к базе данных;

• -U USER, --user USER — имя пользователя базы данных, под которым выполняется подключение. По умолчанию имя текущего пользователя, получаемое командой whoami;

• -W PASSWD, --password PASSWD — пароль для указанного пользователя.

  • asdfwads

Примечание:

Инструмент pgcompacttable использует Perl модуль DBI для соединения с базой данных.

Если настройки соединения не передаются в ключах, то инструмент использует переменные окружения PGHOST, PGPORT, имя текущего пользователя и PGPASSWORD.

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

1. Из файла, указанного в переменной окружения PGPASSFILE.

2. Из файла HOME/.pgpass.

Ключи работы с БД:

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

Все ключи, кроме --all, можно использовать несколько раз.

• -a, --all – обработать все базы данных в кластере;

• -d DBNAME, --dbname DBNAME – имя базы данных для обработки. По умолчанию – все базы данных, которыми владеет пользователь, под которым выполняется подключение;

• -n SCHEMA, --schema SCHEMA – имя схемы для обработки. По умолчанию обрабатывается публичная (public) схема;

• -N SCHEMA, --exclude-schema SCHEMA – имя исключаемой из обработки схемы;

• -t TABLE, --table TABLE – имя таблицы для обработки. По умолчанию – все таблицы обрабатываемой схемы;

• --tables-like 'LIKE expression' – SQL LIKE условие поиска таблиц для обработки. По умолчанию – все таблицы обрабатываемой схемы;

• -T TABLE, --exclude-table TABLE – имя исключаемой из обработки схемы.

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

• -R, --routine-vacuum – включить использование VACUUM. По умолчанию выключено;

• -r, --no-reindex – выключить переиндексирование таблиц после их обработки;

• --no-initial-vacuum – выключить запуск VACUUM перед обработкой таблицы;

• -i, --initial-reindex – включить переиндексирование таблицы перед ее обработкой;

• -s, --print-reindex-queries – выводить запросы на переиндексацию. Пример применения: выполнение самостоятельного переиндексирования после работы инструмента;

• --reindex-retry-count – максимальное количество попыток замены имени индекса. По умолчанию 100;

• --reindex-retry-pause – задержка между попытками реиндексации, в секундах. По умолчанию 1 секунда;

• --reindex-lock-timeout – задержка перед переиндексацией после выполнения запросов ALTER TABLE, в миллисекундах. По умолчанию 1000 миллисекунд;

• -f, --force – принудительная реорганизация всех таблиц в указанной базе данных;

• -E RATIO, --delay-ratio RATIO – коэффициент для вычисления задержки между раундами. Задержка вычисляется как произведение времени выполнения прошлого раунда и указанного коэффициента. По умолчанию 2;

• -Q Query, --after-round-query Query – SQL выражение, выполняемое после каждого раунда обработки базы данных;

• -o COUNT, --max-retry-count COUNT – максимальное количество попыток повторной обработки в случае ошибки. По умолчанию 10.

Пример использования#

Реорганизовать таблицу bloated_table:

pgcompacttable  --dbname -t bloated_table

Аналитика производительности Pangolin#

Для сбора данных в Pangolin создается дочерний процесс, в цикле которого происходит периодический опрос статистики активности и блокировок.

Для последующего анализа собранной статистики можно сформировать отчет с указанием временного диапазона. Отчет сохраняется в формате HTML.

Конфигурационные параметры#

Примечание:

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

• При изменении пути к папке хранения файлов необходимо содержимое старой папки перенести в новую.

Настройка параметров для аналитики производительности осуществляется в файле postgresql.conf:

• performance_insights.enable — включает/выключает функциональность (значение по умолчанию — false);

• performance_insights.sampling_enable — включает/выключает сбор данных (значение по умолчанию — true). После изменения настроек параметра требуется выполнить одно из предложенных действий:

  • перезагрузка;

  • вызов команды SELECT pg_reload_conf();;

  • отправить сигнал SIGHUB;

• performance_insights.sampling_period — задает периодичность сбора данных в миллисекундах (значение по умолчанию — 1s). Минимальное значение min - 100ms, значение max — не ограничено;

• performance_insights.num_samples_in_ram — количество итераций сбора данных активности текущих сессий, хранящихся в оперативной памяти (значение по умолчанию — 900). Определяет время хранения данных активности текущих сессий в оперативной памяти. Время определяется количеством сборов данных (performance_insights.num_samples_in_ram), умноженным на периодичность сбора данных (performance_insights.sampling_period). Таким образом, время хранения данных активности текущих сессий по умолчанию (900 * 1s) —> 900 секунд (или 15 минут), при этом:

  • min значение - 1;

  • max значение — не ограничено (2147483647);

• performance_insights.num_samples_in_files — количество итераций сбора данных активности текущих сессий, хранящихся в файлах (на диске) (значение по умолчанию — 86400). Определяет время хранения данных активности текущих сессий в файлах (на диске). Время определяется количеством сборов данных (performance_insights.num_samples_in_files) умноженное на периодичность сбора данных (performance_insights.sampling_period). Таким образом, время хранения данных активности текущих сессий по умолчанию (86400 * 1s) —> 86400 секунд (или 1 сутки), при этом:

  • min значение - 1;

  • max значение — не ограничено (2147483647);

• performance_insights.directory — путь к папке, в которую сохраняются файлы с полученными данными (значение по умолчанию ${PGDATA}/pg_perf_insights);

  Внимание!

  Рекомендуется задать директорию хранения, находящуюся за пределами директории с данными СУБД (PGDATA).

  В противном случае возможна потеря статистики при реиницилизации кластера.

• performance_insights.masking — включает/выключает маскирование параметров запроса, параметров соединения и имени пользователя (значение по умолчанию — true). Параметр performance_insights.masking рекомендуется поместить под защиту.

Функции#

Для работы с данными по аналитике производительности Pangolin применяются следующие функции:

• pg_stat_get_activity_history — возвращает набор записей с данными об активности сессий в определенный момент времени;

• pg_stat_get_activity_and_lock_status_history — возвращает данные активности текущих сессий вместе с данными блокировок (если они имеются) и затраченными ресурсами (CPU, IO);

• pg_stat_get_activity_history_last — возвращает набор записей с данными об активности сессий в последний период обновления истории;

• pg_lock_status_history — возвращает набор записей с информацией о блокировках в определенный момент времени;

• pg_stat_activity_history_reset — функция для управления сохраненными данными, которая полностью очищает историю;

• pg_stat_activity_and_lock_status_history_report — формирует отчет из собранных данных.

Примечание:

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

Подробное описание функций в «Документация на публичные API: PL/pgSQL», раздел «Аналитика производительности Pangolin».

Получение диагностической информации об узле СУБД#

Утилита формирования диагностического отчета предназначена для упрощения и ускорения сбора информации о состоянии и настройках стенда Pangolin. Информация собирается посредством запуска утилиты и передачи в нее определенных параметров сбора и формирует на выходе набор файлов, упакованный в tar.gz архив. Некоторые компоненты отчета требуют повышенных привилегий или доступов к каталогам.

После установки Pangolin утилита доступна в каталоге /usr/pgsql-05-se/diagnostic_tool. Есть возможность скопировать каталог в любое другое доступное пользователю место. Утилита представляет собой скомпилированный бинарный файл и набор .so библиотек. Утилита не требует выполнения дополнительных действий для работоспособности и установки дополнительных модулей.

Примечание:

Утилита diagnostic_tool версии 1.0 работает с версией Pangolin не ниже 5.2.0.

Файловый состав утилиты следующий:

• diag — основной файл, который является точкой входа и используется для запуска сбора статистики. Утилита осуществляет сбор аргументов, вывод справки и последовательный запуск модулей сбора информации. После окончания работы утилиты осуществляется архивирование отчета, вывод сообщения о завершении работы и пути до сформированного отчета;

• utils/common.so — модуль общих переиспользуемых функций (печать в общий лог, создание каталогов, синтаксический анализ текста в CSV и т.д.);

• utils/vars.so — модуль глобальных переменных;

• utils/sql.so — модуль SQL-скриптов для работы с БД;

• utils/linux_info.so — модуль сбора информации об ОС;

• utils/linux_stat.so — модуль сбора статистики использования ОС;

• utils/logs_collect.so — модуль сбора лог-файлов;

• utils/config.so — модуль сбора конфигурационных файлов.

Использование#

Для запуска утилиты необходимо перейти в каталог diagnostic_tool с исполняемым файлом утилиты и ее библиотеками. Затем произвести запуск утилиты. Примеры:

    ./diag
    ./diag --help
    ./diag --host 127.0.0.1 --port 5433 --user psimax --database first_db --pgbuser pgbouncer --logs --log_lines_count 10

Утилита поддерживает следующие параметры:

    $ ./diag --help
    usage: diag [-h] [-H HOST] [-p PORT] [-U USER] [-d DATABASE]
                [--pgbuser PGBUSER] [--logs] [--pgdata PGDATA]
                [--log_lines_count LOG_LINES_COUNT] [--version]

    This is the Pangolin or PostgreSQL Diagnostics Collection Script. Some parts
    of script need superuser or root privileges, if the privileges are
    insufficientsome information cannot be collected.

    optional arguments:
      -h, --help            show this help message and exit
      -H HOST, --host HOST  database server host or socket directory (default:
                            127.0.0.1)
      -p PORT, --port PORT  database server port (default: 5433)
      -U USER, --user USER  database user name (default: postgres)
      -d DATABASE, --database DATABASE
                            database name (default: postgres)
      --pgbuser PGBUSER     pgbouncer user name
      --logs                collect log files
      --pgdata PGDATA       pgdata dir path
      --log_lines_count LOG_LINES_COUNT
                            count last lines of log for collecting (default: 300)
      --version             show program's version number and exit

Описание параметров:

• -U USER, --user USER

  Параметр username для подключения к СУБД Pangolin.

  Значение по умолчанию: postgres.

• -p PORT, --port PORT

  Параметр port для подключения к СУБД Pangolin.

  Значение по умолчанию: 5433.

• -h, --help

  Выводит справку по использованию утилиты.

• -H HOST, --host HOST

  Параметр host для подключения к СУБД Pangolin.

  Значение по умолчанию: 127.0.0.1.

• -d DATABASE, --database DATABASE

  Параметр database для подключения к СУБД Pangolin.

  Значение по умолчанию: postgres.

• --pgdata PGDATA

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

  Значение по умолчанию отсутствует.

• --pgbuser PGBUSER

  Параметр username для подключения к виртуальной БД pgbouncer для сбора статистики использования pgbouncer.

  Значение по умолчанию отсутствует.

• --logs

  Параметр, включающий сбор лог-файлов. По умолчанию лог-файлы не собираются.

• --log_lines_count LOG_LINES_COUNT

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

  Значение по умолчанию: 300.

• --version

  Выводит версию утилиты и завершает работу.

В случае, если переданные параметры --user или --pgbuser отличаются от значений по умолчанию, утилита запросит пароль для данных пользователей. В ответ на запрос утилиты введите соответствующий пароль (пароль при вводе не отображается).

После того, как утилита отработала, она выдаст сообщение о том, где сохранена диагностическая информация. Отчет формируется в каталоге с утилитой из префикса pgse_diag_out_ и временной метки запуска утилиты YYYYMMDD_HHmmSS, где YYYY — год, MM — месяц, DD — день, HH — часы, mm — минуты, SS — секунды. Сформированный архив с отчетом можно скопировать с удаленного хоста, например, с помощью scp:

scp postgres@10.11.12.13:/home/postgres/diagnostic_tool/pgse_diag_out_20220607_145935.tar.gz .

Чтобы проверить содержимое архива его нужно распаковать:

tar -xvf ./pgse_diag_out_20220607_145935.tar.gz

Внимание!

Перед отправкой отчета нужно убедиться в отсутствии недопустимой к передаче информации, а именно в отсутствии :

• непараметризованных запросов или значений параметров в секции collect hot statements stat файла diag.log;

• непараметризованных запросов или значений параметров в секции collect slow statements stat файла diag.log;

• непараметризованных запросов или значений параметров в файле csv/dbms/hot_statements.csv;

• непараметризованных запросов или значений параметров в файле csv/dbms/slow_statements.csv;

• логинов/паролей, а также иной чувствительной информации в секции collect running services файла diag.log (в колонке COMMAND строка запуска процесса);

• логинов/паролей, а также иной чувствительной информации в файле csv/psaux.csv (в колонке COMMAND строка запуска процесса);

• логинов/паролей(в том числе хешей паролей) в конфигурационном файле config/haproxy.cfg;

• логинов/паролей(в том числе хешей паролей) в конфигурационном файле config/pg_hba.conf;

• логинов/паролей(в том числе хешей паролей) в конфигурационном файле config/postgresql.yml (конфигурационный файл patroni, проверить в том числе секцию pg_hba);

• непараметризованных запросов и значений параметров в файле logs/postgresql-XXXXXXXX.log, где XXXXXXXX комбинация из даты и порядкового номера лог файла.

Отчетность по нагрузке Pangolin#

Отчетность по нагрузке в Pangolin реализована с помощью расширения pg_profile. Для расширения pg_profile в БД создается набор таблиц под историческое хранилище. Это хранилище будет накапливать статистические выборки с кластера PostgreSQL. Выборки собираются вызовом функции take_sample(). Для сбора статистики по расписанию можно использовать cron или расширение pg_cron.

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

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

Когда сохраняется выборка, вызывается функция pg_stat_statements_reset(), как гарантия того, что отчеты не будут потеряны из-за достижения параметра pg_stat_statements.max. Также отчет будет содержать раздел, информирующий о том, что количество собранной статистики в любой выборке достигнет 90% от параметра pg_stat_statements.max.

При установке расширения автоматически создается локальный сервер. Это сервер для кластера, где установлено расширение pg_profile.

Для подключения к БД используется расширение db_link.

Собрать статистику с других кластеров#

Расширение pg_profile, установленное на один кластер, может собирать также статистику с других кластеров, именуемых servers (далее — серверы).

Для этого:

• создайте необходимые серверы (функция create_server()), указав имя и строку подключения (подробнее о функции см. «Документация на публичные API», раздел «Отчетность по нагрузке Pangolin»);

• убедитесь, что подключение может быть установлено ко всем БД всех серверов.

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

Настройка параметров#

Задать параметры настроек можно в файле postgresql.conf (указаны значения по умолчанию):

• track_activities = on — мониторинг текущих команд для каждого процесса в pg_stat_activity;

• track_counts = on — мониторинг текущих команд для каждого процесса в pg_stat_all_tables;

• track_io_timing = on — мониторинг времени чтения/записи блоков в pg_stat_statements, pg_stat_kcache;

• track_functions = none — включает подсчет вызовов функций и времени их выполнения. Значение pl включает отслеживание только функций на процедурном языке, а all — также функций на языках SQL и C для представления pg_stat_user_functions;

• track_activity_query_size = 1024 — задает число байт, которое будет зарезервировано для отслеживания выполняемой в данный момент команды в каждом активном сеансе в pg_stat_statements.

Параметры pg_profile#

• pg_profile.topn = 20 — количество основных объектов (statements, relations и т.д.), которые должны быть представлены в каждой отсортированной таблице отчета. Этот параметр влияет на размер выборки — чем больше объектов необходимо отобразить в отчете, тем больше объектов нужно сохранить в выборке;

• pg_profile.max_sample_age = 7 — срок хранения выборок в днях. Выборки, возраст которых равен pg_profile.max_sample_age дней и более, будут автоматически удалены при следующем вызове take_sample();

• pg_profile.track_sample_timings = off — когда этот параметр включен, расширение pg_profile будет отслеживать точное время сбора выборок;

• pg_profile.query_length = 20000 — ограничение размера запросов, применяется только к тем запросам, которые выполнялись во время сбора статистики. Не применяется к запросам из pg_stat_statements.

Параметры pg_stat_statements (влияют на pg_stat_kcache)#

• pg_stat_statements.max = 5000 (из установщика) — максимальное количество различных запросов, по которым хранится статистика. Если этот параметр будет недостаточно большим — расширение pg_profile будет выдавать предупреждения (в случае, если кэш при сборе статистики заполнен на 80%);

• pg_stat_statements.track = 'top' — типы запросов (top/nested) по которым хранится статистика: top, all, none;

• pg_stat_statements.track_utility = 'on' — при значении параметра off, статистика будет храниться только для запросов SELECT, INSERT, UPDATE и DELETE;

• pg_stat_statements.track_planning = 'off' — сохранять отдельно статистику этапа планирования;

• pg_stat_statements.save = 'on' — сохранять статистику в файл, в случае штатной перезагрузки сервера.

Параметры pg_stat_kcache#

• pg_stat_kcache.linuz_hz = -1 — установить частоту аппаратных прерываний (тиков ЦПУ) для компенсации ошибок выборки. Для данного расширения можно явно указать, какой параметр задан в системе (параметр CONFIG_HZ ядра linux). По умолчанию установлено значение -1 — это означает, что расширение попытается автоматически рассчитать эту частоту при старте.

Параметры подсчета точных размеров отношений (указаны значения по умолчанию)#

• relnblocks_enable = 'on' — включение механизма подсчета (pg_profile автоматом подхватит значение из этого механизма, а не из pg_class);

• relnblocks_hash_max_size = 1000000 — максимальное количество отношений (имеющих физические файлы на диске) в базе. В случае переполнения этого числа новое отношение нельзя будет создать.

  Внимание!

  Необходимо внимательно отнестись к параметру relnblocks_hash_max_size и взять достаточный запас, так как к этим отношениям относятся:

  • таблица;

  • индекс;

  • генератор последовательности;

  • таблица хранения сверхбольших атрибутов;

  • материализованное представление.

•   `relnblocks_hash_init_size = 1024` — количество отношений (имеющих физические файлы на диске) в базе, на которые будет предварительно выделен кэш.
  

  В этом случае хеш-таблица для отслеживания размеров объектов будет размещена одним выровненным куском памяти, и поиск по ней будет быстрее.

Использование pg_profile#

Во время установки расширение создает один активированный локальный сервер для кластера, где установлено расширение.

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

SELECT pgse_profile.create_server('omega','host=name_or_ip dbname=postgres port=5432');

Выборки#

Каждая выборка содержит статистическую информацию о рабочей нагрузке БД со времени предыдущей выборки.

Функция сбора выборок также обслуживает хранилище сервера — удаляет устаревшие выборки в соответствии с политикой хранения.

Сохранение выборки#

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

Для взятия выборок по расписанию можно использовать cron или похожие инструменты. Пример с 30-ти минутным периодом:

*/30 * * * *   psql -c 'SELECT pgse_profile.take_sample();' > /dev/null 2>&1

Рассмотренный выше вызов функции не имеет проверки на ошибки результатов take_sample().

Функцию take_sample() можно вызвать так, чтобы она вернула OK для всех серверов, где выборка взята успешно, и показала текст ошибки для неудачных попыток:

select * from take_sample();

server     |        result                                                                 |      elapsed
-----------+-------------------------------------------------------------------------------+---------------
 ok_node   |          OK                                                                   | 00:00:00.48
 fail_node | could not establish connection                                               +| 00:00:00
           | SQL statement "SELECT dblink_connect('server_connection',server_connstr)"    +|
           | PL/pgSQL function take_sample(integer) line 69 at PERFORM                    +|
           | PL/pgSQL function take_sample_subset(integer,integer) line 27 at assignment  +|
           | SQL function "take_sample" statement 1                                       +|
           | FATAL:  database "nodb" does not exist                                        |
(2 rows)

Хранение данных выборки#

Чтобы не хранить данные выборок вечно, существует политика хранения.

Уровни хранения:

1. Обычное хранение (действует, если не определен другой уровень хранения). Задайте параметр pg_profile.max_sample_age в файле postgresql.conf.

2. Определите параметр max_sample_age сервера при создании сервера или с помощью функции set_server_max_sample_age() для существующего сервера.

   Примечание:

   Параметр max_sample_age отменяет глобальный параметр pg_profile.max_sample_age для конкретного сервера.

3. Baseline переопределяет срок хранения для включенных (included) выборок с наивысшим приоритетом. Создайте baseline (см. ниже раздел «Baselines»).

Список выборок#

Используйте функцию show_samples(), чтобы получить список существующих выборок в репозитории. Эта функция также покажет обнаруженное время сброса статистики.

Подробное описание функции show_samples() в «Документация на публичные API», раздел «Отчет по нагрузке Pangolin».

Тайминги сбора выборок#

Расширение pg_profile будет собирать подробную статистику по времени сбора выборок, когда включен параметр pg_profile.track_sample_timings.

Baselines#

Baseline — это именованная последовательность выборок с собственными настройками хранения.

Baseline можно использовать в функциях построения отчетов как интервал выборки. Неопределенный baseline хранения означает бесконечное хранение.

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

Отчеты#

Отчеты создаются в формате HTML функциями по работе с отчетами. В pg_profile есть два типа доступных отчетов:

• регулярные отчеты — содержат статистическую информацию о загруженности экземпляра за период отчета;

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

Посмотреть отчет можно в любом веб-браузере.

Функции регулярных отчетов и отчетов по изменениям описаны в «Документация на публичные API», раздел «Отчет по нагрузке Pangolin».

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

get_diffreport([server name,] baseline varchar(25), time_range tstzrange [, description text])

get_diffreport([server name,] time_range tstzrange, baseline varchar(25) [, description text])

get_diffreport([server name,] start1_id integer, end1_id integer, baseline varchar(25) [, description text])

get_diffreport([server name,] baseline varchar(25), start2_id integer, end2_id integer [, description text])

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

Примеры формирования отчетов#

psql -Aqtc "SELECT profile.get_report(480,482)" -o report_480_482.html

Для любых других серверов по их именам:

psql -Aqtc "SELECT profile.get_report('omega',12,14)" -o report_omega_12_14.html

Формирование отчета по временному промежутку:

psql -Aqtc "select profile.get_report(tstzrange('2020-05-13 11:51:35+03','2020-05-13 11:52:18+03'))" -o report_range.html

Формирование отчета за последние 24 часа:

psql -Aqtc "select profile.get_report(tstzrange(now() - interval '1 day',now()))" -o last24h_report.html

Отключение функциональности#

Отключение pg_stat_kcache:#

1. Удалите значение pg_stat_kcache из параметра shared_preload_libraries.

2. Перезагрузите сервер.

Отключение подсчета точного размера отношений:#

Установите relnblocks_enable в off.

Отключение сбора статистика pg_profile (не требует перезагрузки):#

1. Определите jobid задачи для pg_profile с помощью запроса:

   SELECT * from cron.job;
   

2. Отключите задачу запросом:

   SELECT cron.unschedule(jobid);
   

Отслеживание времени изменения объекта СУБД#

В целях повышения удобства сопровождения СУБД, при выявлении причин снижения ее производительности, вызванного выполнением DDL-операций над объектами БД, предоставляется инструмент в виде двух представлений psql_dba_objects и psql_all_objects.

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

Пример выполнения запроса представления psql_dba_objects:

select * from pg_catalog.psql_dba_objects;

-[ RECORD 1 ]-+------------------------------
oid           | 16384
name          | t1
namespace     | 2200
type          | table
owner         | postgres
ispredef      | f
created       | 2022-05-31 12:58:20.765479+03
last_ddl_time | 2022-05-31 12:58:20.765479+03
deleted       |

Пример выполнения запроса представления psql_all_objects:

select * from pg_catalog.psql_all_objects;

-[ RECORD 1 ]-+------------------------------
oid           | 16384
name          | t1
namespace     | 2200
type          | table
owner         | postgres
ispredef      | f
created       | 2022-05-31 12:58:20.765479+03
last_ddl_time | 2022-05-31 12:58:20.765479+03
deleted       |

Примечание:

Обновление поля last_ddl_time осуществляется в обработчиках DDL-команд, изменяющих логическую структуру объекта, а так же GRANT/REVOKE. В обработчиках остальных DDL-команд, не модифицирующих логическую структуру объекта, значение поля не меняется. К таким DDL-командам относятся:

• REINDEX;

• CLUSTER;

• TRUNCATE;

• VACUUM;

• VACUUM FULL;

• REFRESH MATERIALIZED VIEW [CONCURRENTLY].

Для очистки записей о датах изменения удаленных объектов используется функция purge_object_mod_dates([start_time timestamptz, end_time timestamptz]) → void.

Функция принимает на вход начало и конец интервала времени, в пределах которого требуется очистить записи объектов, даты удаления которых попадают в этот интервал. Входные аргументы могут быть опущены, тогда функция удалит записи о всех удаленных объектах за все время существования БД.

В качестве неопределенной границы интервала времени может использоваться значение NULL. Вызов упрощенной вариации функции без аргументов purge_object_mod_dates() равнозначен вызову purge_object_mod_dates(NULL, NULL) и purge_object_mod_dates('-infinity', 'infinity').

Пример использования функции purge_object_mod_dates():

select purge_object_mod_dates('2022-06-08 09:24:51'::timestamptz, '2022-06-08 10:24:51'::timestamptz);
select purge_object_mod_dates('-infinity', '2022-06-08 10:24:51'::timestamptz);
select purge_object_mod_dates('2022-06-08 09:24:51'::timestamptz, 'infinity');
select purge_object_mod_dates('-infinity', 'infinity');
select purge_object_mod_dates(NULL, '2022-06-08 10:24:51'::timestamptz);
select purge_object_mod_dates('2022-06-08 09:24:51'::timestamptz, NULL);
select purge_object_mod_dates(NULL, NULL);
select purge_object_mod_dates();
select purge_object_mod_dates(now() - interval '1 day', now());

Для сохранения информации о датах изменения удаленных объектов, в течение некоторого интервала времени, введен параметр object_modification_date_keep_interval. В пределах этого интервала времени функции очистки запрещено удалять записи с датами изменения удаленных объектов, которые входят в этот интервал. Интервал отсчитывается от момента выполнения функции очистки psql_purge_object_mod_dates(). Параметр принимает неотрицательные значения типа interval. Если указан нулевой интервал (0), то ограничения на выполнение функции очистки не накладываются. Значение по умолчанию - 1 week (1 неделя). Для изменения параметра необходим перезапуск сервера Pangolin.

Внимание!

Может потребоваться увеличить параметр max_worker_processes, чтобы это число включало дополнительные рабочие процессы, обновляющие даты создания и модификации объектов в новой БД после ее создания.

Отключению функциональности#

Для включения/отключения функциональности предназначен конфигурационный параметр enable_monitor_object_modification_date (в файле postgresql.conf). Значение параметра по умолчанию - on. Для изменения параметра необходим перезапуск сервера Pangolin.

Управление планами запросов#

Для возможности ручной оптимизации планов запросов в Pangolin используются расширения pg_outline и pg_hint_plan.

Расширение pg_hint_plan#

Расширение pg_hint_plan управляет планом выполнения с помощью подсказывающих фраз (подсказок, hint), записываемых в виде простых описаний в SQL-комментариях особого вида.

Настроечные параметры pg_hint_plan#

Для Platform V Pangolin расширение pg_hint_plan модифицировано: запросы создания, изменения и удаления ролей не обрабатываются расширением pg_hint_plan и не попадают в лог даже при включенном детальном логировании расширения.

* — Уровни важности сообщений ошибок синтаксического анализа и отладочных сообщений. Описание допустимых значений:

• ERROR — сообщает об ошибке, из-за которой прервана текущая команда;

• WARNING — предупреждения о возможных проблемах;

• NOTICE — информация, которая может быть полезной пользователям;

• INFO — неявно запрошенная пользователем информация;

• LOG — информация, полезная для администраторов;

• DEBUG — максимальный уровень детализации для разработчиков.

** – Детализация отладочных сообщений — количество информации, записываемой в журнал сервера для каждого сообщения. Каждое последующее значение добавляет больше полей в выводимое сообщение. Описание допустимых значений:

• off — 0 (сообщения выключены);

• on — 1;

• detailed — 2;

• verbose — 3.

Активация расширения#

Расширение pg_hint_plan по умолчанию выключено.

Чтобы включить расширение pg_hint_plan, используйте настроечные параметры pg_hint_plan.enable_hint и pg_hint_plan.enable_hint_table:

• для применения ко всем сессиям — установите значение on в postgresql.conf;

• для конкретных сессий — установите значение true через команды SET или ALTER USER SET/ALTER DATABASE SET.

Пример:

SET pg_hint_plan.enable_hint = TRUE;
SET pg_hint_plan.enable_hint_table = TRUE;

Отключение функциональности#

Чтобы отключить функциональность расширения, установите значение off для настроечных параметров pg_hint_plan.enable_hint и pg_hint_plan.enable_hint_table.

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

• исключите библиотеку из параметра shared_preload_libraries;

• измените настройки конкретных сессий через ALTER USER SET/ALTER DATABASE SET.

После отключения функциональности удалите расширение командой:

DROP EXTENSION pg_hint_plan

Включение журналирования#

Чтобы включить журналирование, в конфигурации сервера необходимо применить рекомендуемые настройки:

pg_hint_plan.parse_messages = warning
pg_hint_plan.debug_print = off
pg_hint_plan.message_level = debug

Методы доступа#

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

Здесь POSIX Regexp... – список регулярных выражений, используемых для того, чтобы не перечислять поодиночке множество индексов с похожими названиями.

Чтобы зафиксировать метод доступа, в подсказке указывается псевдоним таблицы-источника и, если в методе используются индексы — их также нужно перечислить, разделяя список пробелами.

Внимание!

Подсказка NoIndexScan включает в себя действие подсказки NoIndexOnlyScan.

Наличие карты видимости таблицы (слой visibility map) необходимо для метода доступа Index Only Scan. Этот слой по умолчанию не создается при создании таблицы, поэтому до первого autovacuum / VACUUM / VACUUM FULL по новой таблице не получится осуществить доступ к таблице методом Index Only Scan.

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

Примеры:

• Указание использовать сканирование по конкретному индексу:

  /*+ IndexScan(t1 ix_table1_2ind_f1_f2) */ select ctid, * from table1_2ind t1 where f1 = 10;
  

• Указание не использовать последовательное сканирование:

  /*+ NoSeqScan(t1) */ select * from table1 t1 where t1.f1 > 1;
  

Закрепить в запросе выбранный метод объединения можно при помощи подсказок:

Примеры:

• Указание использовать объединение nested loop:

  /*+ NestLoop(t1 t2)*/ select t1.f2, t2.f2 from table1 t1, table2 t2 where t1.f1=t2.f1;
  

• Указание не использовать объединение hash join:

  /*+ NoHashJoin(t1 t2) */ select t1.f2 from table1 t1 where t1.f2 in (select f2 from table2 t2 where f2 > '10000');
  

Особенности#

В данном разделе приведены особенности работы с расширением pg_hint_plan.

Использование с PL/pgSQL#

Расширение pg_hint_plan работает с запросами в PL/pgSQL блоках с некоторыми ограничениями:

• подсказки (hint) действуют только на следующие типы запросов:

  • запросы, возвращающие одну запись (SELECT, INSERT, UPDATE и DELETE);

  • запросы, возвращающие множественные записи (RETURN QUERY);

  • динамические SQL-выражения (EXECUTE);

  • открытие курсора (OPEN);

  • итерация по результату запроса (LOOP FOR);

• комментарий с подсказкой (hint) должен быть помещен после первого слова запроса, так как обработка выражений PL/pgSQL отбрасывает комментарии вне выражений (комментарий перед выражением также будет отброшен).

  CREATE FUNCTION hints_func(integer) RETURNS integer AS $$
  DECLARE
      id  integer;
      cnt integer;
  BEGIN
      SELECT /*+ NoIndexScan(a) */ aid
          INTO id FROM pgbench_accounts a WHERE aid = $1;
      SELECT /*+ SeqScan(a) */ count(*)
          INTO cnt FROM pgbench_accounts a;
      RETURN id + cnt;
  END;
  $$ LANGUAGE plpgsql;
  

Строчные и прописные символы в именах объектов#

Имена объектов в подсказках (hint) регистрозависимы.

Например, имя объекта TBL в подсказке соответствует только имени "TBL" в базе данных и не соответствует именам без кавычек TBL, tbl и Tbl.

Экранирование спецсимволов в именах объектов#

Объекты в параметрах подсказок pg_hint_plan, которые содержат скобки, двойные кавычки или пробелы, должны быть заключены в двойные кавычки. Правила экранирования символов в Pangolin аналогичны правилам PostgreSQL.

Конкретный вход таблицы при множественном использовании#

Расширение pg_hint_plan идентифицирует целевые объекты подсказок (hint) с использованием псевдонимов (alias), если они заданы.

Пример применения: необходимо указать на конкретное использование объекта в выражении, в котором объект используется множество раз.

/*+ HashJoin(t1 t1) */
EXPLAIN SELECT * FROM s1.t1
JOIN public.t1 ON (s1.t1.id=public.t1.id);
INFO:  hint syntax error at or near "HashJoin(t1 t1)"
DETAIL:  Relation name "t1" is ambiguous.
...
/*+ HashJoin(pt st) */
EXPLAIN SELECT * FROM s1.t1 st
JOIN public.t1 pt ON (st.id=pt.id);
                             QUERY PLAN
---------------------------------------------------------------------
 Hash Join  (cost=64.00..1112.00 rows=28800 width=8)
   Hash Cond: (st.id = pt.id)
   ->  Seq Scan on t1 st  (cost=0.00..34.00 rows=2400 width=4)
   ->  Hash  (cost=34.00..34.00 rows=2400 width=4)
         ->  Seq Scan on t1 pt  (cost=0.00..34.00 rows=2400 width=4)

Управление планом запроса в представлении или правиле перезаписи#

Подсказки (hint) не применимы к представлениям в выражениях, но могут влиять на поведение запросов, если находятся в тексте самих представлений.

Это возможно, если имена объектов в подсказках соответствуют именам объектов в представлениях.

Если имена не совпадают, объектам в представлениях можно назначить псевдонимы. Использование псевдонимов в запросах позволит обращаться к представлениям и влиять на поведение запроса.

CREATE VIEW v1 AS SELECT * FROM t2;
EXPLAIN /*+ HashJoin(t1 v1) */
          SELECT * FROM t1 JOIN v1 ON (c1.a = v1.a);
                            QUERY PLAN                            
------------------------------------------------------------------
 Hash Join  (cost=3.27..18181.67 rows=101 width=8)
   Hash Cond: (t1.a = t2.a)
   ->  Seq Scan on t1  (cost=0.00..14427.01 rows=1000101 width=4)
   ->  Hash  (cost=2.01..2.01 rows=101 width=4)
         ->  Seq Scan on t2  (cost=0.00..2.01 rows=101 width=4)

Наследование таблиц#

Подсказки (hint) могут указывать только на родителя наследования, и подсказка влияет на всех наследников. Подсказки, одновременно указывающие прямо на наследников, игнорируются.

Подсказки для множественных запросов#

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

Примечание:

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

Выражение VALUES#

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

/*+ MergeJoin(*VALUES*_1 *VALUES*) */
      EXPLAIN SELECT * FROM (VALUES (1, 1), (2, 2)) v (a, b)
      JOIN (VALUES (1, 5), (2, 8), (3, 4)) w (a, c) ON v.a = w.a;
INFO:  pg_hint_plan: hint syntax error at or near "MergeJoin(*VALUES*_1 *VALUES*) "
DETAIL:  Relation name "*VALUES*" is ambiguous.
                               QUERY PLAN                                
-------------------------------------------------------------------------
 Hash Join  (cost=0.05..0.12 rows=2 width=16)
   Hash Cond: ("*VALUES*_1".column1 = "*VALUES*".column1)
   ->  Values Scan on "*VALUES*_1"  (cost=0.00..0.04 rows=3 width=8)
   ->  Hash  (cost=0.03..0.03 rows=2 width=8)
         ->  Values Scan on "*VALUES*"  (cost=0.00..0.03 rows=2 width=8)

Подзапросы#

Подзапросы в следующем контексте иногда можно указать в подсказке (hint), используя имя ANY_subquery.

IN (SELECT ... {LIMIT | OFFSET ...} ...)
= ANY (SELECT ... {LIMIT | OFFSET ...} ...)
= SOME (SELECT ... {LIMIT | OFFSET ...} ...)