Ah, I guess I was just lucky as it seemed to work before. I'll go ahead and do
some tweaking to the statement, thanks for the advice.
Ryan Moore
----------
Perigee.net Corporation
704-849-8355 (sales)
704-849-8017 (tech)
www.perigee.net
----- Original Message -----
From: Mark Martinec <[EMAIL PROTECTED]>
To: [email protected]
CC: Ryan Moore <[EMAIL PROTECTED]>
Sent: 11/09/2005 11:49:33 AM -0500
Subject: [AMaViS-user] sql whitelist/blacklist priority issues
Ryan,
Since upgrading to amavisd 2.3.3 amavis wasn't returning the correct result
for our user whitelist/blacklist entries in SQL. If a user had [EMAIL PROTECTED]
whitelisted and @aol.com blacklisted, the blacklist entry was matching and
causing the message to be blocked/tagged.
We were running 2.2.1 previously. All entries in the db have the same
priority, which I'm sure is the cause of the problem.
Indeed it is.
You get entries in more or less random order if the sort key
is the same for all of them. One should assign priorities
to entries according to provided examples (or similar).
This can well be done automatically by web GUI when user
enters an address to be blacklisted. Counting dots after @
is probably just fine to assign priority, while string length
is not general enough.
...so I'm not sure why this started happening with the upgrade.
The order was unpredictable before, it is still unpredictable now.
If it worked before, you were just lucky (probably not always).
In any case, I changed the ORDER BY parameter for
$sql_select_white_black_list to just 'length(mailaddr.email)' (in place of
'mailaddr.priority') so that specific email address entries get hit before
domain wide entries. This is so that users could whitelist [EMAIL PROTECTED] but
then blacklist @aol.com. Perhaps amavisd should use 'ORDER BY
mailaddr.priority,length(mailaddr.email) ...' so that full email entries
with the same priority get matched before domain name based entries?
The SQL statements are fully configurable via %sql_clause,
use a more sophisticated sort order if you like.
The [EMAIL PROTECTED] is more more specific than
.informationclearinghouse.info despite being shorter.
While one can get away with a simple length-based logic
of sender address, it is not a general solution and
may lure admins into believing that it works correctly
in all lookups.
Mark
-------------------------------------------------------
SF.Net email is sponsored by:
Tame your development challenges with Apache's Geronimo App Server. Download
it for free - -and be entered to win a 42" plasma tv or your very own
Sony(tm)PSP. Click here to play: http://sourceforge.net/geronimo.php
_______________________________________________
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/
-------------------------------------------------------
SF.Net email is sponsored by:
Tame your development challenges with Apache's Geronimo App Server. Download
it for free - -and be entered to win a 42" plasma tv or your very own
Sony(tm)PSP. Click here to play: http://sourceforge.net/geronimo.php
_______________________________________________
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/
