Auto-delete guest tags

In case you have a guest login enabled and don't want to accept guest spamming you can prevent it using the following bash script with cron trigger (running every 10 minutes). Guest tags are even useless because each guest can delete the guest tags from another guest session. So nobody can guarantee that those will exist a longer time. Deleting such stuff helps to keep clean useful tags which were not created by guests but regular users who wanted to put them to public.

Tag deletion

This script is looking within i time fence of 10 minutes. If the script skipped in the meantime, it's possible that comments were overlooked. They have to be cleaned manually then.

#!/bin/bash
#check for tags which have been created the last 10 minutes. if result is not empty we send a new email
DB_USER="user"
DB_NAME="db"
OUT=$(psql -t -U$DB_USER $DB_NAME --no-align --command="
    /*werden Tags gelöscht, wenn der Benutzer gelöscht wird?*/
    SELECT
        T.tag_name_c,
        U.use_username_c,
        T.tag_createdate_d||'\n'
    FROM
        t_tag AS T
    JOIN t_user AS U ON U.use_id_c = T.tag_iduser_c
    WHERE
        T.tag_deletedate_d IS NULL AND (
        T.tag_iduser_c IN (
        SELECT
            use_id_c
        FROM t_user AS U
        JOIN t_user_group AS UG ON UG.ugp_iduser_c = U.use_id_c
        JOIN t_group AS G ON G.grp_id_c = UG.ugp_idgroup_c
        WHERE
            U.use_deletedate_d IS NULL AND
            UG.ugp_deletedate_d IS NULL AND
            G.grp_deletedate_d IS NULL AND
            G.grp_name_c NOT IN ('Editoren','Administratoren') AND
            T.tag_createdate_d + interval '10 minute' >= now()
        ) OR
        T.tag_iduser_c = 'guest') AND /*guest ist in keiner Gruppe, deshalb muss er gesondert aufgeführt werden*/
        T.tag_createdate_d + interval '10 minute' >= now()
    ;
 
")
  
if [[ ! -z $OUT ]]; then
    #echo -e -n _${OUT}_
    #first inform about the document via mail
    echo -e -n " "$OUT | mail -s "your.dms.de guest tags" post@fix.org
  
    OUT=$(psql -t -U$DB_USER $DB_NAME --no-align --command="
        SELECT
            T.tag_id_c
        FROM
            t_tag AS T
        JOIN t_user AS U ON U.use_id_c = T.tag_iduser_c
        WHERE
            T.tag_deletedate_d IS NULL AND (
            T.tag_iduser_c IN (
            SELECT
                use_id_c
            FROM t_user AS U
            JOIN t_user_group AS UG ON UG.ugp_iduser_c = U.use_id_c
            JOIN t_group AS G ON G.grp_id_c = UG.ugp_idgroup_c
            WHERE
                U.use_deletedate_d IS NULL AND
                UG.ugp_deletedate_d IS NULL AND
                G.grp_deletedate_d IS NULL AND
                G.grp_name_c NOT IN ('Editoren','Administratoren') AND
                T.tag_createdate_d + interval '10 minute' >= now()
            ) OR
            T.tag_iduser_c = 'guest') AND
            T.tag_createdate_d + interval '10 minute' >= now()
        ;
    ")
 
    #echo $OUT
 
    BASE_URL="https://your.dms.de"
    BASE_URL="http://localhost:8080/dms"
    TEEDY_USER="password"
    AUTH_TOKEN=$(psql -t -U$DB_USER $DB_NAME --command="SELECT aut_id_c FROM t_authentication_token AS A JOIN t_user AS U ON U.use_id_c = A.aut_iduser_c WHERE use_username_c = '$TEEDY_USER' AND aut_lastconnectiondate_d IS NOT NULL LIMIT 1;")
    if [ -z "$AUTH_TOKEN" ]
    then
        echo "NO AUTHTOKEN. Please create a session for the user first to automate things!" >&2 #print to stderr to trigger cron.d mail on error
        exit 1
    else
        for VAR in $OUT; do
            echo
            curl --silent -X DELETE -H "Cookie: auth_token=$AUTH_TOKEN" "$BASE_URL/api/tag/$VAR" -k
        done
    fi
else
    echo "Nothing to send and nothing to fix ..."
fi

cron.d script in /etc/cron.d/teedy-clean-comments

SHELL=/bin/sh
PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin
*/10 * * * * root /opt/teedy-clean-tags.sh > /dev/null