20

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.

Improve this question

3 Answers 3

Reset to default
22

The best way to upgrade your postgres service with a new password encryption 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

An alternative is:

sudo service postgresql restart

Another alternative is:

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

14

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
Improve this answer

Comments

0

My problem was with remote connection.

postgres 14 came with scram-sha-256 enabled and I had to put it in pg_hba.conf

# Allow replication connections from localhost, by a user with the
# replication privilege.

host    all             all              0.0.0.0/0                       scram-sha-256
host    all             all              ::/0                            scram-sha-256

also, I had to remove the comment on password_encryption at postgresql.conf

# - Authentication -

#authentication_timeout = 1min          # 1s-600s
password_encryption = scram-sha-256     # scram-sha-256 or md5

Then, I followed Teocci's tip above to change password

\password

And after the whole day on that, it worked.

Improve this answer

Comments

Your Answer

Draft saved
Draft discarded

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.