anonfloppa

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);

anonfloppa

CREATE TEMP TABLE old_rooms AS
select distinct e_create.room_id
from events e_create 
where e_create.type='m.room.create' and 
e_create.origin_server_ts < [INSERT TIMESTAMP HERE]; # filter rooms created before timestamp, newer rooms not affected


CREATE TEMP TABLE old_rooms_new_events AS
select * 
from events e_types 

anonfloppa

###### PASSWORDS #######

# A shared secret (between Coturn and Synapse) used for authentication.
# You can put any string here, but generating a strong one is preferred (e.g. `pwgen -s 64 1`).
matrix_coturn_turn_static_auth_secret: '[REDACTED]'

# A secret used to protect access keys issued by the server.
# You can put any string here, but generating a strong one is preferred (e.g. `pwgen -s 64 1`).
matrix_synapse_macaroon_secret_key: '[REDACTED]'

anonfloppa

select 
       event_json.json::json->'content'->>'body' as filename,
       event_json.json::json->'content'->>'url' as url
from events 
left outer join event_json on 
    events.event_id=event_json.event_id
left outer join room_stats_state on 
    room_stats_state.room_id=events.room_id
where event_json.json::json->'content'->>'url'<>'' and events.room_id='ROOM ID' limit 1000;

anonfloppa

# function to prevent saving the user ip
CREATE OR REPLACE FUNCTION delete_ip()
RETURNS trigger AS
$BODY$
BEGIN
    NEW.ip = '127.0.0.1';
    RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;

anonfloppa

import datetime
import requests
import urllib
import sys
import time

server = 'https://matrix.SERVER.TLD'
token = 'PUT TOKEN HERE'

# devices older than 180 days for users who didn't login for 45 days will be deleted

anonfloppa

import datetime
import requests
import time
import urllib

token = 'PUT TOKEN HERE'

days_from_now_start = 270
days_from_now_end = 60 # will stop at minus 90 so we keep 3 months of history

anonfloppa

import requests
import urllib

token = '<TOKEN>'
server = 'https://matrix.<SERVER NAME>.<TLD>'
api_rooms = f'{server}/_synapse/admin/v1/rooms?dir=b&from=0&limit=20&order_by=joined_local_members&access_token={token}'
delete_api = f'{server}/_synapse/admin/v1/rooms/'

local_users = 0
while local_users == 0:

anonfloppa

import requests
import pprint

token = 'PUT YOUR TOKEN HERE'
room = '!ROOM_ID:SERVER.DOM'
server_api = 'PUT YOUR SERVER HERE' # example: matrix.anontier.nl

query_members = f'https://{server_api}/_synapse/admin/v1/rooms/{room}/members?dir=b&from=0&limit=99999&order_by=id&access_token={token}'
query_devices = f'https://{server_api}/_matrix/client/r0/keys/query?access_token={token}'

anonfloppa

select event_json.json::json->'content'->>'body' as message,
       events.room_id,
       room_stats_state.name,
       events.sender
from events 
left outer join event_json on 
    events.event_id=event_json.event_id
left outer join room_stats_state on 
    room_stats_state.room_id=events.room_id
where event_json.json::json->'content'->>'body'<>'' and events.sender='@ACCOUNT_NAME:SERVER.DOM';