Amavis / SpamAssassin ignoring userpref in SQL

[Hans van Zijst]

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