Как настроить PostgreSQL для приложения на VPS

Для небольшого приложения на VPS настройте отдельную базу PostgreSQL, ограниченную роль для рабочего процесса и проверяемое локальное соединение. Не запускайте сайт с правами postgres и не открывайте порт базы всему интернету. Результатом должна стать работающая операция приложения при отсутствии лишних административных полномочий.

Ниже разобрана новая база для backend, который работает на том же Linux-сервере вне контейнера. PostgreSQL уже установлен; версию и действующий кластер нужно проверить до изменений. Это не инструкция по переносу существующей production-базы или настройке высокой доступности. Имена myapp, myapp_owner и myapp_app учебные: используйте уникальные имена своего проекта и сначала повторите настройку в тестовой среде.

Что проверить до создания базы

Выясните, какой сервер PostgreSQL обслуживает проект: системный пакет, контейнер или внешняя услуга. Проверьте версию сервера, не только клиента psql. На одной машине могут существовать несколько кластеров с разными портами и каталогами. Соединение не с тем кластером способно привести к настройке прав в базе, которую приложение вообще не использует.

sudo -u postgres psql
SELECT version();
SHOW port;
SHOW listen_addresses;
SHOW hba_file;

Первая команда подходит для типичной установки Linux с системным пользователем postgres и разрешённым локальным администрированием. Для контейнера и управляемой базы вход другой. Если доступа нет, не меняйте аутентификацию на trust ради продолжения инструкции. Обратитесь к администратору с точным описанием среды.

Запишите, кто выполняет миграции, кто запускает приложение и какие операции ему нужны. У отчётного сервиса может быть только чтение, у формы заявок — запись в определённые таблицы. Право создавать роли и базы обычному HTTP-процессу не требуется. Настройка должна исходить из этих действий, а не из желания убрать любое сообщение permission denied.

Для существующего сервера подтвердите резервную копию и окно изменений. Создание новой базы не требует удаления старой, но ошибка выбора кластера или широкая правка pg_hba.conf способна затронуть соседние проекты. Здесь все команды рассчитаны на новую изолированную базу; их нельзя выполнять поверх неизвестной рабочей конфигурации без разбора текущих прав.

Как разделить владельца и рабочую роль

Роль PostgreSQL определяет вход и полномочия внутри сервера базы. Она не является автоматически пользователем Linux. Практичная схема: отдельный владелец объектов для миграций и отдельная роль для runtime. Backend получает только разрешения, необходимые при обычной работе.

В административной сессии новой базы создайте роли и базу:

CREATE ROLE myapp_owner NOLOGIN;
CREATE ROLE myapp_app LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE
  NOREPLICATION NOBYPASSRLS;
CREATE DATABASE myapp OWNER myapp_owner;
\password myapp_app
\connect myapp
CREATE SCHEMA app AUTHORIZATION myapp_owner;

Команда psql \password предлагает ввести пароль отдельно, без помещения его в SQL-строку примера. Установите уникальный пароль защищённым способом. Назначение атрибутов LOGIN и административных прав описано в CREATE ROLE; безопасный ввод пароля — в справочнике psql.

NOLOGIN не запрещает уполномоченной роли выполнять SET ROLE; оно запрещает непосредственный вход под именем владельца. В приведённом примере отдельный механизм миграций ещё не создан. Его нужно спроектировать: кто получает возможность действовать как myapp_owner, где хранится доступ и когда он применяется. Не выдавайте членство владельца роли myapp_app, иначе разделение теряет смысл.

Предлагаемая схема рассчитана на обычные таблицы и последовательности в app. Расширения, функции, процедуры и row-level security требуют дополнительных решений. Не расширяйте доступ всему кластеру, если конкретной миграции не хватает права на один объект. В журнале запуска сохраняйте текст ошибки без пароля и адресов закрытой инфраструктуры.

Как выдать права на текущие и будущие таблицы

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

REVOKE CONNECT ON DATABASE myapp FROM PUBLIC;
GRANT CONNECT ON DATABASE myapp TO myapp_app;
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
GRANT USAGE ON SCHEMA app TO myapp_app;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO myapp_app;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA app TO myapp_app;
ALTER DEFAULT PRIVILEGES FOR ROLE myapp_owner IN SCHEMA app
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO myapp_app;
ALTER DEFAULT PRIVILEGES FOR ROLE myapp_owner IN SCHEMA app
  GRANT USAGE, SELECT ON SEQUENCES TO myapp_app;

Это пример для приложения с чтением и записью. Для отчёта оставьте только необходимое чтение; для отдельных таблиц выдавайте точечные права. Перед отзывом CONNECT в существующей базе найдите все её процессы, включая резервирование и миграции, и предусмотрите их доступ. Не применяйте этот блок без адаптации к уже работающей системе.

ALL TABLES затрагивает существующие объекты, а ALTER DEFAULT PRIVILEGES задаёт разрешения для будущих объектов конкретной создающей роли. Если миграции создают таблицу как другая роль, настройки myapp_owner не подхватятся автоматически. Поэтому проверьте фактического владельца новой таблицы после миграции.

Выделенная схема app помогает отделить объекты проекта, но её имя нужно явно учесть в ORM и запросах. Для примеров безопаснее квалифицированные имена app.orders вместо предположения о текущем search_path. Документация о схемах объясняет поиск объектов и влияние права CREATE. Права public зависят также от версии и истории обновления базы; не считайте все установки одинаковыми.

Право на sequence может понадобиться для генерации идентификатора при вставке. Отказ после успешного SELECT не означает, что пароль неверный. Сначала установите, какой объект использует операция. TRUNCATE, изменение структуры и владение объектами не добавлены в пример runtime: их необходимость должна подтверждаться задачей.

Как настроить локальное соединение без открытого порта

Для backend на том же хосте начните с localhost или Unix socket. Сетевые интерфейсы задаёт listen_addresses, правила допуска — pg_hba.conf. Эти уровни независимы: правильный пароль не заставит сервер слушать адрес, а открытый порт не выдаёт права на таблицы.

Для учебного TCP-соединения по IPv4 правило выглядит так:

host    myapp    myapp_app    127.0.0.1/32    scram-sha-256

Это одна запись в выбранном pg_hba.conf, а не его полная замена. Убедитесь, что клиент поддерживает SCRAM и пароль роли хранится в совместимом формате. Сохраните текущий файл и прочитайте порядок существующих правил. PostgreSQL использует первое подходящее правило, не перебирая следующие после неудачной аутентификации. Подробности приведены в описании pg_hba.conf.

Если приложение выбирает ::1, правило для 127.0.0.1 его не охватывает. Если оно находится в контейнере, loopback относится к контейнеру, а не к хосту. Для такого размещения потребуется другая ограниченная сеть и схема адресов. Не исправляйте несовпадение добавлением 0.0.0.0/0 trust. Сначала определите реальный источник соединения.

Применение изменений зависит от параметра: некоторые перечитываются, другие требуют перезапуска. Например, listen_addresses задаётся при старте сервера; это указано в настройках соединений. Не перезапускайте общий кластер без согласованного окна. После применения проверьте фактические адреса и новое соединение, не полагаясь на запись в файле.

Как проверить вход и ограничение полномочий

Проверяйте соединение под той же ролью и по тому же маршруту, который использует приложение. Успешный вход администратора через socket не подтверждает, что runtime подключится по TCP. Для примера выполните:

psql -h 127.0.0.1 -p 5432 -U myapp_app -d myapp -W
SELECT current_user, current_database();
SELECT has_schema_privilege(current_user, 'app', 'USAGE');
SELECT has_schema_privilege(current_user, 'app', 'CREATE');

5432 — пример, проверьте свой порт. Пароль вводится в приглашении. В рассматриваемой схеме USAGE должен быть разрешён, CREATE не выдан. Дополнительно проверьте атрибуты роли и отсутствие членства, которое косвенно расширяет доступ. Для принятия настройки мало увидеть ожидаемое имя пользователя: нужны разрешённая операция и проверка лишних прав.

На тестовой базе выполните штатные миграции, затем безопасный сценарий runtime с подготовленными данными. Если приложение только читает, подтвердите чтение и отсутствие записи. Не испытывайте запрет DROP на настоящей production-таблице: ошибка в роли может превратить отрицательный тест в удаление. Для таких тестов нужна отдельная среда.

Ограничение роли снижает последствия ошибки, но не делает все разрешённые действия безопасными. Роль с DELETE может удалить доступные ей строки. Если приложение обслуживает несколько клиентов, доступ к строкам должен соответствовать его модели изоляции; отдельная база и роль сами по себе не реализуют авторизацию посетителей.

Как различать ошибки доступа

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

Ошибка	Что проверять
Connection refused	Адрес, порт, процесс и сетевой маршрут
No pg_hba.conf entry	Базу, роль, источник и тип соединения
Password authentication failed	Роль, её пароль, клиент и метод аутентификации
Permission denied for schema	USAGE, CREATE и выбранную схему
Permission denied for table или sequence	Права объекта, его владельца и default privileges

Проверьте строку подключения без вывода пароля. Ошибка кодирования специальных символов в URL может выглядеть как неверная учётная запись. Лучше использовать предусмотренные библиотекой отдельные параметры или корректное кодирование. Не публикуйте полный DATABASE_URL в форме обращения.

Для автоматических libpq-клиентов существует password file с требованиями к правам. Это не универсальное решение для любого драйвера. Назначьте механизм своей программе и проверьте его под рабочим пользователем. Общий план хранения приведён в статье о ключах и секретах на VPS.

Чек-лист приёмки PostgreSQL для приложения

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

Рабочее соединение завершает настройку доступа, но не резервирование. Перед накоплением реальных заявок подготовьте резервную копию базы и проверку восстановления. Для обсуждения схемы приложения используйте Умную форму, без паролей и содержимого базы.

Ответы на частые вопросы

Можно ли использовать роль postgres в приложении

Для обычного runtime это избыточные полномочия. Создайте отдельную роль с нужными операциями; административный доступ оставьте обслуживанию.

Нужно ли открывать 5432 для сайта

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

Почему после новой миграции пропали права

Новая таблица могла быть создана другой ролью. Default privileges относятся к фактической создающей роли; проверьте владельца объекта и процесс миграций.

Чем пользователь Linux отличается от роли PostgreSQL

Первый управляет процессами и файловым доступом ОС, вторая — входом и правами базы. Их связь зависит от способа аутентификации и не возникает только из совпадения имён.

Один пароль защищает данные всех клиентов

Пароль защищает соединение роли, но не заменяет авторизацию пользователей приложения. Изоляцию клиентских данных нужно проектировать отдельно.

Нужно ли менять настройки памяти по этой инструкции

Нет. Здесь настраивается доступ. Параметры производительности выбирают по нагрузке и ресурсам, а не копируют вместе с созданием роли.