How to map domain and address aliases to username/mailbox account for per-user spamassassin bayes filtering?

Tobias Franzén lists.zxinn at
Thu Apr 30 02:03:24 CEST 2015


Today I constructed a SELECT query to extract a mapping between actual 
username, and alias name, from the postfixadmin database.
I use this to map recipient with actual users, and this part appears to 
be working.

For reference, and those who are interested:
$sql_select_policy = 'SELECT aliasmap_all.username AS sa_username FROM
   (SELECT aggregate.target_username AS username,
CONCAT(aggregate.recipient_user,"@",aliasmap_domain.alias_domain) AS 
     (SELECT m.username AS target_username,
             m.local_part AS recipient_user,
             m.domain AS recipient_domain
      FROM mailbox AS m
      SELECT m.username AS target_username,
             SUBSTRING_INDEX(a.address,"@",1) AS recipient_user,
             SUBSTRING_INDEX(a.address,"@",-1) AS recipient_domain
      FROM mailbox AS m, alias AS a
      WHERE a.goto=m.username AND AND
     ) AS aggregate
     (SELECT SUBSTRING(a1.goto,2) AS recipient_domain,
             SUBSTRING(a1.address,2) AS alias_domain
      FROM alias AS a1
      WHERE AND SUBSTRING(a1.address,1,1)="@"
      SELECT d1.domain AS recipient_domain,
             d1.domain AS alias_domain
      FROM domain AS d1
      WHERE AND d1.backupmx=0
      SELECT d2.target_domain AS recipient_domain,
             d2.alias_domain AS alias_domain
      FROM alias_domain AS d2
     ) AS aliasmap_domain
     ON aliasmap_domain.recipient_domain=aggregate.recipient_domain
   ) AS aliasmap_all
WHERE recipient IN (%k)';

The following is from a mail filtered for the hostmaster alias, pointing 
to the actual final destination "mailbox" (in this example).

Apr 30 01:21:04.961 /usr/local/sbin/amavisd[39930]: 
(39930-01) lookup_sql_field(sa_userconf) rec=0, "hostmaster at" 
result: undef
Apr 30 01:21:04.961 /usr/local/sbin/amavisd[39930]: 
(39930-01) lookup_sql_field, no such fields: sa_userconf
Apr 30 01:21:04.961 /usr/local/sbin/amavisd[39930]: 
(39930-01) query_keys: cached hostmaster at
Apr 30 01:21:04.961 /usr/local/sbin/amavisd[39930]: 
(39930-01) lookup_hash(hostmaster at matches key ".", result=sql:
Apr 30 01:21:04.961 /usr/local/sbin/amavisd[39930]: 
(39930-01) lookup [sa_userconf] => true,  "hostmaster at" 
matches, result="sql:", matching_key="."
Apr 30 01:21:04.961 /usr/local/sbin/amavisd[39930]: 
(39930-01) lookup_sql_field(sa_username) rec=0, "hostmaster at" 
result: "mailbox at"
Apr 30 01:21:04.961 /usr/local/sbin/amavisd[39930]: 
(39930-01) lookup [sa_username] => true,  "hostmaster at" 
matches, result="mailbox at", matching_key="/cached/"
Apr 30 01:21:04.962 /usr/local/sbin/amavisd[39930]: 
(39930-01) SA user config: "sql:mailbox at", username: 
"mailbox at", 0, (0)hostmaster at
Apr 30 01:21:04.962 /usr/local/sbin/amavisd[39930]: 
(39930-01) SA user config: saving SA user config
Apr 30 01:21:04.962 /usr/local/sbin/amavisd[39930]: 
(39930-01) SA dbg: config: copying current conf to backup
Apr 30 01:21:05.001 /usr/local/sbin/amavisd[39930]: 
(39930-01) loading SA user config from SQL mailbox at
Apr 30 01:21:05.002 /usr/local/sbin/amavisd[39930]: 
(39930-01) SA dbg: config: Conf::SQL: executing SQL: SELECT preference, 
value FROM sauserprefs WHERE username = 'mailbox at' OR 
username = '$GLOBAL' OR username = CONCAT('%','') ORDER BY 
username ASC
Apr 30 01:21:05.002 /usr/local/sbin/amavisd[39930]: 
(39930-01) SA dbg: config: retrieving prefs for mailbox at from 
SQL server
Apr 30 01:21:05.003 /usr/local/sbin/amavisd[39930]: 
(39930-01) SA user config: switching SA (0) username "vscan" -> 
"mailbox at"
Apr 30 01:21:05.003 /usr/local/sbin/amavisd[39930]: 
(39930-01) SA dbg: info: user has changed

I see that my query for user preferences (sauserprefs table) is loaded 

However, at least two things indicate that the user preferences are not 
properly loaded, or at least not honored once we return to amavis.

The received test spam mail, after "attach" defang, states a required 
score of either 1.0 or 2.0 (this varies from time to time, with the same 
mail and same configuration):
Content analysis details:   (1001.0 points, 2.0 required)

amavis has overwritten the headers with its own, and shows required=6.2 
($sa_tag2_level_deflt=6.2 in amavisd.conf).
X-Spam-Status: Yes, score=1000.986 tagged_above=-100 required=6.2
     tests=[ALL_TRUSTED=-1, GTUBE=1000, PYZOR_CHECK=1.985,
     TVD_SPACE_RATIO=0.001] autolearn=no autolearn_force=no

Amavis also does not honor the "rewrite_header Subject" from 
spamassassin, nor from my user preferences.

This is confusing.

They seem to be mapped from different amavis-internal lookups, and the 
sa_userconf is ignored?

Apr 30 01:44:08.664 /usr/local/sbin/amavisd[39929]: 
(39929-06) lookup [spam_tag2_level] => true,  "hostmaster at" 
matches, result="6.2", matching_key="(constant:6.2)"
Apr 30 01:44:08.664 /usr/local/sbin/amavisd[39929]: 
(39929-06) lookup [spam_subject_tag2] => true,  "hostmaster at" 
matches, result="*** Spam [_SCORE_] *** ", matching_key="(constant:*** 
Spam [_SCORE_] *** )"
Apr 30 01:44:08.664 /usr/local/sbin/amavisd[39929]: 
(39929-06) headers CLUSTERING: NEW CLUSTER <hostmaster at>: 
score=1000.986, tag=1, tag2=1, local=1, bl=, s=*** Spam [1000.986] *** , 
... etc ...
Apr 30 01:44:08.665 /usr/local/sbin/amavisd[39929]: 
(39929-06) fwd: scanner provided a header field X-Spam-Status, but we 
preferred our own

Do I need to write additional queries to match these settings?
Will I need to expand my (already large) query in $sql_select_policy?
Where does the "required score" value seen in the "attach" mangler come 


On 2015-04-29 01:25, Tobias Franzén wrote:
> Hello,
> My main goal is to leverage a header like X-Original-To, which I have 
> come to rely on in my old(yet current) mail server, with as little 
> overhead as possible.
> I've found two options which work, both with their own drawbacks.
> 1) Split incoming mail into 1-per-recipient and add X-Original-To 
> header for each mail with local recipient.
> This first option relies on before-content-filtering address mapping 
> (directly after adding the header) in postfix, which also allows 
> per-user bayes rules filtering in amavisd-new.
> Drawback: Extreme overhead as amavisd-new is called once each for all 
> local and all external recipient addressed in every incoming mail.
> 2) Pass mail through amavisd-new before dividing the mail into 
> 1-per-recipient to properly add X-Original-To header.
> This second option relies on after-content-filtering address mapping.
> Drawback: My current amavisd-new configuration does not know how to 
> apply per-user bayes rules filtering for virtual alias users.
> postfixadmin
> I use postfixadmin as the backend for my mapping of mailboxes and 
> aliases to store all mail recipient configuration.
> This includes "wildcard" domain alias ( ->, 
> address alias (user_y at -> user_x at, and 
> mailboxes (user_x at
> Technically speaking, postfixadmin appears to allows for a 1-to-many 
> relationship for address aliases, however my setup only uses a 1-to-1 
> mapping, which should make what I'm attempting to do much easier.
> postfix
> SQL queries to fetch...
> Address alias: SELECT goto FROM alias WHERE address='%s' AND active='1'
> Domain alias: SELECT domain FROM domain WHERE domain='%s' AND 
> backupmx='0' AND active='1'
> Mailbox: SELECT maildir FROM mailbox WHERE username='%s' AND active='1'
> spamassassin
> I've configured spamassassin to use per-user bayes rules with mysql 
> backend. Also some per-user preferences using sauserprefs plugin from 
> Roundcube webmail.
> amavisd-new
> Integrating clamv and spamassassin filtering. I call on amavisd-new 
> via smtp content filter in postfix
> # use userpref SQL connection from SA for ALL recipients
> @sa_userconf_maps = ({
>   '.' => 'sql:'
> });
> # use recipient email address as _USERNAME_ in userpref mySQL table 
> (_TABLE_)
> @sa_username_maps = new_RE (
>   [ qr'^([^@]+ at .*)'i => '${1}' ]
> );
> Is there a way to expand each email address in amavisd-new to traverse 
> the domain aliases and address aliases to find a match among the 
> mailboxes?
> Perhaps by using a stored procedure in mysql, or views, or virtual 
> tables, or something like that. I'm not yet familiar with these 
> aspects of mysql, and their integration with amavisd-new.
> Or can I somehow pass all actual usernames (where domains aliases and 
> address aliases expand to mailbox accounts) for an email from postfix 
> to amavisd-new without performing before-content-filter address 
> mapping in postfix?
> Although my usecases would involve very few such occurrences, I prefer 
> to not have the potential extreme overhead associated with executing 
> one instance per recipient in multi-recipient emails (both incoming 
> and outgoing). Although for outgoing I can at least bypass amavisd-new 
> in reasonably good conscience.
> Regards,
> Tobias

More information about the amavis-users mailing list