Koozali.org: home of the SME Server

Obsolete Releases => SME 8.x Contribs => Topic started by: si_blakely on April 24, 2012, 04:40:00 PM

Title: Sogo Contacts and Spamassassin whitelist
Post by: si_blakely on April 24, 2012, 04:40:00 PM

I am using Sogo on SME8b7. I wanted to create a Spamassassin whitelist of all the email addresses in the contacts list. With my old SME server I used OpenXchange, so this was a LDIF search. Sogo keeps the contacts in MySQL, so here is my solution. 

Create /var/lib/spamassassin/allemails.sql

Code: [Select]

connect sogo;
SELECT CONCAT(
   'SELECT * FROM (SELECT c_mail FROM ',
   GROUP_CONCAT(tb SEPARATOR ' UNION SELECT c_mail FROM '),
   ') AS emails ORDER BY c_mail'
)
INTO @mailquery FROM
(
SELECT TABLE_NAME tb 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE column_name LIKE 'c_mail' 
AND TABLE_SCHEMA='sogo'
) AS tbls;
PREPARE stmt FROM @mailquery;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

This dumps the contents of the c_mail columns from all the sogo*_quick tables.

Create /etc/cron.daily/sogo_whitelist and

Code: [Select]

#!/bin/bash
export HOME=/var/lib/spamassassin
cd $HOME
mysql --defaults-file=/root/.my.cnf -u root < /var/lib/spamassassin/allemails.sql > /tmp/sogo_emails.txt
sed "/c_mail/d" /tmp/sogo_emails.txt > /tmp/sogo_emails2.txt
rm -f /tmp/sogo_emails.txt
sed "/^$/d" /tmp/sogo_emails2.txt > /tmp/sogo_emails3.txt
rm -f /tmp/sogo_emails2.txt
sed "s/^/whitelist_from /" /tmp/sogo_emails3.txt > /etc/mail/spamassassin/sogo_whitelist
rm -f /tmp/sogo_emails3.txt
service spamassassin condrestart > /dev/nullchmod a+x /etc/cron.daily/sogo_whitelist

This tidies up the whitelist and adds the whitelist_from entry to each line, then reloads SA

Finally create the custom template
/etc/e-smith/templates/custom/etc/mail/spamassassin/local.cf/80sogo_whitelist

Code: [Select]

include sogo_whitelist
then
expand-template /etc/mail/spamassassin/local.cf
/etc/cron.daily/sogo_whitelist
service spamassassin restart

Title: Re: Sogo Contacts and Spamassassin whitelist
Post by: _alex on April 24, 2012, 05:06:50 PM

Sogo user speaking.
Thanks for your effort. I'll test this ASAP.

Title: Re: Sogo Contacts and Spamassassin whitelist
Post by: _alex on April 25, 2012, 12:00:52 PM

Hello,

I have tested it, and spam rate has actually increased. The problem is that a lot of spammers triy to come in the form:
mail from:<youruser@yourdomain.com>
rcpt to:<youruser@yourdomain.com>

One solution might be to exclude email adresses of your own domain(s) from the whitelist

Title: Re: Sogo Contacts and Spamassassin whitelist
Post by: si_blakely on April 25, 2012, 02:30:39 PM

I don't have my own internal addresses added as contacts, so I didn't see that problem (we use redirected gmail addresses for our personal external email, and google traps a lot of that sort of spam).
I did think that there was an SA rule to delete those sort of emails - I'll take a look.

Title: Re: Sogo Contacts and Spamassassin whitelist
Post by: si_blakely on April 26, 2012, 01:53:55 PM

I have updated /etc/cron.daily/sogo_whitelist to strip out contact entries with domains that the server hosts

Code: [Select]

#!/bin/bash
export HOME=/var/lib/spamassassin
cd $HOME

mysql --defaults-file=/root/.my.cnf -u root < /var/lib/spamassassin/allemails.sql > /tmp/sogo_emails.txt
sed "/c_mail/d" /tmp/sogo_emails.txt > /tmp/sogo_emails2.txt
rm -f /tmp/sogo_emails.txt
sed "/^$/d" /tmp/sogo_emails2.txt > /tmp/sogo_emails3.txt
rm -f /tmp/sogo_emails2.txt
# remove all local domain addresses to get avoid forged local FROM: addresses
domains=`db domains keys`
for i in $domains
do
     sed "/$i/d" /tmp/sogo_emails3.txt > /tmp/sogo_emails2.txt
     rm /tmp/sogo_emails3.txt
     mv /tmp/sogo_emails2.txt /tmp/sogo_emails3.txt
done
sed "s/^/whitelist_from /" /tmp/sogo_emails3.txt > /etc/mail/spamassassin/sogo_whitelist
rm -f /tmp/sogo_emails3.txt
service spamassassin condrestart > /dev/null
Hope this helps

Title: Re: Sogo Contacts and Spamassassin whitelist
Post by: si_blakely on April 28, 2012, 11:00:35 AM

One more version because db is not in the path

Code: [Select]

#!/bin/bash
export HOME=/var/lib/spamassassin
cd $HOME

mysql --defaults-file=/root/.my.cnf -u root < /var/lib/spamassassin/allemails.sql > /tmp/sogo_emails.txt
sed "/c_mail/d" /tmp/sogo_emails.txt > /tmp/sogo_emails2.txt
rm -f /tmp/sogo_emails.txt
sed "/^$/d" /tmp/sogo_emails2.txt > /tmp/sogo_emails3.txt
rm -f /tmp/sogo_emails2.txt
# remove all local domain addresses to get avoid forged local FROM: addresses
domains=`/sbin/e-smith/db domains keys`
for i in $domains
do
     sed "/$i/d" /tmp/sogo_emails3.txt > /tmp/sogo_emails2.txt
     rm /tmp/sogo_emails3.txt
     mv /tmp/sogo_emails2.txt /tmp/sogo_emails3.txt
done
sed "s/^/whitelist_from /" /tmp/sogo_emails3.txt > /etc/mail/spamassassin/sogo_whitelist
rm -f /tmp/sogo_emails3.txt
service spamassassin condrestart > /dev/null
Si