hyperreal.coffee

Change password for user

sudo -u user_name psql db_name

ALTER USER user_name WITH PASSWORD 'new_password';

Update password auth method to SCRAM

Edit /etc/postgresql/16/main/postgresql.conf.

password_encryption = scram-sha-256

Restart postgresql.service.

At this point, any services using the old MD5 auth method will fail to connect to their PostgreSQL databases.

Update the settings in /etc/postgresql/16/main/pg_hba.conf.

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

Enter a psql shell and determine who needs to upgrade their auth method.

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

\password username

Restart postgresql.service and all services using a PostgreSQL database.