hyperreal.coffee

Change password for user

1sudo -u user_name psql db_name

1ALTER USER user_name WITH PASSWORD 'new_password';

Update password auth method to SCRAM

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

1password_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.

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

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

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

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