Whitelisting via SQL lookup to other address book

[Patrick Domack]

That is cause amavis is looked for a user id, on mine the user-id  
number is returned via the sql_select_policy select. The ? in the  
sql_select_white_black_list is replaced with that user-id number.

Normally the table name sql_select_policy finds it's info is called users.

In my case I have it locate the virtual users table line that postfix  
uses, just return the auto-inc id, then on the white_black_list I join  
those two tables and select via that virtual user table id


Quoting Geoff Sweet <gorf at whootis.com>:

> Hi friends,
> I am still struggling with this a little bit.  So I altered my config with:
>
> 1. added the SQL connectiong information:
> @lookup_sql_dsn =(  
> ['DBI:mysql:database=users_horde;host=sql1.prod.whootis.net;port=3306',  
> 'amavisd', 'somepasswd'] );
>
> 2.
> $sql_clause{ 'sel_wblist'} = "SELECT 'W' as wb FROM turba_objects  
> WHERE  owner_id = ? AND object_email = (%K)";
>
> I understand that the ? is for preparing statements for  
> placeholders, and I assume that the %K is some array passed in from  
> Perl?  Anyway once I get that setup, I get errors like these:
>
> Jun  2 14:16:20 mail1 amavis[20980]: (20980-01) (!)lookup_sql: sql  
> exec: err=1142, S1000, DBD::mysql::st execute failed: SELECT command  
> denied to user 'amavisd'@'mail1.prod.whootis.net' for table 'users'  
> at (eval 69) line 166, <GEN9> line 5., 1142, SELECT command denied  
> to user 'amavisd'@'mail1.prod.whootis.net' for table 'users'
> Jun  2 14:16:20 mail1 amavis[20980]: (20980-01) (!!)TROUBLE in  
> process_request: sql exec: err=1142, S1000, DBD::mysql::st execute  
> failed: SELECT command denied to user  
> 'amavisd'@'mail1.prod.whootis.net' for table 'users' at (eval 69)  
> line 166, <GEN9> line 5. at (eval 70) line 280, <GEN9> line 5.
> Jun  2 14:16:20 mail1 amavis[20980]: (20980-01) (!)Requesting  
> process rundown after fatal error
>
> I know what the problem is - there is no database table called  
> "users".  Maybe I'm just not understanding how this works.  All I  
> want to do is get amavisd to query a pre-existing database table for  
> addresses that match the sender/recipient and whitelist them if it  
> exists. However it seems that when using SQL lookups, amavisd  
> expects the larger schema to already exist.  Is this not as simple  
> as I think?
>
> Thanks loads for everyones help so far.
>
>
> On 5/13/2011 6:37 AM, Mark Martinec wrote:
>> Geoff,
>>
>>> 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,
>> Assigning to $sql_select_white_black_list suffices, as Patrick said.
>>
>>> 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
>>> );
>> No need to, the 'sel_wblist' =>  \$sql_select_white_black_list is a
>> default entry in the %sql_clause hash and need not be changed.
>> Also you don't want to lose other key/value pairs in %sql_clause
>> by replacing the entire hash, while you only wanted to change
>> one key/value pair.
>>
>> If you'd like to get rid of the compatibility measure (indirection
>> through$sql_select_white_black_list ), you may assign your
>> replacement SQL clause directly into $sql_clause{ 'sel_wblist'},
>> and forget about $sql_select_white_black_list, e.g.:
>>
>> $sql_clause{ 'sel_wblist'} =
>>   "SELECT 'W' as wb from mail.virtual join hor...";
>>
>>> 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?
>> No need to. You may if really needed, which it is not in this case.
>>
>>   Mark