Possible to use sauserpref and MySQL in Roundcube?

1

New to the forums, and fairly new to Mail-in-a-box. My Mail-in-a-box is setup and running well. I’ve done some upgrades successfully, and have been using Roundcube and a couple of local mail clients. All works very well.

One of my requirements for running a self-hosted server, is to allow family with their own accounts to manage their own blocklist_from and safelist_from settings in Spamassassin/Spampd. If this is already available out of the box, please forgive me, but I didn’t see it.

I thought setting up sauserpref as a plugin with Roundcube would be the most elegant way to do this. To that end, I have the plugin installed and functioning in Roundcube without any errors. I have installed DBD:MySQL and setup a config file in /etc/spamassassin/sauserpref.cf:

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

I tried sauserpref.cf both with and without:

loadplugin Mail::SpamAssassin::SQLBasedAddrList

spamassassin -D --lint shows this file is being read in, no errors. Restarting both spampd and spamassassin with systemctl restart spampd spamassassin works without any errors.

If I add blacklist_from myemail@domain.com, as a global settings to the /etc/spamassassin/sauserpref.cf file, emails from that address are successfully blocked. If I attempt to use SQL, after adding myemail@domain.com to the roundcube->sauserpref settings, nothing happens.

In debugging, I have enabled mysql general_log, and tail -f the log file, and can see that spampd/spamassassin doesn’t touch mysql. This would lead me to believe that I am missing something.

Add --sql-config to /etc/defaults/spampd ADDOPTS, results in the error Unknown option: sql-config.

I am aware that all of these settings will be overwritten the next time I upgrade the system or reinitial it. My plan is to write a script to reconfig after each mailinabox update.

The short of it is: Has anyone been successful in getting this to work? Is there another way to let users manager their own spamassassin settings?

Thank you.

2

Some additional information and some progress. I’ve adjusted the /etc/defaults/spamassassin options to:

OPTIONS="--create-prefs --max-children 5 --helper-home-dir --sql-config -x -u debian-spamd"

And then running from the cli:

echo -e "From: myemail@domain.com\nTo:mail@onmailinaboxserver.com\nSubject: Test\n\n" | spampd -u 'mail@onmailinaboxserver.com'

We finally see it is being blocked from the sauserpref.cf settings for MySQL:

X-Spam-Flag: YES
X-Spam-Level: **************************************************
X-Spam-Status: Yes, score=108.1 required=6.0 tests=EMPTY_MESSAGE,HDRS_MISSP,
	MISSING_DATE,MISSING_MID,NO_RECEIVED,NO_RELAYS,PYZOR_CHECK,
	USER_IN_BLACKLIST,USER_IN_BLOCKLIST autolearn=no autolearn_force=no
	version=3.4.6
X-Spam-Report: 
	*  0.0 USER_IN_BLOCKLIST From: user is listed in the block-list
	*  100 USER_IN_BLACKLIST DEPRECATED: See USER_IN_BLOCKLIST
	* -0.0 NO_RELAYS Informational: message was not relayed via SMTP
	*  2.0 PYZOR_CHECK Listed in Pyzor
	*      (https://pyzor.readthedocs.io/en/latest/)
	*  2.3 EMPTY_MESSAGE Message appears to have no textual parts
	*  1.4 MISSING_DATE Missing Date: header
	*  0.1 MISSING_MID Missing Message-Id: header
	* -0.0 NO_RECEIVED Informational: message has no Received headers
	*  2.2 HDRS_MISSP Misspaced headers

Is debian-spamd the correct user for spamassassin?

And of course, using spamc is not using spampd via sending a real email, and sending a real email, spampd/spamassassin still doesn’t read from SQL. I ran into this in iRedMail as well and could not figure out a work-around due to how they launch spamassassin as from amavis, I think?

Suggestions on next steps welcome.

3

And tried enabled ADDOPTS="–saconfig=/etc/spampd.conf in /etc/defaults/spampd and added /etc/spamassassin/sauserpref.cf info to that conf file.

First: There is a bug in /etc/defaults/spampd. The last few lines are:

# The following sample entry enables use of a config file 
# by spampd which can be used to override parameters from
# the system-wide SpamAssassin configuration
#
#ADDOPTS="--config=/etc/spampd.conf" 
ADDOPTS="--maxsize=2000"
#ADDOPTS=""

But, --config isn’t a valid option, it should

#ADDOPTS=“–saconfig=/etc/spampd.conf”

Anyway, still doesn’t work :slight_smile: