Whitelisting via SQL lookup to other address book

[Patrick Domack]

Quoting Geoff Sweet <gorf at whootis.com>:

> Greetings everyone, I am running amavisd-new 2.6.4.  It's been very  
> stable for me. I would like to try to extend it a little via SQL.  
> The vast majority of my virtual users read email via a web  
> interface. That interface (horde) provides a fairly robust address  
> book system.  I'm hoping that someone could help me understand if it  
> would be possible to tune the $sql_select_white_black_list and  
> %sql_clause to look into that address book database for whitelisting.
>
> I can see in the structure of the table that the addresses in the  
> address book are sorted via the address of the user.  I also can see  
> in the documentation how to build a SQL query that will return the  
> same format of data as described in the README.sql.txt.  What I am  
> not totally clear on is what else I do or don't need to do.
>
> If I add the $sql_select_white_black_list into the amavisd.conf with  
> my query, I then need to add the %sql_clause lines as well?  
> Something like this:
>
> $sql_select_white_black_list = 'SELECT SOME STUFF';
> %sql_clause = (
> 'sel_wblist' => \$sql_select_white_black_list
> );
>
> Correct?  If I understand correctly, anything else in the sql_clause  
> that is left out defaults back to whatever is in the cofig file?  Or  
> do I have to uncomment the entire %sql_clause block and define the  
> entire SQL structure?
>
> Thanks for the help,
>
> -Geoff Sweet

Modify for your own user mapping sql lookup method.

$sql_select_white_black_list =
     "SELECT 'W' as wb from mail.virtual join horde.turba_objects".
     " ON (virtual.address=turba_objects.owner_id)".
     " WHERE (virtual.id=?) AND (turba_objects.object_email IN (%k))";