[AMaViS-user] Looking up @local_domains_maps in sql

[Adi Linden]

Hi,

I am pretty new to amavis-new. It took a few hours to realize that a
recipients domain has to be listed in @local_domains_maps in order to have
spam related headers attached. The machine amavis-new is installed on
keeps all domain and user information in a mysql database. Amongst things,
there is a domain table that contains one line for each hosted domain.

How can I get the domains from this table into @local_domains_maps?

Thanks,
Adi

-------------------------------------------------------
This SF.net email is sponsored by: IT Product Guide on ITManagersJournal
Use IT products in your business? Tell us what you think of them. Give us
Your Opinions, Get Free ThinkGeek Gift Certificates! Click to find out more
http://productguide.itmanagersjournal.com/guidepromo.tmpl
_______________________________________________
AMaViS-user mailing list
AMaVi...@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/amavis-user
AMaViS-FAQ:http://www.amavis.org/amavis-faq.php3
AMaViS-HowTos:http://www.amavis.org/howto/

[Mark Martinec]

Adi,

> I am pretty new to amavis-new. It took a few hours to realize that a
> recipients domain has to be listed in @local_domains_maps in order to have
> spam related headers attached. The machine amavis-new is installed on
> keeps all domain and user information in a mysql database. Amongst things,
> there is a domain table that contains one line for each hosted domain.
> How can I get the domains from this table into @local_domains_maps?

If you also use SQL for amavisd-new lookups (README_FILES/README.lookups)
then the field 'local' is already there (implied or explicit) and there
is no need to also provide a static @local_domains_maps.

If this is not the case, you can dump your sql table into a file (one domain
per line) and load it into an associative array (hash) during amavisd start,
by specifying something like:

@local_domains_maps = ( read_hash("$MYHOME/local_domains") );

as suggested in the example of amavisd.conf-sample.

If you have some dexterity in Perl, you could also directly
do a sql SELECT and feed the results into a hash by a few-liner
code in amavisd.conf (this file is just a normal Perl program,
it is not limited to simple assignments to config variables).

Mark

[Adi Linden]

> If this is not the case, you can dump your sql table into a file (one domain
> per line) and load it into an associative array (hash) during amavisd start,
> by specifying something like:
>
> @local_domains_maps = ( read_hash("$MYHOME/local_domains") );

I looked at this method. The problem is that changes are not dynamically
propagated to amavis. So I would have to write an external script that
queries the sql database, determines if there has been any change and
restarts amavis. Then have this run periodically from cron. Doable, but
not my favorite solution.

> If you have some dexterity in Perl, you could also directly
> do a sql SELECT and feed the results into a hash by a few-liner
> code in amavisd.conf (this file is just a normal Perl program,
> it is not limited to simple assignments to config variables).

I don't use perl all that much, quite a few things I still don't know
about. I know how to get the information I want from the database, but I
don't know how to plug the result into the @local_domains_maps
dynamically.

This is a perl script that dumps my local domains to stdout with one
domain per line. Any way to get this into the @local_domains_maps without
writing to a file and referencing the file in @local_domains_maps?

#!/usr/bin/perl
use DBI;
$db_handle = DBI->connect("dbi:mysql:database=postfix;host=127.0.0.1:3306;user=postfix;password=password");
$statement = $db_handle->prepare("SELECT * FROM domain");
$statement->execute();
while ($row_ref = $statement->fetchrow_hashref())
{
print "$row_ref->{domain}\n";
}
$db_handle->disconnect();

Thanks,
Adi

[Mark Martinec]

Adi,

> > @local_domains_maps = ( read_hash("$MYHOME/local_domains") );
> I looked at this method. The problem is that changes are not dynamically
> propagated to amavis. So I would have to write an external script that
> queries the sql database, determines if there has been any change and
> restarts amavis. Then have this run periodically from cron. Doable, but
> not my favorite solution.

Agreed.

> ...I know how to get the information I want from the database,

> but I don't know how to plug the result into the @local_domains_maps
> dynamically.
>
> This is a perl script that dumps my local domains to stdout with one
> domain per line. Any way to get this into the @local_domains_maps without
> writing to a file and referencing the file in @local_domains_maps?

> ... print "$row_ref->{domain}\n";

The low-level answer first (then tell afterwards that it is
perhaps not that useful;)

If you leave @local_domains_maps at its default, which is:
(\%local_domains, \@local_domains_acl, \$local_domains_re),
you can just empty the %local_domains before the loop:
%local_domains = ();
and fill it with values within your loop:
$local_domains{ lc($row_ref->{domain}) } = 1;

The question is where to call this piece of code.
Perhaps the sub child_init_hook would be a good place,
loading the table at every child birth, i.e. every
$max_requests mail messages processed by a child
approximately.

The more general reply would be: doing one SQL SELECT
for every per-recipient parameter is too expensive
(two dozens of parameters). Which is why amavisd-new
does one query per recipient, and collects all relevant
parameters in one SELECT. The 'local' field is one
of these parameters. The result is then cached and the
individual values can be checked very quickly in various
corners of the code. See README.lookups, the SQL section.

This approach is incompatible with what you had in mind.
You may try the above dirty hack, or you may try to
modify the SELECT (adding joins) and/or modify your schema
to let the SELECT access your table with local domains
in the same go with other fields. See $sql_select_policy
in amavisd.conf-sample.

Mark

-------------------------------------------------------
This SF.Net email is sponsored by:
Sybase ASE Linux Express Edition - download now for FREE
LinuxWorld Reader's Choice Award Winner for best database on Linux.
http://ads.osdn.com/?ad_id=5588&alloc_id=12065&op=click

[Adi Linden]

> The question is where to call this piece of code.
> Perhaps the sub child_init_hook would be a good place,
> loading the table at every child birth, i.e. every
> $max_requests mail messages processed by a child
> approximately.

That means I'll have to patch the amavisd perl script direct rather than
doing it as a configuration directive.

> The more general reply would be: doing one SQL SELECT
> for every per-recipient parameter is too expensive
> (two dozens of parameters). Which is why amavisd-new
> does one query per recipient, and collects all relevant
> parameters in one SELECT. The 'local' field is one
> of these parameters. The result is then cached and the
> individual values can be checked very quickly in various
> corners of the code. See README.lookups, the SQL section.

I install my virus scanner on a server-wide basis with server-wide
policies. I don't allow individual user preferences. So I don't have any
SQL queries happening, other than trying to fill @local_domains_maps.
Since all my user data is kept in SQL, I could do the proper SQL lookup
per recipient. But the SQL database does not contain any of the fields
amavis is looking for.

I guess to do this I will have to answer some other questions first. I am
doing virtual domains with postfix. So I have virtual_domain_maps,
virtual_alias_maps, etc defined in postfix. What recipient information
does amavis see? The original recipient from the message envelope or the
recipient after virtual alias expansion? That matters as to whether I need
to lookup recipients in the mailbox table only or the alias table, too.

The README.lookups shows this sample SQL SELECT clause.

$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';

So for my purposes, could I do this to lookup in the usename/mailbox
database?

$sql_select_policy = 'SELECT username FROM mailbox'.
' WHERE (active=1) AND (username IN (%k))';

Or this to find the recipient address in the alias table?

$sql_select_policy = 'SELECT address FROM alias' .
' WHERE (active=1) AND (address IN (%k))';

In both of these cases no useful information is returned to amavid since
the fields amavisd expects do not exist in these tables.

Adi

[Mark Martinec]

Adi,

> > The question is where to call this piece of code.
> > Perhaps the sub child_init_hook would be a good place,

> That means I'll have to patch the amavisd perl script direct

> rather than doing it as a configuration directive.

Indeed.

Most of the code can be wrapped in a subroutine
which can be declared in amavisd.conf if you like,
but the call must be inserted into the amavisd.

> I install my virus scanner on a server-wide basis with server-wide
> policies. I don't allow individual user preferences. So I don't have any
> SQL queries happening, other than trying to fill @local_domains_maps.
> Since all my user data is kept in SQL, I could do the proper SQL lookup
> per recipient. But the SQL database does not contain any of the fields
> amavis is looking for.

I understand. Some fields are required, others are optional.

With 2.2.0 the only required fields are
id, spam_tag_level, spam_tag2_level, spam_kill_level
all the rest have some useful defaults. Although
the spam*_level fields are required, their value can be
undefined (sql NULL), which will fall back to static settings.
A field 'local' can be present, although its default is probably
just fine (README.lookups => sql). The 'id' is only used for
joins with wblist table if enabled - but it must be present.

I believe you can fake these required fields by SQL aliases,
something along the lines of:

select NULL as spam_tag_level, NULL as spam_tag2_level,
NULL as spam_kill_level, ...

> I guess to do this I will have to answer some other questions first. I am
> doing virtual domains with postfix. So I have virtual_domain_maps,
> virtual_alias_maps, etc defined in postfix. What recipient information
> does amavis see? The original recipient from the message envelope or the
> recipient after virtual alias expansion?

Depends on whether you do virtual mapping before or after content filtering,
or both (the Postfix default). See README.postfix, search for 'cleanup'.

> The README.lookups shows this sample SQL SELECT clause.
> $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';
> So for my purposes, could I do this to lookup in the usename/mailbox
> database?
> $sql_select_policy = 'SELECT username FROM mailbox'.
> ' WHERE (active=1) AND (username IN (%k))';

> In both of these cases no useful information is returned to amavid since

> the fields amavisd expects do not exist in these tables.

Yes, something like that, combined with the missing fields as aliases
from my example.

Mark