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

**URL:** <https://forum.virtualmin.com/t/saving-spamassassin-user-prefs-in-database-am-i-missing-a-step-solved/113882>\
**Category:** Webmin\
**Created:** [February 4, 2022, 10:33pm UTC](https://forum.virtualmin.com/t/saving-spamassassin-user-prefs-in-database-am-i-missing-a-step-solved/113882 "2022-02-04T22:33:02Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![keenmouse](https://cdn.forum.virtualmin.com/user_avatar/forum.virtualmin.com/keenmouse/32/1525_2.png) [@keenmouse](https://forum.virtualmin.com/u/keenmouse)\
**Post date:** [February 4, 2022, 10:33pm UTC](https://forum.virtualmin.com/t/saving-spamassassin-user-prefs-in-database-am-i-missing-a-step-solved/113882/1 "2022-02-04T22:33:02Z")

</div>

| 🛈 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:

```auto
	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`:

```auto
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

```

---

<div class="post-metadata">

**Author:** ![system](https://cdn.forum.virtualmin.com/uploads/default/original/2X/c/cd96d761e326db4b2bef94886a639109bd7a8e76.svg) [@system](https://forum.virtualmin.com/u/system)\
**Post date:** [April 5, 2022, 10:33pm UTC](https://forum.virtualmin.com/t/saving-spamassassin-user-prefs-in-database-am-i-missing-a-step-solved/113882/2 "2022-04-05T22:33:48Z")

</div>

Closed on April 5, 2022, 10:33pm UTC

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.
