Saving SpamAssassin user prefs in database -- Am I missing a step? (solved)

[keenmouse]

🛈 SYSTEM INFORMATION
OS type and version	Debian 10
Webmin version	1.984
Virtualmin version	6.17-3

Edit:

Storing preferences in a database was not the issue.

The problem is that USER_IN_WHITELIST is just another SpamAssassin test with an associated weight rather than an instruction to bypass spam filtering. This is a good thing, because even whitelisted addresses can be forged, so there should still be a threshold above which they can be flagged as spam.

If a user is receiving non-spam messages that nevertheless score high enough that they are not sufficiently offset by the USER_IN_WHITELIST score, then you can either decrease the overall spam score threshold, which may allow more spam through from all addresses, or change the weight of the USER_IN_WHITELIST test, e.g. by inserting something like score USER_IN_WHITELIST -10.0 in either the system-wide .cf file or an individual user’s .cf file.

Keep in mind when testing with GTUBE that SpamAssassin has a specific test for that (named GTUBE) with a weight of 1000!

---

Original problem:

I’m trying to store SpamAssassin user preferences in a SQL database, but the database preferences seem to be ignored. Here’s what I have done:

• Created a database for SpamAssassin settings.
• Created userpref table:

	CREATE TABLE userpref (
		username varchar(100) NOT NULL default '',
		preference varchar(50) NOT NULL default '',
		value varchar(100) NOT NULL default '',
		prefid int(11) NOT NULL auto_increment,
		PRIMARY KEY  (prefid),
		KEY username (username)
	);

• Configured database in Servers > SpamAssassin Mail Filter > SQL and LDAP Databases.
• Configured database in Webmin > Usermin Configuration > Usermin Module Configuration > SpamAssasin Mail Filter.
• Edited /etc/default/spamassassin and added -x -Q to OPTIONS command line.
  • systemctl daemon-reload
  • systemctl restart spamassassin
  • Confirmed with systemctl status spamassassin that the service started with the -x -Q options.
• Created a mail user in Virtualmin.
• Logged in to Usermin as new user.
• Whitelisted an email address in Mail > SpamAssassin Mail Filter > Allowed and Denied Addresses.
• Confirmed that the whitelist entry exists in the database table.
• Sent message containing GTUBE test string from whitelisted email address.
• Message was delivered to user’s Spam folder.

Is there something I’ve missed?

EDIT: I’ve just noticed that user_scores_dsn was not set in /etc/spamassassin/local.cf. I set it appropriately for my system to DBI:mysql:spamassassin:localhost and restarted the spamassassin service, but this made no difference.

EDIT: According to the spamassassin service journal, spamd read scores from SQL and flagged the message as spam, and the spamd result line includes USER_IN_WHITELIST:

Feb 04 15:37:56 [receiving server] spamd[6371]: spamd: connection from ::1 [::1]:49190 to port 783, fd 5
Feb 04 15:37:56 [receiving server] spamd[6371]: spamd: setuid to [destination address] succeeded, reading scores from SQL
Feb 04 15:37:56 [receiving server] spamd[6371]: spamd: processing message <20220204223755.GL28579@[sending server]> for [destination address]:1003
Feb 04 15:37:56 [receiving server] spamd[6412]: util: setuid: ruid=1003 euid=1003 rgid=1001 1001 egid=1001 1001
Feb 04 15:37:56 [receiving server] spamd[6371]: spamd: identified spam (903.6/5.0) for [destination address]:1003 in 0.4 seconds, 881 bytes.
Feb 04 15:37:56 [receiving server] spamd[6371]: spamd: result: Y 903 - DKIM_ADSP_NXDOMAIN,GTUBE,HELO_LH_HOME,NO_DNS_FOR_FROM,RDNS_DYNAMIC,TVD_SPACE_RATIO,USER_IN_WHITELIST scantime=0.4,size=881,user=[destination address],uid=1003,required_score=5.0,rhost=::1,raddr=::1,rport=49190,mid=<20220204223755.GL28579@[sending server]>,autolearn=no autolearn_force=no
Feb 04 15:37:56 [receiving server] spamd[6369]: prefork: child states: II