A script to reduce incoming spam 4 sqlgrey users

[nicola.piazzi]

If you use sqlgrey consider to use this simple script to reduce incoming spam


# sqlgrey.tbs.sh
# SqlGrey Temporary Blacklist Sources
# -----------------------------------
#
# REQUIREMENTS :
# This script can be used only when SqlGrey is in use and it write in sqlgrey database on mysql
# Also database mailscanner is required
# Another requirement is that SqlGrey is configured to use C class ('greymethod = classc' in sqlgrey.conf)
#
# DESCRIPTION :
# SqlGrey initially reject connections and put a record in 'connect' table so the second time it accept
# combination is classc+email, when accept it put it in 'from_awl' table and then accept it forever
# When more records of that domain are found SqlGray transfer data in table 'domain_awl'
# This script scans 'maillog' table to search emails older than VMINUTES minutes that spamassassin classified as spam
# with a score defined in VMINSCORE.
# For each spam element script delete SqlGrey entries so that source must pass another time to greylisting
# After VMINUTES source is able to send other mails and if they results in bad classification it will be
# stopped for some minutes another time.
# This greatly reduce the number of bad classified email that arrives.
#
# NOTE :
# domain_awl deletion is commented to have more safety, it is very difficoult that some spam have a record here
# to make a good hit is recommended to put an high value in group_domain_level of /etc/sqlgrey/sqlgrey.conf
# for example group_domain_level = 15
#
#
# USAGE :
# 1) Put your MySql root password in ROOTPWD variable
# 2) Put the score of mail to intercept in VMINSCORE variable
# 3) Put the minutes to go back in search in VMINUTES variable
# 4) Put in root crontab (crontab -e) a line like this to run every minute (depend on where you put this script) :
# * * * * * /batch/sqlgrey.tbs.sh > /dev/null


# Start time
start=`date +%s`

# Parameters
ROOTPWD=soinykn0HveX8UeAC41kCCYx0Q9tDg
VMINSCORE=5
VMINUTES=15
VLOGFILE=/batch/sqlgrey.tbs.log

# Date & Time
NOW=$(date +"%m-%d-%Y")

# Main selection query, table mailscanner
S1="SELECT id \
FROM mailscanner.maillog \
WHERE timestamp > DATE_SUB(now(), INTERVAL $VMINUTES MINUTE) \
AND spamwhitelisted = 0 \
AND clientip NOT LIKE '10.%' \
AND clientip NOT LIKE '192.168.%' \
AND sascore > $VMINSCORE;"

res1=($(mysql -N -u root -p${ROOTPWD} -e "${S1}"))
cnt=${#res1[@]}
for (( i=0 ; i<${cnt} ; i++ ))
do
#echo "Found line " $i " " ${res1}
S2="SELECT id, clientip, from_address, from_domain \
FROM mailscanner.maillog \
WHERE id = '${res1}';"
res2=($(mysql -N -u root -p${ROOTPWD} -e "${S2}"))
wip=${res2[1]}
wemail=${res2[2]}
wdomain=${res2[3]}
wclassc=`echo $wip | cut -d"." -f1-3`
wname=${wemail%@*}
#wdomain=${wemail#*@}
echo $NOW " - CLASSC:" $wclassc " NAME:" $wname " DOMAIN:" $wdomain >> $VLOGFILE
# Delete element from sqlgrey.connect
D1="SELECT src, rcpt FROM sqlgrey.connect WHERE src = '$wclassc' AND rcpt = '$wemail';"
D1D="DELETE FROM sqlgrey.connect WHERE src = '$wclassc' AND rcpt = '$wemail';"
r1=($(mysql -N -u root -p${ROOTPWD} -e "${D1}"))
if [ ! -z ${r1[0]} ] ; then
echo " delete record . connect : " ${r1[0]} " " ${r1[1]} >> $VLOGFILE
echo $D1D | mysql -N -u root -p$ROOTPWD
fi
# Delete element from sqlgrey.from_awl
D2="SELECT src, sender_name, sender_domain FROM sqlgrey.from_awl WHERE src = '$wclassc' AND sender_name = '$wname' AND sender_domain = '$wdomain';"
D2D="DELETE FROM sqlgrey.from_awl WHERE src = '$wclassc' AND sender_name = '$wname' AND sender_domain = '$wdomain';"
r2=($(mysql -N -u root -p${ROOTPWD} -e "${D2}"))
if [ ! -z ${r2[0]} ] ; then
echo " delete record . from_awl : " ${r2[0]} " " ${r2[1]} " " ${r2[2]} >> $VLOGFILE
echo $D2D | mysql -N -u root -p$ROOTPWD
fi
##- # Delete element from sqlgrey.from_awl
##- D3="SELECT src, sender_domain FROM sqlgrey.domain_awl WHERE src = '$wclassc' AND sender_domain = '$wdomain';"
##- D3D="DELETE FROM sqlgrey.domain_awl WHERE src = '$wclassc' AND sender_domain = '$wdomain';"
##- r3=($(mysql -N -u root -p${ROOTPWD} -e "${D3}"))
##- if [ ! -z ${r3[0]} ] ; then
##- echo " delete record . domain_awl : " ${r3[0]} " " ${r3[1]} >> $VLOGFILE
##- echo $D3D | mysql -N -u root -p$ROOTPWD
##- fi
done

# Truncating logfile
tail -n 500 $VLOGFILE > $VLOGFILE.tmp
mv $VLOGFILE.tmp $VLOGFILE

end=`date +%s`
runtime=$((end-start))
##-echo "Time elapsed : " $runtime