Re: score_sender_maps lookup from sql

Still no luck enabling soft black/white lists, any help here
would be 
appreciated.

In general I`m trying to enable soft black/white lists
without putting any 
other configuration in SQL, I have config files for this.

Here is my /etc/amavis/conf.d/50-user	

use strict;

#
# Place your configuration directives here.  They will
override those in
# earlier files.
#
# See /usr/share/doc/amavisd-new/ for documentation and
examples of
# the directives you can use in this file
#

$max_servers = 20;

# Enable mysql lookups
lookup_sql_dsn = (
   ['DBI:mysql:database=amavisd;host=xxx', 'amavisd',
'xxx'],
);
#$sql_select_policy = undef;

#
$sql_select_white_black_list = 1;

#$log_level=5;
# Enable header tags for all domains
$local_domains_re ='.';
local_domains_maps =
 (%local_domains,local_domains_acl,$local_domains_re);

# Enable Virus check
bypass_virus_checks_maps = (
   %bypass_virus_checks, bypass_virus_checks_acl, 
$bypass_virus_checks_re);

# Enable smap check
bypass_spam_checks_maps = (
   %bypass_spam_checks, bypass_spam_checks_acl,
$bypass_spam_checks_re);

$sa_local_tests_only = 0;
$notify_method = 'smtp:127.0.0.1:25';

# Set different policies
$inet_socket_port = [10040,10041];
$interface_policy{'10040'} = 'INCOMING_UNTRUSTED';
$interface_policy{'10041'} = 'OUTGOING_UNTRUSTED';

$policy_bank{'INCOMING_UNTRUSTED'} = {
   # Set spam levels
   spam_tag_level_maps  => [-999.0],
   spam_tag2_level_maps => [6.31], # add 'spam detected'
headers at that level
   spam_kill_level_maps => [16.31], # triggers spam
evasive actions
   final_spam_destiny   => D_PASS,
   forward_method       => 'smtp:127.0.0.1:10126',
};

$policy_bank{'OUTGOING_UNTRUSTED'} = {
   # Set spam levels
   spam_tag_level_maps  => [-999.0],
   spam_tag2_level_maps => [3.31], # add 'spam detected'
headers at that level
   spam_kill_level_maps => [16.31], # triggers spam
evasive actions
   final_spam_destiny   => D_PASS,
   forward_method       => 'smtp:127.0.0.1:25',
};



#------------ Do not modify anything below this line
-------------
1;  # insure a defined return

On Wednesday 08 August 2007 17:14:44 Pavel Georgiev wrote:
> Hi list,
>
> I`m trying to setup amavis-new (2.3.3) with postfix
(2.2.10) on Ubuntu
> 6.06. I have a working setup with two policy banks
defined in the config.
>
> I decided to add a soft black/white lists, but I cannot
find the right
> documentation for this.
>
> I created 4 tables as described here:
> http://infocenter.guardiandigital.com/arc
hive/amavis/2004/Dec/0316.html
>
> and enabled sql lookups with lookup_sql_dsn = ( ... )
>
> I see in the logs that a query is done for policies:
>
>  lookup_sql select: SELECT *,users.id FROM users LEFT
JOIN policy ON
> users.policy_id=policy.id WHERE users.email IN
(?,?,?,?,?,?) ORDER BY
> users.priority DESC
>
> which return no records (I dont need policies in SQL)
and then when wbl
> check is made no actual sql queries are made:
>
> amavis[18915]: (18915-01) wbl: checking sender
<xxx>
> amavis[18915]: (18915-01) lookup_sql_field(id),
"xxx" no matching records
> amavis[18915]: (18915-01) lookup => undef,
"xxx" does not match
> amavis[18915]: (18915-01) wbl: (SQL) recip <xxx>,
0 matches
>
>
> How do I enable sql lookups for score_sender_maps (an
example would be
> appreciated).
>
> In general I dont need any configuration is sql, I only
want to put sender
> addresses (that are global, not per recipient). Is it
possible to have sql
> lookup only for this in a custom sql tables that would
have only two fields
> - address and score. I only care of the envelope sender
is listed in that
> table so that I can get the score for it.
>
> Thanks!
>
>
------------------------------------------------------------
-------------
> This SF.net email is sponsored by: Splunk Inc.
> Still grepping through log files to find problems? 
Stop.
> Now Search log events and configuration files using
AJAX and a browser.
> Download your FREE copy of Splunk now >>  http://get.splunk.com/
> _______________________________________________
> AMaViS-user mailing list
> AMaViS-userlists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/amavis-user

> AMaViS-FAQ:http://www.amav
is.org/amavis-faq.php3
> AMaViS-HowTos:http://www.amavis.org/ho
wto/



------------------------------------------------------------
-------------
This SF.net email is sponsored by: Splunk Inc.
Still grepping through log files to find problems?  Stop.
Now Search log events and configuration files using AJAX and
a browser.
Download your FREE copy of Splunk now >>  http://get.splunk.com/
_______________________________________________
AMaViS-user mailing list
AMaViS-userlists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/amavis-user

AMaViS-FAQ:http://www.amav
is.org/amavis-faq.php3
AMaViS-HowTos:http://www.amavis.org/ho
wto/

OK, I got a solution, I`m not 100% sure that it is right so
I would appreciate 
your comment on the following setup. The goal is to have a
simple global soft 
blacklisting in SQL and keep everything else in text
configuration files. Let 
me know if I`m missing/doing wrong something:


/etc/amavis/conf.d/50-user:
use strict;

#
# Place your configuration directives here.  They will
override those in
# earlier files.
#
# See /usr/share/doc/amavisd-new/ for documentation and
examples of
# the directives you can use in this file
#

$max_servers = 20;

# Enable mysql lookups
lookup_sql_dsn = (
   ['DBI:mysql:database=amavisd;host=xxxx', 'amavisd',
'xxx'],
);

$sql_select_white_black_list = 'SELECT sum(wb) as wb FROM
amavis_wblist WHERE 
email IN (%k)';

# Enable Virus check
bypass_virus_checks_maps = (
   %bypass_virus_checks, bypass_virus_checks_acl, 
$bypass_virus_checks_re);

# Enable smap check
bypass_spam_checks_maps = (
   %bypass_spam_checks, bypass_spam_checks_acl,
$bypass_spam_checks_re);

$sa_local_tests_only = 0;
$notify_method = 'smtp:127.0.0.1:25';

# Set different policies
$inet_socket_port = [10040,10041];
$interface_policy{'10040'} = 'INCOMING_UNTRUSTED';
$interface_policy{'10041'} = 'OUTGOING_UNTRUSTED';

$policy_bank{'INCOMING_UNTRUSTED'} = {
   # Set spam levels
   spam_tag_level_maps  => [-999.0],
   spam_tag2_level_maps => [6.31], # add 'spam detected'
headers at that level
   spam_kill_level_maps => [16.31], # triggers spam
evasive actions
   final_spam_destiny   => D_PASS,
   forward_method       => 'smtp:127.0.0.1:10126',
};

$policy_bank{'OUTGOING_UNTRUSTED'} = {
   # Set spam levels
   spam_tag_level_maps  => [-999.0],
   spam_tag2_level_maps => [3.31], # add 'spam detected'
headers at that level
   spam_kill_level_maps => [16.31], # triggers spam
evasive actions
   final_spam_destiny   => D_PASS,
   forward_method       => 'smtp:127.0.0.1:25',
};



#------------ Do not modify anything below this line
-------------
1;  # insure a defined return



I have for tables as described in README.sql-mysql.txt:
mailaddr
policy
users
wblist

Additionally I have a custom table amavis_wblist which I use
for soft listing:
mysql> describe amavis_wblist;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| email | varchar(255) |      | PRI |         |       |
| wb    | float        | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+


The only data that I have in the first four tables is this
line which should 
match all users and still use the policies defined in the
text conf:
INSERT INTO `users` VALUES (1,1,1,'.','','Y');

This also sets all domains as "local" which is
desired for setting the spam 
headers on all mails.

Any comments are wellcome.

On Monday 20 August 2007 15:39:21 Pavel Georgiev wrote:
> Still no luck enabling soft black/white lists, any help
here would be
> appreciated.
>
> In general I`m trying to enable soft black/white lists
without putting any
> other configuration in SQL, I have config files for
this.
>
> Here is my /etc/amavis/conf.d/50-user
>
> use strict;
>
> #
> # Place your configuration directives here.  They will
override those in
> # earlier files.
> #
> # See /usr/share/doc/amavisd-new/ for documentation and
examples of
> # the directives you can use in this file
> #
>
> $max_servers = 20;
>
> # Enable mysql lookups
> lookup_sql_dsn = (
>    ['DBI:mysql:database=amavisd;host=xxx', 'amavisd',
'xxx'],
> );
> #$sql_select_policy = undef;
>
> #
> $sql_select_white_black_list = 1;
>
> #$log_level=5;
> # Enable header tags for all domains
> $local_domains_re ='.';
> local_domains_maps =
>  (%local_domains,local_domains_acl,$local_domains_re);
>
> # Enable Virus check
> bypass_virus_checks_maps = (
>    %bypass_virus_checks, bypass_virus_checks_acl,
> $bypass_virus_checks_re);
>
> # Enable smap check
> bypass_spam_checks_maps = (
>    %bypass_spam_checks, bypass_spam_checks_acl,
> $bypass_spam_checks_re);
>
> $sa_local_tests_only = 0;
> $notify_method = 'smtp:127.0.0.1:25';
>
> # Set different policies
> $inet_socket_port = [10040,10041];
> $interface_policy{'10040'} = 'INCOMING_UNTRUSTED';
> $interface_policy{'10041'} = 'OUTGOING_UNTRUSTED';
>
> $policy_bank{'INCOMING_UNTRUSTED'} = {
>    # Set spam levels
>    spam_tag_level_maps  => [-999.0],
>    spam_tag2_level_maps => [6.31], # add 'spam
detected' headers at that
> level spam_kill_level_maps => [16.31], # triggers
spam evasive actions
> final_spam_destiny   => D_PASS,
>    forward_method       => 'smtp:127.0.0.1:10126',
> };
>
> $policy_bank{'OUTGOING_UNTRUSTED'} = {
>    # Set spam levels
>    spam_tag_level_maps  => [-999.0],
>    spam_tag2_level_maps => [3.31], # add 'spam
detected' headers at that
> level spam_kill_level_maps => [16.31], # triggers
spam evasive actions
> final_spam_destiny   => D_PASS,
>    forward_method       => 'smtp:127.0.0.1:25',
> };
>
>
>
> #------------ Do not modify anything below this line
-------------
> 1;  # insure a defined return
>
> On Wednesday 08 August 2007 17:14:44 Pavel Georgiev
wrote:
> > Hi list,
> >
> > I`m trying to setup amavis-new (2.3.3) with
postfix (2.2.10) on Ubuntu
> > 6.06. I have a working setup with two policy banks
defined in the config.
> >
> > I decided to add a soft black/white lists, but I
cannot find the right
> > documentation for this.
> >
> > I created 4 tables as described here:
> > http://infocenter.guardiandigital.com/arc
hive/amavis/2004/Dec/0316.html
> >
> > and enabled sql lookups with lookup_sql_dsn = ( ... )
> >
> > I see in the logs that a query is done for
policies:
> >
> >  lookup_sql select: SELECT *,users.id FROM users
LEFT JOIN policy ON
> > users.policy_id=policy.id WHERE users.email IN
(?,?,?,?,?,?) ORDER BY
> > users.priority DESC
> >
> > which return no records (I dont need policies in
SQL) and then when wbl
> > check is made no actual sql queries are made:
> >
> > amavis[18915]: (18915-01) wbl: checking sender
<xxx>
> > amavis[18915]: (18915-01) lookup_sql_field(id),
"xxx" no matching records
> > amavis[18915]: (18915-01) lookup => undef,
"xxx" does not match
> > amavis[18915]: (18915-01) wbl: (SQL) recip
<xxx>, 0 matches
> >
> >
> > How do I enable sql lookups for score_sender_maps
(an example would be
> > appreciated).
> >
> > In general I dont need any configuration is sql, I
only want to put
> > sender addresses (that are global, not per
recipient). Is it possible to
> > have sql lookup only for this in a custom sql
tables that would have only
> > two fields - address and score. I only care of the
envelope sender is
> > listed in that table so that I can get the score
for it.
> >
> > Thanks!
> >
> >
------------------------------------------------------------
-------------
> > This SF.net email is sponsored by: Splunk Inc.
> > Still grepping through log files to find problems?
 Stop.
> > Now Search log events and configuration files
using AJAX and a browser.
> > Download your FREE copy of Splunk now >>  http://get.splunk.com/
> > _______________________________________________
> > AMaViS-user mailing list
> > AMaViS-userlists.sourceforge.net
> > https://lists.sourceforge.net/lists/listinfo/amavis-user

> > AMaViS-FAQ:http://www.amav
is.org/amavis-faq.php3
> > AMaViS-HowTos:http://www.amavis.org/ho
wto/
>
>
------------------------------------------------------------
-------------
> This SF.net email is sponsored by: Splunk Inc.
> Still grepping through log files to find problems? 
Stop.
> Now Search log events and configuration files using
AJAX and a browser.
> Download your FREE copy of Splunk now >>  http://get.splunk.com/
> _______________________________________________
> AMaViS-user mailing list
> AMaViS-userlists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/amavis-user

> AMaViS-FAQ:http://www.amav
is.org/amavis-faq.php3
> AMaViS-HowTos:http://www.amavis.org/ho
wto/



------------------------------------------------------------
-------------
This SF.net email is sponsored by: Splunk Inc.
Still grepping through log files to find problems?  Stop.
Now Search log events and configuration files using AJAX and
a browser.
Download your FREE copy of Splunk now >>  http://get.splunk.com/
_______________________________________________
AMaViS-user mailing list
AMaViS-userlists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/amavis-user

AMaViS-FAQ:http://www.amav
is.org/amavis-faq.php3
AMaViS-HowTos:http://www.amavis.org/ho
wto/