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. enter image description here

Any help?

• mustaccio: Perhaps you could show us, by copying and pasting properly formatted text, not images, how exactly you "created a new superuser called "test" (with password "test") [and] a database called "test" as well and granted all privileges"...
• Laurenz Albe: What is the result of SELECT rolpassword FROM pg_authid WHERE rolname = 'test';? The start of the result string will show us how your password is hashed. You can simply remove the localhost from the URL, and the connection should work again.
• aprospero: @LaurenzAlbe the start of the result is "SCRAM-SHA-256". So the command would be "postgresql://test:test@:5432/test"?
• Laurenz Albe: The command would be psql postgresql://test:test@:5432/test to get a socket connection. Did you reload PostgreSQL to make sure the config file is actually used? What exactly is the error message in the PostgreSQL log?
• aprospero: With that command I get "psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory. Is the server running locally and accepting connections on that socket?". Yes, I did "sudo service postgresql restart" many times. On the log at "/var/log/postgresql/postgresql-15-main.log" this error seems not to be logged.

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.

• aprospero: I changed the 4 lines beginning with "host" to md5 in the pg_hba.conf file, got a restart with "sudo service postgresql restart", but still not working. The error remains the same. Did I get wrong something?
• Adrian Klaver: 1) You sure you changed the correct file? 2) Does psql -d test -U test still work? 3) As a superuser do select * from pg_shadow ; to check whether the prefix for the passwords are md5 or SCRAM-SHA-256.
• aprospero: 1) Yes, I changed it at "/etc/postgresql/15/main/pg_hba.conf". 2) Yes, it still works 3) I see the prefix SCRAM-SHA-256 for the passwords. Not an expert, but maybe because the user test was created before changing the pg_hba.conf file?