Ron White wrote:
> On Wed, 2010-04-28 at 13:44 -0400, W B Hacker wrote:
>> Ron White wrote:
>>> As my ACL grows It has crossed my mind that for every acl stanza making
>>> use of a MySQL that fires off, there is the roundtrip cost of the query.
>>> Whilst some of the lookups can be optimised by ACL layout, I'm not going
>>> to be able to avoid some similar very similar queries when I check my
>>> database for certain flags being set on a per user basis.
>>>
>>> I spotted that there were a couple of sets of variables available to use
>>> in ACL's. The per session '$acl_c0-9' and (near enough 'per rcpt')
>>> $acl_m0-9
>>>
>>> Probably a simple question, but I can't find a relate-able example in
>>> the 'query-style lookups' chunk of my book. Would it be possible for me
>>> to have a single query that returned multiple results and assign each
>>> result to $acl_m0-> so I can look at them elsewhere in the ACL without
>>> the need to make a costly similar query?
>>>
>>> For example, I understand how this checks for a recipient:
>>> #************************************************
>>> #CHECK USER IS IN OUR LIST OF VALID RECIPIENTS
>>> #************************************************
>>> drop !recipients = ${lookup mysql{MYSQL_RCPT}}
>>> domains = +hosted_domains
>>> message = No such user
>>> log_message = user $local_part@$domain not defined in database
>>>
>>> .. but could I change the query:
>>> MYSQL_RCPT
>>> SELECT email FROM mailusers WHERE email='${quote_mysql:$local_part}@
>>> ${quote_mysql:$domain}'
>>>
>>> To something like this:
>>>
>>> SELECT email,usertype,inbound,outbound FROM mailusers WHERE
>>> email='${quote_mysql:$local_part}@${quote_mysql:$domain}';
>>>
>>> and actually access the other items? ($0-> ???). Would this break
>>> anything ? any pitfalls ? even possible ?
>>>
>>> Again, my thanks to anyone who has time to cast an eye over it.
>>>
>>>
>> Almost forget...
>>
> Thanks Bill.
>> If using that 'drop' you illustrated above, either insure that 'postmaster@',
>> abuse@', et al are IN THE DB as well as on the system..
> Once I iron out the wrinkles I'm most likely going to 'hard' code that
> in the runtime config. It's not in production yet :-)
>
> I'm just not clear on how (or where) the returned fields can be
> recovered from when I run a query that returns multiple fields. I'm sure
> I read something on it, but I can't find it again.
>
> Anyway, I'll attack it again tomorrow and carry out some experiments
> (CUE EVIL LAUGH.....)
>
>
>
>
Mostly the placing of the field called adjacent to the variable 'seeking
satisfaction' with the right attention to the brackets, quotes, and nesting /
concatenation, 'JFW'.
Example from a transport that drops dodgy messages into a quarantine folder and
has to determine or construct WHICH such folder from DB info. In this case,
'pg_login_name' is not necessarily an smtp critter - think of it as an internal
'master' identity not related to any specific e-mail address ... except, of
course, by the DB...
quarantine_delivery:
<the usuals redacted>
directory = /${lookup pgsql{SELECT pg_mailroot FROM mailprof \
WHERE pg_local_part='${local_part}' \
AND pg_domain='${domain}' LIMIT 1}}/\
${lookup pgsql{SELECT pg_login_name FROM mailprof \
WHERE pg_local_part='${local_part}' AND pg_domain='${domain}' \
LIMIT 1}}/\
${lookup pgsql{SELECT pg_maildir FROM mailprof \
WHERE pg_local_part='${local_part}' AND pg_domain='${domain}' \
LIMIT 1}}/.Suspect\
NB: the final newline AND a blank line subsequent save headaches.
Another approach is to use a SELECT <field_name> AS <variable-bucket>
into which it is to be placed. My Exim has not (yet) needed that, but Dovecot
has, as in:
user_query = SELECT pg_mailroot||'/'||pg_login_name||'/Maildir' AS mail, pg_uid
AS uid, pg_gid AS gid FROM <redacted> WHERE <redacted>
BTW - as should now be obvious, I (no longer) practice what I preach w/r
reducing SQL calls by use of stuffed acl_m.
As PostgreSQL can't even be detected breaking a sweat I actually do more of the
reverse - making SQL calls instead of using Exim built-ins.
Not really a good example to others - but SQL is just easier for me, and the
server doesn't get to vote.
Bill
