Подключение к БД PostgreSQL с локальной и удалённой машины
Имеется БД PostgreSQL для хранения логинов к серверу CalDAV (DAViCal). К ней не удаётся подключиться.
root@server:/# systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
Active: active (exited) since Sat 2020-11-14 10:51:18 UTC; 19h ago
Process: 815 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
Main PID: 815 (code=exited, status=0/SUCCESS)
Nov 14 10:51:18 it-server-1 systemd[1]: Starting PostgreSQL RDBMS...
Nov 14 10:51:18 it-server-1 systemd[1]: Finished PostgreSQL RDBMS.
root@server:/# psql davical -c 'select username, password from usr;'
psql: error: could not connect to server: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?
И как подключиться к БД, если пользователь есть в PostgreSQL, но его нет в Ubuntu?
pg_hba.conf (некоторые закомментированные строки удалены):
# TYPE DATABASE USER ADDRESS METHOD
local all all peer
host all all 127.0.0.1/32 md5
host all all ::1/128 md5
local replication all peer
host replication all 127.0.0.1/32 md5
host replication all ::1/128 md5
local all postgres peer
host all all 0.0.0.0/0 md5
local davical davical_app trust
local davical davical_dba trust
host davical davical_app md5
host davical davical_dba md5
Логи (эти строки повторяются в логах ):
2020-11-14 06:10:00.685 UTC [30884] LOG: starting PostgreSQL 12.4 (Ubuntu 12.4-0ubuntu0.20.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-10ubuntu2) 9.3.0, 64-bit
2020-11-14 06:10:00.685 UTC [30884] LOG: listening on IPv4 address "127.0.0.1", port 5432
2020-11-14 06:10:00.688 UTC [30884] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2020-11-14 06:10:00.710 UTC [30885] LOG: database system was shut down at 2020-11-14 06:09:58 UTC
2020-11-14 06:10:00.718 UTC [30884] LOG: database system is ready to accept connections
2020-11-14 06:10:15.148 UTC [35875] davical_dba@davical LOG: provided user name (davical_dba) and authenticated user name (root) do not match
2020-11-14 06:10:15.148 UTC [35875] davical_dba@davical FATAL: Peer authentication failed for user "davical_dba"
2020-11-14 06:10:15.148 UTC [35875] davical_dba@davical DETAIL: Connection matched pg_hba.conf line 94: "local all all peer"
2020-11-14 06:15:57.528 UTC [30884] LOG: received fast shutdown request
2020-11-14 06:15:57.533 UTC [30884] LOG: aborting any active transactions
2020-11-14 06:15:57.544 UTC [30884] LOG: background worker "logical replication launcher" (PID 30891) exited with exit code 1
2020-11-14 06:15:57.544 UTC [30886] LOG: shutting down
2020-11-14 06:15:57.568 UTC [30884] LOG: database system is shut down
2020-11-14 06:16:29.536 UTC [700] LOG: starting PostgreSQL 12.4 (Ubuntu 12.4-0ubuntu0.20.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-10ubuntu2) 9.3.0, 64-bit
2020-11-14 06:16:29.537 UTC [700] LOG: listening on IPv4 address "127.0.0.1", port 5432
2020-11-14 06:16:29.540 UTC [700] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2020-11-14 06:16:29.651 UTC [715] LOG: database system was shut down at 2020-11-14 06:15:57 UTC
2020-11-14 06:16:29.674 UTC [700] LOG: database system is ready to accept connections
2020-11-14 06:38:26.928 UTC [700] LOG: received fast shutdown request
2020-11-14 06:38:26.942 UTC [700] LOG: aborting any active transactions
2020-11-14 06:38:26.951 UTC [700] LOG: background worker "logical replication launcher" (PID 721) exited with exit code 1
2020-11-14 06:38:26.952 UTC [716] LOG: shutting down
2020-11-14 06:38:26.978 UTC [700] LOG: database system is shut down
2020-11-14 06:38:55.269 UTC [760] LOG: starting PostgreSQL 12.4 (Ubuntu 12.4-0ubuntu0.20.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-10ubuntu2) 9.3.0, 64-bit
2020-11-14 06:38:55.271 UTC [760] LOG: listening on IPv4 address "127.0.0.1", port 5432
2020-11-14 06:38:55.274 UTC [760] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2020-11-14 06:38:55.400 UTC [785] LOG: database system was shut down at 2020-11-14 06:38:26 UTC
2020-11-14 06:38:55.448 UTC [760] LOG: database system is ready to accept connections
2020-11-14 07:01:10.417 UTC [1680] root@davical FATAL: role "root" does not exist
2020-11-14 07:01:46.528 UTC [1693] root@root FATAL: role "root" does not exist
2020-11-14 09:43:14.691 UTC [5722] client@davical FATAL: role "client" does not exist
2020-11-14 09:56:17.018 UTC [760] LOG: received fast shutdown request
2020-11-14 09:56:17.023 UTC [760] LOG: aborting any active transactions
2020-11-14 09:56:17.032 UTC [760] LOG: background worker "logical replication launcher" (PID 792) exited with exit code 1
2020-11-14 09:56:17.033 UTC [787] LOG: shutting down
2020-11-14 09:56:17.062 UTC [760] LOG: database system is shut down
2020-11-14 09:56:41.379 UTC [681] LOG: starting PostgreSQL 12.4 (Ubuntu 12.4-0ubuntu0.20.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-10ubuntu2) 9.3.0, 64-bit
2020-11-14 09:56:41.380 UTC [681] LOG: listening on IPv4 address "127.0.0.1", port 5432
2020-11-14 09:56:41.383 UTC [681] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2020-11-14 09:56:41.421 UTC [681] LOG: end-of-line before authentication method
2020-11-14 09:56:41.421 UTC [681] CONTEXT: line 4 of configuration file "/etc/postgresql/12/main/pg_hba.conf"
2020-11-14 09:56:41.421 UTC [681] LOG: end-of-line before authentication method
2020-11-14 09:56:41.421 UTC [681] CONTEXT: line 5 of configuration file "/etc/postgresql/12/main/pg_hba.conf"
2020-11-14 09:56:41.421 UTC [681] FATAL: could not load pg_hba.conf
2020-11-14 09:56:41.423 UTC [681] LOG: database system is shut down
pg_ctl: could not start server
2020-11-14 10:51:15.644 UTC [766] LOG: starting PostgreSQL 12.4 (Ubuntu 12.4-0ubuntu0.20.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-10ubuntu2) 9.3.0, 64-bit
2020-11-14 10:51:15.645 UTC [766] LOG: listening on IPv4 address "0.0.0.0", port 5432
2020-11-14 10:51:15.645 UTC [766] LOG: listening on IPv6 address "::", port 5432
2020-11-14 10:51:15.649 UTC [766] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2020-11-14 10:51:15.708 UTC [766] LOG: end-of-line before authentication method
2020-11-14 10:51:15.708 UTC [766] CONTEXT: line 6 of configuration file "/etc/postgresql/12/main/pg_hba.conf"
2020-11-14 10:51:15.708 UTC [766] LOG: end-of-line before authentication method
2020-11-14 10:51:15.708 UTC [766] CONTEXT: line 7 of configuration file "/etc/postgresql/12/main/pg_hba.conf"
2020-11-14 10:51:15.708 UTC [766] FATAL: could not load pg_hba.conf
2020-11-14 10:51:15.710 UTC [766] LOG: database system is shut down
pg_ctl: could not start server
Examine the log output.
Ответы (1 шт):
Process: 815 ExecStart=/bin/true
Начать, пожалуй, стоит с того, что вы смотрите решительно не тот сервис. /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 davical davical_app md5
Тогда как записи host требуют соответствующий формат
TYPE DATABASE USER ADDRESS METHOD
ADDRESS (как для TYPE local) подключений не может быть пропущен. Более того, при указании IP адреса это должен быть строго CIDR формат с указанием маски сети. Указать просто 192.168.1.2 будет так же неверно, должно быть 192.168.1.2/32 для указания правила для одного хоста.
Порядок правил pg_hba имеет решающее значение
Как видно по логу, изначально вы получали FATAL'ы при подключении к базе. И вот почему:
Когда вы подключаетесь через unixsock - это тип подключения local. PostgreSQL просматривает правила по порядку и находит
local all all peer
База и пользователь указаны all - значит применяем этот тип аутентификации. Всё дальнейшие правила значения не имеют, они проверяться никогда не будут. Даже если там указано trust.
Аналогично записи но уже для tcp/ip подклююючений:
host all all 0.0.0.0/0 md5
host davical davical_app какой-то-CIDR md5
host davical davical_dba какой-то-CIDR md5
Последние две не имеют решительно никакого смысла, т.к. из-за расположенного выше более широкого правила они никогда проверяться не будут. (0.0.0.0/0 - это любой IP)
Если локальным пользователям желательно ходить по паролям, то резонно заменить local all all peer правило на два:
local all postgres peer
local all all md5
Тогда, в соответствии с порядком проверок, для административного пользователя postgres будет выполняться проверка peer, а для всех остальных - по паролю.
Никогда не используйте trust, а открывать базу на весь мир 0.0.0.0/0 - это всё равно плохая идея даже несмотря на использование аутентификации.