Веб-стек / Ступень 7 из 9

Подключение, роль и pg_hba

Не запущена Запускается обычно ~20 с

Шаг 6 из 7 · лаба · 25 мин

Приложение не подключается к PostgreSQL

Служба падает при старте с ошибкой подключения к базе, хотя PostgreSQL запущен. Верни ей доступ по паролю.

Ситуация

Служба app работает с базой PostgreSQL appdb от имени пользователя базы appuser и подключается к ней по TCP на 127.0.0.1:5432. С утра служба не поднимается: падает при старте, и systemd перезапускает её раз в несколько секунд. Сам PostgreSQL запущен.

Команды администратора запускай через sudo. Интернета на машине нет.

Задача

Сделай так, чтобы:

appuser подключался к appdb по TCP на 127.0.0.1 со своим паролем — так же, как это делает служба;
без верного пароля appuser к базе не подключался;
служба app работала и держала подключение к базе.

Лаба · самостоятельно

Команды в этой лабе не даны: набирай их в терминале сам. Цели проверяются на машине после каждой команды.

Цели

0 из 3

appuser с паролем подключается к appdb по TCP на 127.0.0.1 — ещё не выполнена
С неверным паролем appuser не подключается — ещё не выполнена
Служба app работает и подключена к базе — ещё не выполнена

Ctrl+Enter

Запустит машину node1 · Ubuntu 24.04

Машина запускается

Подсказки

0 из 3

Сообщить о проблеме

Цели лабы

0 из 3

appuser с паролем подключается к appdb по TCP на 127.0.0.1 — ещё не выполнена
С неверным паролем appuser не подключается — ещё не выполнена
Служба app работает и подключена к базе — ещё не выполнена

node1 Ubuntu 24.04

Для запуска машины нужен вход

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

После входа вернёшься на эту лабу.

Цели лабы

0 из 3

appuser с паролем подключается к appdb по TCP на 127.0.0.1 — ещё не выполнена
С неверным паролем appuser не подключается — ещё не выполнена
Служба app работает и подключена к базе — ещё не выполнена

Службы и systemd · подобрано по лабе

PostgreSQL — доступ, pg_hba.conf, дамп и восстановление

Шпаргалка

$_ — вставить в терминал

sudo systemctl start postgresql в лабах служба выключена
pg_lsclusters версия, порт, статус
sudo -u postgres psql консоль суперпользователя
psql -h 127.0.0.1 -U appuser -d appdb вход по TCP с паролем
sudo -u postgres psql -c 'TABLE pg_hba_file_rules' правила доступа
sudo ss -tlnp | grep 5432 на каком адресе слушает
sudo -u postgres pg_dump -Fc -f /tmp/appdb.dump appdb
sudo -u postgres pg_restore -l /tmp/appdb.dump оглавление

Два файла в /etc/postgresql/16/main/: postgresql.conf — параметры сервера, pg_hba.conf — кто и как может войти. Какие файлы и значения сервер прочитал на самом деле — SHOW hba_file;, SHOW listen_addresses;.

pg_hba.conf

pg_hba.conf — правила доступа, по строке на правило. В Ubuntu по умолчанию:

text

# TYPE  DATABASE  USER      ADDRESS        METHOD
local   all       postgres                 peer
local   all       all                      peer
host    all       all       127.0.0.1/32   scram-sha-256
host    all       all       ::1/128        scram-sha-256

Поле	Что значит
TYPE	`local` — через сокет, `host` — по TCP
DATABASE	имя базы или `all`
USER	роль или `all`
ADDRESS	адрес клиента с маской; у `local` пусто
METHOD	как проверять: `peer`, `scram-sha-256`, `reject`

peer пускает пользователя Linux только в роль с тем же именем, поэтому суперпользователь входит как sudo -u postgres psql. scram-sha-256 — пароль. reject — отказ без проверки.

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

Правку применяет sudo systemctl reload postgresql или SELECT pg_reload_conf();. Что сервер прочитал, показывает представление pg_hba_file_rules: номер строки, поля и столбец error. С ошибкой в файле сервер его не принимает и работает по старым правилам, а в журнале — pg_hba.conf was not reloaded.

Адрес и порт

По умолчанию listen_addresses = 'localhost' — только 127.0.0.1 и ::1, порт 5432. Чтобы принимать подключения из сети, нужны три вещи: адрес в listen_addresses ('*' — все), строка host для адреса клиента в pg_hba.conf и открытый порт в файрволе. listen_addresses меняется только через sudo systemctl restart postgresql: после reload параметр помечен pending_restart, а сервер слушает по-старому.

Свою настройку удобно держать отдельным файлом: postgresql.conf читает каталог conf.d рядом с собой (include_dir = 'conf.d'), и обновление пакета такой файл не трогает:

bash

echo "listen_addresses = '127.0.0.1'" \
  | sudo tee /etc/postgresql/16/main/conf.d/listen.conf
sudo systemctl restart postgresql
sudo ss -tlnp | grep 5432

conf.d читается после строки include_dir в конце postgresql.conf, и действует последнее значение. С listen_addresses = '*' в postgresql.conf и '127.0.0.1' в conf.d/listen.conf сервер слушает 127.0.0.1. Откуда взято значение и ждёт ли оно перезапуска, показывает pg_settings:

text

student@ubuntu:~$ sudo -u postgres psql -Atc "SELECT setting,
> sourcefile, sourceline, pending_restart FROM pg_settings
> WHERE name='listen_addresses'"
127.0.0.1|/etc/postgresql/16/main/conf.d/listen.conf|1|f

После правки файла и reload значение прежнее, pending_restart — t, а в журнале /var/log/postgresql/postgresql-16-main.log:

text

LOG:  parameter "listen_addresses" cannot be changed without restarting
  the server
LOG:  configuration file "/etc/postgresql/16/main/conf.d/listen.conf"
  contains errors; unaffected changes were applied

Каждую строку каждого файла с её судьбой показывает pg_file_settings (applied, error — например, setting could not be applied).

Роль и база для приложения

bash

sudo -u postgres psql -c "CREATE ROLE appuser LOGIN PASSWORD 'apppass'"
sudo -u postgres createdb -O appuser appdb
psql -h 127.0.0.1 -U appuser -d appdb -c 'SELECT current_user'

Новый пароль — ALTER ROLE appuser PASSWORD '…';, список ролей — \du в psql, баз — \l.

Пароль без ввода: ~/.pgpass

bash

echo '127.0.0.1:5432:appdb:appuser:apppass' > ~/.pgpass
chmod 600 ~/.pgpass
psql -h 127.0.0.1 -U appuser -d appdb

Строка — адрес:порт:база:роль:пароль, * подходит к любому значению. Режим — только 600: иначе psql пишет password file … has group or world access и файл пропускает. Переменная PGPASSWORD важнее файла.

Дамп: форматы

Логический дамп снимает pg_dump — команды и данные, из которых база создаётся заново. Роли в него не входят: их сохраняет pg_dumpall --globals-only.

Формат	Команда	Восстанавливает
простой SQL	`pg_dump -f appdb.sql appdb`	`psql -f`
custom	`pg_dump -Fc -f appdb.dump appdb`	`pg_restore`
каталог	`pg_dump -Fd -j 4 -f appdb.dir appdb`	`pg_restore -j 4`

Простой SQL читается глазами: CREATE TABLE, COPY с данными, setval для счётчиков. В начале и в конце — строки \restrict и \unrestrict со случайным ключом: защита psql от чужих команд в данных, так и должно быть. Формат custom сжат, начинается с байтов PGDMP и позволяет посмотреть оглавление и восстановить часть:

bash

sudo -u postgres pg_restore -l /tmp/appdb.dump

text

216; 1259 16387 TABLE public orders appuser
215; 1259 16386 SEQUENCE public orders_id_seq appuser
3396; 0 16387 TABLE DATA public orders appuser
3404; 0 0 SEQUENCE SET public orders_id_seq appuser

Над оглавлением — шапка архива: Archive created at 2026-10-20 10:05:53 UTC, dbname: appdb и версии сервера. По ней видно, какая это копия и откуда. Данные одной таблицы без восстановления печатает pg_restore -f - -t orders /tmp/appdb.dump — определение таблицы и строки COPY.

Восстановить в новую базу

bash

sudo -u postgres createdb -O appuser appdb_copy
sudo -u postgres pg_restore -d appdb_copy /tmp/appdb.dump
sudo -u postgres psql -d appdb_copy -c 'SELECT count(*) FROM orders'

Новая пустая база — без конфликтов: код 0. Счётчик id дамп восстанавливает строкой setval('public.orders_id_seq', 3, true) — следующая вставка получит id 4.

Владельцы. createdb -O appuser задаёт владельца самой базы. Владельцев таблиц pg_restore от postgres берёт из архива — строками ALTER TABLE … OWNER TO appuser, и такая роль должна существовать. С --no-owner владельцем станет тот, кто восстанавливает, а с --no-owner --role=r — роль r.

Ключ pg_restore	Что делает
`-l`	только оглавление архива
`-L список`	восстановить строки оглавления из файла
`-f -`	напечатать SQL вместо записи в базу
`--exit-on-error`	остановиться на первой ошибке
`--single-transaction`	всё или ничего: при ошибке откат
`--clean --if-exists`	удалить объекты перед созданием
`--create -d postgres`	создать базу с именем из дампа
`--no-owner --role=r`	владельцем объектов станет роль r

Сверить копию с источником

sql

SELECT count(*) FROM orders;
SELECT md5(string_agg(t::text, ',' ORDER BY id)) FROM orders t;
SELECT last_value FROM orders_id_seq;

Одинаковые число строк, сумма выборки и значение счётчика в источнике и копии — данные совпадают. Владельца таблиц покажет \dt.

Connection refused

connection to server at "127.0.0.1", port 5432 failed: Connection refused и Is the server running on that host and accepting TCP/IP connections?. Служба не запущена (pg_lsclusters, systemctl status postgresql) или не слушает этот адрес: sudo ss -tlnp | grep 5432, SHOW listen_addresses;. Через сокет то же выглядит как No such file or directory и Is the server running locally….

no pg_hba.conf entry

FATAL: no pg_hba.conf entry for host "127.0.0.1", user "appuser", database "appdb", no encryption — ни одна строка не подошла к этому адресу, базе и роли. Сообщение приходит дважды: psql пробует подключиться с SSL, потом без. Добавь строку host appdb appuser <адрес>/32 scram-sha-256, reload и проверь pg_hba_file_rules.

pg_hba.conf rejects connection for host … — подошла строка reject, она стоит выше разрешающей.

password authentication failed

Правило нашлось, пароль не подошёл. Для роли, которой нет, сервер пишет то же самое — проверь \du. Пароль задаёт ALTER ROLE … PASSWORD; помни, что PGPASSWORD перекрывает ~/.pgpass. fe_sendauth: no password supplied — пароль спросили, а взять его неоткуда.

Peer authentication failed

psql -U appuser без -h идёт через сокет, а там правило peer: пользователь Linux должен называться как роль. Подключайся с -h 127.0.0.1 и паролем или sudo -u postgres psql. role "student" does not exist — psql без -U берёт роль по имени пользователя Linux.

Правка pg_hba.conf не действует

Файл не перечитан или перечитан с ошибкой — тогда работают старые правила, а pg_reload_conf() всё равно возвращает t. Ошибку покажет SELECT line_number, error FROM pg_hba_file_rules WHERE error IS NOT NULL;, в журнале — pg_hba.conf was not reloaded.

pg_restore: relation "…" already exists

Восстановление в базу, где объекты уже есть: ошибка на каждом, в конце pg_restore: warning: errors ignored on restore: 4, код 1, и часть данных могла лечь поверх. Восстанавливай в новую пустую базу. --single-transaction при первой ошибке откатывает всё. --create -d postgres при живой базе с тем же именем — database "appdb" already exists.

input file appears to be a text format dump

pg_restore получил простой SQL — восстанавливай его psql -f. Обратная ошибка: psql -f на custom-дампе — The input is a PostgreSQL custom-format dump. Use the pg_restore command-line client….

trust в pg_hba.conf

trust пускает без пароля любого, кто подходит под строку. host all all 0.0.0.0/0 trust открывает все базы каждому, кто дотянется до порта.

pg_restore --clean в рабочую базу

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