Spamassassin User Preferences via SQL

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

SpamAssassin può ora caricare gli score da un database SQL.  L'obiettivo è far sì che una applicazione web (PHP/perl/ASP/etc.) possa consentire agli utenti di aggiornare le loro preferenze su come SpamAssassin debba filtrare la loro posta. L'uso più comune per un sistema come questo è quello in cui gli utenti aggiornano la welcome/block list di indirizzi senza bisogno di di aggiornare il file $HOME/.spamassassin/user_prefs.

Si può saltare questa pagina nel caso si voglia consentire solo l'uso di opzioni globali su tutto il server via /etc/mail/spamassassin.

Tenere presente che le regole per la gestione dello spamming a livello utente saranno facilmente gestite per mezzo del plugin "sauprefs" della webmail Rouncube.

Changelog

• 27 luglio 2023
  - aggiunta una colonna ts per salvare il timestamp, che potrà in seguito servire per cancellare i vecchi record se la tabella dovesse ingigantirsi.
• 12 luglio 2021
  - la lunghezza del campo varchar "preference" nella tabella userpref del database è stata aumentata a 50 (era 30) per consentire il salvataggio di variabili lunghe come ad esempio  "bayes_auto_learn_threshold_spam", che veniva troncata prima della modifica.

Prerequisiti

• Modulo Perl Mail::SpamAssassin::BayesStore::MySQL
• spamd deve essere eseguito con --sql-config  --nouser-config (-q -x)
• simscan deve essere configurato con l'opzione --enable-spamc-user=y  affinchè venga correttamente passato a spamc l'indirizzo del destinatario come variabile USERNAME

Installare da CPAN

> perl -MCPAN -e shell

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

Lanciare spamd con -x (--nouser-config, disabilita le opzioni su file per l'utente) e -q (--sql-config abilita le opzioni via SQL; utilizzabile solo con -x). Naturalmente spamd può essere lanciato dallo script spamdctl di cui si è parlato prima.

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

Creare il database e il file di configurazione

> mysql -u root -p

CREATE DATABASE spamassassin;
CREATE USER 'spamassassin'@'<mail-server-IP>' IDENTIFIED BY '***';
GRANT USAGE ON * . * TO 'spamassassin'@'<mail-server-IP>' 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'@'<mail-server-IP>';

NB: <mail-server-IP> è l'indirizzo IP del server di posta, che potrà essere localhost nel caso il server mysql risieda nella stessa macchina del server di posta.

La colonna ts salva il timestamp. Potrà tornare utile in seguito al fine di eliminare vecchi record se la tabella dovesse diventare troppo grande.

Aggiungere il login di mysql e la query sql al file di configurazione:

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 è l'indirizzo IP del server sql; si può porre semplicemente localhost nel caso MySQL e qmail risiedano nella stessa macchina. Notare che ogni file.cf nella cartella /etc/mail/spamassassin sarà caricato.

Test

Inserire sul database alcune preferenze globali, basate sul dominio o per uno specifico utente:

> mysql -u root -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','4.0');
INSERT INTO userpref (username,preference,value) VALUES ('user@testdomain2.com','required_hits','5.5');
INSERT INTO userpref (username,preference,value) VALUES ('myself@mydomain.net','blocklist_from','spam@senderdomain.net');
exit;

test $GLOBAL

Inviare un messaggio di test per la regola $GLOBAL. Ci aspettiamo che required_hits sia 4.5 nel messaggio ricevuto:

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

ciò produce:

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

test sul DOMINIO

Inviare un messaggio di test per la regola basata sul dominio (testdomain.net). Ci aspettiamo che required_hits sia 4.0:

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

test sulle regole di uno specifico utente

Inviare un messaggio di test all'utente user@tesdomain2.com che ha una regola a suo nome nel DB. Ci aspettiamo qui che required_hits sia 5.5:

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

blocklist test

Testiamo ora l'ultima regola, che vede l'indirizzo spam@senderdomain.net nella blocklist dell'utente myself@mydomain.net. Ci aspettiamo un putteggio (spam-score) vicino a 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--

il chè non ha bisogno di commenti. A meno che... hai osservato tutti prerequisiti di cui sopra? :-)

Debug

Ricordo di aver avuto problemi all'inizio nel fare la connessione a MySQL, o meglio non veniva passato correttamente l'utente destinatario a spamc perchè non avevo configurato conseguentemente simscan con --enable-spamc-user=y), quindi prima di fare il debug ricontrolla l'esistenza di prerequisiti di cui sopra.

Per cercare gli errori lancia spamd in modo interattivo abilitando il debug con -D. Prima arresta eventuali demoni di spamd già esistenti:

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

e questo è ciò che si dovrebbe vedere proprio all'inizio del log se tutto è 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

Quando spamd viene lanciato è bene controllare che il file con le impostazione SQL sia stato correttamente caricato. Ad esempio:

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

# ==========================================================================
# service type  private unpriv  chroot  wakeup  maxproc command + args
#               (yes)   (yes)   (yes)   (never) (50)
# ==========================================================================
...
spamassassin
          unix  -       n       n       -       -       pipe
   flags=Rq user=nobody argv=/path/to/spamc -u ${user} -e /path/to/postfix/sendmail -oi -f ${sender} ${recipient}

${user}

${recipient}