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

[Tobias Franzén]

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