Удалённые подключения к PostgreSQL: psql, URI и режимы SSL

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

Установка PostgreSQL по умолчанию сознательно отказывает удалённым подключениям. Она слушает только localhost, а её файл host-based аутентификации не содержит ничего, кроме локальных правил. Для базы на вашем ноутбуке это разумное поведение, а для базы, до которой должно достучаться приложение, оно бесполезно, поэтому кто-то должен это изменить - либо вы на своей машине, либо хост, прежде чем выдаст вам учётные данные.

Что на самом деле нужно удалённому подключению#

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

1. DNS и маршрутизация. Имя хоста разрешается, и пакеты доходят до машины. Один лишь ping не доказывает ни того, ни другого, потому что ICMP часто заблокирован, пока TCP работает.
2. Слушающий сокет. listen_addresses в postgresql.conf включает адрес, на который приходят пакеты. По умолчанию это localhost; '*' означает все интерфейсы. Изменение требует перезапуска, а не перезагрузки конфигурации.
3. Путь через файрвол. Входящий порт 5432/tcp, желательно только с тех адресов, которые должны им пользоваться. О том, как выглядит разумный набор правил, рассказывает правила файрвола, которые важны.
4. Подходящая строка в `pg_hba.conf`. Host-based аутентификация проверяется для каждого подключения по адресу источника, базе, роли и типу соединения. Побеждает первое совпадение, а отсутствие совпадения - это отказ, а не переход к следующему правилу.
5. Учётные данные, которые роль может предъявить. Пароль при scram-sha-256, клиентский сертификат при cert или ничего при trust, который вам не должен встречаться ни на чём, достижимом из интернета.

Строка подключения по частям#

Клиенты PostgreSQL на основе libpq принимают два формата, и большинство драйверов языков принимают как минимум первый.

postgresql://appuser:s3cret@db.example.com:5432/shop?sslmode=requirehost=db.example.com port=5432 dbname=shop user=appuser sslmode=require

Форма URI принимает postgresql:// или более короткое postgres://; это одна и та же схема. Всё после ? - параметры libpq, и полезные из них стоит знать по имени:

Две детали ловят людей. Первая - процентное кодирование: пароль с @, :, /, ?, # или % ломает URI, если эти символы не экранировать (@ превращается в %40). Сгенерированные пароли любят именно такие символы. Если строка выглядит верно, а клиент сообщает странное имя хоста, причина в неэкранированном @. У ключевой формы такой проблемы нет, и это повод предпочитать её в shell-скриптах.

Вторая - у всех частей строки подключения есть эквиваленты в переменных окружения, и отсутствующая часть берётся из них: PGHOST, PGPORT, PGDATABASE, PGUSER, PGPASSWORD, PGSSLMODE, PGAPPNAME. psql без аргументов пытается подключиться к базе с именем вашего пользователя операционной системы через локальный сокет, поэтому голый psql на свежей машине падает с непонятным сообщением про сокет в /var/run/postgresql.

Хранение пароля в PGPASSWORD кладёт его в окружение процесса, где его может прочитать всё на машине. Лучшая привычка - файл паролей:

db.example.com:5432:shop:appuser:s3cretdb.example.com:5432:*:readonly:other-secret

У него должны быть права chmod 0600, иначе libpq молча его игнорирует. В Windows тот же файл лежит по адресу %APPDATA%\postgresql\pgpass.conf, и права там не проверяются. В первых четырёх полях допускаются подстановочные знаки. Для секретов приложений обычным ответом по-прежнему остаётся окружение - как не выпускать их из репозитория, см. в переменные окружения и секреты.

psql и команды, которые стоит знать#

psql - эталонный клиент, и именно его подразумевает каждое сообщение об ошибке. Для него не нужен установленный сервер PostgreSQL: в Debian и Ubuntu это postgresql-client, в Fedora postgresql, в macOS brew install libpq (и добавьте его каталог bin в PATH), а в Windows официальный установщик позволяет снять галочку с сервера и оставить инструменты командной строки.

Более новый psql прекрасно работает со старым сервером. Обратное годится для обычных запросов, но не для pg_dump, который отказывается делать дамп с сервера новее себя. Подбирайте клиент под самый новый сервер, с которым работаете.

$ psql "postgresql://appuser@db.example.com:5432/shop?sslmode=require"$ psql -h db.example.com -U appuser -d shop -c "select version();"$ psql -h db.example.com -U appuser -d shop -f schema.sql

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

\copy - то, что люди упускают. COPY orders TO '/tmp/orders.csv' выполняется на сервере, пишет на диск сервера и требует привилегированной роли. \copy orders TO 'orders.csv' CSV HEADER выполняет тот же запрос и пишет файл на вашей машине, что почти всегда и имелось в виду.

Режимы SSL: что именно защищает каждый#

sslmode - это спектр от «не беспокоиться» до «докажи, что ты тот сервер, который я просил», и середина у него слабее, чем кажется.

prefer - значение libpq по умолчанию, и оно опасно: соединение, которое должно быть зашифровано, тихо пойдёт открытым текстом, если что-то не так с TLS на сервере. require шифрует, но принимает любой сертификат вообще, в том числе предъявленный тем, кто окажется между вами и базой. Только verify-ca и verify-full затрудняют перехват, и только verify-full ловит сертификат, который действителен, но выдан для другой машины.

Для них нужен файл CA. PostgreSQL 16 и новее принимают sslrootcert=system, что означает хранилище доверенных сертификатов операционной системы, - правильный ответ, когда база предъявляет сертификат публичного CA. Иначе укажите в sslrootcert файл, который выдаёт ваш хост, - при самоподписанной схеме это собственный сертификат сервера.

postgresql://appuser@db.example.com:5432/shop?sslmode=verify-full&sslrootcert=system

У драйверов не на libpq свои значения по умолчанию, и они не одинаковы. Драйвер JDBC и несколько драйверов для Go и .NET не согласуют TLS, пока вы не попросите. Всегда задавайте режим явно, а не доверяйте значению по умолчанию, которое вы не читали.

Серверная сторона: listening, pg_hba.conf и файрвол#

Если вы администрируете сервер сами, впускает или не впускает два файла. postgresql.conf решает, где сервер слушает:

listen_addresses = '*'port = 5432password_encryption = scram-sha-256

pg_hba.conf решает, кого пустить после прихода. Он читается сверху вниз, и используется первая строка, у которой совпали тип соединения, база, пользователь и адрес источника, - даже если она вас отвергает, а следующая строка пустила бы.

# TYPE   DATABASE  USER      ADDRESS            METHODlocal    all       postgres                     peerhostssl  shop      appuser   203.0.113.10/32    scram-sha-256hostssl  shop      readonly  203.0.113.0/24     scram-sha-256host     all       all       0.0.0.0/0          reject

hostssl подходит только для соединений TLS, так вы делаете шифрование обязательным на стороне сервера, а не надеетесь, что клиент об этом попросил. host подходит для любых. hostnossl подходит только для нешифрованных и существует в основном ради их отклонения. Изменения этого файла требуют перезагрузки, а не перезапуска:

SELECT pg_reload_conf();

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

На RE:NODE линейка баз данных - это PostgreSQL и MongoDB, каждый тариф несёт одно выделение, и вы подключаетесь по этому хосту и порту с паролем суперпользователя, сгенерированным панелью для этого сервера, а не с паролем по умолчанию, общим для всех установок одного образа. Поскольку к базе обращается приложение через порт, а не браузер, у этих тарифов нет слота обратного прокси: перед 5432 нечего полезного ставить.

GUI-клиенты и туннели#

Каждому настольному клиенту нужны те же пять полей плюс место, где задаётся режим SSL. pgAdmin 4 - официальный и самый тяжёлый; DBeaver - обычный кроссплатформенный выбор; TablePlus и DataGrip - платные, к которым привыкают. Все они с готовностью подключатся с выключенным SSL, так что проверьте эту настройку после создания подключения, а не считайте её само собой разумеющейся.

Где клиент полезен, а терминал нет: просмотр незнакомой схемы, разбор широкого результата, правка значения вручную во время инцидента. Где он хуже: всё, что вы захотите повторить, потому что запрос, сохранённый в файле .sql и запущенный через psql -f, можно ревьюить, а клик - нет.

Если машина даёт shell-доступ, SSH-туннель полностью убирает порт базы из публичной сети:

$ ssh -N -L 5433:127.0.0.1:5432 you@vds.example.com$ psql -h 127.0.0.1 -p 5433 -U appuser -d shop

Тогда база слушает только на localhost, а единственный открытый порт - SSH. Это правильная схема на VDS, которым вы управляете, - см. SSH-ключи и укрепление защиты, - но для неё нужна учётная запись SSH на машине, которой в тарифе управляемой базы может и не быть. На хосте с панелью эквивалентная защита - ограничение адресов источников и verify-full.

Подключение из кода приложения#

Каждый драйвер - тонкая обёртка над теми же параметрами. Держите всю строку в одной переменной окружения, обычно DATABASE_URL, и позвольте драйверу её разобрать.

import { Pool } from "pg";const pool = new Pool({  connectionString: process.env.DATABASE_URL,  max: 10,  idleTimeoutMillis: 30_000,  connectionTimeoutMillis: 5_000,});const { rows } = await pool.query("select id, total from orders where id = $1", [id]);

import osimport psycopgwith psycopg.connect(os.environ["DATABASE_URL"], connect_timeout=5) as conn:    with conn.cursor() as cur:        cur.execute("select id, total from orders where id = %s", (order_id,))        row = cur.fetchone()

SQLAlchemy   postgresql+psycopg://appuser:s3cret@db.example.com:5432/shopDjango       DATABASES["default"] = dj_database_url.config()  # reads DATABASE_URLGo (pgx)     pgxpool.New(ctx, os.Getenv("DATABASE_URL"))JDBC         jdbc:postgresql://db.example.com:5432/shop?sslmode=verify-full

Два правила действуют на любом языке. Используйте плейсхолдеры ($1, %s, ?) и никогда не склеивайте строки: именно это делает SQL-инъекцию невозможной, а не маловероятной. И открывайте пул один раз при старте, а не соединение на каждый запрос: каждое подключение PostgreSQL - это фоновый процесс со своей памятью, и сотня таких навредит задолго до того, как поможет. Арифметика размеров пула - в пулы соединений и лимиты, и это та же арифметика, что определяет max_connections в настройка PostgreSQL для небольших серверов.

Ошибки, которые вы действительно увидите#

`connection refused` - на этом адресе и порту никто не слушает, либо файрвол сбросил пакет с reset. Проверьте listen_addresses, проверьте, что служба запущена, проверьте порт. Эта ошибка никогда не приходит от аутентификации.

`timeout expired` или зависание - пакеты уходят в пустоту. Файрвол, который отбрасывает, а не отвергает, неверное имя хоста или адрес, до которого у машины нет маршрута. Причина иная, чем у «refused», хотя ощущается так же.

`no pg_hba.conf entry for host "203.0.113.10", user "appuser", database "shop", no encryption` - вы дошли до сервера, и он прочитал свои правила. Полезная часть - завершающая фраза: no encryption означает, что вы подключились открытым текстом, а совпали только строки hostssl. Задайте sslmode=require и попробуйте снова, прежде чем что-либо править.

`password authentication failed for user "appuser"` - роль существует и пароль неверный, либо роли нет вовсе. PostgreSQL намеренно их не различает. Проверьте лишний пробел в конце и пароль, который был раскодирован из процентной записи не так, как вы ожидали.

`database "shop" does not exist` - вы подключились к серверу. Выполните psql -l, чтобы увидеть, что там есть; имя чувствительно к регистру, если было создано в кавычках.

`FATAL: sorry, too many clients already` - заполнен max_connections. Почти всегда это приложение, которое открывает соединения и не возвращает их, а не настоящая нагрузка.

`server closed the connection unexpectedly` - фоновый процесс умер, либо что-то посередине сдалось. Смотрите лог сервера на остановку из-за нехватки памяти, а на любом балансировщике или NAT-устройстве - на таймаут простоя, более короткий, чем у вашего пула.

`SSL error: certificate verify failed` - verify-ca или verify-full с файлом CA, который не подписывает сертификат сервера, либо имя хоста не совпадает. Сравните имя, к которому вы подключаетесь, с субъектом сертификата.

Две привычки сокращают всё это. Проверяйте через psql, прежде чем винить приложение: он использует ту же библиотеку и сообщает настоящую ошибку, тогда как ORM может обернуть её в три слоя. И меняйте что-то одно за раз, двигаясь от рабочего локального подключения наружу.

FAQ#

Какой порт использует PostgreSQL?

По умолчанию 5432/tcp. Это лишь соглашение, заданное настройкой port, а перенос порта - это лёгкая безвестность, а не безопасность: сканеры находят базу на любом порту за считаные минуты. По-настоящему помогает ограничение адресов источников.

Нужен ли мне PostgreSQL локально, чтобы пользоваться psql?

Нет. Установите только клиентский пакет: postgresql-client в Debian и Ubuntu, libpq из Homebrew в macOS или официальный установщик Windows со снятой галочкой серверного компонента. Более новый клиент без проблем подключается к более старому серверу.

Почему подключение работает с моего ноутбука, но не с сервера приложения?

Потому что pg_hba.conf сопоставляет по адресу источника. Адрес вашего ноутбука разрешён, а адрес сервера приложения - нет, либо сервер приложения выходит через другой публичный адрес, чем вы думаете. Смотрите точный адрес в сообщении об ошибке: сервер печатает тот, который увидел.

Достаточно ли sslmode=require?

Он шифрует трафик, что останавливает пассивный перехват, но принимает любой сертификат, поэтому не останавливает активного атакующего, способного перенаправить ваш трафик. Используйте verify-full с корневым сертификатом всякий раз, когда соединение проходит через сеть, которую вы не контролируете.

Сколько соединений должно открывать моё приложение?

Меньше, чем вы думаете. Пула из десяти-двадцати на процесс приложения хватает для большинства нагрузок, а общая сумма по всем процессам и фоновым worker-ам должна укладываться в max_connections с запасом для администратора. На сервере базы данных с 1-2 ГБ эта сумма должна быть в пределах нескольких десятков.

Можно ли подключиться с телефона или ноутбука по мобильной сети?

Можно, но адрес источника постоянно меняется, а значит, нужно либо широкое правило pg_hba.conf, либо туннель. Предпочитайте туннель или конечную точку приложения перед базой, а не открытие 5432 всему миру.