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.
- 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 libraries2.
- 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.
… where alice, bob, and eve are the users you're moving to scram-sha-256. We'll set their passwords in a minute.
$ sudo su - postgres
postgres=# create role scram_sha_256_users nologin ROLE alice, bob, eve;
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.
password_encryption = 'scram-sha-256'
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
\set HISTFILE /dev/null
psql -U postgres, and set these settings (just for this one session):
set log_min_duration_statement = -1;
set log_statement = 'none';
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';
With all that done, you are now on much more secure footing. Enjoy!
\set HISTFILE /dev/null
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).