techne/postgresql.org

PostgreSQL

• Change password for user
• Update password auth method to SCRAM

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:

sudo systemctl 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:

sudo systemctl restart postgresql.service
sudo systemctl restart mastodon-web.service mastodon-sidekiq.service mastodon-streaming.service
sudo systemctl restart matrix-synapse.service