Whitelisting via SQL lookup to other address book

[Geoff Sweet]

  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