1#!/usr/bin/env bash 2 3# this script will use the api: 4# https://matrix-org.github.io/synapse/latest/admin_api/purge_history_api.html 5# 6# It will purge all messages in a list of rooms up to a cetrain event 7 8################################################################################################### 9# define your domain and admin user 10################################################################################################### 11# add this user as admin in your home server: 12DOMAIN=yourserver.tld 13# add this user as admin in your home server: 14ADMIN="@you_admin_username:$DOMAIN" 15 16API_URL="$DOMAIN:8008/_matrix/client/r0" 17 18################################################################################################### 19#choose the rooms to prune old messages from (add a free comment at the end) 20################################################################################################### 21# the room_id's you can get e.g. from your Riot clients "View Source" button on each message 22ROOMS_ARRAY=( 23'!DgvjtOljKujDBrxyHk:matrix.org#riot:matrix.org' 24'!QtykxKocfZaZOUrTwp:matrix.org#Matrix HQ' 25) 26 27# ALTERNATIVELY: 28# you can select all the rooms that are not encrypted and loop over the result: 29# SELECT room_id FROM rooms WHERE room_id NOT IN (SELECT DISTINCT room_id FROM events WHERE type ='m.room.encrypted') 30# or 31# select all rooms with at least 100 members: 32# SELECT q.room_id FROM (select count(*) as numberofusers, room_id FROM current_state_events WHERE type ='m.room.member' 33# GROUP BY room_id) AS q LEFT JOIN room_aliases a ON q.room_id=a.room_id WHERE q.numberofusers > 100 ORDER BY numberofusers desc 34 35################################################################################################### 36# evaluate the EVENT_ID before which should be pruned 37################################################################################################### 38# choose a time before which the messages should be pruned: 39TIME='12 months ago' 40# ALTERNATIVELY: 41# a certain time: 42# TIME='2016-08-31 23:59:59' 43 44# creates a timestamp from the given time string: 45UNIX_TIMESTAMP=$(date +%s%3N --date='TZ="UTC+2" '"$TIME") 46 47# ALTERNATIVELY: 48# prune all messages that are older than 1000 messages ago: 49# LAST_MESSAGES=1000 50# SQL_GET_EVENT="SELECT event_id from events WHERE type='m.room.message' AND room_id ='$ROOM' ORDER BY received_ts DESC LIMIT 1 offset $(($LAST_MESSAGES - 1))" 51 52# ALTERNATIVELY: 53# select the EVENT_ID manually: 54#EVENT_ID='$1471814088343495zpPNI:matrix.org' # an example event from 21st of Aug 2016 by Matthew 55 56################################################################################################### 57# make the admin user a server admin in the database with 58################################################################################################### 59# psql -A -t --dbname=synapse -c "UPDATE users SET admin=1 WHERE name LIKE '$ADMIN'" 60 61################################################################################################### 62# database function 63################################################################################################### 64sql (){ 65 # for sqlite3: 66 #sqlite3 homeserver.db "pragma busy_timeout=20000;$1" | awk '{print $2}' 67 # for postgres: 68 psql -A -t --dbname=synapse -c "$1" | grep -v 'Pager' 69} 70 71################################################################################################### 72# get an access token 73################################################################################################### 74# for example externally by watching Riot in your browser's network inspector 75# or internally on the server locally, use this: 76TOKEN=$(sql "SELECT token FROM access_tokens WHERE user_id='$ADMIN' ORDER BY id DESC LIMIT 1") 77AUTH="Authorization: Bearer $TOKEN" 78 79################################################################################################### 80# check, if your TOKEN works. For example this works: 81################################################################################################### 82# $ curl --header "$AUTH" "$API_URL/rooms/$ROOM/state/m.room.power_levels" 83 84################################################################################################### 85# finally start pruning the room: 86################################################################################################### 87# this will really delete local events, so the messages in the room really 88# disappear unless they are restored by remote federation. This is because 89# we pass {"delete_local_events":true} to the curl invocation below. 90 91for ROOM in "${ROOMS_ARRAY[@]}"; do 92 echo "########################################### $(date) ################# " 93 echo "pruning room: $ROOM ..." 94 ROOM=${ROOM%#*} 95 #set -x 96 echo "check for alias in db..." 97 # for postgres: 98 sql "SELECT * FROM room_aliases WHERE room_id='$ROOM'" 99 echo "get event..." 100 # for postgres: 101 EVENT_ID=$(sql "SELECT event_id FROM events WHERE type='m.room.message' AND received_ts<'$UNIX_TIMESTAMP' AND room_id='$ROOM' ORDER BY received_ts DESC LIMIT 1;") 102 if [ "$EVENT_ID" == "" ]; then 103 echo "no event $TIME" 104 else 105 echo "event: $EVENT_ID" 106 SLEEP=2 107 set -x 108 # call purge 109 OUT=$(curl --header "$AUTH" -s -d '{"delete_local_events":true}' POST "$API_URL/admin/purge_history/$ROOM/$EVENT_ID") 110 PURGE_ID=$(echo "$OUT" |grep purge_id|cut -d'"' -f4 ) 111 if [ "$PURGE_ID" == "" ]; then 112 # probably the history purge is already in progress for $ROOM 113 : "continuing with next room" 114 else 115 while : ; do 116 # get status of purge and sleep longer each time if still active 117 sleep $SLEEP 118 STATUS=$(curl --header "$AUTH" -s GET "$API_URL/admin/purge_history_status/$PURGE_ID" |grep status|cut -d'"' -f4) 119 : "$ROOM --> Status: $STATUS" 120 [[ "$STATUS" == "active" ]] || break 121 SLEEP=$((SLEEP + 1)) 122 done 123 fi 124 set +x 125 sleep 1 126 fi 127done 128 129 130################################################################################################### 131# additionally 132################################################################################################### 133# to benefit from pruning large amounts of data, you need to call VACUUM to free the unused space. 134# This can take a very long time (hours) and the client have to be stopped while you do so: 135# $ synctl stop 136# $ sqlite3 -line homeserver.db "vacuum;" 137# $ synctl start 138 139# This could be set, so you don't need to prune every time after deleting some rows: 140# $ sqlite3 homeserver.db "PRAGMA auto_vacuum = FULL;" 141# be cautious, it could make the database somewhat slow if there are a lot of deletions 142 143exit 144