Re: [exim] auto-reply with exceptions from SQL lookup

Góra strony
Delete this message
Reply to this message
Autor: W B Hacker
Data:  
Dla: exim users
Temat: Re: [exim] auto-reply with exceptions from SQL lookup
Veres Imre wrote:
> Hello Bill,
>
>> Yes. Maybe.
>>
>> SQL INSERT or UPDATE can be called as easily as SELECT, and all of them
>> from essentially ANYWHERE in an Exim ~/configure.
>>
>> The 'maybe' is not SQL limited.
>
> thank you for answering! I'm afraid I did not expressed my problem clearly, because I'd like to find answer to a bit different thing. :-) Imagine that a user goes to vacation, set his/her out-of-office message and he/she can also configure addresses where autoreply messages should not go to. I could create the configuration for everything but these exceptional email addresses.
>
> I want to achieve this in the routers section, where I have a condition to find out if the user was on vacation. I have also a "senders" clause where I'd like to negate addresses (so the autoreply will never goes to those addresses). I'd like to populate the "senders" list from MySQL (so I definitely don't want to INSERT or UPDATE but to SELECT). Unfortunately it does not seem to work.
>
> virtual_vacation:
>      driver = accept
>      condition = ${if and{ \
>                  {!match {$h_precedence:}{(?i)junk|bulk|list}} \
>                  {eq {${lookup mysql{select users.on_vacation from users,domains \
>                  where localpart = '${quote_mysql:$local_part}' \
>                  and domain = '${quote_mysql:$domain}' \
>                  and users.on_vacation = '1' \
>                  and users.domain_id=domains.domain_id}}}{1}} \
>                  {!def:header_List-Id: } \
>                  {!def:header_X-Autoreply-From: } \
>                  {!match {$h_Subject:} {\N(Out of Office)$\N} } \
>                  {!match {$h_Subject:} {\N(Auto Reply)\N} } \
>                  {!match {$h_Subject:} {\N(Auto-Reply)\N} } \
>                   }}
>      senders = !${lookup mysql{select vn.address from users u,domains d,noautoreply vn \
>              where u.localpart = '${quote_mysql:$local_part}' \
>              and d.domain = '${quote_mysql:$domain}' \
>              and u.domain_id=d.domain_id \
>              and vn.user_id = u.user_id}{$value}}
>      no_verify
>      no_expn
>      unseen
>      transport = virtual_vacation_delivery

>
>> From the config snippet above, the "senders" part does not work correctly. How can I give many addresses here from MySQL?
>
> (I have already got help to extend the "condition" section with other useful things.)
>
> Thanks again, BR,
>
> Imre
>


Imre,

MUA wrap will probably munge all that code ..but nevermind ...

After going through a similar exercise to what you seek, I took a
different course.

1) An autoreply COULD be done in acl's during smtp time of the incoming
transaction. Use 'fakereject' and a custom SQL-supplied blurb inserted
as the error message.

Plus: Never goes to the wrong correspondent

Minus: Will probably NOT be read as intended, as the submitting MTA
wraps it in its own 'usual' non-delivery error report.

BT,DT,GTTS Not reliable enough...


2) If the autoreply is to be the 'usual' separate message generated and
sent back, one is not limited to the router/transport phase of a(ny)
MTA. It can be done entirely by tools external to Exim. Most common
historically has been in the Luser's MUA - which can give a sysadmin
fits....

But that opens the door to another server-side approach...using parts of
OTHER common multipurpose tools that one can configure easily with THEIR
toolsets instead of reinventing part of an existing wheel.

One such tool in the case of decision-making autoreply is a Mailing List
Manager.

We used Ecartis, which has essentially been retired now, but any decent
MLM already has the tools you need in its kit.

- a 'per user' list can be set up from config templates with very little
effort, then switched active/inactive with even LESS effort.

- Tools to decide whom is 'allowed in', prevent spam and other forms of
autoreply 'abuse', ability to alter or outright replace headers AND
body, use 'digest' with adjustable timing and size thresholds to reduce
repetition or onpass a periodic overview of whom has been 'calling'
about what subject to the attention of a covering co-worker or family
member - or just a 'more private' email account ....and otherwise decide
who gets what and on which criteria .... are already present, tested,
proven, and documented.

Moreover, with appropriate use of sub-admin and moderator rights to
manage their own list membership, each Luser can control it all
themselves. Always, sometimes, fully, partially.... or not directly at
all. Your call. But likely to be less work than DB alterations and
never-ending SQL code mods in any case.

Basically you use a 'list' with few or zero outbound members, then do
the 'delivery' as moderator bounces, 'errors to', 'ccerrors', or 'echo
post' with modified content sent back instead of what was submitted.

Sounds complex - but the MLM package is a proven workhorse with all the
bells and whistles, just drops-in, and takes few resources when idle.

Thereafter you don't have to create anything new - just cleverly
configure what has already been built and tested by MLM experts.

The result is configs that are highly portable from one Luser to the
next, and MUCH easier to document and onpass to another admin if YOU
want to go on vacation.

So .. I wouldn't go into anything more complex *within* Exim at all.

Especially as a per-user MLM toolset has OTHER benefits - not just
autoreply, but autoforward-equivalency, autodivert, digests,
archiving...and of course - the conventional list usage for
friends/family, and business uses.

Those can be a BIG general-purpose plus.

BTW - MySQL is fine at what it is most often called on to do. But if you
really do want to do *complex* stuff, I'd suggest replacing it with
PostgreSQL. It's a drop-in as far as Exim is concerned, your code and
data migrate with very little effort, and you'd get a richer all around
integrated toolset, including stored procedures, 'triggers' and a few
more flexible data types.

HTH

Bill