How to Upgrade Your PostgreSQL Passwords to SCRAM

In a lot of PostgreSQL environments, it’s common practice to protect user accounts with a password. Starting with PostgreSQL 10, the way PostgreSQL manages password-based authentication got a major upgrade with the introduction of SCRAM authentication, a well-defined standard that is a significant improvement over the current system in PostgreSQL. What’s better is that almost all PostgreSQL drivers now support this new method of password authentication, which should help drive further adoption of this method.

While it may be easy to take advantage of SCRAM authentication in new PostgreSQL deployments, there are a few steps involved in upgrading your existing systems to utilize this method. This article will briefly explain how SCRAM works in PostgreSQL (to try to encourage you to upgrade!) and then walk you through the steps of how to upgrade your existing PostgreSQL clusters to use SCRAM authentication.

A Very Brief Overview of SCRAM

Prior to PostgreSQL 10, password authentication was available using the md5 method (and well, there was also a plaintext for drivers that did not support md5), where PostgreSQL defined its own unique authentication scheme. Using this authentication method, passwords are stored like this in PostgreSQL:

'md5' || md5(password + username)

While this method works, it does present a few challenges:

If you have access to someone's username / password combination, or their PostgreSQL MD5-styled hash, you could log into any PostgreSQL cluster where the user has the same username / password.
Even though it is fairly simple for each PostgreSQL driver to implement support for this method, It does not follow any particular standard. Without going into details, know that the "shared secret" between the PostgreSQL client and server is effectively shared over the wire every time the PostgreSQL-style md5 authentication method is used.

The Salted Challenge Response Authentication Mechanism, aka SCRAM, is designed to allow two parties to both verify that they know a shared secret (e.g. a password) without ever sending the secret between each other. To make this less abstract, in PostgreSQL, SCRAM is used so that the PostgreSQL server can verify that the client knows the correct password, and likewise, the client can verify that the server knows the correct password too, all without ever sending the actual password, not even in a hashed format!

What’s better, SCRAM is a defined standard (RFC5802); PostgreSQL implements SCRAM-SHA-256 (RFC7677), with the notable difference in that it uses the SHA-256 hashing function instead of SHA-1.

Going into how SCRAM authentication works is a much longer topic (and one that I definitely like to talk about). The rest of this article explains how to upgrade your current systems to take advantage of SCRAM authentication.

Upgrading to SCRAM Authentication

Step 0: Determine if you can upgrade to SCRAM

There are two key criteria to determine if you can upgrade your password-based authentication systems to use SCRAM:

You are running PostgreSQL 10 or above
All of the drivers that are used to connect to your PostgreSQL cluster have SCRAM compatibility. The PostgreSQL community has conveniently provided a list of drivers as well as if they support SCRAM

If your system meets both of those criteria, you can begin the process of upgrading to SCRAM!

Step 1: Validate your pg_hba.conf settings

The PostgreSQL pg_hba.conf file determines how your clients can connect to PostgreSQL. If you’ve looked at your pg_hba.conf file, you may have also noticed a line similar to this one:

 TYPE	DATABASE		USER		ADDRESS		METHOD
local	all				all						md5

The above states that any user trying to connect to your PostgreSQL cluster through a local connection (e.g. a UNIX socket) must use the md5 authentication method. For the purposes of upgrading to SCRAM, ensure that your password-based authentication methods are set to md5 - we will change this setting later once all of the passwords are upgraded.

Step 2: Change PostgreSQL's `password_encryption` method

In your postgresql.conf configuration file there is a setting called password_encryption that determines how passwords should be hashed. At this point, it's likely set to md5. To begin the upgrade process, you need to switch this value to scram-sha-256 i.e.

password_encryption = scram-sha-256

When this is done, you will have to reload your PostgreSQL cluster (a restart is not required).

Step 3: Determine Who Needs to Upgrade

The next step is to determine which of your users need to upgrade their passwords. As you may not be able to set all of their passwords on your own, you may want to reach out to these users to have them upgrade their passwords. To determine who needs to upgrade their passwords to SCRAM, as a privileged user (e.g. a superuser), you can run this SQL:

SELECT
    rolname, rolpassword ~ '^SCRAM-SHA-256\$' AS has_upgraded
FROM pg_authid
WHERE rolcanlogin;

This query looks for users that have the LOGIN privilege (i.e. they can login to your PostgreSQL cluster) and determines if their password still exists in a PostgreSQL-style MD5 hash. If has_upgraded is FALSE, then the user needs to re-hash their password.

Note: there are some extreme edge cases where the above query will register a false positive (e.g. if you've had a plaintext password that starts with SCRAM-SHA-256$) but in all likelihood, the above should work.

Step 4: Upgrade the Password

There are two recommended ways to re-hash the password:

Upgrade Method #1: via `\password` in psql

Using the command-line interface from psql, you can use the \password command, i.e:

\password

Or if you want to set the password for someone else on your system:

\password username

You will be prompted to enter a new password. This new password will be converted to a SCRAM verifier, and the upgrade for this user will be complete.

Upgrade Method #2: Create your own SCRAM Verifier + ALTER ROLE

The second method is to create your own SCRAM verifier and then user ALTER ROLE username PASSWORD '$SCRAM_VERIFIER'. I've written a little Python script (using Python 3.7) that helps with this.

https://gist.github.com/jkatz/e0a1f52f66fa03b732945f6eb94d9c21

If you are able to successfully run the script, you should receive output that looks similar to:

SCRAM-SHA-256$4096:UrxBRgDElbaS4iwfRzn59g==$SErsniXa5gEr03cXhcFPLSM4C/22IKTJ9emThT+wPrM=:rSaLPYfC3eor3cq3f1Zq6Dw2Rl7HwIUHCMP7avpJQak=

This is an example of a SCRAM verifier that PostgreSQL stores and is used during SCRAM authentication. If I want to set this to be used as part of my new password:

ALTER ROLE jkatz
    PASSWORD 'SCRAM-SHA-256$4096:UrxBRgDElbaS4iwfRzn59g==$SErsniXa5gEr03cXhcFPLSM4C/22IKTJ9emThT+wPrM=:rSaLPYfC3eor3cq3f1Zq6Dw2Rl7HwIUHCMP7avpJQak=';

Why Not `ALTER ROLE username PASSWORD 'newpassword';`?

While the above command will re-hash your password, one side effect is that your plaintext password could end up being logged based on your cluster logging settings! As such, it effectively defeats one of the nice advantages of SCRAM, i.e. not having to expose the shared secret between two parties. So please don't use the above method.

Step 5: Update pg_hba.conf to use only "scram-sha-256"

Here's a fun fact: to ease the upgrade, your applications can still perform SCRAM verification even if md5 is chosen as the authentication method! If a user account has already had its password upgraded into a SCRAM verifier, it will use SCRAM authentication even if md5 is the authentication method!

However, once all the passwords are upgraded, you will want to update your pg_hba.conf file and switch all of your entries that use md5 to now use scram-sha-256. First, check that all of your users have been upgraded to use SCRAM, i.e.:

SELECT
    rolname, rolpassword ~ '^SCRAM-SHA-256\$' AS has_upgraded
FROM pg_authid
WHERE rolcanlogin;

returns TRUE for has_upgraded for all of your users.

Then, modify your md5 entires in your pg_hba.conf file to use scram-sha-256 e.g. using my example above:

 TYPE	DATABASE		USER		ADDRESS		METHOD
local	all				all						scram-sha-256