#CREATE SPAMASSASSIN RULE SQL HOW TO#
#CREATE SPAMASSASSIN RULE SQL INSTALL#
#CREATE SPAMASSASSIN RULE SQL UPDATE#

Updates 2 November 2017 Added missing Debian packages. Newest_token_age int(11) NOT NULL default '0', Last_expire_reduce int(11) NOT NULL default '0', Last_atime_delta int(11) NOT NULL default '0', Last_expire int(11) NOT NULL default '0', Token_count int(11) NOT NULL default '0', Username varchar(200) NOT NULL default '', Msgid varchar(200) binary NOT NULL default '', INSERT INTO bayes_global_vars VALUES ('VERSION','3') Variable varchar(30) NOT NULL default '', The tables with the default layout are created with the following commands. The Bayes settings are similar to the ones seen above.īayes_store_module Mail::SpamAssassin::BayesStore::SQLīayes_sql_dsn dbi:mysql:spamassassin:localhostīayes_sql_password my_spamassassin_password The following statements delete entries that are older than 6 months and addresses that occurred only once in the past 15 days.ĭELETE FROM awl WHERE lastupdate <= DATE_SUB(SYSDATE(), INTERVAL 6 MONTH) ĭELETE FROM awl WHERE count = 1 AND lastupdate <= DATE_SUB(SYSDATE(), INTERVAL 15 DAY)

#CREATE SPAMASSASSIN RULE SQL UPDATE#

UPDATE awl SET lastupdate = NOW() WHERE lastupdate < 1 With this additional column it is possible to selectively delete old and unused records.ĪLTER TABLE awl ADD lastupdate timestamp default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP Hence it is suggested to change the table to include a timestamp of the last modification for each record.

SpamAssassin only adds data to the auto-whitelist table and does not delete from it. PRIMARY KEY (username,email(150),signedby(150),ip)) Signedby varchar(255) NOT NULL default '', You can use only a subset of the fields in the key like this: The default table layout in /usr/share/doc/spamassassin/sql/awl_mysql.sql creates a unique key that is too long for the InnoDB engine. User_awl_sql_password my_spamassassin_password User_awl_dsn dbi:mysql:spamassassin:localhost The setting below tells SpamAssassin to use the auto-whitelist in the SQL database.Īuto_whitelist_factory Mail::SpamAssassin::SQLBasedAddrList Loadplugin Mail::SpamAssassin::Plugin::AWL Make sure the AWL plugin is enabled in /etc/spamassassin/v310.pre: # INSERT INTO userpref (username, preference, value) VALUES ('alice', 'whitelist_from', up the auto-whitelist configuration INSERT INTO userpref (username, preference, value) VALUES 'required_hits', '4.0') Preference varchar(50) NOT NULL default '', Username varchar(100) NOT NULL default '', Example layouts are in /usr/share/doc/spamassassin/sql/ The username field contains the username whose e-mail is being filtered or for a global option. User_scores_sql_password my_spamassassin_passwordįor the default query to work the userpref table must have at least the username, preference and value fields. User_scores_dsn dbi:mysql:spamassassin:localhost On the file /etc/spamassassin/local.cf set: SpamAssassin can load user specific settings from the database, if the user_scores_dsn variable is set (and spamd is started with the -sql-config option).

GRANT ALL ON spamassassin.* TO PRIVILEGES The MariaDB Databaseįirst, create a database and a user in MariaDB:ĬREATE USER IDENTIFIED BY 'my_spamassassin_password' In /etc/default/spamassassin add the options -sql-config ( -q), -nouser-config ( -x) and -u mail to the OPTIONS configuration.Īlso in /etc/default/spamassassin set the CRON option to 1 to enable automatic updates of the rules.

#CREATE SPAMASSASSIN RULE SQL INSTALL#

Quite likely you may be have to install the following packages:Īpt install libgeo-ip-perl libnet-cidr-lite-perl libencode-detect-perl libnet-patricia-perl Check them with spamassassin -lint -debug. As the title suggests these instructions are heavily Debian-based.Īfter installing SpamAssassin, make sure to install missing Perl packages. Most of the information is based on the documentation in /usr/share/doc/spamassassin/sql, the SpamAssassin SQL wiki.

#CREATE SPAMASSASSIN RULE SQL HOW TO#

This post is about how to configure SpamAssassin to store its volatile data in a MariaDB database. About SpamAssassin with MariaDB on Debian stretch.SpamAssassin with MariaDB on Debian stretch

Create spamassassin rule sql