PostgreSQL authentication: Migrate from MD5 to SCRAM-SHA-256

For quite a long time PostgreSQL supports authentication via SCRAM-SHA-256 now, so migrating is long overdue. This small guide will show you how to do this.

The support for SCRAM-SHA-256 was introduced in version 10 of the PostgreSQL database and it is the default for freshly installed systems since version 13.

Since it is not only used for internal storage of password hashes on the database side but also for clients which have to hash their connection password in the correct way there likely was some impact when databases were upgraded. However in some cases clients were not ready and so sometimes the DBMS was configured to stick with md5 and most likely forgotten. ;-)

1. Check clients and change configuration

As long as your client software does not tell you something like “authentication type/method not supported” when trying to connect via SCRAM-SHA-256 you should be ready to migrate. If not try to update your client software (library, driver).

In case of a fresh PostgreSQL installation you shouldn’t need to configure anything.

If you have a configuration file (postgresql.conf) from an older version then you should look for the option password_encryption and configure it accordingly:

password_encryption = scram-sha-256

Now restart the service and check the settings via SHOW password_encryption; on the SQL console (e.g. via psql).

For cleaner configuration you should check the pg_hba.conf file and replace any “md5” with “scram-sha-256” but that isn’t strictly necessary.

2. All passwords MUST be set again!

Because already existing passwords are saved as MD5 hashes they have to be set again! As a superuser you can set them via password USERNAME on the SQL console.