postgresqlauthentication

I tried to change postgresql md5 to scram-sha-256 and I get FATAL password authentication failed


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.


Solution

  • 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

    1. start with the existing users, md5
    2. update postrgres.conf to use scram and reload the configuration
    3. reset the user password: it will now be saved as scram in pg_authid
    4. you can still use md5 in pg_hba.conf
    5. when happy with the move from md5 to scram, update pg_hba.conf to specify scram instead of md5