Amavis Mailing List Archive

From: Johannes Peeters (jpeeters@lashout.net)
Date: Fri Dec 31 2004 - 08:28:28 EST

• Previous message: Markus Oestreicher: "Re: [AMaViS-user] Transport maps instead of content_filter"

Hi,

I've build a mail setup with 4 tables:
aliases (domainid, alias, destination, policy)
users (domainid, user, policy)
domains (domainid, domain)
policy (amavis_fields)

Now i'm trying to configure amavisd-new. The problem is an email address
can exist in the aliases table or the users table, so 1 simple sql query
won't work.

I'm using mysql 4.0.7, so subselects or views aren't possible.

I wrote 1 big query to get it working:
$sql_select_policy = 'SELECT DISTINCT policy.*'.
                     ' FROM users, aliases, domains, policy'.
                     ' WHERE ('.
                         ' aliases.domainid = domains.domainid'.
                         ' AND aliases.policyid = policy.policyid'.
                         ' AND ('.
                             ' CONCAT( aliases.alias, \'@\',
domains.domain ) IN (%k)'.
                         ' )'.
                     ' )'.
                     ' OR ('.
                         ' users.domainid = domains.domainid'.
                         ' AND users.policyid = policy.policyid'.
                         ' AND ('.
                             ' CONCAT( users.user, \'@\', domains.domain
) IN (%k)'.
                         ' )'.
                     ' )';

However, the DBI module complaints that it needs 10 replacement strings (5
for every %k), but amavis provides only 5.

Is there someone who is working with this setup?

I tried to edit the amavis code to use this 2 lookups, but my perl skills
aren't that good to get it working.

$sql_select_policy_users = 'SELECT * FROM users,domains,policy'.
  ' WHERE (users.policyid = policy.policyid) AND (users.domainid =
  ' domains.domainid) AND (CONCAT(users.user,\'@\',domains.domain) IN
  ' (%k))';

$sql_select_policy_aliases = 'SELECT * FROM aliases,domains,policy'.
  ' WHERE (aliases.policyid = policy.policyid) AND (aliases.domainid =
  ' domains.domainid) AND (CONCAT(aliases.alias,\'@\',domains.domain) IN
  ' (%k))';

Thanks,
Johannes Peeters

-------------------------------------------------------
The SF.Net email is sponsored by: Beat the post-holiday blues
Get a FREE limited edition SourceForge.net t-shirt from ThinkGeek.
It's fun and FREE -- well, almost....http://www.thinkgeek.com/sfshirt
_______________________________________________
AMaViS-user mailing list
AMaViS-user@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/

• Previous message: Markus Oestreicher: "Re: [AMaViS-user] Transport maps instead of content_filter"