PostgreSQL 17 установка + начальная настройка + SSL/TLS | Debian/AlmaLinux

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

1. Установка PostgreSQL.
2. Настройка доступов к PostgreSQL.
3. Подключение к PostgreSQL с использованием сертификатов.

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

Установка

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

Debian

Для добавления официального репозитория и установки последней версии PostgreSQL используем postgresql-common:

sudo apt install -y postgresql-common ca-certificates openssl
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh

sudo apt update
sudo apt -y install postgresql-17

Соглашаемся на добавление репозитория apt.postgresql.org, нажав Enter

AlmaLinux

Добавим RPM репозиторий:

sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-10-x86_64/pgdg-redhat-repo-latest.noarch.rpm

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

sudo dnf -qy module disable postgresql

Установим, инициализируем, включим автозапуск и запустим службу:

sudo dnf install -y postgresql17-server openssl
sudo /usr/pgsql-17/bin/postgresql-17-setup initdb
sudo systemctl enable postgresql-17
sudo systemctl start postgresql-17

Настройка подключений

По умолчанию авторизоваться в postgres можно только с помощью системного пользователя postgres:

su - postgres

psql -U postgres

Или с помощью sudo:

sudo -u postgres psql -U postgres

Давать доступ к учётной записи postgres настоятельно не рекомендуется. Если требуется настроить подключение к БД в стороннем приложении, правильнее будет:

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

Создание пользователя и БД

Создадим пользователя с именем test_user и паролем change_me:

CREATE USER test_user WITH PASSWORD 'change_me';

Далее создадим базу данных test_database и выдадим к ней полный доступ ранее созданному пользователю test_user:

CREATE DATABASE test_database WITH OWNER test_user;

Подключение удалённых клиентов

Если сейчас попытаться подключится к базе данных test_database под пользователем test_user с удалённого хоста, то получим ошибку:

В соединении отказано. Это происходит потому, что по умолчанию PostgreSQL принимает подключения только с адреса 127.0.0.1

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

Debian

sudo apt install postgresql-client-17

AlmaLinux

sudo dnf install -y postgresql

Чтобы иметь возможность подключаться к БД с другого хоста, добавим ip адрес внешнего сетевого интерфейса в /etc/postgresql/17/main/postgresql.conf (/var/lib/pgsql/17/data/postgresql.conf для RHEL):

Раскомментируйте параметр listen_addresses и добавьте через запятую IP адрес интерфейса, на который будут приходить запросы

Если попробуем повторить подключение — увидим, что этого недостаточно:

ВАЖНО: в pg_hba.conf нет записи для компьютера "192.168.122.1", пользователя "test_user", базы "test_database", без шифрования

Добавим в /etc/postgresql/17/main/pg_hba.conf требуемую запись:

192.168.122.1/32 - IP клиента, scram-sha-256 - метод аутентификации

Чтобы изменения вступили в силу, перезапустим postgresql.service:

Debian: sudo systemctl restart postgresql.service

AlmaLinux: sudo systemctl restart postgresql-17.service

firewalld

При использовании дистрибутива на базе RHEL удалённые подключения всё ещё могут не работать из-за настроек firewalld. Разрешим подключения к порту 5432:

firewall-cmd --add-port=5432/tcp --permanent
firewall-cmd --reload

Подключение

Для подключения к БД используем psql:

psql -U test_user -h pgal.test -d test_database -W --password

Подключение к БД и вывод информации о текущем подключении

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

Подключение к БД с использованием шифрования

В составе postgres имеется библиотека libpq, которая автоматически будет шифровать передаваемые данные, если и на клиенте, и на сервере установлен OpenSSL (может не работать в некоторых сборках).

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

Выпуск сертификатов

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

mkdir -p ~/certs/rootca ~/certs/intermediateca ~/certs/postgres/pg.test

Выпустим корневой сертификат:

openssl req -new -nodes -out ~/certs/rootca/root.csr -keyout ~/certs/rootca/root.key -subj "/CN=Anton Oreshin Certification Authority"

openssl x509 -req -in ~/certs/rootca/root.csr -days 3650 -extfile /etc/ssl/openssl.cnf -extensions v3_ca -signkey ~/certs/rootca/root.key -out ~/certs/rootca/root.crt

chmod og-rwx ~/certs/rootca/root.key

На основе корневого выпустим промежуточный сертификат:

openssl req -new -nodes -out ~/certs/intermediateca/intermediate.csr -keyout ~/certs/intermediateca/intermediate.key -subj "/CN=Anton Oreshin Intermediate Certification Authority"

openssl x509 -req -in ~/certs/intermediateca/intermediate.csr -days 1825 -extfile /etc/ssl/openssl.cnf -extensions v3_ca -CA ~/certs/rootca/root.crt -CAkey ~/certs/rootca/root.key -CAcreateserial -out ~/certs/intermediateca/intermediate.crt

chmod og-rwx ~/certs/intermediateca/intermediate.key

И, наконец, сертификат сервера:

openssl req -new -nodes -out ~/certs/postgres/pg.test/leaf.csr -keyout ~/certs/postgres/pg.test/leaf.key -subj "/CN=pg.test"

openssl x509 -req -in ~/certs/postgres/pg.test/leaf.csr -days 365 -CA ~/certs/intermediateca/intermediate.crt -CAkey ~/certs/intermediateca/intermediate.key -CAcreateserial -out ~/certs/postgres/pg.test/leaf.crt

cat ~/certs/postgres/pg.test/leaf.crt ~/certs/intermediateca/intermediate.crt ~/certs/rootca/root.crt > ~/certs/postgres/pg.test/server.crt

chmod og-rwx ~/certs/postgres/pg.test/leaf.key

cp ~/certs/postgres/pg.test/leaf.key ~/certs/postgres/pg.test/server.key

Верификация сервера клиентом

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

Переместим server.crt и server.key в data_directory ($PGDATA) Postgresql и зададим ограниченные права доступа к ключу:

Debian

chown postgres:postgres /var/lib/postgresql/17/main/server.key

chmod 0600 /var/lib/postgresql/17/main/server.key

AlmaLinux

chown postgres:postgres /var/lib/pgsql/17/data/server.key

chmod 0600 /var/lib/pgsql/17/data/server.key

Включим поддержку ssl в конфиге /etc/postgresql/17/main/postgresql.conf (/var/lib/pgsql/17/data/postgresql.conf для RHEL), раскомментировав строки:

Также указал путь к dhparams.pem, сгенерированному командой openssl dhparam -out /var/lib/postgresql/17/main/dhparams.pem 2048

В /etc/postgresql/17/main/pg_hba.conf (/var/lib/pgsql/17/data/pg_hba.conf для RHEL) укажем, что поддерживаются только зашифрованные соединения, заменив host на hostssl:

Для применения изменений перезапустим postgresql.service:

Debian: sudo systemctl restart postgresql.service

AlmaLinux: sudo systemctl restart postgresql-17.service

На клиенте достаточно иметь корневой сертификат центра сертификации. Скопируем его содержимое в ~/.postgresql/root.crt:

cp ~/certs/rootca/root.crt ~/.postgresql/root.crt

Подключимся к базе данных, указав параметр sslmode=verify-full, который будет проверять соответствие CN сертификата и адреса сервера:

psql -U test_user -h pg.test -d "dbname=test_database sslmode=verify-full"

*список доступных параметров: тыц

Зашифрованное соединение

Вывести список подключений с информацией о шифровании:

SELECT datname, usename, ssl, client_addr
FROM pg_stat_ssl
JOIN pg_stat_activity
ON pg_stat_ssl.pid = pg_stat_activity.pid;

Использование клиентских сертификатов

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

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

mkdir -p ~/certs/postgres/test_user

openssl req -new -nodes -out ~/certs/postgres/test_user/leaf.csr -keyout ~/certs/postgres/test_user/leaf.key -subj "/CN=test_user"

openssl x509 -req -in ~/certs/postgres/test_user/leaf.csr -days 365 -CA ~/certs/intermediateca/intermediate.crt -CAkey ~/certs/intermediateca/intermediate.key -CAcreateserial -out ~/certs/postgres/test_user/leaf.crt

cat ~/certs/postgres/test_user/leaf.crt ~/certs/intermediateca/intermediate.crt ~/certs/rootca/root.crt > ~/certs/postgres/test_user/postgresql.crt

chmod og-rwx ~/certs/postgres/test_user/leaf.key

cp ~/certs/postgres/test_user/leaf.key ~/certs/postgres/test_user/postgresql.key

Скопируем сертификат и ключ клиента в ~/.postgresql:

mkdir -p ~/.postgresql

cp ~/certs/postgres/test_user/postgresql.crt ~/.postgresql/postgresql.crt

cp ~/certs/postgres/test_user/postgresql.key ~/.postgresql/postgresql.key

На сервер в data_directory загрузите root.crt и добавьте его в postgresql.conf (параметр ssl_ca_file):

Если сервер не запускается, попробуйте указать полный путь к root.crt

В pg_hba.conf измените метод аутентификации на cert:

Только зашифрованное соединение, аутентификация по сертификату

Отправим postgresql.service в рестарт:

Debian: sudo systemctl restart postgresql.service

AlmaLinux: sudo systemctl restart postgresql-17.service

Проверка соединения

Заключение

В этой статье я постарался максимально простым языком рассмотреть методы установки и начальной настройки PostgreSQL. Если у вас есть вопросы или замечания — не стесняйтесь, приходите в комментарии =)

Если публикация была полезна — ставьте лайк и подписывайтесь на канал, чтобы чаще видеть в ленте подобный контент. Всем добра!)