Дамп восстановился, а приложение не пустили: про роли в PostgreSQL

Переносил небольшую базу на чистый сервер. Это учёт для знакомой мастерской: заказы, клиенты, пара справочников. Postgres, одна база, одно приложение, которое ходит в неё под своим пользователем. Резервные копии делались каждую ночь через pg_dump в custom-формате и уезжали на другую машину. Пробное восстановление когда-то тоже делали. Казалось, переезд займёт полчаса.

На новом сервере поставил ту же мажорную версию, создал пустую базу и запустил восстановление:

pg_restore -d имя_базы /путь/к/дампу.dump

Полетели строки. Сначала ошибки про владельца: роль такая-то не существует. Потом про GRANT. В конце pg_restore сообщил, что ошибки были, но он их проигнорировал. Таблицы на месте, количество строк сходится с тем, что я записал перед переносом. Формально данные восстановлены.

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

Причина простая, и о ней почему-то вспоминают при первом переезде, а не при настройке копий. pg_dump сохраняет одну базу: схему, данные, права на объекты внутри неё. Роли и их пароли к конкретной базе не относятся, они общие для всего кластера. В дамп одной базы они не попадают. Тестовое восстановление когда-то прошло без вопросов, потому что делалось на том же сервере, где все роли уже были.

Выхода два, и они для разных случаев.

Первый — сохранять глобальные объекты отдельно:

pg_dumpall --globals-only > globals.sql

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

Второй — сознательно отказаться от исходных владельцев:

pg_restore --no-owner --no-privileges --role=новая_роль -d имя_базы дамп.dump

Так объекты достанутся указанной роли, а права потом выдаются заново. Это удобно, когда переносишь базу в другое окружение, где роли и так другие. Но надо понимать, что исходная схема прав теряется. Если в базе было несколько ролей с разным доступом, придётся вспоминать, кому что выдавали.

Я в тот раз пошёл вторым путём: роль одна, восстановить её руками несложно. Потом добавил выгрузку globals в ночное задание и отдельно переписал проверку копии. Раньше проверка была такой: файл есть, размер разумный, pg_restore --list читает оглавление. Это подтверждает, что дамп не битый. Это не подтверждает, что из него можно поднять работающую систему на чистой машине.

Ещё пара мест, которые на чистом сервере могут вести себя иначе, чем на старом. Я их не встретил, но проверяю. Расширения: дамп содержит CREATE EXTENSION, а пакет расширения на новом сервере должен быть установлен. Локаль и правила сортировки базы: если на новой системе другая библиотека сортировки, порядок строк и индексы по тексту могут вести себя не так, как вы привыкли. Про это у Postgres есть отдельные предупреждения в документации, лучше прочитать до переноса, а не после.

В итоге для себя я перестал считать копией один файл дампа. Копия — это дамп, globals, записка с версией Postgres и списком расширений, и одна проверка на машине, где нет ничего от старого сервера.

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

Дочитал и пошёл смотреть, что у меня дома копируется. За кинетиком стоит мини-ПК с семейным фотоархивом, внутри постгрес. Каждую ночь дамп уезжает на флешку, воткнутую в роутер. Скрипт я писал сам год назад и, честно, им гордился.

Открыл его. Одна строка: pg_dump, база, файл. Про роли ни слова. Выходит, при переезде фотки жены я восстановлю, а приложение спросит, кто я такой. Это не резервная копия, это сувенир на память о том, что копия была.

Дописал рядом вторую строку, отдельным файлом:

pg_dumpall --globals-only -f /путь/globals.sql

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

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

у меня бот модерации на постгресе в докере, и тут есть подвох. официальный образ сам создаёт юзера из POSTGRES_USER, но только при первом запуске на пустом томе. поэтому на чистом контейнере роль бота есть, дамп встаёт, и кажется, что globals не нужны. до первой роли сбоку, типа отдельного readonly для дашборда. её никто не создаст, и будет та же история «ну бот отвалился». пойду допишу globals, пока в чате всех разносят на хэллоуинских стикерах.

hopstop писал:
официальный образ сам создаёт юзера из POSTGRES_USER, но только при первом запуске на пустом томе

блин, а тут ещё одна ловушка, на которую легко наступить, когда допишешь globals. В globals.sql попадёт и та самая роль из POSTGRES_USER, вместе с хэшем пароля со старого сервера. На новом контейнере она уже есть, так что CREATE ROLE выдаст «already exists». Это просто шум. А вот следующий за ним ALTER ROLE ... PASSWORD молча поставит старый пароль. Если в новом .env пароль другой, бот получит auth failed, хотя роль на месте и дамп встал чисто.

Поэтому я бы перед восстановлением глянул, что именно лежит в файле, без выполнения:

grep -E '^(CREATE|ALTER) ROLE' globals.sql

Дальше реши, чей пароль главный, старый или из .env, и подгони второй. Ещё мелочь: юзер из POSTGRES_USER в официальном образе суперпользователь. Если бот ходит под ним, твой readonly для дашборда, пожалуй, первая роль с нормальными правами во всей схеме))

А скрипты из /docker-entrypoint-initdb.d тоже срабатывают только на пустом томе, так что держать там создание ролей как замену globals не выйдет, при переезде со старым томом они просто не запустятся.

Знакомо по звуку: проект открылся, дорожки на месте, а плагины чужие. Всё играет, но не тем голосом. Тихий ALTER ROLE — ровно такой случай.

tema_py писал:
Если в новом .env пароль другой, бот получит auth failed

Может получить и при том же пароле. До 14-й версии хэши по умолчанию были md5. Если в pg_hba на новом сервере стоит scram-sha-256, md5-хэш из globals через него не пройдёт. В выводе grep смотри на начало хэша: md5… или SCRAM-SHA-256$. Если там md5, задай пароль заново при password_encryption = scram-sha-256.

quiet_fox писал:
Если там md5, задай пароль заново

Вот тут и вылезает статья расходов, которую никто не закладывает. Файл globals весит копейки, флешка тоже. Дорого другое: чтобы задать пароль заново, нужно знать, где этот пароль ещё живёт. В .env бота, в конфиге дашборда, в бумажке у мастера под клавиатурой. Каждое такое место при переезде — ещё десять минут поиска, и, как обычно, в самый неудобный момент.

Сижу сейчас в воскресенье вечером и прикидываю на пальцах, цифры условные. Пробное восстановление на пустой машине: час, раз в квартал, итого четыре часа в год. Переезд без пробы: те же полчаса из первого поста плюс неизвестно сколько на роли, хэши и «почему auth failed, роль же есть». И всё это пока мастерская стоит без учёта заказов. Неизвестную величину я в смету ставить не люблю. Час по расписанию дешевле, чем вечер в панике.

Так что я бы к globals дописал ещё одну вещь, бесплатную: обычный текстовый список, какая роль каким приложением используется и где у него лежит пароль. Без самих паролей, просто адреса. Тогда md5 против scram превращается из расследования в пункт чек-листа)

список «где лежат пароли» — по сути карта сокровищ. пункт «бумажка под клавиатурой у мастера» я бы в неё вписывать постеснялся)

Бумажку как раз вписать, с пометкой «заменить». Карта без неё просто не показывает дыру. А сам список держать не на одной флешке с globals.

Пометка «заменить» — это хорошо, только у нас такая висит на мониторе бухгалтера третий год. Рядом надо писать, кто заменит и до какого числа.

quiet_fox писал:
Если там md5, задай пароль заново при password_encryption = scram-sha-256.

о, точно, про md5 я вообще не подумал. Значит, хэши из globals проблемные в обе стороны: и чужой пароль подсунут, и формат может быть старый. Тогда может, их вообще не тащить?

У pg_dumpall есть флаг под это:

pg_dumpall --globals-only --no-role-passwords > globals.sql

Роли, членства и атрибуты в файл попадают, а пароли нет. На новом сервере роли встают без пароля, и каждому приложению пароль задаётся заново из его .env. При scram по умолчанию хэш сразу будет нового формата. Получается, тихого ALTER ROLE со старым хэшем просто нет, и угадывать, md5 там или SCRAM, не нужно.

Цена понятная: пока пароль не задан, роль войти не может. Зато ошибка сразу громкая, а не «роль есть, а auth failed». И тут как раз пригодится список «роль → приложение → где лежит пароль» из соседнего поста, по нему и идёшь.

А проверять, что реально легло после восстановления, удобнее уже в базе, а не в файле (нужен суперюзер):

SELECT rolname, left(rolpassword, 14) FROM pg_authid WHERE rolcanlogin;

Пустое значение — пароль ещё не задан, md5… — старый хвост остался. Минимальная проверка, не аудит, но к этому чек-листу ложится норм))

--no-role-passwords беру. пусть бот лучше сразу громко падает на auth failed, чем полвечера гадать, чей хэш там лёг. пароль у меня всё равно только в .env)