PostgreSQL

PostgreSQL является бесплатной системой управления реляционными базами данных с открытым исходным кодом (СУБД) с упором на расширяемость и соответствие SQL. Подробнее о PostgreSQL.

KOMRAD использует PostgreSQL для хранения обнаруженных, идентифицированных и зарегистрированных инцидентов и директив (правил) корреляции, а также для хранения настроек конфигурации коллекторов, плагинов, рассылок, конфигурации виджетов, ГосСОПКА, push-уведомлений, правил фильтров в базе komrad-preferences, для хранения данных системы авторизации pauth-preferences и для хранения данных об активах scanner.

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

Таблица соответствия:

Операционная система	Версия PostgreSQL
Ubuntu 20.04.1	PostgreSQL 12.11
Astra Linux 1.7.1 (Smolensk)	PostgreSQL 11.10
Astra Linux SE 1.6 (Smolensk) Update 6	PostgeSQL 9.6.10
OSNova Linux (Onyx 2.0)	PostgreSQL 11.7

Требования

В принципе, запустить PostgreSQL возможно на любой современной Unix-совместимой платформе.

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

• GNU make версии 3.80 или новее.

Другие программы make или ранние версии GNU make работать не будут. Иногда GNU make устанавливается под именем gmake. Чтобы проверить наличие и версию GNU make, введите:

make --version

• Компилятор C, соответствующий ISO/ANSI C (как минимум, совместимый с C99). Рекомендуется использовать последние версии GCC, но известно, что PostgreSQL собирается самыми разными компиляторами и других производителей

• Для распаковки пакета исходного кода необходим tar, а также gzip или bzip2

• По умолчанию при сборке используется библиотека GNU Readline. Она позволяет запоминать все вводимые команды в psql (SQL-интерпретатор командной строки для PostgreSQL) и затем, пользуясь клавишами-стрелками, возвращаться к ним и редактировать их. Это очень удобно и мы настоятельно рекомендуем пользоваться этим. Если Вы не желаете использовать эту возможность, Вы должны добавить указание --without-readline для configure. В качестве альтернативы часто можно использовать библиотеку libedit с лицензией BSD, изначально разработанную для NetBSD. Библиотека libedit совместима с GNU Readline и подключается, если libreadline не найдена, или когда configure передаётся указание --with-libedit-preferred. Если Вы используете систему на базе Linux с пакетами, учтите, что Вам потребуются два пакета: readline и readline-devel, если в вашем дистрибутиве они разделены

• По умолчанию для сжатия данных используется библиотека zlib. Если Вы не хотите её использовать, Вы должны передать configure указание --without-zlib. Это указание отключает поддержку сжатых архивов в pg_dump и pg_restore.

Также проверьте, достаточно ли места на диске. Вам потребуется около 100 Мб для исходного кода в процессе компиляции и около 20 Мб для каталога инсталляции. Пустой кластер баз данных занимает около 35 Мб. Базы данных занимают примерно в пять раз больше места, чем те же данные в обычном текстовом файле. Если Вы планируете запускать регрессионные тесты, Вам может временно понадобиться ещё около 150 Мб. Проверить наличие свободного места можно с помощью команды df.

Более подробную информацию об этом можно получить на сайте разработчика.

Установка

Возможны различные варианты установки PostgreSQL, он может размещается на одном хосте в случае лицензии Base или AiO, так может быть установлен на другом хосте в случае наличия лицензии Enterprise.

Порядок установки

1. В терминале Astra Linux введите:

   sudo apt install postgresql ca-certificates -y

2. В терминале Osnova или Ubuntu введите:

   sudo apt install postgresql -y

3. После завершения установки выполните подключение к PostgreSQL:

   sudo su - postgres

4. Наделите пользователя правами на создание новых баз данных, где вместо pass установите пароль для пользователя postgres:

   psql -c "ALTER USER postgres WITH CREATEDB LOGIN PASSWORD 'pass';"

5. Установите расширение, выполнив:

   psql -c "CREATE EXTENSION pg_trgm;"

6. Создайте базы данных, выполнив:

   createdb -O postgres komrad-preferences
   createdb -O postgres pauth-preferences
   createdb -O postgres scanner
   exit

Командная строка

Подключение выполняется от имени отдельного пользователя по умолчанию это postgres. Подключение к PostgreSQL:

sudo su - postgres

Параметры командной строки

• -c, --command=КОМАНДА - выполнить одну команду (SQL или внутреннюю) и выйти

• -d, --dbname=БД - имя подключаемой базы данных (по умолчанию postgres)

• -f, --file=ИМЯ_ФАЙЛА - выполнить команды из файла и выйти

• -l, --list - вывести список баз данных и выйти

• -v, --set=, --variable=ИМЯ=ЗНАЧЕНИЕ - присвоить переменной psql ИМЯ заданное ЗНАЧЕНИЕ (например: -v ON_ERROR_STOP=1)

• -V, --version - показать версию и выйти

• -X, --no-psqlrc - игнорировать файл параметров запуска (~/.psqlrc)

• -1 ("один"), --single-transaction - выполнить как одну транзакцию (в неинтерактивном режиме)

• -?, --help[=options] - показать эту справку и выйти

• --help=commands - перечислить команды с \ и выйти

• --help=variables - перечислить специальные переменные и выйти

Параметры ввода/вывода

• -a, --echo-all - отображать все команды из скрипта

• -b, --echo-errors - отображать команды с ошибками

• -e, --echo-queries - отображать команды, отправляемые серверу

• -E, --echo-hidden - выводить запросы, порождённые внутренними командами

• -L, --log-file=ИМЯ_ФАЙЛА - сохранять протокол работы в файл

• -n, --no-readline - отключить редактор командной строки readline

• -o, --output=ИМЯ_ФАЙЛА - направить результаты запроса в файл (или канал |)

• -q, --quiet - показывать только результаты запросов, без сообщений

• -s, --single-step - пошаговый режим (подтверждение каждого запроса)

• -S, --single-line - однострочный режим (конец строки завершает команду)

Параметры вывода

• -A, --no-align - режим вывода невыровненной таблицы

• -F, --field-separator=СТРОКА - строка разделителей полей при невыровненном выводе (по умолчанию: |)

• -H, --html - вывод таблицы в формате HTML

• -P, --pset=ПАР[=ЗНАЧ] - определить параметр печати ПАР (с заданным ЗНАЧЕНИЕМ) (см. описание \pset)

• -R, --record-separator=СТРОКА - разделитель записей при невыровненном выводе (по умолчанию: новая строка)

• -t, --tuples-only - выводить только кортежи

• -T, --table-attr=ТЕКСТ - установить атрибуты HTML-таблицы (width, border)

• -x, --expanded - включить развёрнутый вывод таблицы

• -z, --field-separator-zero - сделать разделителем полей при невыровненном выводе нулевой байт

• -0, --record-separator-zero - сделать разделителем записей при невыровненном нулевой байт

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

• -h, --host=ИМЯ - имя сервера баз данных или каталог сокетов (по умолчанию: /var/run/postgresql)

• -p, --port=ПОРТ - порт сервера баз данных (по умолчанию: 5432)

• -U, --username=ИМЯ - имя пользователя (по умолчанию: postgres)

• -w, --no-password - не запрашивать пароль

• -W, --password - запрашивать пароль всегда (обычно не требуется)

Конфигурационные файлы

• /etc/postgresql/version/main/postgresql.conf подробнее
• /etc/postgresql/version/main/auth-pg-hba-conf подробнее
• /etc/postgresql/version/main/pg_ctl.conf подробнее
• /etc/postgresql/version/main/pg_ident.conf подробнее

Работа с базой данных

Полный набор доступных команд здесь

Создать базы данных

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

createdb -O postgres komrad-preferences
createdb -O postgres pauth-preferences
createdb -O postgres scanner

Удалить базы

dropdb "komrad-preferences" 
dropdb "pauth-preferences" 
dropdb "scanner"

Проверить наличие баз

psql -l

Подключение к базе данных komrad-preferences

psql postgres://postgres:pass@localhost:5432/komrad-preferences

Устранение неисправностей

Сервер не запущен

Проверьте, запущен ли сервер

Команда:

sudo systemctl status postgresql

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

sudo systemctl start postgresql

Проверьте журналы

Основной лог:

sudo nano /var/log/postgresql/postgresql

В случае успешного запуска Вы должны увидеть строки, содержащие:

• starting PostgreSQL (.04.1) on x86_64-pc-linux-gnu
• listening on IPv4 address "127.0.0.1", port 5432
• listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
• database system is ready to accept connections

Типовые ошибки запуска здесь

Проверьте логи system.d

Если из логов postgresql Вы не получили необходимой информации или логов нет, то вы можете посмотреть логи system.d командой:

sudo journalctl -u postgresql

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

Проверьте:

Параметры подключения к postgresql с другой машины.

• postgresql.conf

sudo nano /etc/postgresql/version/main/postgresql.conf

раскомментируйте строку, содержащую listen_addresses = localhost

#-----------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#-----------------------------------------------------------------
# - Connection Settings -
listen_addresses = '*' # what IP address(es) to listen on;
 # comma-separated list of addresses;
# defaults to 'localhost'; use '*' for all
 # (change requires restart)

осторожно

В данном примере мы разрешили прослушивание запросов на всех IP-адресах (*), но, если требуется более безопасная настройка, можно перечислить последние через пробел. Например:

listen_addresses = 192.168.0.15 10.10.0.16

Сохраните файл и закройте его

• pg_hba.conf

sudo nano /etc/postgresql/9.6/main/pg_hba.conf

После последней строки файла с новой строки добавить:

host all postgres `IP-адрес_KOMRAD`/32 md5

Сохраните файл и закройте его, после чего перезапустите службу, выполнив:

sudo systemctl restart postgresql

Создание и настройка файла pgpass

Файл .pgpass в домашнем каталоге пользователя может содержать пароли, которые будут использоваться, если для подключения требуется пароль (и пароль не задаётся другим способом). В Microsoft Windows этот файл называется %APPDATA%\postgresql\pgpass.conf (где %APPDATA% обозначает каталог с данными приложений в профиле пользователя). Имя используемого файла паролей также можно задать в параметре подключения passfile или в переменной окружения PGPASSFILE.

Этот файл должен содержать строки следующего формата:

имя_компьютера:порт:база_данных:имя_пользователя:пароль

Вы можете вставить в этот файл комментарий-памятку, скопировав показанную строку в него и добавив в начало #. Первые четыре поля могут содержать строковые значения, либо знак *, соответствующий всему. Применяться будет пароль, указанный в первой из строк, значения полей в которой соответствуют текущему соединению. Поэтому, если вы используете звёздочки, поместите более конкретные записи первыми. Если запись должна содержать символ : или \, добавьте перед ним \. Поле с именем узла сопоставляется с параметром подключения host (если он указан) или с параметром hostaddr (если указан он); в случае отсутствия обоих параметров подразумевается имя localhost. Имя узла localhost также подразумевается, когда соединение устанавливается через Unix-сокет, и параметр host соответствует установленному в libpq каталогу сокетов по умолчанию. На ведомом сервере имя базы данных replication соответствует подключениям к ведущему серверу, которые применяются для потоковой репликации. Поле база_данных имеет ограниченную ценность, так как пользователи используют один пароль для всех баз данных в кластере.

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

chmod 0600 ~/.pgpass

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

Как уменьшить автоматический рост базы данных PostgreSQL?

Если ваша БД слишком быстро растёт, то можно попробовать сдержать её рост с помощью процесса "автовакуума", который управляет внутренним процессом очистки.

Откройте на редактирование файл настроек PostgreSQL (где * — ваша версия PostgreSQL)

sudo nano /etc/postgresql/*/main/postgresql.conf

В конце файла вставьте следующие строки:

autovacuum = on # Enable autovacuum subprocess? 'on'
log_autovacuum_min_duration = 0 # -1 disables, 0 logs all actions and
autovacuum_max_workers = 5 # max number of autovacuum subprocesses
autovacuum_naptime = 1min # time between autovacuum runs
autovacuum_vacuum_threshold = 300 # min number of row updates before
autovacuum_analyze_threshold = 200 # min number of row updates before
autovacuum_vacuum_scale_factor = 0.01 # fraction of table size before vacuum
autovacuum_analyze_scale_factor = 0.005 # fraction of table size before analyze

подсказка

Не забудьте сохранить файл, нажав Ctrl + X и y

После чего перезапустите PostgreSQL:

sudo systemctl restart postgresql

Изменения пароля роли в PostgreSQL

Изменить пароль роли:

sudo -u postgres psql -c "ALTER ROLE postgres WITH password 'passwort'"

Где username — имя пользователя, у которого вы хотите изменить пароль, а вместо passwort укажите новый пароль.

О команде ALTER ROLE более подробно можно узнать здесь.

Изменения пароля в KOMRAD

Откройте файл .pgpass:

sudo nano /etc/echelon/.pgpass

Укажите вместо pass (пароль по умолчанию) новый пароль.

подсказка

Не забудьте сохранить файл, нажав Ctrl + X и y

После чего перезапустите PostgreSQL:

sudo systemctl restart postgresql

Как узнать размер таблиц в PostgreSQL?

1. Подключаемся к PostgreSQL

   sudo su - postgres

2. Выбираем нужную базу

   psql komrad-preferences

Запрос выводит 20 самых больших таблиц:

SELECT nspname || '.' || relname AS "relation",
    pg_size_pretty(pg_relation_size(C.oid)) AS "size"
  FROM pg_class C
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  WHERE nspname NOT IN ('pg_catalog', 'information_schema')
  ORDER BY pg_relation_size(C.oid) DESC
  LIMIT 20;

Узнать общий размер таблиц:

SELECT nspname || '.' || relname AS "relation",
    pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
  FROM pg_class C
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  WHERE nspname NOT IN ('pg_catalog', 'information_schema')
    AND C.relkind <> 'i'
    AND nspname !~ '^pg_toast'
  ORDER BY pg_total_relation_size(C.oid) DESC
  LIMIT 20;

Узнать размер всех таблиц:

SELECT nspname || '.' || relname AS "relation",
    pg_size_pretty(pg_relation_size(C.oid)) AS "size"
  FROM pg_class C
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  WHERE nspname NOT IN ('pg_catalog', 'information_schema')
  ORDER BY pg_relation_size(C.oid) DESC;

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