PostgreSQL : cannot connect with a created role

Question

I am using PostgreSQL 11.3 on Fedora 30. With the postgres user, I can use psql or connect to the cluster in PgAdmin 4, but when I create another user, for example :

CREATE ROLE myuser LOGIN PASSWORD '...';

I always get an error like :

FATAL: password authentication failed for user "myuser"

I had a look at other similar questions here (including this one, but I don't have a case folding problem), and I still cannot make it work.

Here is my pg_hba.conf file :

# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all scram-sha-256
# 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 scram-sha-256
host replication all 127.0.0.1/32 scram-sha-256
host replication all ::1/128 scram-sha-256

What I am doing wrong? Thank you for any help

• eckes: Is the encoded password of that role maybe in md5 format? If you don’t reconfigure Postgres to use scram encoding then password changes won’t write the new format. You can check the prefix of the author table to verify or just change the hba config to „md5" it will accept both formats then.
• jjanes: When you fail authentication, the postgresql server assumes you are an intruder and so returns intentionally vague error messages to the client. Look in the server log file for more informative errors messages.

Answer 1

I found where the problem was! I had changed the line

password_encryption = scram-sha-256

in /var/lib/pgsql/data/postgresql.conf

but had forgotten to uncomment it...

• eckes: I think it’s logged on Server side in that case. It’s unfortunate that Postgres does not allow to specify the password algorithm in the alter statement.
• user1738984: I found that one thing that also works is to do set password_encryption = 'scram-sha-256'; before the alter statement