Update the Prosody Blocklist for the mod_firewall plugin.

update-blocklist.sh

#!/bin/bash

DATABASE="/var/lib/prosody/prosody.sqlite"
OLDMD5=`md5sum /etc/prosody/blocklist.pfw |awk '{ print $1 }'`

if [ ! -f /usr/bin/jq ]; then
    apt update
    apt -y install jq
fi

echo "# This is was auto built by the https://gist.github.com/mattrude/f31c5d88e03bebd9695e9a16108cbfea script" > /tmp/blocklist.pfw
echo "" >> /tmp/blocklist.pfw

if [ -f ${DATABASE} ]; then
    for DOMAIN in `echo 'select host from prosody;' |/usr/bin/sqlite3 ${DATABASE} |sort |uniq`
    do
        for USER in `echo 'select user from prosody WHERE host = "'${DOMAIN}'";' |/usr/bin/sqlite3 ${DATABASE} |sort |uniq`
        do
            echo "## Starting the per jid section for user ${USER}@${DOMAIN}" >> /tmp/blocklist.pfw
            echo "" >> /tmp/blocklist.pfw
            echo 'select value from prosody where host = "'${DOMAIN}'" AND user = "'${USER}'" AND store = "privacy" AND key = "lists" AND type = "json";' \
            |/usr/bin/sqlite3 ${DATABASE} |/usr/bin/jq .blocklist[] \
            |grep value |grep "@" |sed 's/    "value"/FROM/g' |sed 's/"//g' \
            |sed 's/,//g' |awk ' {print;} NR % 1 == 0 { print "TO: '${USER}@${DOMAIN}'"; }' \
            |awk ' {print;} NR % 2 == 0 { print "DROP."; }' \
            |awk ' {print;} NR % 3 == 0 { print ""; }' >> /tmp/blocklist.pfw

            echo "## Starting the domain section for user ${USER}@${DOMAIN}" >> /tmp/blocklist.pfw
            echo "" >> /tmp/blocklist.pfw
            echo 'select value from prosody where host = "'${DOMAIN}'" AND user = "'${USER}'" AND store = "privacy" AND key = "lists" AND type = "json";' \
            |/usr/bin/sqlite3 ${DATABASE} |/usr/bin/jq .blocklist[] \
            |grep value |grep -v "@" |sed 's/    "value": /FROM: <*>@/g' |sed 's/"//g' \
            |sed 's/,//g' |awk ' {print;} NR % 1 == 0 { print "TO: '${USER}@${DOMAIN}'"; }' \
            |awk ' {print;} NR % 2 == 0 { print "DROP."; }' \
            |awk ' {print;} NR % 3 == 0 { print ""; }' >> /tmp/blocklist.pfw

        done
    done
fi

echo "# Complete." >> /tmp/blocklist.pfw

NEWMD5=`md5sum /tmp/blocklist.pfw |awk '{ print $1 }'`

if [ $OLDMD5 == $NEWMD5 ]; then
    rm -f /tmp/blocklist.pfw
else
    mv /tmp/blocklist.pfw /etc/prosody/blocklist.pfw
    /usr/bin/prosodyctl reload > /dev/null
fi