Data rentention management

[anubister]

Data retention management is provided only in the Enterprise Edition : https://docs.mattermost.com/administration/data-retention.html I think it is however a important function for the privacy management and also to control the disk usage of Mattermost, therefore I would suggest to provide the following script to help Yunohost's administrators (it deletes all messages and medias older than a given number of days) :

#!/bin/bash

# configure vars
DB_USER="mattermost"
DB_NAME="mattermost"
DB_PASS=""
DB_HOST="localhost"
RETENTION="93" #number of days to *keep*; 93 ~ 3 months
DATA_PATH="/home/yunohost.app/mattermost/"

# calculate epoch in milisec
delete_before=$(date  --date="$RETENTION day ago"  "+%s%3N")
echo $(date  --date="$RETENTION day ago")
export PGPASSWORD=$DB_PASS

# get list of files to be removed
mysql -h "$DB_HOST" -u"$DB_USER" -p"$DB_PASS" "$DB_NAME" --disable-column-names -e "SELECT Path FROM FileInfo WHERE CreateAt < $delete_before;" > /tmp/mattermost-paths.list
mysql -h "$DB_HOST" -u"$DB_USER" -p"$DB_PASS" "$DB_NAME" --disable-column-names -e "SELECT ThumbnailPath FROM FileInfo WHERE CreateAt < $delete_before;" >> /tmp/mattermost-paths.list
mysql -h "$DB_HOST" -u"$DB_USER" -p"$DB_PASS" "$DB_NAME" --disable-column-names -e "SELECT PreviewPath FROM FileInfo WHERE CreateAt < $delete_before;" >> /tmp/mattermost-paths.list

# get list of posts to be removed
mysql -h "$DB_HOST" -u"$DB_USER" -p"$DB_PASS" "$DB_NAME" -e "SELECT * FROM Posts WHERE CreateAt < $delete_before;" 

# cleanup db 
mysql -h "$DB_HOST" -u"$DB_USER" -p"$DB_PASS" "$DB_NAME" -e "DELETE FROM Posts WHERE CreateAt < $delete_before;"
mysql -h "$DB_HOST" -u"$DB_USER" -p"$DB_PASS" "$DB_NAME" -e "DELETE FROM FileInfo WHERE CreateAt < $delete_before;"

# delete files
while read -r fp; do
        if [ -n "$fp" ]; then
                echo "$DATA_PATH""$fp"
                #shred -u "$DATA_PATH""$fp"
        mv "$DATA_PATH""$fp" /tmp/backup_mattermost/
        fi
done < /tmp/mattermost-paths.list

#TODO: delete empty folders

#cleanup after yourself
rm /tmp/mattermost-paths.list
exit 0

Based on https://github.com/aljazceru/mattermost-retention I personally stop the Mattermost service before running this script but I don't know if it is a strong requirement. I don't know what's the best way to integrate it.

[kemenaran]

This script looks pretty neat. I guess it could be integrated pretty easily, by putting it into a cron directory, and have it look at the yunohost settings to get the number of days of retention.

I don't really have the bandwidth to do improvements (other than bug fixes) right now (because life™), but I can definitely mentor someone willing to give it a shot :)

[freddewitt]

Do you think that it's could be possible to just deleted old attached files ? I would love to deleted 2 years old attached files then 4 yrs old messages at the same time (or choose to) Great work

[ericgaspar]

quick implantation https://github.com/YunoHost-Apps/mattermost_ynh/tree/retention I just changed mysql with psql

[anubister]

quick implantation https://github.com/YunoHost-Apps/mattermost_ynh/tree/retention I just changed mysql with psql

I had to use a slightly different syntax: psql -h "$DB_HOST" -U"$DB_USER" -d "$DB_NAME" -c "" to be confirmed with more tests... (yunohost 11 only for the moment), but this is in good way!

[anubister]

Do you think that it's could be possible to just deleted old attached files ? I would love to deleted 2 years old attached files then 4 yrs old messages at the same time (or choose to) Great work

Yes! I can make sense also from my point of view on a server with low resources to purge files and keep text messages which are much less consuming. You just have to comment this line https://github.com/YunoHost-Apps/mattermost_ynh/blob/retention/conf/retention.sh#L25 .

Probably we could split this script with 2 different parameters for posts and for files, add a cron task to automatise, and the job is done :)