Spamassassin User Preferences via SQL

27 luglio 2023 by Roberto Puzzanghera 6 commenti

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

 

Commenti

Ciao! Innanzitutto complimenti

Ciao!

Innanzitutto complimenti per il tuo sito, si trovano guide accurate e dettagliate, per di più sia in inglese che in italiano.

Io ho un problema nella configurazione delle user_prefs su mysql, ho seguito la tua guida ma le user_prefs non funzionano, lanciando spamd con il debug attivo leggo:

config: retrieving prefs for spamd from SQL server

come se spamassassin non effettua la query a seconda dell'utente della mail ma solo con il suo utente spamd (nella mia configurazione uso dei virtual user su ldap).

Da cosa può dipendere?

Grazie mille!

Rispondi |

non saprei...

non saprei... in effetti dovresti avere una riga analoga con l'indirizzo email dell'utente che fa la query..

Rispondi |

Sembra come se a spamassassin

Sembra come se a spamassassin non arrivi l'utente della mail, come se non gli venisse passato da postfix, la mia configurazione di postfix con spamassassin è la seguente:

 

smtp      inet  n       -       -       -       -       smtpd        -o content_filter=spamassassin

 

spamassassin unix -     n       n       -       -       pipe       

user=spamd argv=/usr/bin/spamc -f -e       

/usr/sbin/sendmail -oi -f ${sender} ${recipient}

Rispondi |

uh.. purtroppo non sono

uh.. purtroppo non sono esperto di postfix, uso qmail Embarassed

Rispondi |

Ciao, ho risolto scrivo qui

Ciao, ho risolto scrivo qui così chi si trova nel mio stesso errore può risolverlo.

Praticamente bastava leggere la documentazione di spamassassin+postfix che dice:

If you use user preferences stored in SQL, you should change "spamassassin" service in master.cf to following: 

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

Io poi ho sostituito 

${user}

con

${recipient}

perchè uso l'autenticazione su ldap con doppio cn, quindi esistono due domini che si autenticano e può esistere sia pippo@dominio1 che pippo@dominio2 quindi la variabile user che sarebbe uguale a "pippo" non basta ed ho bisogno che sia specificato il dominio, e con il parametro recipient funziona visto che filtro solo la posta in entrata.

Ora postfix passa l'utente giusto a spamd che riesce a leggere le userpref.

PS: nella mia configuarzione spamassassin non ha accesso alle maildir degli utenti, quindi non posso effettuare il normale sa-learn. Attraverso il db è possibile? Nel senso che Spamassassin fa un learn non in base alle cartelle che io gli segnalo come spam e ham, ma attraverso le entry nel db?

Saluti

Sacha

Rispondi |

PS: nella mia configuarzione

PS: nella mia configuarzione spamassassin non ha accesso alle maildir degli utenti, quindi non posso effettuare il normale sa-learn. Attraverso il db è possibile? Nel senso che Spamassassin fa un learn non in base alle cartelle che io gli segnalo come spam e ham, ma attraverso le entry nel db?

proprio cosi :)

grazie del contributo!

Rispondi |