Koozali.org: home of the SME Server

Sogo Contacts and Spamassassin whitelist

Offline si_blakely

  • *
  • 57
  • +0/-0
Sogo Contacts and Spamassassin whitelist
« 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/null
chmod 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

Offline _alex

  • ***
  • 103
  • +0/-0
Re: Sogo Contacts and Spamassassin whitelist
« Reply #1 on: April 24, 2012, 05:06:50 PM »
Sogo user speaking.
Thanks for your effort. I'll test this ASAP.

Offline _alex

  • ***
  • 103
  • +0/-0
Re: Sogo Contacts and Spamassassin whitelist
« Reply #2 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

Offline si_blakely

  • *
  • 57
  • +0/-0
Re: Sogo Contacts and Spamassassin whitelist
« Reply #3 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.

Offline si_blakely

  • *
  • 57
  • +0/-0
Re: Sogo Contacts and Spamassassin whitelist
« Reply #4 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

Offline si_blakely

  • *
  • 57
  • +0/-0
Re: Sogo Contacts and Spamassassin whitelist
« Reply #5 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