Spamassassin userprefs & SQL

Info: http://spamassassin.apache.org/dist/sql/README - http://wiki.apache.org/spamassassin/UsingSQL

SpamAssassin can now load users' score files from an SQL database.  The concept here is to have a web application (PHP/perl/ASP/etc.) that will allow users to be able to update their local preferences on how SpamAssassin will filter their e-mail. The most common use for a system like this would be for users to be able to update the white/black list of addresses without the need for them to update their $HOME/.spamassassin/user_prefs file.

You can skip this page if you want to manage only global options via /etc/mail/spamassassin.

Be aware that user rules will be easily managed by means of the "sauprefs" plugin of Rouncube webmail.

Prerequisites

  • Perl module Mail::SpamAssassin::BayesStore::MySQL
  • spamd must be executed with --sql-config  --nouser-config (-q -x)
  • simscan must be compiled with --enable-spamc-user=y  to pass to spamc the sender address as the variable USERNAME

Install from CPAN

> perl -MCPAN -e shell

o conf prerequisites_policy ask
install Mail::SpamAssassin::BayesStore::MySQL
install DBI
quit

Run spamd with -x (--nouser-config, disable user config files) and -q (--sql-config enable SQL config only useful with -x). You may want to have this inside your spamdctl script:

/usr/bin/spamd -A 127.0.0.1,your-firewall-IP -x -q -u spamd -s stderr 2>&1

Setting up the database

> mysql -u root -p

mysql> CREATE DATABASE spamassassin;
mysql> CREATE USER 'spamassassin'@'your-IP' IDENTIFIED BY '***';
mysql> GRANT USAGE ON * . * TO 'spamassassin'@'your-IP' IDENTIFIED BY '***' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
mysql> USE spamassassin;
mysql> CREATE TABLE userpref (
  username varchar(100) NOT NULL default '',
  preference varchar(30) NOT NULL default '',
  value varchar(100) NOT NULL default '',
  prefid int(11) NOT NULL auto_increment,
  PRIMARY KEY  (prefid),
  KEY username (username)
) ENGINE=MyISAM;
mysql> GRANT ALL PRIVILEGES ON `spamassassin` . * TO 'spamassassin'@'your-IP';

NB: use TYPE=MyISAM; instead of ENGINE=MyISAM; if your MySQL version is older than 5.5 (thanks to Pato).

Create the config file with the mysql login and the sql query:

> cd /etc/mail/spamassassin
> cat > sql.cf << __EOF__
user_scores_dsn                  DBI:mysql:spamassassin:mysql-IP:3306
user_scores_sql_password         password
user_scores_sql_username         spamassassin
user_scores_sql_custom_query     SELECT preference, value FROM _TABLE_ WHERE username = _USERNAME_ OR username = '$GLOBAL' OR username = CONCAT('%',_DOMAIN_) ORDER BY username ASC
__EOF__

Note that every file.cf in the /etc/mail/spamassassin folder will be loaded.

Testing

Insert some global, domain and user rules into the DB:

> mysql -h [MySQL-IP] -u spamassassin -p

mysql> USE spamassassin
mysql> INSERT INTO userpref (username,preference,value) VALUES ('$GLOBAL','required_hits','5.0');
mysql> INSERT INTO userpref (username,preference,value) VALUES ('%testdomain.net','required_hits','4.5');
mysql> INSERT INTO userpref (username,preference,value) VALUES ('user@tesdomain2.com','required_hits','4.0');
mysql> INSERT INTO userpref (username,preference,value) VALUES ('myself@mydomain.net','blacklist_from','sender@senderdomain.net');
mysql> exit;

$GLOBAL test

Try to send a messages to test the $GLOBAL rule. You have to look if the required_hits is 5.0:

echo -e "From: myself@mymailserver.net\nTo:myfriend@domain.net\nSubject: test sauserprefs\n\n" | spamc -u '$GLOBAL'

this produces

Received: from localhost by qmail.mymailserver.net
        with SpamAssassin (version 3.3.1);
        Tue, 30 Nov 2010 23:18:37 +0100
From: myself@mymailserver.net
To: myfriend@domain.net
Subject: test sauserprefs
X-Spam-Checker-Version: SpamAssassin 3.3.1 (2010-04-18) on
        qmail.mymailserver.net
X-Spam-Flag: YES
X-Spam-Level: *****
X-Spam-Status: Yes, score=5.4 required=5.0 tests=BAYES_99,FREEMAIL_FROM,
        MISSING_DATE,MISSING_MID,NO_RECEIVED,NO_RELAYS,TVD_SPACE_RATIO,
        T_TO_NO_BRKTS_FREEMAIL autolearn=no version=3.3.1

DOMAIN test

Try to send a messages to test the testdomain.net rule. You have to look if the required_hits is 4.5:

echo -e "From: myself@mymailserver.net\nTo:user@testdomain.net\nSubject: test sauserprefs\n\n" | spamc -u '%testdomain.net'

Received: from localhost by qmail.mymailserver.net
        with SpamAssassin (version 3.3.1);
        Tue, 30 Nov 2010 23:19:23 +0100
From: myself@mymailserver.net
To: user@testdomain.net
Subject: test sauserprefs
X-Spam-Checker-Version: SpamAssassin 3.3.1 (2010-04-18) on
        qmail.mymailserver.net
X-Spam-Flag: YES
X-Spam-Level: *****
X-Spam-Status: Yes, score=5.4 required=4.5 tests=BAYES_99,FREEMAIL_FROM,
        MISSING_DATE,MISSING_MID,NO_RECEIVED,NO_RELAYS,TVD_SPACE_RATIO,
        T_TO_NO_BRKTS_FREEMAIL autolearn=no version=3.3.1

USER test

Try to send a messages to test the user@tesdomain2.com rule. You have to look if the required_hits is 4.0:

echo -e "From: myself@mymailserver.net\nTo:user@tesdomain2.com\nSubject: test sauserprefs\n\n" | spamc -u 'user@tesdomain2.com'

Received: from myself@mymailserver.net by qmail.mymailserver.net
        with SpamAssassin (version 3.3.1);
        Tue, 30 Nov 2010 23:20:25 +0100
From: myself@mymailserver.net
To: user@tesdomain2.com
Subject: test sauserprefs
X-Spam-Checker-Version: SpamAssassin 3.3.1 (2010-04-18) on
        qmail.mymailserver.net
X-Spam-Flag: YES
X-Spam-Level: *****
X-Spam-Status: Yes, score=5.4 required=4.0 tests=BAYES_99,FREEMAIL_FROM,
        MISSING_DATE,MISSING_MID,NO_RECEIVED,NO_RELAYS,TVD_SPACE_RATIO,
        T_TO_NO_BRKTS_FREEMAIL autolearn=no version=3.3.1

Blacklist test

Let's test the last rule, which says that that sender@senderdomain.net is blacklisted by myself@mydomain.net. We expect a spam-score near to 100.

> echo -e "From: sender@senderdomain.net\nTo:myself@mydomain.net\nSubject: sql sauserprefs test\n\n" | spamc -u 'myself@mydomain.net'

Received: from localhost by mail.yourdomain.net
        with SpamAssassin (version 3.3.1);
        Thu, 06 Jan 2011 17:37:57 +0100
From: sender@senderdomain.net
To: myself@mydomain.net
Subject: sql sauserprefs test
X-Spam-Checker-Version: SpamAssassin 3.3.1 (2010-03-16) on mail.myserver.net
X-Spam-Flag: YES
X-Spam-Level: **************************************************
X-Spam-Status: Yes, score=101.4 required=5.0 tests=BAYES_05,FREEMAIL_FROM,
        MISSING_DATE,MISSING_MID,NO_RECEIVED,NO_RELAYS,T_TO_NO_BRKTS_FREEMAIL,
        USER_IN_BLACKLIST autolearn=no version=3.3.1
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="----------=_4D25EFE5.A45179CE"

This is a multi-part message in MIME format.

------------=_4D25EFE5.A45179CE
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit

Spam detection software, running on the system "qmail.sagredo.eu", has
identified this incoming email as possible spam.  The original message
has been attached to this so you can view it (if it isn''t spam) or label
similar future email.  If you have any questions, see
admin@sagredo.eu for details.

Content preview:  [...]

Content analysis details:   (101.4 points, 5.0 required)

 pts rule name              description
---- ---------------------- --------------------------------------------------                                                             
 100 USER_IN_BLACKLIST      From: address is in the user''s black-list                                                                      
 0.0 FREEMAIL_FROM          Sender email is freemail                                                                                   
                            (sender[at]senderdomain.net)                                                                             
-0.0 NO_RELAYS              Informational: message was not relayed via SMTP                                                                
-0.5 BAYES_05               BODY: Bayes spam probability is 1 to 5%                                                                        
                            [score: 0.0185]                                                                                                
 0.5 MISSING_MID            Missing Message-Id: header                                                                                     
-0.0 NO_RECEIVED            Informational: message has no Received headers                                                                 
 1.4 MISSING_DATE           Missing Date: header                                                                                           
 0.0 T_TO_NO_BRKTS_FREEMAIL T_TO_NO_BRKTS_FREEMAIL



------------=_4D25EFE5.A45179CE
Content-Type: message/rfc822; x-spam-type=original
Content-Description: original message before SpamAssassin
Content-Disposition: inline
Content-Transfer-Encoding: 8bit

From: sender@senderdomain.net
To:myself@mydomain.net
Subject: sql sauserprefs test



------------=_4D25EFE5.A45179CE--

No need of any further comment about this output... unless.. did you remember to satisfy every requirement above? :-)

Troubleshooting

I remember that I had problems in doing the SQL connection, at the beginning. And I forgot to compile accordingly simscan (remember --enable-spamc-user=y), so double check everything.

To investigate all issues run a non daemonized spamd enabling the debug with the -D option. First of all stop all existing spamd daemons:

spamdctl stop
spamd -D -x -q -u spamd -A 127.0.0.1

and this is what you have to see at the very begenning of the log when things are ok:

Jan  6 17:26:28.149 [26422] info: spamd: connection from yourdomain.net [your-IP] at port 56975
Jan  6 17:26:28.199 [26422] dbg: config: Conf::SQL: executing SQL: SELECT preference, value FROM userpref WHERE username = 'yourself@yourdomain.net' OR username = '$GLOBAL' OR username = CONCAT('%','yourdomain.net') ORDER BY username ASC
Jan  6 17:26:28.200 [26422] dbg: config: retrieving prefs for yourself@yourdomain.net from SQL server

When you start spamd check out the loading of the sql.cf file. For example:

Jan  6 19:01:30.975 [28808] dbg: config: read file /etc/mail/spamassassin/90-sql.cf