Spamassassin AWL avec MySQL

Aprés avoir vu “Bayes”, attaquons “AWL”, c'est a dire: Auto-White-List

On va aussi effectuer une phase de migration d'une base “DBFILE” vers “MySQL”.

~~READMORE~~

Dixit la doc: préférer le moteur “MyISAM”.

Les schemas de la base de données sont dans /usr/share/doc/spamassassin/sql/awl_mysql.sql

Pour info:

• ip varchar(40) … ⇒ prêt pour gérer l'ipv6 (ce dont je n'ai cure)
• signedby … ⇒ prêt pour auto_whitelist_distinguish_signed

En tant que “root” :

$ mysql -u root -p 

Créer la database “sa_awl” :

mysql> create database sa_awl;

Sous shell, créer la table:

$ sed -e 's/TYPE=MyISAM/ENGINE=MyISAM/' awl_mysql.sql | mysql -u root -p sa_awl

Je retourne dans MySQL pour autoriser mes 2 frontaux a interroger la base:

$ mysql -u root -p 

( Les utilisateurs ont déjà été créé pour installer Bayes )

mysql> GRANT SELECT, UPDATE, DELETE, INSERT ON sa_awl.awl TO 'sa_user'@'mx0.local.eez.fr';
mysql> GRANT SELECT, UPDATE, DELETE, INSERT ON sa_awl.awl TO 'sa_user'@'mx1.local.eez.fr';

On va migrer 1 seul base “awl” d'un frontal.
Chez moi, l'utilisateur qui gère le spam c'est “debian-spamd” .

La base de données se trouve là dedans:

/var/lib/spamassassin/.spamassassin/auto-whitelist

On récupère cette base (surement en arrêtant l'“autowhitelist” et en ayant les bons droits.)

Soit le fichier auto-whitelist dans le répertoire courant (hors du contexte de spamassassin).

D'abord, purger un peu la base:

$ sa-awl --clean ./auto-whitelist

(Il ne doit pas y avoir d'erreur)

On va utiliser un scripte Perl pour créer un fichier “.sql” contenant l'export et la conversion de la base existante.

Voici le script “awl_dump.pl” :

• Perl: awl_dump.pl
• Perl/Txt: awl_dump.pl.txt
• Zippé: awl_dump.pl.zip

Ce scripte est inspiré de celui-ci : http://svn.apache.org/repos/asf/spamassassin/trunk/tools/convert_awl_dbm_to_sql

D'abord, vérifier qu'il n'y a pas (trop) d'erreur:

$ perl awl_dump.pl 1>/dev/null

S'il y a quelques “WARNING…” , on peut constater que ce sont des problèmes mineures (pas d'IP associé a un SPAM… on s'en fout!)

Maintenant, transformer la base en requête d'importation SQL :

$ perl awl_dump.pl 1>./awl_dump.sql

Voila, le fichier “awl_dump.sql” contient une série de requêtes SQL … Par exemple, extrait:

...
INSERT INTO awl (username,email,ip,count,totscore) VALUES ('debian-spamd','service-clients@newsletter.jajabou4999.com','81.92',26,83.537)
INSERT INTO awl (username,email,ip,count,totscore) VALUES ('debian-spamd','tradition-des-vosges-plandefou-341777@eml-ava.com','91.209',4,46.476)
...

Maintenant, il suffit d'importer cela dans la base:

$ mysql -u root -p sa_awl < ./awl_dump.sql

La nouvelle configuration dans local.cf :

# awl
auto_whitelist_factory          Mail::SpamAssassin::SQLBasedAddrList
user_awl_dsn                    DBI:mysql:database=sa_awl;host=mail.local.eez.fr;
user_awl_sql_username           sa_user
user_awl_sql_password           <PASSWORD>
user_awl_sql_table              awl

Redemarrer:

# /etc/init.d/spamassassin restart

Tester:

On est content , ça marche.

Mais il manque une “column” bien utile : la date de la dernière modification.

On va la créer:

$ mysql -u root -p sa_awl

mysql> ALTER TABLE awl ADD ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

Query OK, 85083 rows affected (0.19 sec)
Records: 85083  Duplicates: 0  Warnings: 0

Maintenant, on va fixer la valeur par defaut, c'est à dire l' “heure actuelle” (sinon, c'est “0000-00-00 00:00:00” ).

mysql> UPDATE awl SET ts=DEFAULT WHERE ts=0;

Query OK, 84734 rows affected (0.05 sec)
Records: 84734  Duplicates: 0  Warnings: 0

Maintenant, on peut executer, de temps en temps, une petit commande pour purger les enregistrements obsoletes:

mysql> DELETE FROM awl WHERE ts < (NOW() - INTERVAL 35 DAY);

Pour l'exemple, voici une mise en place dans CRON :

Dans /root/local créer un fichier mysql-root.cnf contenant:

[client]
user=root
password=<PASSWORD>
batch

# chmod 0600 mysql-root.cnf

Dans crontab on peut maintenant ajouter:

1 5 * * sun echo 'DELETE FROM awl WHERE ts < (NOW() - INTERVAL 35 DAY);' | mysql --defaults-file='/root/local/mysql-root.cnf' sa_awl >/dev/null

Voila.

• http://notes.sagredo.eu/node/86

Autre scripte (non testé) pour faire la migration:
http://svn.apache.org/repos/asf/spamassassin/trunk/tools/convert_awl_dbm_to_sql