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

Comments

Hi. Thank you for very good Howto. I use MySQl 5.5 and had to change SQL syntax - Setting up the database TYPE=MyISAM; I had to change to Engine=MyISAM;

Pato.

Thank you! I just added a note.

Hello all:

I just tried the above step and get strange result in blacklist_from test it is not working at all but strange is if I placed the sender in Global section it is work but then it is not for user blacklist but is for whole server blacklist and other three score test all passed.

Any hints could provide, thanks.

I've done that test right now and it works, but not when stopping smapd (I don't remember why I suggested to stop the service at the time I wrote this page).

first of all double check the user_scores_sql_custom_query and manually see if the rule is actually saved in your spamassassin' userprefs database as a 'blacklist_from' preference.

Post the log if you like, better via the contact button above

I just stop the spamd service and got the LOG below;

Fri Apr 26 20:40:42 2013 [264] info: logger: removing stderr method
Fri Apr 26 20:40:43 2013 [266] info: zoom: able to use 424/424 'body_0' compiled rules (100%)
Fri Apr 26 20:40:46 2013 [266] info: spamd: server started on port 783/tcp (running version 3.3.2)
Fri Apr 26 20:40:46 2013 [266] info: spamd: server pid: 266
Fri Apr 26 20:40:46 2013 [266] info: spamd: server successfully spawned child process, pid 267
Fri Apr 26 20:40:46 2013 [266] info: spamd: server successfully spawned child process, pid 268
Fri Apr 26 20:40:46 2013 [266] info: prefork: child states: IS
Fri Apr 26 20:40:46 2013 [266] info: prefork: child states: II

and then I try to perform your testing query and got the LOG below;

Fri Apr 26 20:41:57 2013 [267] info: spamd: connection from localhost [127.0.0.1] at port 28654
Fri Apr 26 20:41:57 2013 [267] warn: spamd: still running as root: user not specified with -u, not found, or set to root, falling back to nobody
Fri Apr 26 20:41:57 2013 [267] info: spamd: processing message (unknown) for myself@mydomain.net:65534
Fri Apr 26 20:42:00 2013 [267] info: spamd: identified spam (7.9/6.0) for myself@mydomain.net:65534 in 3.1 seconds, 92 bytes.
Fri Apr 26 20:42:00 2013 [267] info: spamd: result: Y 7 - EMPTY_MESSAGE,MISSING_DATE,MISSING_FROM,MISSING_HEADERS,MISSING_MID,MISSING_SUBJECT,NO_HEADERS_MESSAGE,NO_RECEIVED,NO_RELAYS scantime=3.1,size=92,user=myself@mydomain.net,uid=65534,required_score=6.0,rhost=localhost,raddr=127.0.0.1,rport=28654,mid=(unknown),autolearn=no
Fri Apr 26 20:42:00 2013 [266] info: prefork: child states: II

It seems something difference from your shown.

can you enable the debug mode running spamd with -D? this will show the query to the db.
You should also take care to this line. I suppose that you are not running spamd as "spamd" user  (-u spamd option):

Fri Apr 26 20:41:57 2013 [267] warn: spamd: still running as root: user 
not specified with -u, not found, or set to root, falling back to nobody