Jim Cheetham wrote:
> I'm using a sqlite query in a match_domain condition in an ACL for check_rcpt, but it's failing.
>
> Testing from the commandline suggests that I can't do a "where" clause in the SQL query ...
>
Dunno anything about SQLite...
... but Exim is no barrier to a 'proper' SQL 'WHERE' clause - even a complex
one. Providing that the relations have what is sought, of course..
This is in long-term production in PostgreSQL, should work with DB2, Oracle,
MySQL unaltered:
# RCPT_5: IF Destination address in our domain AND active AND valid
user THEN flag for possible acceptance
#
warn
set acl_m10 = ${lookup pgsql{SELECT pg_active from mailprof \
WHERE pg_active AND pg_domain='${quote_pgsql:$domain}' \
AND pg_local_part='${quote_pgsql:$local_part}'}}
Notes:
- pg_active is boolean, acl_m10 follows suit. The variable could be eliminated
in favor of a direct 'accept', but in our case user-specific acceptance criteria
must also be passed, hence the 'warn' verb.
- the 'pg_' prefix to a variable name is 'just me'. EG: pg_domain is my DB
field, $domain is an Exim variable. YMMV.
Now -- all that said ... why do you feel you need the extra:
'if match_domain....' part of the test???
JM2CW, but seems to be advantageous to JFDI in one SQL call AND NOT also ask
Exim to do a 'match' ....
..or the reverse - eg not use SQL for this test at all. Exim reads all manner of
DB just fine on its own and with fewer things to maintain.
HTH,
Bill Hacker
> Here's the DB table, and the working query :-
>> $ sqlite3 -header /etc/exim4/mta.db 'select * from domains'
>> name|status
>> local.dom|active
>> unpaid.dom|inactive
>> good.dom|active
>> bad.dom|blocked
>> ok.dom|active
>>
>> $ sqlite3 /etc/exim4/mta.db 'select name from domains where status="active"'
>> local.dom
>> good.dom
>> ok.dom
>
> Running this through exim -be shows the problem ... only 'ok.dom' should pass, 'bad.dom' should fail.
>> $ exim -be '${if match_domain{ok.dom}{sqlite; /etc/exim4/mta.db select name from domains where status="active";}{yes}{no}}'
>> yes
>> $ exim -be '${if match_domain{bad.dom}{sqlite; /etc/exim4/mta.db select name from domains where status="active";}{yes}{no}}'
>> yes
>
> Any clues as to what I'm doing wrong would be appreciated!
>
> I'm using the standard Ubuntu 9.04 provision of exim4-daemon-heavy :-
>> $ exim -bV
>> Exim version 4.69 #1 built 11-Feb-2009 13:51:47
>> Copyright (c) University of Cambridge 2006
>> Berkeley DB: Berkeley DB 4.6.21: (September 27, 2007)
>> Support for: crypteq iconv() IPv6 PAM Perl Expand_dlfunc GnuTLS move_frozen_messages Content_Scanning Old_Demime
>> Lookups: lsearch wildlsearch nwildlsearch iplsearch cdb dbm dbmnz dnsdb dsearch ldap ldapdn ldapm mysql nis nis0 passwd pgsql sqlite
>> Authenticators: cram_md5 cyrus_sasl dovecot plaintext spa
>> Routers: accept dnslookup ipliteral iplookup manualroute queryprogram redirect
>> Transports: appendfile/maildir/mailstore/mbx autoreply lmtp pipe smtp
>> Fixed never_users: 0
>> Size of off_t: 8
>> Configuration file is /etc/exim4/exim4.conf
>
> -jim
>
>
