commit b86a05ebfc3c5145191a17079a6601c4300dd749 Author: Lunar lunar@torproject.org Date: Fri Sep 26 15:10:45 2014 +0200

expire-old-tickets: add new script to remove old information from RT database --- expire-old-tickets/expire-old-tickets | 123 +++++++++++++++++++++++++++++++++ 1 file changed, 123 insertions(+)

diff --git a/expire-old-tickets/expire-old-tickets b/expire-old-tickets/expire-old-tickets new file mode 100755 index 0000000..3cc82e0 --- /dev/null +++ b/expire-old-tickets/expire-old-tickets @@ -0,0 +1,123 @@ +#!/bin/sh +# +# This program is free software. It comes without any warranty, to +# the extent permitted by applicable law. You can redistribute it +# and/or modify it under the terms of the Do What The Fuck You Want +# To Public License, Version 2, as published by Sam Hocevar. See +# http://sam.zoy.org/wtfpl/COPYING for more details. +# +# This script will use rt-shredder to remove old tickets and users from the RT +# database, and then encrypt the saved sqldump. + +set -e + +# GnuPG keys used to encrypt the sql dumps +KEYS="$(grep -v '^[^[:space:]]*#' <<END_OF_KEYS)" +# andrew +0291ECCBE42B22068E685545627DEE286B4D6475 +# roger +F65CE37F04BA5B360AE6EE17C218525819F78451 +# nick +B35BF85BF19489D04E28C33C21194EBB165733EA +# mikeperry +C963C21D63564E2B10BB335B29846B3C683686CC +# lunar +0603CCFD91865C17E88D4C798382C95C29023DF9 +END_OF_KEYS + +# How long must we keep old tickets +EXPIRE_AFTER="100" # days + +# Where to write the SQL dumps +DUMP_DIR="/srv/rtstuff/shredded" + +# Free space requirement in $DUMP_DIR +MIN_STORAGE_SPACE="50" # MB + +warn_about_upcoming_expirations() { + local key + local info + local main_uid + local expiration_date + local in_ten_days_epoch + local expiration_epoch + + for key in $KEYS; do + info="$(gpg --batch --quiet --list-keys --with-colons "$key")" + main_uid="$(echo "$info" | awk -F: '$1 ~ /^pub$/ { print $10 }')" + for expiration_date in $(echo "$info" | awk -F: '$2 ~ /^[^e]$/ { if (($1 == "pub") || ($1 == "sub" && $12 == "e")) { print $7 } }'); do + in_ten_days_epoch="$(date --date='+10 days' +%s)" + expiration_epoch="$(date --date="$expiration_date" +%s)" + if [ "$in_ten_days_epoch" -gt "$expiration_epoch" ]; then + echo "Warning! $main_uid expires on $expiration_date." + fi + done + done +} + +encrypt() { + local key + local recipients + + for key in $KEYS; do + recipients="${recipients:+$recipients }--recipient $key" + done + gpg --batch --always-trust $recipients --encrypt +} + +is_encryption_working() { + echo 'test' | encrypt > /dev/null + +} + +is_there_enough_free_space() { + local free_space + local min_space_in_kbytes + + free_space="$(df -P -k "$DUMP_DIR" | awk '/^// { print $4 }')" + min_space_in_kbytes="$(expr "$MIN_STORAGE_SPACE" '*' 1024 '*' 1024)" + test "$free_space" -le "$min_space_in_kbytes" +} + +fill_rt_config() { + local file="$1" + + cat /etc/request-tracker4/RT_SiteConfig.d/[0-9][0-9]* > "$file" + echo "1;" >> "$file" +} + +if ! [ -d "$DUMP_DIR" ]; then + echo "$DUMP_DIR does not exist. Exiting." >&2 + exit 1 +fi + +if ! is_there_enough_free_space; then + echo "$DUMP_DIR has less than $MIN_STORAGE_SPACE MB of available disk space. Exiting." >&2 + exit 1 +fi + +warn_about_upcoming_expirations + +if ! is_encryption_working; then + echo "Encryption is not working as it should. Exiting." >&2 + exit 1 +fi + +# Re-create RT_SiteConfig.pm from .d directory as we don't have the rights to +# read /etc/request-tracker4/RT_SiteConfig.pm +TMP_SITE_CONFIG=$(mktemp) +trap "rm -f '$TMP_SITE_CONFIG'" EXIT + +fill_rt_config "$TMP_SITE_CONFIG" + +DATE="$(date --date="-$EXPIRE_AFTER days" +%Y-%m-%d)" +TICKETS_SQL="$DUMP_DIR/tickets-shredded-$DATE.sql" +USERS_SQL="$DUMP_DIR/users-shredded-$DATE.sql" + +RT_SITE_CONFIG="$TMP_SITE_CONFIG" /usr/sbin/rt-shredder --force --sqldump "$TICKETS_SQL" --plugin "Tickets=query,(Status = 'resolved' OR Status = 'rejected' OR Status = 'deleted') AND LastUpdated < '$DATE';limit,999999999" +xz --stdout --compress "$TICKETS_SQL" | encrypt > "$TICKETS_SQL.xz.gpg" +shred -u -n 1 "$TICKETS_SQL" + +RT_SITE_CONFIG="$TMP_SITE_CONFIG" /usr/sbin/rt-shredder --force --sqldump "$USERS_SQL" --plugin "Users=status,any;member_of,Unprivileged;no_tickets,1;replace_relations,Nobody;limit,999999999" +xz --stdout --compress "$USERS_SQL" | encrypt > "$USERS_SQL.xz.gpg" +shred -u -n 1 "$USERS_SQL"