Auto-delete guest documents

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 documents  are even useless because each guest can delete the guest documents from another guest session. So nobody can guarantee that those will exist a longer time. Deleting such stuff helps to keep clean useful documents which were not created by guests but regular users who wanted to put them to public.

Document deletion

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

#!/bin/bash
#check for documents 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="
     SELECT
        D.doc_title_c,
        U.use_username_c,
        D.doc_createdate_d||'\n'
    FROM
        t_document AS D
    JOIN t_user AS U ON U.use_id_c = D.doc_iduser_c
    WHERE
        D.doc_deletedate_d IS NULL AND (
        D.doc_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
            D.doc_createdate_d + interval '10 minute' >= now()
        ) OR
        D.doc_iduser_c = 'guest') AND /*guest ist in keiner Gruppe, deshalb muss er gesondert aufgeführt werden*/
        D.doc_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 documents" webmaster@stadtfabrikanten.org
  
    OUT=$(psql -t -U$DB_USER $DB_NAME --no-align --command="
      SELECT
        D.doc_id_c
    FROM
        t_document AS D
    JOIN t_user AS U ON U.use_id_c = D.doc_iduser_c
    WHERE
        D.doc_deletedate_d IS NULL AND (
        D.doc_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
            D.doc_createdate_d + interval '10 minute' >= now()
        ) OR
        D.doc_iduser_c = 'guest') AND /*guest ist in keiner Gruppe, deshalb muss er gesondert aufgeführt werden*/
        D.doc_createdate_d + interval '10 minute' >= now()
    ;
   ")
 
    #echo $OUT
 
    BASE_URL="https://your.dms.de"
    BASE_URL="http://localhost:8080/dms"
    TEEDY_USER="pass"
    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
            curl --silent -X DELETE -H "Cookie: auth_token=$AUTH_TOKEN" "$BASE_URL/api/document/$VAR" -k
        done
    fi
else
    echo "Nothing to send and nothing to fix ..."
fi