On Wed, 2010-04-28 at 16:09 -0400, W B Hacker wrote:
> 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
>
>
Thank you both Bill & Jeremy,
This 'experiment' seems to do the trick:
require set acl_m_tmp = ${lookup mysql{SELECT
inbound,quota_st,quota_sz,spamscan,spamscore,virusscan,quarantine,greylist FROM mailusers \
WHERE usertype=0 \
AND active=1 \
AND email='${quote_mysql:$local_part}@${quote_mysql:
$domain}';}{$value} {}}
logwrite = :main: variable contains $acl_m_tmp
set acl_m_inbound = ${extract{inbound}{$acl_m_tmp}}
set acl_m_quota_st = ${extract{quota_st}{$acl_m_tmp}}
set acl_m_quota_sz = ${extract{quota_sz}{$acl_m_tmp}}
set acl_m_spamscan = ${extract{spamscan}{$acl_m_tmp}}
set acl_m_spamscore = ${extract{spamscore}{$acl_m_tmp}}
set acl_m_virusscan = ${extract{virusscan}{$acl_m_tmp}}
set acl_m_quarantine = ${extract{quarantine}{$acl_m_tmp}}
set acl_m_greylist = ${extract{greylist}{$acl_m_tmp}}
logwrite = inbound is $acl_m_inbound
logwrite = quota_st is $acl_m_quota_st
logwrite = quota_sz is $acl_m_quota_sz
logwrite = spamscan is $acl_m_spamscan
logwrite = spamscore is $acl_m_spamscore
logwrite = virusscan is $acl_m_virusscan
logwrite = quarantine is $acl_m_quarantine
logwrite = greylist is $acl_m_greylist
There is probably a short cut to assign everything in one hit, but at
this moment I'm just delighted to have this working in concept :-)
I am most obliged to you gentlemen. Thank you.
