Obsolete Releases > SME 8.x Contribs

Sogo Contacts and Spamassassin whitelist

(1/2) > >>

si_blakely:
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: ---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;

--- End code ---

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

Create /etc/cron.daily/sogo_whitelist and

--- Code: ---#!/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
--- End code ---
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: ---include sogo_whitelist
--- End code ---

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

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

_alex:
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

si_blakely:
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.

si_blakely:
I have updated /etc/cron.daily/sogo_whitelist to strip out contact entries with domains that the server hosts
--- Code: ---#!/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
--- End code ---

Hope this helps

Navigation

[0] Message Index

[#] Next page