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

[Tobias Franzén]

Hello,

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 
recipient
    FROM
     (SELECT m.username AS target_username,
             m.local_part AS recipient_user,
             m.domain AS recipient_domain
      FROM mailbox AS m
      WHERE m.active=1
      UNION
      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 m.active=1 AND a.active=1
     ) AS aggregate
   LEFT JOIN
     (SELECT SUBSTRING(a1.goto,2) AS recipient_domain,
             SUBSTRING(a1.address,2) AS alias_domain
      FROM alias AS a1
      WHERE a1.active=1 AND SUBSTRING(a1.address,1,1)="@"
      UNION
      SELECT d1.domain AS recipient_domain,
             d1.domain AS alias_domain
      FROM domain AS d1
      WHERE d1.active=1 AND d1.backupmx=0
      UNION
      SELECT d2.target_domain AS recipient_domain,
             d2.alias_domain AS alias_domain
      FROM alias_domain AS d2
      WHERE d2.active=1
     ) 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 mail.example.com /usr/local/sbin/amavisd[39930]: 
(39930-01) lookup_sql_field(sa_userconf) rec=0, "hostmaster at example.com" 
result: undef
Apr 30 01:21:04.961 mail.example.com /usr/local/sbin/amavisd[39930]: 
(39930-01) lookup_sql_field, no such fields: sa_userconf
Apr 30 01:21:04.961 mail.example.com /usr/local/sbin/amavisd[39930]: 
(39930-01) query_keys: cached hostmaster at example.com
Apr 30 01:21:04.961 mail.example.com /usr/local/sbin/amavisd[39930]: 
(39930-01) lookup_hash(hostmaster at example.com) matches key ".", result=sql:
Apr 30 01:21:04.961 mail.example.com /usr/local/sbin/amavisd[39930]: 
(39930-01) lookup [sa_userconf] => true,  "hostmaster at example.com" 
matches, result="sql:", matching_key="."
Apr 30 01:21:04.961 mail.example.com /usr/local/sbin/amavisd[39930]: 
(39930-01) lookup_sql_field(sa_username) rec=0, "hostmaster at example.com" 
result: "mailbox at example.com"
Apr 30 01:21:04.961 mail.example.com /usr/local/sbin/amavisd[39930]: 
(39930-01) lookup [sa_username] => true,  "hostmaster at example.com" 
matches, result="mailbox at example.com", matching_key="/cached/"
Apr 30 01:21:04.962 mail.example.com /usr/local/sbin/amavisd[39930]: 
(39930-01) SA user config: "sql:mailbox at example.com", username: 
"mailbox at example.com", 0, (0)hostmaster at example.com
Apr 30 01:21:04.962 mail.example.com /usr/local/sbin/amavisd[39930]: 
(39930-01) SA user config: saving SA user config
Apr 30 01:21:04.962 mail.example.com /usr/local/sbin/amavisd[39930]: 
(39930-01) SA dbg: config: copying current conf to backup
Apr 30 01:21:05.001 mail.example.com /usr/local/sbin/amavisd[39930]: 
(39930-01) loading SA user config from SQL mailbox at example.com
Apr 30 01:21:05.002 mail.example.com /usr/local/sbin/amavisd[39930]: 
(39930-01) SA dbg: config: Conf::SQL: executing SQL: SELECT preference, 
value FROM sauserprefs WHERE username = 'mailbox at example.com' OR 
username = '$GLOBAL' OR username = CONCAT('%','example.com') ORDER BY 
username ASC
Apr 30 01:21:05.002 mail.example.com /usr/local/sbin/amavisd[39930]: 
(39930-01) SA dbg: config: retrieving prefs for mailbox at example.com from 
SQL server
Apr 30 01:21:05.003 mail.example.com /usr/local/sbin/amavisd[39930]: 
(39930-01) SA user config: switching SA (0) username "vscan" -> 
"mailbox at example.com"
Apr 30 01:21:05.003 mail.example.com /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 
(from secrets.cf.)

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 mail.example.com /usr/local/sbin/amavisd[39929]: 
(39929-06) lookup [spam_tag2_level] => true,  "hostmaster at example.com" 
matches, result="6.2", matching_key="(constant:6.2)"
Apr 30 01:44:08.664 mail.example.com /usr/local/sbin/amavisd[39929]: 
(39929-06) lookup [spam_subject_tag2] => true,  "hostmaster at example.com" 
matches, result="*** Spam [_SCORE_] *** ", matching_key="(constant:*** 
Spam [_SCORE_] *** )"
Apr 30 01:44:08.664 mail.example.com /usr/local/sbin/amavisd[39929]: 
(39929-06) headers CLUSTERING: NEW CLUSTER <hostmaster at example.com>: 
score=1000.986, tag=1, tag2=1, local=1, bl=, s=*** Spam [1000.986] *** , 
mangle=attach
... etc ...
Apr 30 01:44:08.665 mail.example.com /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 
from?

Regards,
Tobias

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 (@example.net -> @example.com), 
> address alias (user_y at example.com -> user_x at example.com), and 
> mailboxes (user_x at example.com).
> 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 master.cf.
> # use userpref SQL connection from SA local.cf 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
>