Many installations have a history of many major PostgreSQL releases.
With PostgreSQL 10 comes scram-sha-256 for hashing passwords, by installing from packages scram-sha-256 is the default setting for new installations since PostgreSQL 13.

With this small blog I will describe how to update password from md5 to scram-sha-256.
For the installation of PostgreSQL there are many blogs at dbi or articles from dbi.

Blog at dbi-services.com
Article at heise.de

So I will not repeat this steps.

The passwords in PostgreSQL are sotred in the table pg_authid and for this blog i have a user test.

$ postgres=# select rolpassword from pg_authid where rolname = 'test';
$              rolpassword
$ -------------------------------------
$  md56e4b266b2a0fbaa2c08d61bdefe7ee48
$ (1 row)
$ 
$ postgres=#

Visible is that the password is hashed in md5.

Mostly for ip ranges, sometimes also for specified users there is a corosponding entry within pg_hba.conf.

$ # TYPE  DATABASE        USER            ADDRESS                 METHOD
$ host    all             test            127.0.0.1/32            md5

At first step we need to switch the parameter password_encryption from md5 to scram-sha-256.

$ postgres=# alter system set password_encryption = 'scram-sha-256';
$ ALTER SYSTEM
$ postgres=#

And activate this change.

$ postgres=# select pg_reload_conf();
$  pg_reload_conf
$ ----------------
$  t
$ (1 row)
$ 
$ postgres=#

But keep the line within pg_hba till all affected passwords are updated.

Updating the passwords to change from md5 to scram-sha-256.

$ postgres=# alter role test with password 'password';
$ ALTER ROLE
$ postgres=#

Check the new hashing.

$ postgres=# select rolpassword from pg_authid where rolname = 'test';
$                                                               rolpassword
$ ---------------------------------------------------------------------------------------------------------------------------------------
$  SCRAM-SHA-256$4096:OSi8R7U5YM0ejUq982OX/g==$82TTXF0cnuq5puyN1mnpTFsSlkLFPDbP7+3TdxtX0B4=:QCRU75g5bDKONib5s9hwsjJsweeiswkyMBFUG0IF1Ts=
$ (1 row)
$ 
$ postgres=#

Now change the entry for this user within pg_hba.conf

$ # TYPE  DATABASE        USER            ADDRESS                 METHOD
$ host    all             test            127.0.0.1/32            scram-sha-256

Reload the configuration of PostgreSQL again.

$ postgres=# select pg_reload_conf();
$  pg_reload_conf
$ ----------------
$  t
$ (1 row)
$ 
$ postgres=#

Now the password encryption change is completed from md5 to scram-sha-256.

In many cases old systems where migrated to a complete new environment, new OS, latest PostgreSQL verion, by using pg_dump and pg_restore.
In this cases all users can be migrated to the new environment by using pg_dumpall -r > users.sql or pg_dumpall –roles-only > users.sql.
These files can be imported with psql -f users.sql, there will be a error messages that postgres user exits but this can be ignored.
All these imported users will still have the md5 hashed passwords, so it make sense to update these user passwords directly afterwards.


Thumbnail [60x60]
by
Karsten Lenz