I'm using postgresql
and as part of learning, I tried to change to login methods to have a more secure login methods. e.g. using scram-sha-256
instead of md5
. I tried to change my password_encryption
to scram-sha256
in postgresql.conf
file, and changed pg_hba.conf
METHOD to scram-sha-256
as well, you can see the changes in the configuration below:
# - Authentication -
#authentication_timeout = 1min # 1s-600s
password_encryption = scram-sha-256 # md5 or scram-sha-256
#db_user_namespace = off
and
# TYPE DATABASE USER ADDRESS METHOD
# 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.
host replication all 127.0.0.1/32 scram-sha-256
host replication all ::1/128 scram-sha-256
Then after restarting the server, I try to login using cmd
when I get following error:
C:\Users\amir>psql -U postgres postgres
Password for user postgres:
psql: error: could not connect to server: FATAL: password authentication failed for user "postgres"
I can solve the problem by changing everything to md5
method and ignoring password_encryption
in postgresql.conf
file. How can I resolve this issue? do I have to change the configuration to default then try to create user and assigning an encrypted password for them.
Each user password hash is saved in the table pg_authid
. It includes the hashing algorithm that is used to transform the password to its hash.
When setting the password_encryption
in postgresql.conf, you are setting the default encryption, i.e. the one used when creating a user or when (re)setting your password. The table pg_authid
is not updated.
When changing pg_hba.conf, you are saying to accept only passwords hashed using the given method. The table pg_authid
is not updated.
There is an important note in the doc:
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
So the solution is to