Spamassassin User Preferences via SQL

July 27, 2023 by Roberto Puzzanghera 20 comments

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

SpamAssassin can now load users' score files from an SQL database.  The concept here is to have a web application (PHP/perl/ASP/etc.) that will allow users to be able to update their local preferences on how SpamAssassin will filter their e-mail. The most common use for a system like this would be for users to be able to update the welcome/block list of addresses without the need for them to update their $HOME/.spamassassin/user_prefs file.

You can skip this page if you want to manage only global options via /etc/mail/spamassassin.

Be aware that user rules will be easily managed by means of the "sauprefs" plugin of Rouncube webmail.

Changelog

  • Jul 27, 2023
    -added a column ts to save the timestamp. It can be useful in the future to purge very old records from the table.
  • Jul 12, 2021
    -bug fix: the "preference" varchar length in the "userpref" database table was increased to 50 (was 30) to create space for long labels such as  "bayes_auto_learn_threshold_spam", which resulted truncated before the modification.

Prerequisites

  • Perl module Mail::SpamAssassin::BayesStore::MySQL
  • spamd must be executed with --sql-config  --nouser-config (-q -x)
  • simscan must be compiled with --enable-spamc-user=y  to pass to spamc the sender address as the variable USERNAME

Install from CPAN

> perl -MCPAN -e shell

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

Run spamd with -x (--nouser-config, disable user config files) and -q (--sql-config enable SQL config only useful with -x). You may want to have this inside your spamdctl script:

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

Replace <external-IP> with your external IP, i.e. the IP of your mail server as seen from the internet.

Setting up the database

> 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: replace <mail-server-IP> with the IP address of your mail server, localhost should be fine if MySQL and qmail live in the same server.

The ts column holding the timestamp can be useful in the future to purge very old records.

Add the mysql login and the sql query to the config file:

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 is the IP address of your mysql server (localhost if the same of you mail server). Note that every file.cf in the /etc/mail/spamassassin folder will be loaded.

Testing

Insert some global, domain and user rules into the DB:

> MYSQL_IP="localhost"
> mysql -h ${MYSQL_IP} -u spamassassin -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','5.5');
INSERT INTO userpref (username,preference,value) VALUES ('user@tesdomain2.com','required_hits','4.0');
INSERT INTO userpref (username,preference,value) VALUES ('myself@mydomain.net','blocklist_from','spam@senderdomain.net');
exit;

$GLOBAL test

Try to send a messages to test the $GLOBAL rule. You have to look if the required_hits is 4.5:

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

this produces

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

DOMAIN test

Try to send a messages to test the testdomain.net rule. You have to look if the required_hits is 5.5:

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

USER test

Try to send a messages to test the user@tesdomain2.com rule. You have to look if the required_hits is 4.0:

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

Blocklist test

Let's test the last rule, which says that that spam@senderdomain.net is blocklisted by myself@mydomain.net. We expect a spam-score near to 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--

No need of any further comment about this output... unless.. did you remember to satisfy every requirement above? :-)

Troubleshooting

I remember that I had problems in doing the SQL connection, at the beginning. And I forgot to compile simscan accordingly  (remember --enable-spamc-user=y), so double check everything.

To investigate all issues run a non daemonized spamd enabling the debug with the -D option. First of all stop all existing spamd daemons:

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

and this is what you have to see at the very begenning of the log when things are 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

When you start spamd check out the loading of the sql.cf file. For example:

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

Comments

A few pointers on this guide

Hi Roberto,

I spend a few hours trying to put this to work, and the main reason it took so long was because I was stuck with a silly error that I'd like to point out.

In this line:

user_scores_dsn DBI:mysql:spamassassin:mysql-IP:3306

We must replace mysql-IP with the actual IP address of the server. There is nothing talking about this in the guide, so I simply copied and paste the whole stuff without thinking twice. It took me hours without being able to connect to the MySQL database (failed to load user ($GLOBAL) scores from SQL database: install_driver(mysql) failed: Can't locate DBD/mysql.pm: lib/DBD/mysql.pm), installing and re-installing DBI and DBD packages, when I finally decided to go back and I saw this. So I suggest you to replace mysql-IP with localhost in the guide, as if someone it using an external MySQL, it will be clear that localhost must be changed to the actual IP address of the server, and if someone like me copies and pastes without paying too much attention, won't get stuck for hours trying to solve something so trivial.

Here are a few more caveats:

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

The actual location is /usr/local/bin/spamd

Suggestion: provide in this page a new spamdctl with the -q change applied.

ENGINE=InnoDB

Should be preferrably replaced with ENGINE=InnoDB

90-sql.cf

During the tutorial, the file is created as sql.cf, but in the debug it is called 90-sql.cf. Should be renamed to sql.cf to keep coherency.

For debugging, I found it useful to have two terminal/SSH sessions opened, one with:

spamd -D -q -x -u spamd

and the other one running the tests, so I could see the errors being logged in real time.

I hope to have helped others who might get stuck at this step.

Reply |

A few pointers on this guide

Hi Gabriel, thanks for your contribution. I tried to clarify what you pointed out

Reply |

user "nobody" in mysql query

Hi Roberto!

I configured userpref for your article:

# cat /etc/mail/spamassassin/sql.cf

user_scores_dsn DBI:mysql:spamassassin:localhost:3306
user_scores_sql_username spamassassin
user_scores_sql_password spamassassin_pass
user_scores_sql_custom_query SELECT preference, value FROM _TABLE_ WHERE username = _USERNAME_ OR username = '$GLOBAL' OR username = CONCAT('%',_DOMAIN_) ORDER BY username ASC

Parameters for running spamassassin:

"--pidfile /var/run/spamd.pid -d -c -m5 -H --socketpath=/var/spool/spamassassin/spamd.sock -q -Q -u spamd -x"

Table entry "userpref":

MariaDB [spamassassin]> select * from userpref;
+-----------------------------+----------------+-----------------+--------+
| username | preference | value | prefid |
+-----------------------------+----------------+-----------------+--------+
| krentiken@bla-bla.ru | whitelist_from | krentik@bla-bla.com | 6 |
+-----------------------------+----------------+-----------------+--------+
1 row in set (0.00 sec)

When a letter arrives at krentiken@bla-bla.ru from krentik@bla-bla.com the spamassassin performs the request:

Oct 28 17:11:00 post spamd [1730]: config: Conf :: SQL: executing SQL: SELECT preference, value FROM userpref WHERE username = 'nobody' OR username = '$ GLOBAL' OR username = CONCAT ('%' , NULL) ORDER BY username ASC
Oct 28 17:11:00 post spamd [1730]: config: retrieving prefs for nobody from SQL server

Why the request is executed from the user 'nobody', not from the user krentiken@bla-bla.ru ? 

Manual start spamc is successful:

# echo -e "From:krentik@bla-bla.com\nTo:krentiken@bla-bla.ru\nSubject: sql sauserprefs test\n\n" | spamc -u krentiken@bla-bla.ru
X-Spam-Checker-Version: SpamAssassin 3.4.0 (2014-02-07) on
post.bla-bla.ru
X-Spam-Level:
X-Spam-Status: No, score=-94.2 required=5.0 tests=FREEMAIL_FROM,HAVE_TO_HEADER,
MISSING_DATE,MISSING_MID,NO_RECEIVED,NO_RELAYS,TXREP,USER_IN_WHITELIST,
WITHOUT_OUR_DOMAIN autolearn=no autolearn_force=no version=3.4.0
From:krentik@bla-bla.com
To:krentiken@bla-bla.ru
Subject: sql sauserprefs test

Reply |

user

It's not parsing the username nor the domain... no idea at the moment, I'm sorry.

Does it happen with every user? 

I'd suggest to update your spamassassin

Reply |

Spamassassin Does Not Process Multiple Recipients

Hello,

I am having the following problem.

When messages have more than one recipient from the same domain, spamassassin does not process the black and white lists, see:

QUOTE DO HEADER:

X-Spam-Status: No, score=1.5 required=5.0 tests=BAYES_50,DKIM_SIGNED, DKIM_VALID,DKIM_VALID_AU,FREEMAIL_FROM,HTML_MESSAGE,PDS_HP_HELO_NORDNS, RDNS_NONE,SPF_HELO_NONE autolearn=no autolearn_force=no version=3.4.1 From: Tiago Oliveira de Jesus <EMAILFROM@gmail.com>To: tia@DOMAIN.NET, iceomed@DOMAIN.NET

If I do the same command line test, it processes:

echo -e "From: EMAILFROM@gmail.com\nTo:iceomed@DOMAIN.NET\nSubject: test sauserprefs\n\n" | spamc -u 'tia@DOMAIN.NET'

X-Spam-Checker-Version: SpamAssassin 3.4.1 (2015-04-28) on mailsrv2.vgt.com.br
X-Spam-Level:
X-Spam-Status: No, score=-96.4 required=5.0 tests=BAYES_50,
DKIM_ADSP_CUSTOM_MED,FREEMAIL_FROM,MISSING_DATE,MISSING_MID,
NML_ADSP_CUSTOM_MED,NO_RECEIVED,NO_RELAYS,TVD_SPACE_RATIO,USER_IN_WHITELIST
autolearn=no autolearn_force=no version=3.4.1
From: EMAILFROM@gmail.com
To:iceomed@DOMAIN.NET
Subject: test sauserprefs


I'm sure the query is not executed because I am looking at the MySQL logs.

If I send the same test to a single email, then spamassassin runs the query to see if the email is in a white or black list.

I have a client who always sends emails and these are going to spam, he is whitelisting users, domain, global, etc ... but he always sends emails to more than one domain recipient ...

Has anyone noticed this behavior?
If so, do you have any idea how to fix it?

Reply |

Spamassassin Does Not Process Multiple Recipients

Sorry, my copy+paste pasted another test, this is a related test:

echo -e "From: EMAILFROM@gmail.com\nTo:iceomed@DOMAIN.NET, tia@DOMAIN.NET\nSubject: test sauserprefs\n\n" | spamc -u 'tia@DOMAIN.NET'
X-Spam-Checker-Version: SpamAssassin 3.4.1 (2015-04-28) on mailsrv2.vgt.com.br
X-Spam-Level:
X-Spam-Status: No, score=-96.4 required=5.0 tests=BAYES_50,
DKIM_ADSP_CUSTOM_MED,FREEMAIL_FROM,MISSING_DATE,MISSING_MID,
NML_ADSP_CUSTOM_MED,NO_RECEIVED,NO_RELAYS,TVD_SPACE_RATIO,USER_IN_WHITELIST
autolearn=no autolearn_force=no version=3.4.1
From: EMAILFROM@gmail.com
To:iceomed@DOMAIN.NET
Subject: test sauserprefs

The order of recipients not important, the result is same.

Reply |

Spamassassin Does Not Process Multiple Recipients

Hi Roberto!

I switch debug at simscan to see how it does at this cases, and see if have more than rcptto, simscan not send user to spamd.

I locate the answer at your last patch to Simscan :-)

else if (MaxRcptTo==1 && i

to

else if (MaxRcptTo>0 && i

This solved in parts my problem, the simscan not run tests for each user  at rcptto, only the first.. but, i change my SQL query to always lookup domain..

Tks

Reply |

Spamassassin Does Not Process Multiple Recipients

Thanks Tiago, I'm going to update that patch

>> This solved in parts my problem, the simscan not run tests for each user at rcptto, only the first.. but, i change my SQL query to always lookup domain..

can you explain a little bit why simscan continues to scan only the 1st rcpt even after your adjustment, and which modification to the SQL query was needed?

Reply |

Spamassassin Does Not Process Multiple Recipients

Hi Roberto!

I sent mail with three rcpt, and, looking the simscan logs, it tests only the first:

CHKUSER accepted sender: from <EMAILTST@gmail.com|remoteinfo/auth:|chkuser-identify:> remote <helo:mail-io1-f50.google.com|remotehostname:unknown|remotehostip:209.85.166.50> rcpt <> : sender accepted
CHKUSER accepted rcpt: from <EMAILTST@gmail.com|remoteinfo/auth:|chkuser-identify:> remote <helo:mail-io1-f50.google.com|remotehostname:unknown|remotehostip:209.85.166.50> rcpt <iceomed@DOMAIN.NET> : found existing recipient
policy_check: remote EMAILTST@gmail.com -> local iceomed@DOMAIN.NET (UNAUTHENTICATED SENDER)
CHKUSER accepted rcpt: from <EMAILTST@gmail.com|remoteinfo/auth:|chkuser-identify:> remote <helo:mail-io1-f50.google.com|remotehostname:unknown|remotehostip:209.85.166.50> rcpt <tia@DOMAIN.NET> : found existing recipient
policy_check: remote EMAILTST@gmail.com -> local tia@DOMAIN.NET (UNAUTHENTICATED SENDER)
CHKUSER accepted rcpt: from <EMAILTST@gmail.com|remoteinfo/auth:|chkuser-identify:> remote <helo:mail-io1-f50.google.com|remotehostname:unknown|remotehostip:209.85.166.50> rcpt <acca@DOMAIN.NET> : found existing recipient
policy_check: remote EMAILTST@gmail.com -> local acca@DOMAIN.NET (UNAUTHENTICATED SENDER)
simscan: pelookup: called with EMAILTST@gmail.com
simscan: pelookup: local part is EMAILTST
simscan: cdb looking up EMAILTST@gmail.com
simscan: pelookup: called with iceomed@DOMAIN.NET
simscan: pelookup: domain is DOMAIN.NET
simscan: cdb looking up DOMAIN.NET
simscan: cdb looking up iceomed@DOMAIN.NET
simscan: pelookup: called with tia@DOMAIN.NET
simscan: pelookup: domain is DOMAIN.NET
simscan: cdb looking up DOMAIN.NET
simscan: cdb looking up tia@DOMAIN.NET
simscan: pelookup: called with acca@DOMAIN.NET
simscan: pelookup: domain is DOMAIN.NET
simscan: cdb looking up DOMAIN.NET
simscan: cdb looking up acca@DOMAIN.NET
simscan: calling /usr/local/bin/spamc spamc -u iceomed@DOMAIN.NET
simscan:[25606]:CLEAN (-98.50/9.50):2.0361s:teste multi:209.85.166.50:EMAILTST@gmail.com:iceomed@DOMAIN.NET,tia@DOMAIN.NET,acca@DOMAIN.NET
mail recv: pid 25606 from <EMAILTST@gmail.com> qp 26264
qmail-smtpd: message accepted: EMAILTST@gmail.com from 209.85.166.50 to acca@DOMAIN.NET helo mail-io1-f50.google.com

And i changed the query to:

user_scores_sql_custom_query SELECT preference, value FROM _TABLE_ WHERE username = _USERNAME_ OR username = '$GLOBAL' OR username = CONCAT('*@',_DOMAIN_) OR username = CONCAT('%@',_DOMAIN_) OR username = CONCAT('%',_DOMAIN_) ORDER BY username ASC

The *@ not used.. but.... :-)

I will to change the plugin of roundcube, to all black/white list, write to  domain, not for user... this "solve" the problem when my client for now...

[] s

Reply |

Spamassassin Does Not Process Multiple Recipients

I think I'm missing something... I don't understand why you had to add this to the query

OR username = CONCAT('*@',_DOMAIN_) OR username = CONCAT('%@',_DOMAIN_)

as "username = CONCAT('%@',_DOMAIN_)" is equivalent to "username = CONCAT('%',_DOMAIN_)" because the "@" is always present before _DOMAIN_, while "*" should not be considered as a wildcard in SQL syntax

Reply |

Spamassassin Does Not Process Multiple Recipients

Roberto, 

*@ realy not do nothing... i will try on desperate moment.

But,  CONCAT('%',_DOMAIN_) not get %@domain.net.. :

MariaDB [spamassassin]> SELECT * FROM userpref WHERE username = CONCAT('%','domain.net') ;
Empty set (0.00 sec)

MariaDB [spamassassin]> SELECT * FROM userpref WHERE username = CONCAT('%@','domain.net') ;
+--------------+----------------+---------------------+--------+---------------------+
| username | preference | value | prefid | create |
+--------------+----------------+---------------------+--------+---------------------+
| %@domain.net | whitelist_from | dnsadmin@afraid.org | 53822 | 2019-06-03 19:32:26 |
+--------------+----------------+---------------------+--------+---------------------+
1 row in set (0.00 sec)

MariaDB [spamassassin]> SELECT * FROM userpref WHERE username like CONCAT('%@','domain.net') ;
+------------------------+--------------------------------+---------------------------+--------+---------------------+
| username | preference | value | prefid | create |
+------------------------+--------------------------------+---------------------------+--------+---------------------+
| tia@domain.net | bayes_auto_learn_threshold_spa | 6 | 26515 | 2019-06-03 19:32:26 |
| gilbertovgt@domain.net | whitelist_from | support@elasticemail.com | 30109 | 2019-06-03 19:32:26 |
| %@domain.net | whitelist_from | dnsadmin@afraid.org | 53822 | 2019-06-03 19:32:26 |
| tia@domain.net | whitelist_from | mailtest@gmail.com | 61279 | 2019-06-03 19:32:26 |
| leandro@domain.net | whitelist_from | gsuite-noreply@google.com | 66046 | 2019-08-01 11:37:26 |
+------------------------+--------------------------------+---------------------------+--------+---------------------+
5 rows in set (0.03 sec)

The original query from mysql.log at mail server is:

SELECT preference, value FROM userpref WHERE username = 'tia@DOMAIN.NET' OR username = '$GLOBAL' OR username = CONCAT('*@','DOMAIN.NET') OR username = CONCAT('%@','DOMAIN.NET') OR username = CONCAT('%','DOMAIN.NET') ORDER BY username ASC

Is try using this without %@, not work. 

Reply |

Spamassassin Does Not Process Multiple Recipients

This is because you stored in the DB username="%@domain.net", which is different from my example above, where username=''%domain.net" (without the @)

Reply |

Spamassassin Does Not Process Multiple Recipients

Hi Roberto!

Is true.. I changed this because haved problem with domain inside another domain, like "mygmail.com".. some like this...

This change have many years, and i forget.

:-)

Thanks how ever for yout time and patience!

After i change the roundcube plugin, i send for you, ok!?

For simscan.. i'm not know C/C++ much to do any change.

[] s!

Reply |

Shell Replacement Error

Hello Roberto!

I noticed now that when we do:

cat > sql.cf << __EOF__
user_scores_dsn                  DBI:mysql:spamassassin:mysql-IP:3306
user_scores_sql_password         password
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__

The shell tries to interpret the $ GLOBAL variable and the file is written with nothing.

I escaped with \$GLOBAL, okay !?

[] s

Tiago

Reply |

Black list test could not pass @@

Hello all:

I just tried the above step and get strange result in blacklist_from test it is not working at all but strange is if I placed the sender in Global section it is work but then it is not for user blacklist but is for whole server blacklist and other three score test all passed.

Any hints could provide, thanks.

Reply |

@Black list test

I've done that test right now and it works, but not when stopping spamd (I don't remember why I suggested to stop the service at the time I wrote this page).

first of all double check the user_scores_sql_custom_query and manually see if the rule is actually saved in your spamassassin' userprefs database as a 'blacklist_from' preference.

Post the log if you like, better via the contact button above

Reply |

@Black list test

I just stop the spamd service and got the LOG below;

Fri Apr 26 20:40:42 2013 [264] info: logger: removing stderr method
Fri Apr 26 20:40:43 2013 [266] info: zoom: able to use 424/424 'body_0' compiled rules (100%)
Fri Apr 26 20:40:46 2013 [266] info: spamd: server started on port 783/tcp (running version 3.3.2)
Fri Apr 26 20:40:46 2013 [266] info: spamd: server pid: 266
Fri Apr 26 20:40:46 2013 [266] info: spamd: server successfully spawned child process, pid 267
Fri Apr 26 20:40:46 2013 [266] info: spamd: server successfully spawned child process, pid 268
Fri Apr 26 20:40:46 2013 [266] info: prefork: child states: IS
Fri Apr 26 20:40:46 2013 [266] info: prefork: child states: II

and then I try to perform your testing query and got the LOG below;

Fri Apr 26 20:41:57 2013 [267] info: spamd: connection from localhost [127.0.0.1] at port 28654
Fri Apr 26 20:41:57 2013 [267] warn: spamd: still running as root: user not specified with -u, not found, or set to root, falling back to nobody
Fri Apr 26 20:41:57 2013 [267] info: spamd: processing message (unknown) for myself@mydomain.net:65534
Fri Apr 26 20:42:00 2013 [267] info: spamd: identified spam (7.9/6.0) for myself@mydomain.net:65534 in 3.1 seconds, 92 bytes.
Fri Apr 26 20:42:00 2013 [267] info: spamd: result: Y 7 - EMPTY_MESSAGE,MISSING_DATE,MISSING_FROM,MISSING_HEADERS,MISSING_MID,MISSING_SUBJECT,NO_HEADERS_MESSAGE,NO_RECEIVED,NO_RELAYS scantime=3.1,size=92,user=myself@mydomain.net,uid=65534,required_score=6.0,rhost=localhost,raddr=127.0.0.1,rport=28654,mid=(unknown),autolearn=no
Fri Apr 26 20:42:00 2013 [266] info: prefork: child states: II

It seems something difference from your shown.

Reply |

@Black list test

can you enable the debug mode running spamd with -D? this will show the query to the db.
You should also take care to this line. I suppose that you are not running spamd as "spamd" user  (-u spamd option):

Fri Apr 26 20:41:57 2013 [267] warn: spamd: still running as root: user 
not specified with -u, not found, or set to root, falling back to nobody

Reply |

SQL syntax - MySQL 5.5

Hi. Thank you for very good Howto. I use MySQl 5.5 and had to change SQL syntax - Setting up the database TYPE=MyISAM; I had to change to Engine=MyISAM;

Pato.

Reply |

Thank you

Thank you! I just added a note.

Reply |