PostgreSQL
2024-10-24
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