Cannot connect on psql specifying connection string postgres

Question

On my Ubuntu machine, I have installed postgres 15. I created a new superuser called "test" (with password "test"). Then, I created a database called "test" as well and granted all privileges to the user test. Here the sequence of steps I made:

# Create a new user on Ubuntu
sudo adduser test

# Create the user test on postgres
sudo -u postgres psql postgres;
CREATE USER test SUPERUSER;
ALTER USER test WITH PASSWORD 'test';
exit;

# Login with test to create the DB and being the owner of the DB
sudo -u test psql postgres;
CREATE DATABASE test;
GRANT ALL PRIVILEGES ON DATABASE test TO test;
exit;

From psql, when I try the following I can successfully connect:

psql -d test -U test

However, If I try:

psql postgresql://test:test@localhost:5432/test

I get: psql: error: connection to server at "localhost" (127.0.0.1), port 5432 failed: FATAL: password authentication failed for user "test"

I browsed on the internet, but without any success. This is the relevant part of mu pg_hba.conf file.

Any help?

Answer 1

You have two different auth methods md5 for local(socket) and scram-sha-256 for host(ip). This psql -d test -U test is using socket and md5, the other is using host and scram-sha-256. Your passwords are set up as md5. The solution is change the scram-sha-256 to md5 and reload pg_hba.conf

Per docs Password auth:

To ease transition from the md5 method to the newer SCRAM method, if md5 is specified as a method in pg_hba.conf but the user's password on the server is encrypted for SCRAM (see below), then SCRAM-based authentication will automatically be chosen instead.

Then you can change your passwords to scram-sha-256 over time.