You can short-circuit this mail and tell me if this is a known bug and I
need to upgrade
:)
(wish there was a CVS repo for amavisd-new, so I could look this
up myself *sigh*).
Environment:
FreeBSD 4.9 i386
amavisd-new-2.2.0
Perl 5.8.5
PostgreSQL 7.3.2
PROBLEM
-------
Anyway, on a particular installation I have the strange behaviour
that for some reason sql_lookup always matches the default (@.) for
certain customers. I have changed the customer domain names, but
the syntax is the same.
The domain is @user-net.com (where user is the obscured part).
In the normal maillog, mail for this customer will NEVER be tagged as SPAM --
it always shows up as "Passed CLEAN", with "Hits: -,"
i.e.:
Feb 14 12:19:06 fw2 amavis[15117]: (15117-01-27) Passed CLEAN, [1.2.3.4]
[1.2.3.4] <[EMAIL PROTECTED]> -> <[EMAIL PROTECTED]>, Message-ID: <[EMAIL
PROTECTED]>, Hits: -, 298 ms
SQL
---
This customer is listed as follows in the SQL table:
amavis=> SELECT * from policy;
id | priority | policy_id | email | fullname
----+----------+-----------+--------------------+----------
1 | 0 | 1 | @. |
[...]
22 | 10 | 2 | @othercustomer.com |
[...]
27 | 10 | 2 | @user-net.com |
amavis=> SELECT id,policy_name as policy,bypass_virus_checks as
vcheck,virus_lover as vlover, banned_files_lover as bfiles, bypass_spam_checks
as byspam,spam_modifies_subj as subj,spam_tag_level as tag1,spam_tag2_level as
tag2,spam_kill_level as kill from policy ;
id | policy | vcheck | vlover | bfiles | byspam | subj | tag1 | tag2 | kill
----+---------+--------+--------+--------+--------+------+------+------+------
1 | Default | Y | Y | Y | Y | | 999 | | 999
2 | Full | N | N | N | N | Y | 3 | 6.3 | 6.3
3 | No Spam | Y | Y | Y | N | Y | 3 | 6.3 | 6.3
4 | 5.5 | N | N | N | N | Y | 3 | 5.5 | 5.5
The problem: mails for @user-net.com NEVER get tagged for SPAM, even though
they match policy 2. Manually running the sql_lookup query for policy:
$sql_select_policy =
'SELECT *,users.id FROM users,policy'
. ' WHERE (users.policy_id=policy.id) AND (users.email IN (%k))'
. ' ORDER BY users.priority DESC';
... where %k == @user-net.com will return the correct info:
id | priority | policy_id | email | fullname | id | policy_name |
bypass_virus_checks | virus_lover | banned_files_lover | bypass_spam_checks |
spam_modifies_subj | spam_tag_level | spam_tag2_level | spam_kill_level | id
----+----------+-----------+--------------+----------+----+-------------+---------------------+-------------+--------------------+--------------------+--------------------+----------------+-----------------+-----------------+----
27 | 10 | 2 | @user-net.com | | 2 | 6.3 | N
| N | N | N | Y
| 3 | 6.3 | 6.3 | 27
Of course in reality it looks up severel possibilities:
Feb 14 12:07:05 fw2 amavis[14949]: (14949-01-17) lookup_sql "[EMAIL
PROTECTED]", query keys: "[EMAIL PROTECTED]", "@user-net.com",
"@.user-net.com", "@.com", "@."
Feb 14 12:07:05 fw2 amavis[14949]: (14949-01-17) lookup_sql select: SELECT
*,users.id FROM users,policy WHERE (users.policy_id=policy.id) AND (users.email
IN (?,?,?,?,?)) ORDER BY users.priority DESC
Running this query manually:
amavis=> SELECT *,users.id FROM users,policy WHERE (users.policy_id=policy.id)
AND (users.email IN ('[EMAIL PROTECTED]', '@user-net.com', '@.user-net.com',
'@.com', '@.')) ORDER BY users.priority DESC;
id | priority | policy_id | email | fullname | id | policy_name |
bypass_virus_checks | virus_lover | banned_files_lover | bypass_spam_checks |
spam_modifies_subj | spam_tag_level | spam_tag2_level | spam_kill_level | id
----+----------+-----------+--------------+----------+----+-------------+---------------------+-------------+--------------------+--------------------+--------------------+----------------+-----------------+-----------------+----
27 | 10 | 2 | @user-net.com | | 2 | 6.3 | N
| N | N | N | Y
| 3 | 6.3 | 6.3 | 27
1 | 0 | 1 | @. | | 1 | Default | Y
| Y | Y | Y |
| 999 | | 999 | 1
Ok, so we have two results: @user-net.com, priority 10, and default with
priority 0 (@.).
And below the result -- it takes the default (@.):
Feb 14 12:07:05 fw2 amavis[14949]: (14949-01-17) lookup_sql([EMAIL PROTECTED])
matches, result=(id=>"1", priority=>"0", policy_id=>"1", email=>"@.",
fullname=>-, local=>"N", id=>"1", policy_name=>"Default", virus_lover=>"Y",
spam_lover=>"Y", banned_files_lover=>"Y", bad_header_lover=>"Y",
bypass_virus_checks=>"Y", bypass_spam_checks=>"Y", bypass_banned_checks=>"Y",
bypass_header_checks=
>"Y", spam_modifies_subj=>"N", virus_quarantine_to=>-, spam_quarantine_to=>-,
>banned_quarantine_to=>-, bad_header_quarantine_to=>-, spam_tag_level=>"999",
>spam_tag2_level=>"999", spam_kill_level=> "999", spam_dsn_cutoff_level=>"999",
>addr_extension_virus=>-, addr_extension_spam=>-, addr_extension_banned=>-,
>addr_extension_bad_header=>-, id=>"1")
--> Only one result is logged! The manual query gives TWO results...
Note that at this point, no results are cached for this particular
recipient, as I just restarted amavisd to be sure.
MORE TESTS
----------
The weird stuff:
- I tried inserting @usernet.com: the result is the same...
- what about other domains ?
Feb 14 12:06:52 fw2 amavis[14948]: (14948-01-16) lookup_sql "[EMAIL
PROTECTED]", query keys: "[EMAIL PROTECTED]", "[EMAIL PROTECTED]",
"@othercustomer.com", "@.othercustomer.com", "@.com", "@."
Feb 14 12:06:52 fw2 amavis[14948]: (14948-01-16) lookup_sql select: SELECT
*,users.id FROM users,policy WHERE (users.policy_id=policy.id) AND (users.email
IN (?,?,?,?,?,?)) ORDER BY users.priority DESC
---> TWO results are returned... and the mail is correctly scored...
Feb 14 12:06:52 fw2 amavis[14948]: (14948-01-16) lookup_sql([EMAIL PROTECTED])
matches, result=(id=>"22", priority=>"10", policy_id=>"2",
email=>"@othercustomer.com", fullname=>"", local=>"Y", id=>"22",
policy_name=>"Full", virus_lover=>"N", spam_lover=>"N",
banned_files_lover=>"N", bad_header_lover=>"N", bypass_virus_checks=>"N",
bypass_spam_checks=>"N", bypass_banned_checks=>"N", bypass_header_checks=>"N",
spam_modifies_subj=>"Y", virus_quarantine_to=>-, spam_quarantine_to=>-,
banned_quarantine_to=>-, bad_header_quarantine_to=>-, spam_tag_level=>"2",
spam_tag2_level=>"6.31", spam_kill_level=>"6.31", spam_dsn_cutoff_level=>"10",
addr_extension_virus=>-, addr_extension_spam=>-, addr_extension_banned=>-,
addr_extension_bad_header=>-, id=>"22")
Feb 14 12:06:52 fw2 amavis[14948]: (14948-01-16) lookup_sql([EMAIL PROTECTED])
matches, result=(id=>"1", priority=>"0", policy_id=>"1", email=>"@.",
fullname=>-, local=>"N", id=>"1", policy_name=>"Default", virus_lover=>"Y",
spam_lover=>"Y", banned_files_lover=>"Y", bad_header_lover=>"Y",
bypass_virus_checks=>"Y", bypass_spam_checks=>"Y", bypass_banned_checks=>"Y",
bypass_header_checks=>"Y",
spam_modifies_subj=>"N", virus_quarantine_to=>-, spam_quarantine_to=>-,
banned_quarantine_to=>-, bad_header_quarantine_to=>-, spam_tag_level=>"999",
spam_tag2_level=>"999", spam_kill_level=>"999" , spam_dsn_cutoff_level=>"999",
addr_extension_virus=>-, addr_extension_spam=>-, addr_extension_banned=>-,
addr_extension_bad_header=>-, id=>"1")
Any ideas ?
-------------------------------------------------------
This SF.net email is sponsored by: Splunk Inc. Do you grep through log files
for problems? Stop! Download the new AJAX search engine that makes
searching your log files as easy as surfing the web. DOWNLOAD SPLUNK!
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=103432&bid=230486&dat=121642
_______________________________________________
AMaViS-user mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/amavis-user
AMaViS-FAQ:http://www.amavis.org/amavis-faq.php3
AMaViS-HowTos:http://www.amavis.org/howto/
