SpamAssassin – « TxRep » en remplaçant de « AWL »

Republication d'article : source

Vite dit

Créer la base: 

$ mysql -u root -p
mysql> create database sa_txrep;

Préparer mysql/mariadb a se manger un gros index: 

mysql> SET GLOBAL innodb_file_format=Barracuda;
mysql> SET GLOBAL innodb_file_per_table=ON;
mysql> SET GLOBAL innodb_large_prefix=1;

Sinon, il pourrait y avoir une erreur comme ça au moment de « CREATE TABLE » : 

ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

Retour sous shell est création de la table txrep : 

$ dpkg -S txrep_mysql.sql
$ cat /usr/share/doc/spamassassin/sql/txrep_mysql.sql
CREATE TABLE txrep (username varchar(100) NOT NULL default '', \
    email varchar(255) NOT NULL default '', \
    ip varchar(40) NOT NULL default '', \
    count int(11) NOT NULL default '0', \
    totscore float NOT NULL default '0', \
    signedby varchar(255) NOT NULL default '', \
    last_hit timestamp NOT NULL default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, \
    PRIMARY KEY (username,email,signedby,ip), \
    KEY last_hit (last_hit)) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;

ATTENTION: ajouter ROW_FORMAT=DYNAMIC après ENGINE=InnoDB .

Source: https://stackoverflow.com/questions/43379717/how-to-enable-large-index-in-mariadb-10 

$ mysql -u root -p sa_txrep < txrep_mysql.sql

On donne les droits sur cette table: 

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

Dans spamassassin … 

# txrep
txrep_factory Mail::SpamAssassin::SQLBasedAddrList
user_awl_dsn DBI:mysql:database=sa_txrep;host=mail.local.eez.fr;
user_awl_sql_username sa_user
user_awl_sql_password user_awl_sql_table txrep
use_txrep 1

Sources: