Подключение к БД PostgreSQL с локальной и удалённой машины
Имеется БД PostgreSQL для хранения логинов к серверу CalDAV ( DAViCal ). К ней не удаётся подключиться.
И как подключиться к БД, если пользователь есть в PostgreSQL, но его нет в Ubuntu?
pg_hba.conf (некоторые закомментированные строки удалены):
Логи (эти строки повторяются в логах ):
Начать, пожалуй, стоит с того, что вы смотрите решительно не тот сервис. /bin/true однозначно не похоже на что-либо, делающее какую-то полезную работу.
Судя по starting PostgreSQL 12.4 (Ubuntu 12.4-0ubuntu0.20.04.1) — могу угадать что вам нужен systemctl status postgresql@12-main
Ну а дальше вопрос уходит полностью в сторону и заключается в двух вещах:
База у вас просто не запущена
Прежде чем подключаться к базе, её, конечно, надо запустить. Вы определённо изменили pg_hba.conf , но внесли некорректные с точки зрения базы настройки. О чём база и ругается в свой лог: end-of-line before authentication method , а ошибки чтения файла конфигурации расцениваем фатальными. Потому общий вердикт could not load pg_hba.conf и база отказывается стартовать пока не будут исправлены настройки.
Ваши изменения pg_hba некорректны
Вы добавили записи вида
Тогда как записи host требуют соответствующий формат
ADDRESS (как для TYPE local ) подключений не может быть пропущен. Более того, при указании IP адреса это должен быть строго CIDR формат с указанием маски сети. Указать просто 192.168.1.2 будет так же неверно, должно быть 192.168.1.2/32 для указания правила для одного хоста.
Порядок правил pg_hba имеет решающее значение
Как видно по логу, изначально вы получали FATAL’ы при подключении к базе. И вот почему:
Когда вы подключаетесь через unixsock — это тип подключения local . PostgreSQL просматривает правила по порядку и находит
База и пользователь указаны all — значит применяем этот тип аутентификации. Всё дальнейшие правила значения не имеют, они проверяться никогда не будут. Даже если там указано trust .
Аналогично записи но уже для tcp/ip подклююючений:
Последние две не имеют решительно никакого смысла, т.к. из-за расположенного выше более широкого правила они никогда проверяться не будут. ( 0.0.0.0/0 — это любой IP)
Если локальным пользователям желательно ходить по паролям, то резонно заменить local all all peer правило на два:
Тогда, в соответствии с порядком проверок, для административного пользователя postgres будет выполняться проверка peer , а для всех остальных — по паролю.
Никогда не используйте trust , а открывать базу на весь мир 0.0.0.0/0 — это всё равно плохая идея даже несмотря на использование аутентификации.
Настройка удаленного подключения к PostgreSQL на Windows Server 2019
По умолчанию PostgreSQL принимает подключения только с локального хоста, для того чтобы мы могли подключиться к СУБД удаленно — нужно внести изменения в конфигурационный файл и открыть порты в Windows.
Редактирование конфигурации PostgreSQL
Перейдите в директорию, в которой располагаются БД PostgreSQL. Для PostgreSQL 13 это следующие директории:
- x86: C:\Program Files (x86)\PostgreSQL\13\data
- x64: C:\Program Files\PostgreSQL\13\data
В директории data найдите файл pg_hba.conf и откройте его любым текстовым редактором. В конец файла добавьте следующую запись:
host postgres postgres all md5
| Вид подключения | База данных | Пользователь | IP-адрес удаленного подключения | Метод аутентификации |
|---|---|---|---|---|
| host | postgres | postgres | all | md5 |
Мы разрешили подключения к СУБД с любого IP под стандартным пользователем postgres к стандартной базе postgres. Конечно, так делать нельзя. Это небезопасно. Администратор должен создать пользователя для удаленного подключения с ограниченными правами.
Далее необходимо убедиться в наличии строки listen_addresses = '*' в файле postgresql.conf. Файл находится там же, в директории data. Откройте файл в текстовом редакторе и найдите раздел CONNECTIONS AND AUTHENTICATION.
В одной из первых строчек раздела будет указан параметр listen_addresses = '*'. Важно убедиться в том, что значение параметра = '*'. Если параметр отсутствует или имеет другое значение. Скопируйте правильное значение параметра из нашей инструкции и вставьте его в файл.
Редактирование конфигурационных файлов PostgreSQL закончено, теперь осталось настроить брандмауэр Windows.
Настройка брандмауэра Windows
Кликнете по иконке поиска, расположенной справа от кнопки Пуск и введите в строку запроса: защит… (в английской версии defend). В поисковой выдачи вы увидите: Монитор брандмауэра Защитника Windows.
Кликнув по монитору брандмауэра, откроется окно управление правилами подключения к портам. Выберите раздел Правила для входящих подключений и кликнете по кнопке Создать правило, расположенной в правом столбце Действия.
Откроется мастер создания правила для нового входящего подключения. Выберите тип правила — для порта. Нажмите кнопку Далее.
Теперь необходимо указать протокол и порт, для которого будет настроено правило входящего доступа. По умолчанию PostgreSQL «слушает» 5432 порт. Вот его и откроем.
Далее мы разрешаем внешнии подключения к порту 5432.
На следующем шаге мы все оставляем без изменений и нажимаем Далее.
На последнем шаге задаем интуитивно понятное имя подключения и описание. Сохраняем правило и проверяем, что оно принято. Мы создали правило под названием PostgreSQL ingoing. Оно разрешает входной трафик на порт 5432 с любого внешнего IP и порта.
Проверка удаленного подключения к PostgreSQL
Протестируем возможность подключения к PostgreSQL под Linux. Сначала проверим доступность 5432 порта. Сделать это удобнее всего утилитой telnet. Если telnet не установлен — скачать его можно следующей командой:
apt install telnet
Возможно, вам понадобятся root-права для установки утилиты. Получить их можно командой: sudo su. Конечно, вы должны знать пароль от root-пользователя.
Проверим доступность 5432 порта:
telnet 185.233.2.45 5432
Синтаксис команды следующий: telnet IP-адрес сервера Порт. В нашем случае IP-адрес сервера — 185.233.2.45, а порт — 5432.
Если порт доступен, telnet вернет следующую информацию:
Trying 185.233.2.45.
Connected to 185.233.2.45.
Escape character is '^]'.
Чтобы прервать подключение нажмите 2 раза Enter или сочетание клавиш Ctrl + Z. Теперь, когда мы убедились в доступности 5432 порта, подключимся к PostgreSQL с помощью специального PostgreSQL клиента — psql.
Скачаем psql из репозитория:
apt install psql
Теперь подключимся удаленно к PostgreSQL. Синтаксис команды следующий:
psql -U пользователь PostgreSQL -h IP сервера -d БД для подключения
В нашем случае команда выглядит так:
psql -U postgres -h 185.233.2.45 -d postgres
Далее необходимо ввести пароль пользователя, под которым осуществляется подключения. Пароль задавался при установки PostgreSQL.
Команда вернула следующую информацию:
psql (12.7 (Ubuntu 12.7-0ubuntu0.20.04.1), server 13.3)
WARNING: psql major version 12, server major version 13.
Some psql features might not work.
Type "help" for help.
Как подключиться к СУБД сервера с другого компьютера
Для удаленного подключения к БД PostgreSQL сервера используя PgAdmin (или любые другие программы, работающие с PostgreSQL ), необходимо указать в конфигурационном файле pg _ hba . conf , каким именно хостам разрешено подключаться к БД.
Для этого необходимо:
1. На удаленном сервере перейти в директорию установленного PostgreSQL . По-умолчанию, это \Program Files (x86)\PostgreSQL\8.4\data\
2. Открыть и отредактировать файл pg _ hba . conf
3. Изменить строку, в которой указаны подключения на:
4. Перезапустить службу postgresql-8.4
5. Для подключения к БД сервера через pgAdmin достаточно указать ip удаленного сервера и логин/пароль к БД (по-умолчанию postgres / postgres )