Home

Synapse wiki

For now, this is just a quick dumping ground of useful Synapse snippets which otherwise are going to get lost across scrollback, gists, scripts etc.

Useful SQL queries

In future these should be wrapped up as a proper admin API (with a matching CLI tool). For now, here's the raw SQL:

Deleting users:

Disclaimer: Not even verified yet, Reference ISSUE

DELETE FROM "users" WHERE name='@username:servername';
DELETE FROM "access_tokens" WHERE user_id='@username:servername';
DELETE FROM "user_ips" WHERE user_id='@username:servername';
DELETE FROM "profiles" WHERE user_id='username';
DELETE FROM "presence_stream" WHERE user_id='@username:servername';
DELETE FROM "devices" WHERE user_id='@username:servername';
DELETE FROM "device_lists_stream" WHERE user_id='@username:servername';

Deleting your copy of a room:

DISCLAIMER: Not verified by a main dev, just a user contributions (MAY EAT YOUR DATABASE):

For reference, this is how I got the list of tables to delete the room from:

SELECT table_name FROM information_schema.columns WHERE column_name = 'room_id';

DELETE FROM state_groups WHERE room_id='<room id here>';
DELETE FROM state_groups_state WHERE room_id='<room id here>';
DELETE FROM events WHERE room_id='<room id here>';
DELETE FROM event_json WHERE room_id='<room id here>';
DELETE FROM state_events WHERE room_id='<room id here>';
DELETE FROM current_state_events WHERE room_id='<room id here>';
DELETE FROM room_memberships WHERE room_id='<room id here>';
DELETE FROM feedback WHERE room_id='<room id here>';
DELETE FROM topics WHERE room_id='<room id here>';
DELETE FROM room_names WHERE room_id='<room id here>';
DELETE FROM rooms WHERE room_id='<room id here>';
DELETE FROM room_hosts WHERE room_id='<room id here>';
DELETE FROM event_forward_extremities WHERE room_id='<room id here>';
DELETE FROM event_backward_extremities WHERE room_id='<room id here>';
DELETE FROM event_edges WHERE room_id='<room id here>';
DELETE FROM room_depth WHERE room_id='<room id here>';
DELETE FROM state_forward_extremities WHERE room_id='<room id here>';
DELETE FROM event_auth WHERE room_id='<room id here>';
DELETE FROM room_aliases WHERE room_id='<room id here>';
DELETE FROM receipts_graph WHERE room_id='<room id here>';
DELETE FROM receipts_linearized WHERE room_id='<room id here>';
DELETE FROM event_search WHERE room_id='<room id here>';
DELETE FROM guest_access WHERE room_id='<room id here>';
DELETE FROM history_visibility WHERE room_id='<room id here>';
DELETE FROM room_tags WHERE room_id='<room id here>';
DELETE FROM room_tags_revisions WHERE room_id='<room id here>';
DELETE FROM room_account_data WHERE room_id='<room id here>';
DELETE FROM event_push_actions WHERE room_id='<room id here>';
DELETE FROM local_invites WHERE room_id='<room id here>';
DELETE FROM pusher_throttle WHERE room_id='<room id here>';
DELETE FROM event_reports WHERE room_id='<room id here>';
DELETE FROM public_room_list_stream WHERE room_id='<room id here>';
DELETE FROM stream_ordering_to_exterm WHERE room_id='<room id here>';
DELETE FROM appservice_room_list WHERE room_id='<room id here>';

What servers are my server talking to?

SELECT * FROM destinations;

What servers are currently participating in this room?

SELECT DISTINCT split_part(state_key, ':', 2)
    FROM current_state_events AS c
    INNER JOIN room_memberships AS m USING (room_id, event_id)
    WHERE room_id = '!cURbafjkfsMDVwdRDQ:matrix.org' AND membership = 'join';

Manually resetting passwords:

scripts/hash_password
UPDATE users SET password_hash='$2a$12$CC5r6fEe......' where name='@user:domain.com';