PostgreSQL 14: Run `psql` as `postgres`, different behaviour on Arch and Ubuntu

Question

I'm running PostgreSQL 14.6 with the same database (mydb) on two systems.

On Arch Linux, I'm able to use psql using both ways:

sudo -i -u postgres psql mydb
sudo -i -u postgres psql 'host=localhost user=postgres dbname=mydb'

Both commands give me a console mydb=#.

On Ubuntu 22.04 LTS, however, the first variant works:

sudo -i -u postgres psql mydb

But not the second one:

sudo -i -u postgres psql 'host=localhost user=postgres dbname=mydb'

Which asks me for the password:

Password for user postgres: 

Does anybody have a hint where this difference could be configured? I don't know where to start my investigation: is it an issue with the DB user or the OS user?

Answer 1

I figured it out. The problem is related to the default configuration in pg_hba.conf.

On Arch Linux (/var/lib/postgres/data/pg_hba.conf):

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

On Ubuntu (/etc/postgresql/14/main/pg_hba.conf):

# 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 
# 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 peer 
host replication all 127.0.0.1/32 scram-sha-256 
host replication all ::1/128 scram-sha-256 

The key is the option trust or peer/scram-sha-256, respectively, in the last column; the former skips authentication on the side of PostgreSQL.