RU

Linux и сеть

Установка и защита PostgreSQL

Установите PostgreSQL 18 из официального репозитория на Debian или Ubuntu: роли и права, удалённый доступ по TLS, автоматические резервные копии и проверка восстановления.

8 мин чтения, обновлено 18 ноября 2026 г.

На этой странице

Ваши значения

Укажите их один раз, и команды в руководстве обновятся.

Это руководство объясняет, как установить PostgreSQL на ваш VPS или выделенный сервер из официального репозитория проекта, создать роли с ограниченными правами, при необходимости открыть зашифрованный удалённый доступ и настроить резервное копирование, которое вы сможете восстановить.

В итоге PostgreSQL будет слушать только локальные подключения, у каждого приложения будет своя роль, а ежедневная резервная копия будет создаваться и проверяться тестом восстановления.

Заказать сервер

Для размещения вашей базы данных HostMyServers предлагает несколько тарифов:

Предварительные требования

  • Сервер под управлением Debian 12 или 13 либо Ubuntu 22.04 или 24.04
  • Доступ по SSH с пользователем, у которого есть sudo
  • Обновлённая система (sudo apt update && sudo apt upgrade)
  • Только для прямого удалённого доступа: доменное имя, указывающее на сервер

Порты, которые нужно открыть

Назначение Порт Нужно открывать?
Приложения на том же сервере 5432/tcp локально Нет
Удалённый доступ через SSH-туннель (рекомендуется) 22/tcp или ваш порт SSH Уже открыт для администрирования
Прямой удалённый доступ по TLS 5432/tcp Да, только с IP-адреса ваших клиентов

Установка PostgreSQL из официального репозитория

Debian и Ubuntu поставляют PostgreSQL, но его версия остаётся неизменной на протяжении всего срока жизни дистрибутива. Репозиторий PGDG (apt.postgresql.org) содержит все поддерживаемые версии.

На момент написания последняя основная версия — PostgreSQL 18 (18.7). Актуальную версию смотрите в политике версий.

  1. Установите необходимые инструменты и загрузите ключ репозитория:

    Терминал
    sudo apt install -y curl ca-certificates gpg
    sudo install -d /usr/share/postgresql-common/pgdg
    sudo curl -fsSL -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc https://www.postgresql.org/media/keys/ACCC4CF8.asc
  2. Проверьте отпечаток ключа:

    Терминал
    gpg --show-key /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc

    Он должен совпадать с B97B0AFCAA1A47F044F244A07FCC7D46ACCC4CF8. Если это не так, остановитесь.

  3. Добавьте репозиторий:

    Терминал
    . /etc/os-release
    sudo tee /etc/apt/sources.list.d/pgdg.sources > /dev/null <<EOF
    Types: deb
    URIs: https://apt.postgresql.org/pub/repos/apt
    Suites: ${VERSION_CODENAME}-pgdg
    Architectures: $(dpkg --print-architecture)
    Components: main
    Signed-By: /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc
    EOF
  4. Установите сервер:

    Терминал
    sudo apt update
    sudo apt install -y postgresql-18

    Пакет автоматически создаёт и запускает экземпляр с именем 18/main.

Проверка установки

Терминал
pg_lsclusters
sudo systemctl status postgresql@18-main --no-pager
sudo -u postgres psql -c "SELECT version();"
sudo ss -tlnp | grep 5432

pg_lsclusters должен показать экземпляр 18 main со статусом online. ss должен показать прослушивание на 127.0.0.1:5432 и [::1]:5432, а не на 0.0.0.0.

Элемент Расположение
Конфигурация /etc/postgresql/18/main/
Дополнительные параметры /etc/postgresql/18/main/conf.d/
Правила доступа /etc/postgresql/18/main/pg_hba.conf
Данные /var/lib/postgresql/18/main/
Журнал /var/log/postgresql/postgresql-18-main.log

Как работает аутентификация

Файл pg_hba.conf определяет, кто может подключаться, к какой базе и каким способом. PostgreSQL читает его строки сверху вниз и применяет первую подходящую. Если этот способ не срабатывает, следующие строки не проверяются.

Выведите действующие правила:

Терминал
sudo -u postgres psql -c "SELECT line_number, type, database, user_name, address, auth_method, error FROM pg_hba_file_rules;"

В новой установке вы должны увидеть два способа:

  • peer для локальных подключений через сокет: имя системного пользователя должно совпадать с именем роли. Поэтому sudo -u postgres psql работает без пароля.
  • scram-sha-256 для TCP-подключений с 127.0.0.1 и ::1: запрашивается пароль.

Создание ролей и базы данных

Не используйте суперпользователя postgres в ваших приложениях. В следующем примере создаются база appdb, роль-владелец app_owner и роль только для чтения app_reader.

  1. Создайте роли. Каждая команда запрашивает пароль, поэтому он не попадает в историю оболочки:

    Терминал
    sudo -u postgres createuser --pwprompt app_owner
    sudo -u postgres createuser --pwprompt app_reader
  2. Создайте базу:

    Терминал
    sudo -u postgres createdb --owner=app_owner appdb
  3. Ограничьте доступ к этой базе:

    Терминал
    sudo -u postgres psql -d appdb
    SQL
    REVOKE ALL ON DATABASE appdb FROM PUBLIC;
    GRANT CONNECT ON DATABASE appdb TO app_reader;
    GRANT USAGE ON SCHEMA public TO app_reader;
    GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_reader;
    ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA public GRANT SELECT ON TABLES TO app_reader;
    \q

    Последняя инструкция также даёт app_reader право на чтение будущих таблиц, которые создаст app_owner.

  4. Проверьте подключение с паролем:

    Терминал
    psql -h 127.0.0.1 -U app_owner -d appdb -c "CREATE TABLE restore_check (id int PRIMARY KEY, note text);"
    psql -h 127.0.0.1 -U app_owner -d appdb -c "INSERT INTO restore_check VALUES (1, 'sauvegarde OK');"
    psql -h 127.0.0.1 -U app_reader -d appdb -c "SELECT * FROM restore_check;"

    Попытка записи от имени app_reader должна завершиться ошибкой permission denied for table restore_check. Таблица restore_check понадобится ниже для проверки восстановления.

Удалённый доступ к базе

Рекомендуемый вариант: SSH-туннель

Туннель не требует изменений ни в PostgreSQL, ни в брандмауэре. На вашем компьютере выполните:

Терминал
ssh -N -L 5433:127.0.0.1:5432 ВАШ_ПОЛЬЗОВАТЕЛЬ@IP_ВАШЕГО_СЕРВЕРА

Не закрывайте эту команду, затем подключите клиент к локальному порту 5433:

Терминал
psql -h 127.0.0.1 -p 5433 -U app_owner -d appdb

Продвинутый вариант: прямое подключение по TLS

Используйте этот вариант, если внешний сервис не может работать через SSH. Для него нужны доменное имя, например db.ваш-домен.com, и действительный сертификат: получить его с помощью Certbot можно по нашему руководству Установка SSL-сертификата Let's Encrypt.

  1. Создайте скрипт, который копирует сертификат для PostgreSQL и будет запускаться при каждом продлении:

    Терминал
    sudo nano /etc/letsencrypt/renewal-hooks/deploy/postgresql.sh
    Терминал
    #!/bin/sh
    set -e
    DOMAIN="db.ваш-домен.com"
    [ "$RENEWED_LINEAGE" = "/etc/letsencrypt/live/$DOMAIN" ] || exit 0
    install -o postgres -g postgres -m 644 "$RENEWED_LINEAGE/fullchain.pem" /etc/postgresql/18/main/server.crt
    install -o postgres -g postgres -m 600 "$RENEWED_LINEAGE/privkey.pem" /etc/postgresql/18/main/server.key
    systemctl reload postgresql@18-main
  2. Сделайте его исполняемым и запустите в первый раз:

    Терминал
    sudo chmod 755 /etc/letsencrypt/renewal-hooks/deploy/postgresql.sh
    sudo RENEWED_LINEAGE=/etc/letsencrypt/live/db.ваш-домен.com /etc/letsencrypt/renewal-hooks/deploy/postgresql.sh
  3. Создайте файл конфигурации:

    Терминал
    sudo nano /etc/postgresql/18/main/conf.d/90-remote-access.conf
    Конфигурация
    listen_addresses = '*'
    ssl = on
    ssl_cert_file = '/etc/postgresql/18/main/server.crt'
    ssl_key_file = '/etc/postgresql/18/main/server.key'
    ssl_min_protocol_version = 'TLSv1.2'
  4. Добавьте в конец /etc/postgresql/18/main/pg_hba.conf правило, ограниченное одной базой, одной ролью и одним адресом:

    Текст
    hostssl appdb app_owner ВАШ_IP/32 scram-sha-256

    hostssl отклоняет любое незашифрованное подключение. Для адреса IPv6 используйте суффикс /128.

  5. Разрешите в брандмауэре только ваш адрес. С UFW:

    Терминал
    sudo ufw allow from ВАШ_IP to any port 5432 proto tcp
  6. Перезапустите PostgreSQL, так как listen_addresses не применяется при простой перезагрузке конфигурации:

    Терминал
    sudo systemctl restart postgresql@18-main
    sudo -u postgres psql -c "SELECT line_number, error FROM pg_hba_file_rules WHERE error IS NOT NULL;"

    Вторая команда не должна вернуть ни одной строки.

  7. Подключитесь с вашего компьютера клиентом psql версии 16 или выше:

    Терминал
    psql "host=db.ваш-домен.com port=5432 dbname=appdb user=app_owner sslmode=verify-full sslrootcert=system"

    Затем убедитесь, что сеанс зашифрован:

    SQL
    SELECT ssl, version FROM pg_stat_ssl WHERE pid = pg_backend_pid();

Автоматизация резервного копирования

pg_dump экспортирует базу без её остановки, но не сохраняет ни роли, ни их пароли. Поэтому следующий скрипт также экспортирует эти глобальные объекты командой pg_dumpall --globals-only.

  1. Создайте каталог назначения:

    Терминал
    sudo install -d -o postgres -g postgres -m 700 /var/backups/postgresql
  2. Создайте скрипт:

    Терминал
    sudo nano /usr/local/sbin/postgresql-backup.sh
    Терминал
    #!/bin/bash
    set -euo pipefail
    umask 077
    
    DEST="/var/backups/postgresql"
    RETENTION_DAYS=14
    STAMP="$(date +%F_%H%M%S)"
    TARGET="$DEST/$STAMP"
    
    mkdir -p "$TARGET"
    pg_dumpall --globals-only -f "$TARGET/globals.sql"
    
    psql -AtX -d postgres -c "SELECT datname FROM pg_database WHERE datallowconn AND NOT datistemplate" |
    while IFS= read -r db; do
      pg_dump -Fc -d "$db" -f "$TARGET/$db.dump"
    done
    
    find "$DEST" -mindepth 1 -maxdepth 1 -type d -mtime +"$RETENTION_DAYS" -exec rm -rf {} +
  3. Создайте службу:

    Терминал
    sudo chmod 755 /usr/local/sbin/postgresql-backup.sh
    sudo nano /etc/systemd/system/postgresql-backup.service
    Конфигурация
    [Unit]
    Description=PostgreSQL backup
    After=postgresql.service
    
    [Service]
    Type=oneshot
    User=postgres
    Group=postgres
    ExecStart=/usr/local/sbin/postgresql-backup.sh
  4. Создайте таймер:

    Терминал
    sudo nano /etc/systemd/system/postgresql-backup.timer
    Конфигурация
    [Unit]
    Description=Daily PostgreSQL backup
    
    [Timer]
    OnCalendar=*-*-* 02:30:00
    RandomizedDelaySec=10min
    Persistent=true
    
    [Install]
    WantedBy=timers.target
  5. Включите его и запустите первую резервную копию:

    Терминал
    sudo systemctl daemon-reload
    sudo systemctl enable --now postgresql-backup.timer
    sudo systemctl start postgresql-backup.service
    sudo systemctl status postgresql-backup.service --no-pager
    sudo -u postgres ls -lR /var/backups/postgresql

Проверка восстановления

Резервная копия имеет ценность, только если вы уже успешно её восстанавливали. Для этого теста используется временная база, и рабочая база не затрагивается.

Терминал
STAMP="$(sudo -u postgres ls -1 /var/backups/postgresql | tail -n 1)"
sudo -u postgres createdb restore_test
sudo -u postgres pg_restore --no-owner --no-privileges --exit-on-error -d restore_test "/var/backups/postgresql/$STAMP/appdb.dump"
sudo -u postgres psql -d restore_test -c "SELECT * FROM restore_check;"
sudo -u postgres dropdb restore_test

Вы должны увидеть строку sauvegarde OK. Повторяйте эту проверку регулярно и сравнивайте также несколько важных для вашего приложения таблиц.

Восстановление после сбоя

На новом сервере с установленным PostgreSQL либо после остановки приложений и удаления повреждённой базы:

Терминал
STAMP="2026-11-18_023000"
sudo -u postgres psql -X -f "/var/backups/postgresql/$STAMP/globals.sql" postgres
sudo -u postgres dropdb --if-exists appdb
sudo -u postgres pg_restore --create --exit-on-error -d postgres "/var/backups/postgresql/$STAMP/appdb.dump"
sudo -u postgres psql -d appdb -c "ANALYZE;"

Замените значение STAMP нужным каталогом. Ошибка role "postgres" already exists при восстановлении ролей является нормальной.

Обновление PostgreSQL

Минорные версии (например, с 18.6 до 18.7) исправляют ошибки и уязвимости. Они устанавливаются вместе с обновлением системы и перезапускают службу:

Терминал
sudo apt update
sudo apt upgrade

Новая основная версия устанавливается рядом со старой и требует миграции. Сначала запустите резервное копирование, затем измените номера версий:

Терминал
OLD=18
NEW=19
sudo systemctl start postgresql-backup.service
sudo apt install -y "postgresql-$NEW"
pg_lsclusters
sudo pg_dropcluster --stop "$NEW" main
sudo pg_upgradecluster -v "$NEW" "$OLD" main
pg_lsclusters

pg_dropcluster здесь удаляет пустой экземпляр, созданный новым пакетом: убедитесь с помощью pg_lsclusters, что в нём нет ничего полезного. Затем pg_upgradecluster помещает новую версию на прежний порт и сохраняет старую в остановленном состоянии. Когда вы проверите свои приложения, удалите её:

Терминал
sudo pg_dropcluster "$OLD" main
sudo apt purge "postgresql-$OLD" "postgresql-client-$OLD"

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

Если что-то пошло не так

FATAL: no pg_hba.conf entry for host

Ни одно правило не соответствует адресу, роли, базе или шифрованию. В сообщении указаны значения, полученные сервером. Убедитесь, что они соответствуют вашей строке hostssl, включая реальный публичный IP-адрес клиента, затем перезагрузите конфигурацию:

Терминал
sudo systemctl reload postgresql@18-main

FATAL: password authentication failed for user

Пароль неверен либо у роли его нет. Задайте его заново из административного сеанса:

Терминал
sudo -u postgres psql -c "\password app_owner"

FATAL: Peer authentication failed for user

Вы подключаетесь через локальный сокет с ролью, отличной от вашего системного пользователя. Добавьте -h 127.0.0.1, чтобы использовать пароль, либо запустите команду от нужного пользователя через sudo -u.

Connection refused или превышено время ожидания

Терминал
pg_lsclusters
sudo ss -tlnp | grep 5432
sudo ufw status
sudo tail -n 50 /var/log/postgresql/postgresql-18-main.log

Connection refused обычно означает, что PostgreSQL остановлен или не слушает на этом адресе. Превышение времени ожидания скорее указывает на брандмауэр.

permission denied for table или for schema public

У роли нет необходимых прав. Выполните GRANT заново, подключившись к нужной базе. Для существующих таблиц, созданных другой ролью, ALTER DEFAULT PRIVILEGES недостаточно: он касается только будущих таблиц.

Служба не запускается после изменения

Журнал указывает файл и строку, вызвавшие проблему:

Терминал
sudo journalctl -u postgresql@18-main -n 50 --no-pager
sudo tail -n 50 /var/log/postgresql/postgresql-18-main.log

При использовании собственного сертификата проверьте также, что server.key принадлежит postgres и имеет права 600.

could not change directory to "/root"

Это сообщение появляется, когда вы запускаете sudo -u postgres из каталога, который postgres не может прочитать. Оно безвредно: перейдите в /tmp, и оно исчезнет.

Ссылки