Hi,
After much trial-and-error I call upon you, my fellow tinkerers. I'm
trying to get Amavis/SpamAssassin to use per-user settings from MySQL,
but for some reason the database is never queried for userprefs.
Running on Debian Bullseye here, Amavisd-new version 2.11.1.
I created my database and pointed SpamAssassin to it, mostly as describe
here:
https://cwiki.apache.org/confluence/display/spamassassin/UsingSQL
After that I created the user '$GLOBAL' with a few settings:
MariaDB [spamassassin]> select * from userpref;
+----------+------------------------------------+-------+--------+
| username | preference | value | prefid |
+----------+------------------------------------+-------+--------+
| $GLOBAL | use_razor2 | 1 | 2 |
| $GLOBAL | use_pyzor | 1 | 3 |
| $GLOBAL | use_dcc | 0 | 4 |
| $GLOBAL | skip_rbl_checks | 1 | 6 |
| $GLOBAL | use_bayes | 1 | 7 |
| $GLOBAL | bayes_auto_learn_threshold_spam | 12.0 | 9 |
| $GLOBAL | bayes_auto_learn_threshold_nonspam | 0.1 | 10 |
| $GLOBAL | required_score | 5 | 11 |
| $GLOBAL | use_bayes_rules | 1 | 12 |
+----------+------------------------------------+-------+--------+
9 rows in set (0.000 sec)
I added the configuration to /etc/spamassassin/local.cf:
user_scores_dsn DBI:mysql:spamassassin:<ip>:3306
user_scores_sql_username spamassassin
user_scores_sql_password <password>
user_scores_sql_custom_query SELECT preference, value FROM userpref
WHERE username = _USERNAME_ OR username = '$GLOBAL' OR username =
CONCAT('%',_DOMAIN_) ORDER BY username ASC
I'm already using bayes with SQL, which works like a charm. Same host
same database, same credentials. I added one user to the bayes_vars
table, user '$GLOBAL'.
For configuring Amavis I used an oldie from this list:
https://www.mail-archive.com/amavis-users@amavis.org/msg00844.html
I configured these two:
@sa_userconf_maps = ({ '.*' => 'sql:' });
@sa_username_maps = new_RE ( [ '.*' => '$GLOBAL' ] );
But no query is ever made for user preferences. I should see something
in the logs like "SA user config:" in my debug log, but alas. Sniffing
traffic to the SQL server shows no single query to table userpref.
What am I missing here?
Kind regards,
Hans van Zijst
