Подготовка к работе и сопровождение сервера

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

При установке из пакетов пользователь добавится автоматически
При установке из исходников нужно создать руками

Прежде чем начать работать с базами данных нужно проинициализировать область хранения данных на диске
Это хранилище называется кластером баз данных
Кластер представляет собой набор баз данных управляемых одним экземпляром сервера

После инициализации в кластере создается база postgres, сам постгрес может жить и без нее, но на ее существование расчитывают внешние вспомогательные программы

Также создается база template1, она используется в качестве шаблона для создаваемых баз данных

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

Какого-либо стандартного пути не существует, но часто данные размещаются в /usr/local/pgsql/data или в /var/lib/pgsql/data

Для создания кластера (для инициализации области хранения данных) нужно воспользоваться командой initdb и передать ей через ключ -D желаемый путь
Команду нужно выполнять от имени пользователя postgres (ну или какой там у вас)
Вместо ключа -D можно установить переменную окружения PGDATA
А еще можно это сделать через pg_ctl:

pg_ctl -D /usr/local/pgsql/data initdb

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

Команда initdb также устанавливает локаль для кластера
Локаль это важно. Отличные от "C" и "POSIX" локали могут негативно влиять на производительность
Также в момент инициализации устанавливается кодировка для всего кластера
Локаль влияет на порядок сортировки, а порядок сортировки влияет на порядок ключей в индексах. Поэтому какую-то суперкастомную установку будет очень сложно перенести

Если делать кластер баз данных на отдельном разделе, то не следует выбирать в качестве каталога данных саму точку монтирования, лучше внутри монтируемой ФС сделать каталог который пренадлежит пользователю postgres, а внутри этого каталога диру для данных
Это исключит некоторые проблемы

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

Запустить постгрес можно разными способами

С помощью команды postgres, передав ей путь до кластера баз данных через ключ -D:

А можно передать через переменную окружения PGDATA:

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

По-человечески запускать постгрес следует, например, так:

Можно написать systemd unit, но для некоторых функций systemd требуется чтобы постгрес был скомпилирован с опцией --with-systemd

Запущенный постгрес создает файл postmaster.pid в каталоге кластера
Это нужно для защиты от запуска нескольких экземпляров с одним каталогом данных
Также это может быть полезно для выключения экземпляра (облегчает поиск pid'a)

Нужно чтобы пользователь от которого запускается постгрес был системным (это задается при создании пользователя, например useradd -r ...)
Либо нужно указать параметр RemoveIPC=no в /etc/systemd/logind.conf

Иначе сервер будет не стабильным потому что ОС будет высвобождать разделяемые ресурсы преждевременно и данные могут теряться

Остановить постгрес можно послав сигнал postmaster'у

• SIGTERM - graceful shutdown
• SIGINT - запрещает новые коннекты и шлет SIGTERM старым коннектам
• SIGQUIT - гасит все жестко

Для выключения сервера не следует использовать сигнал SIGKILL. При таком выключении сервер не сможет освободить разделяемую память и семафоры. Кроме того, при уничтожении главного процесса postgres сигналом SIGKILL, он не успеет передать этот сигнал своим дочерним процессам, так что может потребоваться завершать и их вручную

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

Номер версии постгреса состоит из двух чисел (до версии 10 было три числа) - например 10.1

• 10 - Первое число означает основную версию
• 1 - Второе означает номер корректирующей версии

Все корректирующие версии в рамках одной основной версии - совместимы

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

Для обновления совместимых версий достаточно просто заменить исполняемые файлы при выключенном постгресе
Каталог данных при этом не затрагивается

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

Утилита pg_upgrade позволит провести обновление на месте и быстрее

Еще можно поднять два экземпляра в разных кластерах и на разных портах и запустить переливку данных из старой версии в новую

pg_dumpall -p 5432 | psql -d postgres -p 5433

Для создания копии рекомендуется использовать актуальные версии утилиты pg_dump/pg_dumpall
Соврeменнные версии способны читать данные постгреса начиная с 7 версии

Еще можно настроить репликацию из старого постгреса в новый, а потом переключить мастера на новый постгрес

Любой пользователь в системе может запустить свой постгрес и слушать аутентификационные данные клиентов
Чтобы защититься от поддельного сервера, можно использовать unix-socket в специальном каталоге в который может писать только проверенный пользователь
Также можно защитить клиентов передав им параметр requirepeer, в котором указывается имя владельца серверного процесса (так можно обезопасить локальные подключения)

Шифрование

• Пароли пользователей хранятся в виде хэшей. SCRAM/MD5, scram - предпочтительнее
  Но нужно учитывать что старые клиенты могут не уметь в scram
• С помощью модуля pgcrypto можно шифровать отдельные поля таблицы
• Можно зашифровать каталог данных (если он монтируется отдельно)
• Можно настроить ssl для подключений
  Можно использовать ipsec-туннели
  Можно использовать ssh
• Можно настроить проверку подлинности ssh, тогда можно будет избежать MITM
• А еще можно шифровать данные на клиенте и складывать в базу уже зашифрованные

Чтобы работал ssl, нужно чтобы он был включен при сборке (./configure --with-openssl)
По умолчанию когда включаешь в конфиге ssl=on, сервер будет согласовывать использование ssl с каждым клиентом индивидуально (ssl и non-ssl подключения будут работать на одном и том же порту)
Но можно и настроить индивидуальные параметры, например у какого-то клиента строго принимать только по ssl, итд

Если установил из репозитория то посмотреть с какими ключами он был собран можно так:

root@two:~# pg_config --configure
 '--build=x86_64-linux-gnu' '--prefix=/usr' '--includedir=${prefix}/include'...

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

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

Postgres пользуется openssl. Алгоритмы и шифры можно задавать в конфиге постгреса

Сделать самоподписанные серты для защищенного подключения в postgres можно опираясь на эту страницу: CA,CSR,CRT

Но есть нюанс при выпуске клиентского серта

Поле CN должно содержать имя пользователя под которым будет производиться подключение

root@three:~# openssl x509 -in client.crt -noout -subject
subject=CN = test

ssl = on
ssl_ca_file = '/etc/ssl/certs/server-ca.crt' # в этот файл можно включить всю цепочку
ssl_cert_file = '/etc/ssl/certs/server.crt'
ssl_key_file = '/etc/ssl/private/server.key'

root@two:/etc/ssl# cp ~/server-ca.crt certs/.
root@two:/etc/ssl# cp ~/server.crt certs/.
root@two:/etc/ssl# cp ~/server.key private/.

root@two:/etc/postgresql/13/main# tail -1 pg_hba.conf
hostssl	all	all	0.0.0.0/0	cert

postgres=# create database testdb;
CREATE DATABASE
postgres=# create user test;
CREATE ROLE
postgres=# grant all privileges on database testdb to test;
GRANT
postgres=#

root@three:~# psql "host=two.vandud.ru user=test dbname=testdb sslmode=verify-full sslrootcert=/root/server-ca.crt sslcert=/root/client.crt sslkey=/root/client.key"
psql (13.2 (Debian 13.2-1.pgdg100+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

testdb=> \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 testdb    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres         +
           |          |          |             |             | postgres=CTc/postgres+
           |          |          |             |             | test=CTc/postgres
(4 rows)

testdb=>

Чтобы не вводить длинную команду для подключения
Ее содержимое можно засунуть в ~/.pg_service.conf и ссылаться на содержимое файла через параметр service

root@three:~# cat .pg_service.conf
[test]
host=two.vandud.ru
user=test
dbname=testdb
sslmode=verify-full
sslrootcert=/root/server-ca.crt
sslcert=/root/client.crt
sslkey=/root/client.key

root@three:~# psql "service=test"
psql (13.2 (Debian 13.2-1.pgdg100+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

testdb=>

При работе с сертификатами постгрес может проверять CRL
Для этого в postgres.conf нужно указать путь до файла с CRL в ssl_crl_file

Сервер не примет файл ключа с либеральными правами

Если клиент не поддерживает ssl, то можно использовать ssh
Можно прокинуть туннель и работать через него

ssh -L 63333:localhost:5432 joe@foo.com
psql -h localhost -p 63333 postgres