After upgrading a PostgreSQL instance from version 9.6 to the currently latest version 15.3, your application cannot connect to the server, giving an authentication error even if the user/password used is still the same.
This seems to be the result of a change in the method used in the pg_hba.conf file.
Version 9.6 uses md5 but version 15.3 uses scram-sha-256
Changing this in the file pg_hba.conf file from scram-sha-256 to md5 and reloading the configuration file from inside the database with
select pg_reload_conf(); , results in the application being able to connect to the database server again.
Update: It is possible to use the recommended scram-sha-256 hash, by doing the following:
- Uncomment the line with
#password_encryption = scram-sha-256 # scram-sha-256 or md5in file
- reload the configuration in the database
- recreate the password with the new encryption
production=# alter user user_name with password 'password';