1

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?

1 Answer 1

2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.