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

Question

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.

Answer 1

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

Answer 2

The best way to upgrade your postgres service is:

1. Connect to your server using 'ssh' or open your terminal.

2. Change the password_encryption parameter

Edit postgresql.conf and change password_encryption to

password_encryption = scram-sha-256

Note: Make sure you remove the hash # at the beginning of the line. Then reload the server by running:

sudo systemctl restart postgresql.service

Another alternative is:

sudo service postgresql restart

Another one:

pg_ctl reload -D <data-dir-path>

where <data-dir-path> is the PostgreSQL data directory.

Also, you can run this SQL statement using psql:

SELECT pg_reload_conf();

2.A. Now switch into the postgres user and run psql

sudo -u postgres psql

2.B. Then, look into the log file to see if the reload was successful, and check the new value via SQL:

SHOW password_encryption;

Note that even though you changed the parameter, the old MD5 passwords still work, as long as the authentication method in pg_hba.conf is set to md5.

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 also not updated.

3. Set passwords again

All password authenticated users have to change their password. In psql, a superuser can change any user’s password with

\password <user>

Even if the user sets the same password as before, the password will now be hashed with SHA-256. Before proceeding with the next step, examine the table pg_authid and make sure that it contains no more MD5 hashed passwords.

SELECT
    rolname, rolpassword ~ '^SCRAM-SHA-256\$' AS has_upgraded
FROM pg_authid
WHERE rolcanlogin;

This query looks for users that have the LOGIN privilege (i.e. they can login to your PostgreSQL cluster) and determines if their password still exists in a PostgreSQL-style MD5 hash. If has_upgraded is FALSE, then the user needs to re-hash their password.