Отладка удаленного подключения PostgreSQL

Вопрос или проблема

У меня проблемы с подключением к моей базе данных PostgreSQL из онлайн-источника.

PostgreSQL, похоже, настроен правильно, он работает на своем стандартном порту 5432, а в файле postgresql.conf есть следующая строка

listen_addresses="*"

А в pg_hba.conf есть следующее

host    dbname    usname    all    md5

Я могу подключиться к базе данных PostgreSQL с другого компьютера, используя следующие учетные данные в pgadmin

Name              local
Host              192.xx.xx.xx
Port              5432
Maintenance DB    dbname
Username          usname
Password          psword

Используя их, я могу подключиться без проблем, все работает хорошо.

Затем я перенаправил порт 192.xx.xx.xx:5432 на ADSL-маршрутизаторе и проверил его, используя публичный IP-адрес 197.xx.xx.xx (видимый в интернете IP)
http://www.canyouseeme.org/
Где я увидел, что порт был перенаправлен правильно.

Я отключил SELinux и временно отключил брандмауэр.

Однако, когда я пытаюсь подключиться, используя

Name              online
Host              197.xx.xx.xx
Port              5432
Maintenance DB    dbname
Username          usname
Password          psword

Я получаю сообщение об ошибке, что сервер не слушает

не удалось подключиться к серверу: Соединение отклонено (0x0000274D/10061) Сервер запущен на хосте "197.xx.xx.xx" и принимает TCP/IP соединения на порту 5432?

Почему это происходит? Нужно ли мне добавить дополнительный шаг для того, чтобы он принимал соединения через перенаправление портов?

host    dbname    usname      197.xx.xx.xx/32  md5

host    dbname    usname      197.xx.xx.0/24  md5

Ответ или решение

Построение удаленного подключения к PostgreSQL: Диагностика и Решения

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

1. Настройки PostgreSQL

Ваши настройки postgresql.conf и pg_hba.conf выглядят почти правильно, но есть несколько нюансов.

a. Конфигурация listen_addresses

Вы правильно настроили listen_addresses на *, что позволяет серверу прослушивать записи на всех IP-адресах:

listen_addresses="*"

Это предполагает, что сервер должен принимать входящие соединения с любых сетевых интерфейсов.

b. Настройки pg_hba.conf

Настрока pg_hba.conf также требует внимания. Ваша строка:

host    dbname    usname    all    md5

неправильна в контексте CIDR. Параметр all в поле CIDR не допускается. Чтобы разрешить подключения, вы можете использовать подходящий формат. Например, если хотите разрешить доступ с конкретного IP 197.xx.xx.xx, ваша настройка должна выглядеть так:

host    dbname    usname      197.xx.xx.xx/32  md5

Если вы хотите разрешить доступ для целой подсети (например, 197.xx.xx.0/24), используйте:

host    dbname    usname      197.xx.xx.0/24  md5

2. Настройки Сети

При подключении из Интернета необходимо убедиться, что маршрутизатор корректно настроен для перенаправления порта. Вы указали, что вы перенаправили порт на вашем маршрутизаторе и проверили его с помощью сервиса:

http://www.canyouseeme.org/

Это подтверждение важно, но также необходимо убедиться, что ваш PostgreSQL действительно принимает соединение на этом порту снаружи.

a. Проверка соединений на сервере

Используйте команду netstat на сервере для проверки, слушает ли PostgreSQL подключения на порту 5432:

netstat -tuln | grep 5432

Эта команда должна показать, что PostgreSQL слушает на порту 5432 для всех IP-адресов.

3. Проблемы с брандмауэром

Хотя вы временно отключили брандмауэр, убедитесь, что никакие другие службы безопасности (например, iptables или другие системы контроля доступа) не блокируют соединения.

Для временной диагностики вы можете разрешить весь трафик на порт 5432 совокупностью команд (помните, что это должно использоваться только в целях диагностики):

sudo iptables -A INPUT -p tcp --dport 5432 -j ACCEPT

4. Проверка аутентификационных данных

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

5. Отладка и разрешение

Если после выполнения вышеуказанных шагов вы все еще сталкиваетесь с проблемами, вот некоторые дополнительные рекомендации:

• Перезагрузите PostgreSQL после внесения изменений в конфигурацию:

sudo systemctl restart postgresql

• Изучите файл журнала PostgreSQL (обычно находится в /var/log/postgresql/ или в том месте, где вы его настроили) на предмет ошибок подключения или других предупреждений.
• Проверяйте информацию о соединениях с помощью утилиты pgAdmin или psql на локальном уровне, чтобы убедиться, что настройки аутентификации работают корректно.

Если все вышеупомянутое правильно настроено, и вы все еще получаете сообщение об ошибке "Connection refused", возможно, присутствует проблема на уровне сети, и имеет смысл дополнительно проверить маршрутизацию и настройки вашего интернет-провайдера.

Заключение

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