© Laurenz Albe 2021
Since v10, PostgreSQL has provided support for
scram-sha-256 for password hashing and authentication. This article describes how you can adapt your application safely.
Why do we need
PostgreSQL uses cryptographic hashing for two purposes:
- The actual database password is a hash of the clear text password entered by the user. This prevents a thief from using a stolen password on other systems.
- During password authentication, the client has to hash the (hashed) password with a random salt provided by the server. The password check is successful if the server receives the correct hashed response from the client.
Now, the MD5 hashing method has weaknesses that make it unsuitable for cryptography. In particular, it is too easy to construct a string with a given MD5 hash. These shortcomings do not apply to the way PostgreSQL uses MD5, but it still makes sense to use a better hashing algorithm:
- an expensive hash function makes brute force password attacks more difficult
- during a security audit, it looks better if PostgreSQL doesn’t use a hash function with weaknesses
Hence the introduction of
scram-sha-256 support in v10. If you can, start using the new hashing method. The increased difficulty of brute force password attacks makes it worth the effort.
Problems with switching over to
There are two problems that make it hard to switch over from MD5 to
- Since PostgreSQL does not know the original clear text password, the user has to set the password again, after you change the password encryption method to
- The PostgreSQL client has to support
scram-sha-256authentication, so authentication with older client software will fail.
The error message you get with an old version of
libpq when you attempt to connect to a server that requires
scram-sha-256 authentication is:
authentication method 10 not supported
An old JDBC driver will tell you:
The authentication type 10 is not supported.
Old versions of Npgsql will come back with:
Authentication method not supported (Received: 10)
Step-by-step instructions for switching to
It is actually not difficult to convert to
scram-sha-256, if you follow these guidelines:
1. Upgrade the client software
Upgrade all PostgreSQL client software and drivers that are too old to support the new authentication method. This is a good idea anyway, as it is never smart to get stuck with old, unmaintained software.
2. Change the
postgresql.conf and change the parameter to
password_encryption = scram-sha-256
Make sure you remove the hash (
#) at the beginning of the line. Then reload the server by running
pg_ctl reload -D /postgres/datadir
/postgres/datadir is the PostgreSQL data directory. Alternatively, you can run this SQL statement:
Look into the log file to see if the reload was successful, and check the new value via SQL:
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
3. Set all passwords again
All password authenticated users have to change their password. In
psql, a superuser can change any user’s password with
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.
4. Change the authentication method in
This step is not strictly necessary, because PostgreSQL will use
scram-sha-256 authentication for
scram-sha-256-hashed passwords, even if the authentication method is set to
pg_hba.conf. This is a compatibility feature.
Still, you should adapt
pg_hba.conf by replacing all occurrences of “
md5” with “
scram-sha-256”. That will prevent users who still have an old MD5 password from authenticating.
After that, reload the configuration as above. Then check the log file or examine the view
pg_hba_file_rules to see if the reload was successful.
You can see from the above that it is not so difficult to change from
scram-sha-256. The hard parts are that you have to set all the passwords again, and that you may have to upgrade the client software.