Spamassassin User Preferences via SQL

July 27, 2023 by Roberto Puzzanghera 20 comments

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 welcome/block 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.

Changelog

Jul 27, 2023
-added a column ts to save the timestamp. It can be useful in the future to purge very old records from the table.
Jul 12, 2021
-bug fix: the "preference" varchar length in the "userpref" database table was increased to 50 (was 30) to create space for long labels such as "bayes_auto_learn_threshold_spam", which resulted truncated before the modification.

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
force notest install Mail::SpamAssassin::BayesStore::MySQL
force notest 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/local/bin/spamd -A 127.0.0.1,<external-IP> -x -q -u spamd -s stderr 2>&1

Replace <external-IP> with your external IP, i.e. the IP of your mail server as seen from the internet.

Setting up the database

> mysql -u root -p

CREATE DATABASE spamassassin;
CREATE USER 'spamassassin'@'localhost' IDENTIFIED BY '***';
GRANT USAGE ON * . * TO 'spamassassin'@'localhost' IDENTIFIED BY '***' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
USE spamassassin;
CREATE TABLE userpref (
  username varchar(100) NOT NULL default '',
  preference varchar(50) NOT NULL default '',
  value varchar(255) NOT NULL default '',
  prefid int(11) NOT NULL auto_increment,
  ts timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY  (prefid),
  KEY username (username)
) ENGINE=InnoDB; 
GRANT ALL PRIVILEGES ON spamassassin . * TO 'spamassassin'@'localhost';

NB: replace <mail-server-IP> with the IP address of your mail server, localhost should be fine if MySQL and qmail live in the same server.

The ts column holding the timestamp can be useful in the future to purge very old records.

Add the mysql login and the sql query to the config file:

MYSQL_IP="localhost"
MYSQL_PWD="xxxxxxxx"

cat >> /etc/mail/spamassassin/90-sql.cf << __EOF__
user_scores_dsn                  DBI:mysql:spamassassin:${MYSQL_IP}:3306
user_scores_sql_password         ${MYSQL_PWD}
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__

NB: MYSQL_IP is the IP address of your mysql server (localhost if the same of you mail server). 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_IP="localhost"
> mysql -h ${MYSQL_IP} -u spamassassin -p

USE spamassassin;
INSERT INTO userpref (username,preference,value) VALUES ('$GLOBAL','required_hits','4.5');
INSERT INTO userpref (username,preference,value) VALUES ('%testdomain.net','required_hits','5.5');
INSERT INTO userpref (username,preference,value) VALUES ('user@tesdomain2.com','required_hits','4.0');
INSERT INTO userpref (username,preference,value) VALUES ('myself@mydomain.net','blocklist_from','spam@senderdomain.net');
exit;

$GLOBAL test

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

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=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

DOMAIN test

Try to send a messages to test the testdomain.net rule. You have to look if the required_hits is 5.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=5.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

Blocklist test

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

> echo -e "From: spam@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: spam@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_BLOCKLIST 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_BLOCKLIST      From: address is in the user''s block-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: spam@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 simscan accordingly (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

Filtering networks

TxRep and Bayesian

spamassassin

add a comment

Comments

A few pointers on this guide

Gabriel Torres May 27, 2020 04:20 CET

Hi Roberto,

I spend a few hours trying to put this to work, and the main reason it took so long was because I was stuck with a silly error that I'd like to point out.

In this line:

user_scores_dsn DBI:mysql:spamassassin:mysql-IP:3306

We must replace mysql-IP with the actual IP address of the server. There is nothing talking about this in the guide, so I simply copied and paste the whole stuff without thinking twice. It took me hours without being able to connect to the MySQL database (failed to load user ($GLOBAL) scores from SQL database: install_driver(mysql) failed: Can't locate DBD/mysql.pm: lib/DBD/mysql.pm), installing and re-installing DBI and DBD packages, when I finally decided to go back and I saw this. So I suggest you to replace mysql-IP with localhost in the guide, as if someone it using an external MySQL, it will be clear that localhost must be changed to the actual IP address of the server, and if someone like me copies and pastes without paying too much attention, won't get stuck for hours trying to solve something so trivial.

Here are a few more caveats:

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

The actual location is /usr/local/bin/spamd

Suggestion: provide in this page a new spamdctl with the -q change applied.

ENGINE=InnoDB

Should be preferrably replaced with ENGINE=InnoDB

90-sql.cf

During the tutorial, the file is created as sql.cf, but in the debug it is called 90-sql.cf. Should be renamed to sql.cf to keep coherency.

For debugging, I found it useful to have two terminal/SSH sessions opened, one with:

spamd -D -q -x -u spamd

and the other one running the tests, so I could see the errors being logged in real time.

I hope to have helped others who might get stuck at this step.

Spamassassin User Preferences via SQL

Changelog

Prerequisites

Setting up the database

Testing

$GLOBAL test

DOMAIN test

USER test

Blocklist test

Troubleshooting

Comments

qmail notes

LXC scripts

Other contents

Recent comments

Recent posts