1

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?

5
  • 1
    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"...
    – mustaccio
    Mar 13, 2023 at 23:30
  • 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. Mar 14, 2023 at 4:01
  • @LaurenzAlbe the start of the result is "SCRAM-SHA-256". So the command would be "postgresql://test:test@:5432/test"?
    – aprospero
    Mar 14, 2023 at 8:55
  • 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? Mar 14, 2023 at 16:59
  • 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.
    – aprospero
    Mar 14, 2023 at 18:26

1 Answer 1

2

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.

3
  • 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?
    – aprospero
    Mar 13, 2023 at 22:18
  • 1
    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.
    – Adrian Klaver
    Mar 13, 2023 at 22:40
  • 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?
    – aprospero
    Mar 14, 2023 at 7:53

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.