How to set up scram-sha-256 authentication in PostgreSQL

md5: everyone uses it. But it's insecure — unsuitable for authentication or password storage. PostgreSQL 10 adds support for SCRAM SHA-256 authentication, which is far better¹… but how do you use it? Documentation is scarce.

Below I give the complete steps, for two different cases:

• The case where you migrate every user.
• The case where you migrate only a subset of users. Step 1 explains why you might need to do this. In this case, you can save time and complexity by creating a containing role, adding all the users you want to migrate to that role, and then configuring that role to work with scram-sha-256. That way you don't have to do it once for each user.

Enough preamble; let's begin!

1. First, decide which users to migrate to scram-sha-256. The ideal is to migrate everyone, all at once. In practice you may be unable to do that, for a few reasons:
• Older client tools don't support scram-sha-256. You need at least psql 10, or pgAdmin 4.
• Your app's client libraries may not support scram-sha-256. At a minimum, you will probably have to upgrade your client libraries².
• And it's not enough just to upgrade your libraries; you also have to change the password for each user who's migrating, because PostgreSQL needs to hash this password in scram-sha-256 format. There's no automatic rehash (there can't be, since PostgreSQL doesn't store the cleartext of the old password).
• Finally, besides your own code, you may have third-party apps connecting to your database, whose library versioning policy and password reset policy you don't control.
2. Having chosen the users to migrate, upgrade your relevant client libraries, psql/postgresql-client packages, and/or pgAdmin versions.
3. If you're migrating all users, skip to the next step. If, instead, you're migrating a subset of users, then create a new role, and put all those users in that role. This step saves a bit of repetitive config work.

$ sudo su - postgres
psql
postgres=# create role scram_sha_256_users nologin ROLE alice, bob, eve;

… where alice, bob, and eve are the users you're moving to scram-sha-256. We'll set their passwords in a minute.
4. Edit pg_hba.conf (make a backup copy first, and don't do a reload yet! We'll do that in a future step). If you're migrating all users, you just change 'md5' to 'scram-sha-256' wherever it appears in the file. In that case, also edit your postgresql.conf to set

password_encryption = 'scram-sha-256'

If, instead, you're migrating a subset of users, the pg_hba.conf edits depend on which users you're adding. As appropriate for your setup, alter or remove any lines that tell PostgreSQL to use md5 authentication for your affected users; then add equivalent lines for scram-sha-256 authentication.

For instance, if those users were formerly allowed to log in to all DBs with an md5 password, either locally or from any IPv4 host, then you would do the following. Note that this is only an example, and the particulars depend on your existing setup!

# Local (Unix domain socket) connections:
local all +scram_sha_256_users scram-sha-256
# IPv4 connections, from any IP address:
host all +scram_sha_256_users 0.0.0.0/0 scram-sha-256

5. Coordinate with stakeholders to schedule a downtime. During the downtime, do the following:
6. Stop your app, cron jobs, and any other code that will touch the database (or just be prepared for these to fail when they try to connect).
7. Do a pg_ctl reload to load the new pg_hba.conf. Note that this will break logins for all the users you're migrating!
8. Now it's time to change those passwords. Notably, you must pass the cleartext password to your <code>alter user</code>command, so that PostgreSQL itself can salt and (iteratively) hash it; unlike with md5, you can't do the hashing yourself. This is required by the SCRAM protocol.
9. So first alter the postgres user's ~/.psqlrc, to make sure it won't save cleartext of password-related commands in its history file. Add this line at the bottom:

\set HISTFILE /dev/null

10. Likewise, make sure the cleartext won't go to the log. Do psql -U postgres, and set these settings (just for this one session):

set log_min_duration_statement = -1;
set log_statement = 'none';

11. In the same psql session, change passwords for the relevant users. if your users are going to connect using psql, keep in mind that it won't work with passwords longer than 99 characters. Longer passwords won't crash psql, but it will cause logins to fail.

set password_encryption = 'scram-sha-256';
alter role scram_sha_256_users set password_encryption = 'scram-sha-256';
alter role bob with password 'bobsecret';
alter role alice with password 'alicesecret';
…

12. Securely communicate the new passwords to human users.
13. Update ~/.pgpass entries with the new passwords, for both human users and non-human runners of cron jobs.
14. Change other application password settings (config files, Vault, et cetera).
15. Test, test, test! Make sure your apps, human users, and cron jobs can all connect.
16. End the downtime.
17. Remove this line from your ~/.psqlrc:

\set HISTFILE /dev/null

With all that done, you are now on much more secure footing. Enjoy!

1. Because SCRAM passwords are stored in as a salted, iterated hash, even if they're leaked, they're harder to crack. Also, the SCRAM authentication protocol is immune to MITM attacks using certs from compromised CAs.
Support varies among libraries, though most wrap libpq, and hence should Just Work if compiled with libpq 10 (JDBC is a notable exception; it doesn't use libpq, so you need to get the right JDBC version).