Почему не удается авторизоваться под юзером postgres?

Question

[shupike]

Доброго дня всем! Напомните, пожалуйста, как правильно инициализировать для работы только что установленный PostgreSQL (Ubuntu 24.04.2)?

sudo apt install postgresql postgresql-contrib
sudo -u postgres psql
\l - видны базы, созданные по умолчанию.
Проблема с паролем для пользователя postgres, задал пароль вручную через passwd, проверил - зашел пользователем postgres через ssh - пускает. Но при попытке обратиться к серверу выдается ошибка:
sudo psql -U postgres -d testdb
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: Peer authentication failed for user "postgres"

Отредактировал конфиги, выставил в pg_hba.conf:
# IPv4 local connections:
host all all 127.0.0.1/32 md5
host all postgres 192.168.20.0/24 md5

Также в postgresql.conf выставил так:
# - Connection Settings -

listen_addresses = '*' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost'; use '*' for all
# (change requires restart)
port = 5432 # (change requires restart)
max_connections = 100 # (change requires restart)

И все равно - что из консоли на этом же сервере не могу авторизоваться под юзером postgres, что не могу зацепиться из этой локалки через pgAdmin. Что я пропустил? Заранее спасибо.

Answer 1

[Melkij]

# DO NOT DISABLE!
# If you change this first entry you will need to make sure that the
# database superuser can access the database using some other method.
# Noninteractive access to all databases is required during automatic
# maintenance (custom daily cronjobs, replication, and similar tasks).
#
# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256

по умолчанию ubuntu создаёт примерно такого смысла pg_hba.conf

Обращу отдельное внимание, что первой строкой идёт peer авторизация именно для postgres'а и это будет плохая мысль её ломать.

sudo -u postgres psql
здесь вы зашли от unix пользователя postgres и запустили psql, имя unix пользователя совпадает с именем пользователя базы - это достаточное условие для прохождения peer аутентификации, вы получили доступ к базе. Это обычный вариант использования суперпользователя базы (dba впрочем чаще делают sudo -iu postgres, т.к. нужен не только psql)

sudo psql -U postgres -d testdb
Здесь вы хотите запустить psql от рута и авторизоваться пользователем postgres. "postgres" != "root", поэтому peer аутентификация не пускает авторизацию. То что root это root операционной системы не очень интересует postgresql, делает то что сказано в pg_hba (и pg_ident, но это тема отдельного обсуждения)
sudo здесь совершенно лишний и не выполняет никакого смысла.

Изменение
host all all 127.0.0.1/32 md5
эффекта не имеет, потому что host тип подключения для TCP/IP подключений, а по-умолчанию psql подключается по unix sock.
psql -h 127.0.0.1 форсирует подключение по TCP/IP

задал пароль вручную через passwd, проверил - зашел пользователем postgres через ssh - пускает

Пользователь ОС не имеет отношения к пользователю внутри СУБД, это разные сущности.
Пароль postgres пользователя базы можно изменить через psql командой \password

Тем не менее,
sudo -u postgres psql
достаточно для административного доступа. Не работайте от суперпользователя постоянно.

Answer 2

[Everything_is_bad]

в консоли еще хост localhost укажи, иначе он через сокет пытается

Answer 3

[shupike]

В итоге привел к такому виду:
# DO NOT DISABLE!
# If you change this first entry you will need to make sure that the
# database superuser can access the database using some other method.
# Noninteractive access to all databases is required during automatic
# maintenance (custom daily cronjobs, replication, and similar tasks).
#
# Database administrative login by Unix domain socket
local all postgres trust

# TYPE DATABASE USER ADDRESS METHOD

# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 md5
host all postgres 192.168.20.0/24 md5
# IPv6 local connections:
host all all ::1/128 scram-sha-256
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all trust
host replication all 127.0.0.1/32 scram-sha-256
host replication all ::1/128 scram-sha-256

И да, я забыл, что пользователь Ubuntu и пользователь базы - разные вещи. Сделал вот так:
sudo -u postgres psql
и там:
ALTER USER postgres PASSWORD 'postgres_password';
Заработало и в консоли, и из pgAdmin. Попутно проапргрейдил версию сервера с 16 до 17. Всем огромное спасибо.