From MD5 to scram-sha-256 in PostgreSQL

 

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 scram-sha-256?

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 scram-sha-256

There are two problems that make it hard to switch over from MD5 to scram-sha-256:

• 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 scram-sha-256.
• The PostgreSQL client has to support scram-sha-256 authentication, 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:

An old JDBC driver will tell you:

Old versions of Npgsql will come back with:

Step-by-step instructions for switching to scram-sha-256

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 password_encryption parameter

Edit postgresql.conf and change the parameter to

Make sure you remove the hash (#) at the beginning of the line. Then reload the server by running

where /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 md5.

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 pg_hba.conf

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 md5 in 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.

Conclusion

You can see from the above that it is not so difficult to change from md5 to 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.

If you want to know how to protect yourself from common security problems, read Kaarel's blog or my article about SECURITY DEFINER functions.

---

In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Twitter, Facebook, or LinkedIn.