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