Mark,
First of all thank you for you great insight about the argument..
> > One thing that I can do after I have separated traffic originating
> > inside my network, from the other traffic passing through my mail
> > gateway, is to stop to analyse that traffic agaist viruses.
> >
> > Indeed, I then realized that I would like to continue to scan email
> > originating from my network and destined still to my network.
> >
> > A policy compliant with the my aim could be to omit virus scanning
> > for outbound messages.
>
> |> The idea is to present a bypass_virus_checks='N' for all local
> |> domains, and provide a default of a yes for the rest (either by
> |> an SQL record or by a statical default).
>
> > So,
> >
> > $sql_clause{'sel_policy'} = \$sql_select_policy;
>
> This assignment isn't necesary in a config file, it is already done at
> amavisd initialization time.
>
> > $sql_select_policy = "select policy.virus_lover as virus_lover from
> > (SELECT 1 as virus_lover, domain FROM domain where active=1 and
> domain
> > in (%k)) as policy left join domain on
> (policy.domain=domain.domain)";
> >
> > Could be ok?
> >
> > PS: The query returns:
> > +-------------+
> > | virus_lover |
> > +-------------+
> > | 1 |
> > +-------------+
> > If domain is local and
> >
> > +-------------+
> > | virus_lover |
> > +-------------+
> > Otherwise.
>
> Don't you want just the opposite? You said you wanted all outbound
mail
> to pass without virus checking, which means your local domains
(inbound
> or internal-to-internal mail) need a virus_lover false, and everybody
> else
> (outbound) needs a true,
>
>
> |> Also table names do not matter.
> > $sql_select_policy = "select policy.virus_lover as virus_lover from
>
> As mentioned, table names in the final result do not matter (DBI
module
> strips them off, returning only bare field names), so the above AS
> alias
> is unnecessary, a "SELECT virus_lover FROM ..." suffices.
>
> So now we have a:
>
> SELECT virus_lover FROM
> ( SELECT 0 as virus_lover, domain
> FROM domain WHERE active=1 AND domain IN (%k)
> ) AS policy
> LEFT JOIN domain ON (policy.domain=domain.domain)
>
> As you are not interested in other fields being returned, I don't
> see a need for a LEFT JOIN domain. It appears to me that a simple
> select would do the job as well:
>
> SELECT 0 as virus_lover FROM domain WHERE active=1 AND domain IN (%k)
>
> Now what is still needed is to return a true for a virus_lover
> for everybody else. It could be done by a SQL (a wildcard record for
> a domain '@.'), but in this particular case it is simpler to do
without
> a catchall record and just use a statical lookup table as a fallback,
> e.g.
> push(@virus_lovers_maps, 1);
>
> or the old style, if you prefer:
> %virus_lovers = ('.' => 1);
>
I have assumed that I have I have to take away the
"bypass_virus_check_maps" setting from the policy below:
$inet_socket_port = [10024,10026];
$interface_policy{'10026'} = 'SENDERBYPASS';
$policy_bank{'SENDERBYPASS'} = {
originating => 1,
bypass_virus_checks_maps => [
{
'.conc-bmw.com' => 0,
'.' => 1,
} ],
};
So it become:
$policy_bank{'SENDERBYPASS'} = {
originating => 1,
};
(Is it right to unset "bypass_virus_checks_maps"?)
Then I have added the following config line:
@lookup_sql_dsn =
( ['DBI:mysql:database=mail;host=127.0.0.1;port=3306', 'user',
'password'],);
@storage_sql_dsn = @lookup_sql_dsn; # none, same, or separate database
$sql_select_policy = 'SELECT 0 as virus_lover FROM domain WHERE active=1
AND domain IN (%k)';
%virus_lovers = ('.' => 1);
After restarting amavisd I get error such the following:
Feb 3 15:15:20 av4 postfix/smtp[30345]: AD79E75052E:
to=<[email protected]>, relay=127.0.0.1[127.0.0.1]:10024, delay=2.6,
delays=2.5/0/0.01/0.05, dsn=4.3.2, status=deferred (host
127.0.0.1[127.0.0.1] said: 421 4.3.2 Service shutting down, closing
channel (in reply to RCPT TO command))
While in amavisd-new log I get:
# cat /var/log/amavis | grep "amavis\[483\]: (00483-01)"
amavis[483]: (00483-01) SMTP> 220 [127.0.0.1] ESMTP amavisd-new service
ready
amavis[483]: (00483-01) switch_to_client_time 480 s, smtp response sent
amavis[483]: (00483-01) idle_proc, 4: was busy, 115.6 ms, total idle
0.000 s, busy 0.116 s
amavis[483]: (00483-01) idle_proc, 5: was idle, 0.3 ms, total idle 0.000
s, busy 0.116 s
amavis[483]: (00483-01) SMTP< EHLO myserver.mydomain.tld\r\n
amavis[483]: (00483-01) switch_to_my_time 480 s, SMTP EHLO received
amavis[483]: (00483-01) ESMTP> 250-[127.0.0.1]
amavis[483]: (00483-01) ESMTP> 250-VRFY
amavis[483]: (00483-01) ESMTP> 250-PIPELINING
amavis[483]: (00483-01) ESMTP> 250-SIZE
amavis[483]: (00483-01) ESMTP> 250-ENHANCEDSTATUSCODES
amavis[483]: (00483-01) ESMTP> 250-8BITMIME
amavis[483]: (00483-01) ESMTP> 250-DSN
amavis[483]: (00483-01) ESMTP> 250 XFORWARD NAME ADDR PROTO HELO
amavis[483]: (00483-01) switch_to_client_time 480 s, smtp response sent
amavis[483]: (00483-01) idle_proc, 6: was busy, 2.2 ms, total idle 0.000
s, busy 0.118 s
amavis[483]: (00483-01) idle_proc, 5: was idle, 0.4 ms, total idle 0.001
s, busy 0.118 s
amavis[483]: (00483-01) ESMTP< XFORWARD NAME=smtpd4.aruba.it
ADDR=62.149.128.209\r\n
amavis[483]: (00483-01) switch_to_my_time 480 s, SMTP XFORWARD
received
amavis[483]: (00483-01) ESMTP> 250 2.5.0 Ok XFORWARD
amavis[483]: (00483-01) switch_to_client_time 480 s, smtp response sent
amavis[483]: (00483-01) idle_proc, 6: was busy, 1.5 ms, total idle 0.001
s, busy 0.119 s
amavis[483]: (00483-01) idle_proc, 5: was idle, 0.2 ms, total idle 0.001
s, busy 0.119 s
amavis[483]: (00483-01) ESMTP< XFORWARD PROTO=SMTP
HELO=smtp5.aruba.it\r\n
amavis[483]: (00483-01) switch_to_my_time 480 s, SMTP XFORWARD
received
amavis[483]: (00483-01) ESMTP> 250 2.5.0 Ok XFORWARD
amavis[483]: (00483-01) switch_to_client_time 480 s, smtp response sent
amavis[483]: (00483-01) idle_proc, 6: was busy, 1.1 ms, total idle 0.001
s, busy 0.120 s
amavis[483]: (00483-01) idle_proc, 5: was idle, 0.2 ms, total idle 0.001
s, busy 0.120 s
amavis[483]: (00483-01) ESMTP< MAIL FROM:<[email protected]>
SIZE=355987\r\n
amavis[483]: (00483-01) switch_to_my_time 480 s, SMTP MAIL received
amavis[483]: (00483-01) check_mail_begin_task: task_count=1
amavis[483]: (00483-01) TempDir::prepare: creating directory
/var/amavis/scan/amavis-20090203T152424-00483
amavis[483]: (00483-01) TempDir::prepare_file: creating file
/var/amavis/scan/amavis-20090203T152424-00483/email.txt
amavis[483]: (00483-01) TempDir::prepare_file: layers: unix,perlio
amavis[483]: (00483-01) lookup_ip_acl (mynetworks):
key="62.149.128.209", no match
amavis[483]: (00483-01) lookup (debug_sender) => undef,
"[email protected]" does not match
amavis[483]: (00483-01) ESMTP> 250 2.1.0 Sender
<[email protected]> OK
amavis[483]: (00483-01) switch_to_client_time 480 s, smtp response sent
amavis[483]: (00483-01) idle_proc, 6: was busy, 4.8 ms, total idle 0.001
s, busy 0.125 s
amavis[483]: (00483-01) idle_proc, 5: was idle, 0.2 ms, total idle 0.001
s, busy 0.125 s
amavis[483]: (00483-01) ESMTP< RCPT TO:<[email protected]>
ORCPT=rfc822;[email protected]\r\n
amavis[483]: (00483-01) switch_to_my_time 480 s, SMTP RCPT received
amavis[483]: (00483-01) lookup_acl([email protected]), no
match
amavis[483]: (00483-01) lookup (local_domains) => undef,
"[email protected]" does not match
amavis[483]: (00483-01) query_keys: [email protected],
@recipdomain.tld, @.recipdomain.tld, @.it, @.
amavis[483]: (00483-01) lookup_sql "[email protected]",
query args: "[email protected]", "@recipdomain.tld",
"@.recipdomain.tld", "@.it", "@."
amavis[483]: (00483-01) lookup_sql select: SELECT 0 as virus_lover FROM
domain WHERE active=1 AND domain IN (?,?,?,?,?)
amavis[483]: (00483-01) sql begin, nontransaction
amavis[483]: (00483-01) Connecting to SQL database server
amavis[483]: (00483-01) connect_to_sql: trying
'DBI:mysql:database=postfix;host=127.0.0.1;port=3306'
amavis[483]: (00483-01) connect_to_sql:
'DBI:mysql:database=postfix;host=127.0.0.1;port=3306' succeeded
amavis[483]: (00483-01) sql: preparing and executing: SELECT 0 as
virus_lover FROM domain WHERE active=1 AND domain IN (?,?,?,?,?)
amavis[483]: (00483-01) lookup_sql, "[email protected]" no
match
amavis[483]: (00483-01) lookup_sql_field(message_size_limit),
"[email protected]" no matching records
amavis[483]: (00483-01) lookup (message_size_limit) => undef,
"[email protected]" does not match
amavis[483]: (00483-01) ESMTP> 250 2.1.5 Recipient
<[email protected]> OK
amavis[483]: (00483-01) switch_to_client_time 480 s, smtp response sent
amavis[483]: (00483-01) idle_proc, 6: was busy, 9.5 ms, total idle 0.001
s, busy 0.135 s
amavis[483]: (00483-01) idle_proc, 5: was idle, 0.2 ms, total idle 0.002
s, busy 0.135 s
amavis[483]: (00483-01) ESMTP< DATA\r\n
amavis[483]: (00483-01) switch_to_my_time 480 s, SMTP DATA received
amavis[483]: (00483-01) ESMTP::10024
/var/amavis/scan/amavis-20090203T152424-00483:
<[email protected]> -> <[email protected]>
SIZE=355987 Received: from myserver.mydomain.tld ([127.0.0.1]) by
localhost (myserver.stt.vir [127.0.0.1]) (amavisd-new, port 10024) with
ESMTP for <[email protected]>; Tue, 3 Feb 2009 15:24:24
+0100 (CET)
amavis[483]: (00483-01) ESMTP> 354 End data with <CR><LF>.<CR><LF>
amavis[483]: (00483-01) switch_to_client_time 480 s, smtp response sent
amavis[483]: (00483-01) switch_to_client_time 480 s, receiving data
amavis[483]: (00483-01) switch_to_my_time 480 s, data-end received
amavis[483]: (00483-01) ESMTP< .<CR><LF>
amavis[483]: (00483-01) lookup_sql_field(local),
"[email protected]" no matching records
amavis[483]: (00483-01) lookup_acl([email protected]), no
match
amavis[483]: (00483-01) lookup (local_domains) => undef,
"[email protected]" does not match
amavis[483]: (00483-01) body type: unlabeled, good (h=0, b=0)
amavis[483]: (00483-01) body hash: 8ec750b0aa38e1b1e9ad006b8cd9eab3
amavis[483]: (00483-01) Original mail size: 355987; quota set to:
177993500 bytes
amavis[483]: (00483-01) sql begin, nontransaction
amavis[483]: (00483-01) sql: preparing and executing: SELECT id FROM
maddr WHERE email=?
amavis[483]: (00483-01) prolong_timer check done: remaining time = 480 s
amavis[483]: (00483-01) sending SMTP response: "451 4.5.0 Error in
processing, id=00483-01, sql-enter FAILED: sql exec: err=1146, S1000,
DBD::mysql::st execute failed: Table 'postfix.maddr' doesn't exist at
(eval 66) line 169, <GEN11> line 21."
amavis[483]: (00483-01) ESMTP> 451 4.5.0 Error in processing,
id=00483-01, sql-enter FAILED: sql exec: err=1146, S1000, DBD::mysql::st
execute failed: Table 'postfix.maddr' doesn't exist at (eval 66) line
169, <GEN11> line 21.
amavis[483]: (00483-01) switch_to_client_time 480 s, smtp response sent
amavis[483]: (00483-01) TIMING [total 277 ms] - sql-prepare: 69 (25%)25,
SMTP greeting: 46 (17%)42, SMTP EHLO: 2 (1%)43, SMTP pre-MAIL: 4 (1%)44,
mkdir tempdir: 1 (0%)44, create email.txt: 1 (0%)45, sql-connect: 9
(3%)48, lookup_sql: 2 (1%)49, SMTP pre-DATA-flush: 3 (1%)50, SMTP DATA:
49 (18%)68, check_init: 1 (1%)68, digest_hdr: 1 (0%)68, digest_body: 3
(1%)70, SMTP pre-response: 50 (18%)88, SMTP response: 33 (12%)100,
rundown: 0 (0%)100
amavis[483]: (00483-01) idle_proc, 6: was busy, 140.9 ms, total idle
0.002 s, busy 0.276 s
amavis[483]: (00483-01) idle_proc, 5: was idle, 334.5 ms, total idle
0.336 s, busy 0.276 s
amavis[483]: (00483-01) ESMTP< RSET\r\n
amavis[483]: (00483-01) switch_to_my_time 480 s, SMTP RSET received
amavis[483]: (00483-01) ESMTP> 250 2.0.0 Ok RSET
amavis[483]: (00483-01) switch_to_client_time 480 s, smtp response sent
amavis[483]: (00483-01) idle_proc, 6: was busy, 1.0 ms, total idle 0.336
s, busy 0.276 s
amavis[483]: (00483-01) idle_proc, 5: was idle, 0.3 ms, total idle 0.336
s, busy 0.276 s
amavis[483]: (00483-01) ESMTP< XFORWARD NAME=mx1.calzedonia.com
ADDR=81.118.252.138\r\n
amavis[483]: (00483-01) switch_to_my_time 480 s, SMTP XFORWARD
received
amavis[483]: (00483-01) ESMTP> 250 2.5.0 Ok XFORWARD
amavis[483]: (00483-01) switch_to_client_time 480 s, smtp response sent
amavis[483]: (00483-01) idle_proc, 6: was busy, 0.9 ms, total idle 0.336
s, busy 0.277 s
amavis[483]: (00483-01) idle_proc, 5: was idle, 0.1 ms, total idle 0.336
s, busy 0.277 s
amavis[483]: (00483-01) ESMTP< XFORWARD PROTO=ESMTP
HELO=mx1.calzedonia.com\r\n
amavis[483]: (00483-01) switch_to_my_time 480 s, SMTP XFORWARD
received
amavis[483]: (00483-01) ESMTP> 250 2.5.0 Ok XFORWARD
amavis[483]: (00483-01) switch_to_client_time 480 s, smtp response sent
amavis[483]: (00483-01) idle_proc, 6: was busy, 0.6 ms, total idle 0.336
s, busy 0.278 s
amavis[483]: (00483-01) idle_proc, 5: was idle, 0.1 ms, total idle 0.337
s, busy 0.278 s
amavis[483]: (00483-01) ESMTP< MAIL FROM:<[email protected]>
SIZE=1600\r\n
amavis[483]: (00483-01) switch_to_my_time 480 s, SMTP MAIL received
Infact when I uncomment @lookup_sql_dsn I enable sql Amavisd-new logging
in amavis database.. But I need to lookup for domain on mail database..
what I have to do?
>
> Btw, I assume your SQL table 'domain' really contains only domain
names
> and not also some individual full email addresses, as otherwise you'd
> need to introduce a priority filed and sort on it.
>
> Make sure to have the $sql_lookups_no_at_means_domain set correctly:
> release notes:
> - added global configuration variables $sql_lookups_no_at_means_domain
> and
> $ldap_lookups_no_at_means_domain, both false by default. They
control
> whether a database mail address field with no '@' character
> represents
> a local username, or a domain name. By default (value false) it
> indicates
> a username in SQL and LDAP lookups (but represents a domain in hash
> and
> acl lookups), so domain names in SQL and LDAP should be specified as
> '@domain'. Setting these to true will cause 'xxx' to be interpreted
> as
> a domain name, just like in hash or acl lookups, which may
facilitate
> interoperability with databases from other applications;
>
> Set it to true:
> $sql_lookups_no_at_means_domain = 1;
> if your domains in SQL table look like 'example.com' or
'.example.com'.
>
> Set it to false (or leave at a default), if your domains in SQL table
> look like '@example.com' or '@.example.com'.
My domain are stored in domain field of domain table with the following
format:
- domain.tld
- subdomain. domain.tld
So I have ti set:
$sql_lookups_no_at_means_domain = 1;
Thanks,
rocsca
------------------------------------------------------------------------------
Create and Deploy Rich Internet Apps outside the browser with Adobe(R)AIR(TM)
software. With Adobe AIR, Ajax developers can use existing skills and code to
build responsive, highly engaging applications that combine the power of local
resources and data with the reach of the web. Download the Adobe AIR SDK and
Ajax docs to start building applications today-http://p.sf.net/sfu/adobe-com
_______________________________________________
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/
