room_creation_spam_attack_mitigation.md

here is how I mitigated the spam for now and the future:

I created a table to count the amount of m.room.create events:

CREATE TABLE room_creation_spam (
    user_id text,
    hits int
);
ALTER TABLE ONLY room_creation_spam
    ADD CONSTRAINT room_creation_spam_pk PRIMARY KEY (user_id);

I created a trigger on the events table like so:

CREATE OR REPLACE FUNCTION update_room_creation_spam()
RETURNS trigger AS
$BODY$
BEGIN
    INSERT INTO room_creation_spam(user_id, hits) 
    VALUES(NEW.sender, 1)
    ON CONFLICT (user_id) DO UPDATE SET hits=room_creation_spam.hits+1 WHERE room_creation_spam.user_id=NEW.sender;
    RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER trigger_room_creation_spam
BEFORE INSERT ON events
FOR EACH ROW 
WHEN (NEW.sender like '%:anontier.nl' and NEW.type='m.room.create')
EXECUTE PROCEDURE update_room_creation_spam();

next I created 3 scripts:

reset_room_creation_spam.sh to delete the content of the table every hour with a cronjob

#!/bin/bash

/usr/bin/docker exec --env-file=/matrix/postgres/env-postgres-psql matrix-postgres psql -d synapse -c "delete from room_creation_spam;"

delete_room_creation_spam.sh check the room_creation_spam every 5 minutes with a cronjob and delete the accounts that hit a certain threshold

#!/bin/bash

rm /matrix/postgres/data/room_spam_dump.txt
/usr/bin/docker exec --env-file=/matrix/postgres/env-postgres-psql matrix-postgres psql -d synapse -c "select user_id from room_creation_spam where hits > 120;" -o /var/lib/postgresql/data/room_spam_dump.txt

/usr/bin/python3 delete_room_creation_spam.py

delete_room_creation_spam.py python companion script

with open("/matrix/postgres/data/room_spam_dump.txt", "r") as f:
    data = f.readlines()

delete_api = 'https://matrix.anontier.nl/_synapse/admin/v1/deactivate/'

import requests
import urllib

token = '<ADMIN_TOKEN>'

body = {
    'erase': True
}

for userid in data[2:]:
    userid = urllib.parse.quote(userid.strip())
    if userid == '':
        continue
    print('delete', delete_api + userid)
    r = requests.post(delete_api + userid + '?access_token=' + token, headers={}, json={})
    r = requests.post(delete_api + userid + '?access_token=' + token, headers={}, json=body)
    if r.status_code != 200:
        print(r.text)
print("Done.")

crontab entries:

*/5 * * * * /bin/bash /root/delete_room_creation_spam.sh
10 * * * * /bin/bash /root/reset_room_creation_spam.sh