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