While getting a Matrix chat server up and running is a relatively easy and well documented task, managing and maintaining the server is somewhat nebulous as far as existing documentation is concerned. For example, when my wife asked me to reset her password on our server, I quickly realized that I had no idea how to do such a thing. After some digging around on the net, it became apparent that other people were having similar problems.
Regardless of which method used to install a Matrix server, a homeserver.db file is always present after installation.
This file represents all data associated with the server such as rooms, users, their passwords, and their attributes.
By default, Matrix uses sqlite3 to access this .db file.
However, Matrix’s own documentation recommends migrating the database to use postgres instead.
This guide will walk you through the process of installing postgres, getting it running, migrating the existing Matrix database, and how to change a user’s password. As always, this guide assumes the reader is running a Debian based linux operating system with access to root privileges via sudo. Additionally, this guide assumes that you already have a Matrix instance up and running on your machine. If you do not, you can get on easily using Matrix’s installation guide.
First things first, we need to install postgres.
sudo apt install postgresql postgresql-contrib
During the installation process, a postgres user will be created. Switch to the new postgres user while still maintaining sudo privileges using the following command.
sudo -u postgres bash
Once switched, create a new postgresql user.
createuser –pwprompt synapse_user
Enter the postgres shell and create the new database.
psql
postgres=# createdb –encoding=UTF8 –locale=C –template=template0 --owner=synapse_user synapse;
Next, allow the new postgres user synapse_user connect to the synapse databases. While still in postgres shell, type the following:
postgres=# show hba_file;
Something like this should follow: /etc/postgresql/11/main/pg_hba.conf
Leave postgres shell using \q and open pg_hba.conf in a text editor.
Add the following line:
host synapse synapse_user ::1/128 md5
host synapse synapse_user 127.0.0.1/32 md5
Restart the postgresql server for the changes to take effect.
sudo systemctl restart postgresql
Now we need to configure the homeserver.yaml file to use postgresql instead of sqlite3.
But before making changes it’s important to keep backups of the original .yaml and .db files just in case anything goes wrong.
Additionally important, it’s important to make sure that the .db and .yaml file belongs to the proper user.
In this case the user should be matrix-synapse.
sudo chown matrix-synapse:nogroup /etc/matrix-synapse/homeserver.yaml
sudo chown matrix-synapse:nogroup /var/lib/matrix-synapse/homeserver.db
sudo cp /etc/matrix-synapse/homeserver.yaml /etc/matrix-synapse/homeserver.yaml.backup
sudo cp /var/lib/matrix-synapse/homeserver.db /var/lib/matrix-synapse/homeserver.db.backup
Open homeserver.yaml in a text editor and change the following text.
#database:
# name: sqlite3
# args:
# database: /path/to/homeserver.db
database:
name: psycopg2
txn_limit: 10000
args:
user: synapse_user
password: $your_password
database: synapse
host: localhost
port: 5432
cp_min: 5
cp_max: 10
Once saved, stop the Matrix server.
sudo systemctl stop matrix-synapse
Next, port the existing database using the built in command synapse_port_db.
For whatever reason I had an issue using relative file paths with this command, so I’ve included the full file path instead.
synapse_port_db --curses --sqlite-database /var/lib/matrix-synapse/homeserver.db \
--postgres-config /etc/matrix-synapse/homeserver.yaml
Restart your Matrix server and verify that it is running correctly.
sudo systemctl start matrix-synapse
Now that your Matrix server is using a postgres database, we need to be able to access and change data items as we please. For example, let’s say one of your users wants their password reset.
Open up the synapse database
psql synapse
If you’d like to see the usernames and password hashes of all users in your database, run the following command
synapse=# select name, password_hash from users;
name | password_hash
------------------+--------------------------------------------------------------
@user1:domain.com | $2b$12$lk.TLplwaOThuenkqzj9k.6RxqtK9eaReS2faBVFofWwEdc4P.wbS
@user2:domain.com | $2b$12$xEAMCZZ9r8ShtEgkiCitUOf603W2IUIKh7oj8Ni3A6InOUY1h8iHC
(2 rows)
You may have noticed that the passwords are stored as a hashfile.
This means that the new password you create for your user needs to be hashed before updating the database.
Hashes are created using the command hash_password using a technique called bcrypt.
By default, the number bcrypt_rounds in the homeserver.yaml file is 12, but yours might be configured different.
In any case, it’s a good idea to reference the server’s homeserver.yaml file using the -c flag.
hash_password -p $new_password -c /etc/matrix-synapse/homeserver.yaml
The result should be a hash of the specified password.
$2b$12$y/jlqx9Cc5TPTr3O9itSMuyYnFb2L6XsGseIYD5W.yP0ypJYXtLhy
Copy the hash and go back into the postgres shell
psql synapse
Update the desired user’s password
synapse=# update users
set password_hash = ‘$2b$12$y/jlqx9Cc5TPTr3O9itSMuyYnFb2L6XsGseIYD5W.yP0ypJYXtLhy’
where name = ‘@user1:domain.com’;
If done so correctly, you should see
UPDATE 1
The user should now be able to log in with their new password. Once logged in, they should be able to change it through the client.