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, "[email protected]"
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 [email protected]
Apr 30 01:21:04.961 mail.example.com /usr/local/sbin/amavisd[39930]:
(39930-01) lookup_hash([email protected]) matches key ".", result=sql:
Apr 30 01:21:04.961 mail.example.com /usr/local/sbin/amavisd[39930]:
(39930-01) lookup [sa_userconf] => true, "[email protected]"
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, "[email protected]"
result: "[email protected]"
Apr 30 01:21:04.961 mail.example.com /usr/local/sbin/amavisd[39930]:
(39930-01) lookup [sa_username] => true, "[email protected]"
matches, result="[email protected]", matching_key="/cached/"
Apr 30 01:21:04.962 mail.example.com /usr/local/sbin/amavisd[39930]:
(39930-01) SA user config: "sql:[email protected]", username:
"[email protected]", 0, (0)[email protected]
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 [email protected]
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 = '[email protected]' 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 [email protected] 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" ->
"[email protected]"
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, "[email protected]"
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, "[email protected]"
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 <[email protected]>:
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'^([^@]+@.*)'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
