in last part of this howto we did setup pop3/imap client access.
together with things set in all previous parts it makes a fully working mail system.
but, there are still things that might be added/modified. one of these is vacation support …
if you're not familiar with what vacation means, it is imply auto-responder which replies with predefined text mail when new mail arrive.
basic usecase is simply vacation. if i go abroad, i setup auto responder to inform everybody that between some dates i will be offline, and in case of emergency they should write to some designated person who is in charge of everything in this time.
it looks very simple, and might be tempting to do it with simple “vacation text" field in accounts table, but this solution is not really good.
some time ago i did setup mail system that way. one thing that bothered me is that we (admins) got a lot of requests: please setup my autoresponder between next monday and wednesday, as i will be someplace else.
in general – we knew in advance that somebody needs autoresponder, but it shouldn't be turned on before the designated date.
with simplistic approach (vacation column in accounts table) this requires manual setting of vacation on the day it should start working (and removal as soon as it should stop).
this is far from optimal, so let's do it better.
we will need a place to store vacation text in advance (possibly multiple responses) together with start/end dates. suggested structure:
CREATE TABLE vacations ( id BIGSERIAL , account_id INT4 NOT NULL DEFAULT 0 REFERENCES accounts (id), starting TIMESTAMPTZ NOT NULL DEFAULT now(), finishing TIMESTAMPTZ NOT NULL DEFAULT now(), vacation TEXT NOT NULL DEFAULT '', PRIMARY KEY (id) );
it might be tempting to add soem kind of triggers to ensure that no 2 vacation records overlap, but i think that it is not worth the trouble, especially if we'll think about possible benefits of overlapping vacations.
for example: i might setup some long-term vacation to some text, but then set specific short-term vacation records in specific hours. like this:
- from monday till friday: i'm away from office.
- monday, between 6 and 14:30 – i'm on airports
- tuesday, between 15 and 23 – drinking, do not disturb
- and so on.
ok, so now, let's write function which will return correct vacation text for given user:
CREATE OR REPLACE FUNCTION get_vacation(in_username TEXT, in_domain TEXT) RETURNS TEXT AS ' DECLARE use_username text := trim(both FROM lower(in_username)); use_domain text := trim(both FROM lower(in_domain)); reply text; BEGIN SELECT v.vacation INTO reply FROM accounts a join domains d on a.domain_id = d.id join vacations v on a.id = account_id WHERE a.username = use_username AND d.fullname = use_domain AND now() between v.starting AND v.finishing ORDER BY ( v.finishing - v.starting ) asc, v.id desc LIMIT 1; IF NOT FOUND THEN RETURN NULL; END IF; RETURN reply; END; ' LANGUAGE 'plpgsql';
how does it work?
on empty table:
eximtest=> SELECT * FROM vacations; id | account_id | starting | finishing | vacation ----+------------+----------+-----------+---------- (0 ROWS) eximtest=> SELECT get_vacation('depesz', 'exim.depesz'); get_vacation -------------- [NULL] (1 ROW)
not shocking.
let's add some long-term vacation:
eximtest=> INSERT INTO vacations (account_id, starting, finishing, vacation) VALUES (1, now() - '1 month'::INTERVAL, now() + '1 month'::INTERVAL, 'long vacation'); INSERT 0 1 eximtest=> SELECT get_vacation('depesz', 'exim.depesz'); get_vacation --------------- long vacation (1 ROW)
ok, and what if i'll add second, overlapping, but shorter vacation?
eximtest=> INSERT INTO vacations (account_id, starting, finishing, vacation) VALUES (1, now() - '1 day'::INTERVAL, now() + '1 day'::INTERVAL, 'short vacation'); INSERT 0 1 eximtest=> SELECT * FROM vacations; id | account_id | starting | finishing | vacation ----+------------+-------------------------------+-------------------------------+---------------- 1 | 1 | 2008-01-29 21:52:27.22621+00 | 2008-03-29 21:52:27.22621+00 | long vacation 2 | 1 | 2008-02-28 21:53:13.636741+00 | 2008-03-01 21:53:13.636741+00 | short vacation (2 ROWS) eximtest=> SELECT get_vacation('depesz', 'exim.depesz'); get_vacation ---------------- short vacation (1 ROW)
ok. so the database part works. now for exim configuration.
in exim4.conf.template we have to find last non-user router (which is hub_user_smarthost). after this router we have to add our own:
pg_vacation: debug_print = "R: pg_vacation for $local_part@$domain" driver = accept condition = ${IF AND { { AND { {> { ${strlen:${lookup pgsql {SELECT get_vacation('${local_part}', '${domain}') } }} } {0} } {= { ${strlen:$bheader_list-id:} }{0} } } } { AND { {= { ${strlen:$bheader_x-mailing-list:} }{0} } {= { ${strlen:$bheader_list-post:} }{0} } } } } {yes} {no} } no_verify no_expn unseen transport = pg_t_vacation
whoa. that's one ugly condition.
let's pretty-print it:
condition = ${IF AND { { AND { {> { ${strlen:${lookup pgsql {SELECT get_vacation('${local_part}', '${domain}') } }} } {0} } {= { ${strlen:$bheader_list-id:} }{0} } } } { AND { {= { ${strlen:$bheader_x-mailing-list:} }{0} } {= { ${strlen:$bheader_list-post:} }{0} } } } } {yes} {no} }
now, it's more readable. basically this condition checks whether:
- user vacation (got from get_vacation function) has length > 0
- mail header list-id has length 0 (which means it's empty
- mail header x-mailing-list has length 0 (which means it's empty
- mail header list-post has length 0 (which means it's empty
first part (length of vacation string is obvious, but why do we need to check the headers?
it's simple: we dont want our vacation to be autoreplied in case we will get mail from some mailing list.
and why do i put there 3 separate headers? well, i have seen mails from mailing lists with strange headers, and checking all of these headers seems to work for me.
it is very important to put there “unseen" at the end of router definition. otherwise exim would stop delivery of email on this router, and it would never arrive to inbox of user who has vacation set 🙂
now. this router sends message to “pg_t_vacation" transport. which (of course) doesn't exist. so, let's add it.
in transports section (place is not really important) we have to add this transport:
pg_t_vacation: debug_print = "T: pg_t_vacation for $local_part@$domain" driver = autoreply reply_to = "${local_part}@${domain}" TO = ${sender_address} FROM = "${local_part}@${domain}" subject = "Re: ${quote:${escape:$header_subject:} } [vacation]" text = ${lookup pgsql {SELECT get_vacation('${local_part}', '${domain}') }}
this will make exim respond to mails with text from database, and subject being “Re: original-subject" with added “[vacation]" at the end.
so, having it done, let's check if it will work.
=> telnet 192.168.0.101 smtp Trying 192.168.0.101... Connected to 192.168.0.101. Escape character is '^]'. 220 localhost ESMTP Exim 4.67 Sat, 01 Mar 2008 20:11:47 +0000 EHLO x 250-localhost Hello x [192.168.0.101] 250-SIZE 52428800 250-PIPELINING 250-AUTH LOGIN 250 HELP MAIL FROM: <test@exim.depesz> 250 OK RCPT TO: <depesz@exim.depesz> 250 Accepted DATA 354 Enter message, ending with "." on a line by itself Subject: ble xx . 250 OK id=1JVY42-000350-LP quit 221 localhost closing connection
how does it look in logs?
2008-03-01 20:12:20 1JVY42-000350-LP <= test@exim.depesz H=(x) [192.168.0.101] P=esmtp S=211 2008-03-01 20:12:20 1JVY42-000350-LP => depesz <depesz@exim.depesz> R=pg_user T=pg_delivery 2008-03-01 20:12:20 1JVY4C-00035O-Gz <= <> R=1JVY42-000350-LP U=Debian-exim P=local S=370 2008-03-01 20:12:20 1JVY42-000350-LP => depesz <depesz@exim.depesz> R=pg_vacation T=pg_t_vacation 2008-03-01 20:12:20 1JVY42-000350-LP Completed 2008-03-01 20:12:20 1JVY4C-00035O-Gz => test <test@exim.depesz> R=pg_user T=pg_delivery 2008-03-01 20:12:20 1JVY4C-00035O-Gz Completed
as you can see we see 2 messages in here:
2008-03-01 20:12:20 1JVY42-000350-LP <= test@exim.depesz H=(x) [192.168.0.101] P=esmtp S=211 2008-03-01 20:12:20 1JVY42-000350-LP => depesz <depesz@exim.depesz> R=pg_user T=pg_delivery 2008-03-01 20:12:20 1JVY42-000350-LP => depesz <depesz@exim.depesz> R=pg_vacation T=pg_t_vacation 2008-03-01 20:12:20 1JVY42-000350-LP Completed
and
2008-03-01 20:12:20 1JVY4C-00035O-Gz <= <> R=1JVY42-000350-LP U=Debian-exim P=local S=370 2008-03-01 20:12:20 1JVY4C-00035O-Gz => test <test@exim.depesz> R=pg_user T=pg_delivery 2008-03-01 20:12:20 1JVY4C-00035O-Gz Completed
first is the mail i sent via telnet, while the other is reply. how does it look?
=> cat /mails/exim.depesz/test/maildir/new/1204402340.H882122P11871.localhost
Received: from Debian-exim by localhost with local (Exim 4.67)
id 1JVY4C-00035O-Gz
for test@exim.depesz; Sat, 01 Mar 2008 20:12:20 +0000
From: depesz@exim.depesz
Reply-To: depesz@exim.depesz
To: test@exim.depesz
Subject: Re: "ble " [vacation]
Auto-Submitted: auto-replied
Message-Id: <E1JVY4C-00035O-Gz@localhost>
Date: Sat, 01 Mar 2008 20:12:20 +0000
short vacationlooks ok 🙂
and how it will work if i'll make the mail look like if it's from mailing list?
=> telnet 192.168.0.101 smtp Trying 192.168.0.101... Connected to 192.168.0.101. Escape character is '^]'. 220 localhost ESMTP Exim 4.67 Sat, 01 Mar 2008 20:15:04 +0000 EHLO x 250-localhost Hello x [192.168.0.101] 250-SIZE 52428800 250-PIPELINING 250-AUTH LOGIN 250 HELP MAIL FROM: <test@exim.depesz> 250 OK RCPT TO: <depesz@exim.depesz> 250 Accepted DATA 354 Enter message, ending with "." on a line by itself Subject: ble2 List-ID: <some@id.of.mailing.list> mail content . 250 OK id=1JVY73-00036m-16 quit 221 localhost closing connection Connection closed by foreign host.
exim log shows:
2008-03-01 20:15:33 1JVY73-00036m-16 <= test@exim.depesz H=(x) [192.168.0.101] P=esmtp S=254 2008-03-01 20:15:34 1JVY73-00036m-16 => depesz <depesz@exim.depesz> R=pg_user T=pg_delivery 2008-03-01 20:15:34 1JVY73-00036m-16 Completed
so it works correctly 🙂
another feature done 🙂
as always, you can get current version of exim4.conf.template and config.autogenerated files.
next week i'll show you how to filter mails per user. and why.
Hi!
I think that you should implement some sort of a throttling mechanism – eg. send a vacation reply only once a day or so to a particular sender.
This is what these vacations responders typically work, so I suppose this is “the right way” 🙂
BTW – nice piece of writing. My former installations were based on Exim+PostgreSQL combo, but for about two years now I’ve switched to Postfix+LDAP – it seemed more natural for me to have a central user database inside LDAP, since the information is used by many different applications. This is the way I like it, nothing more 🙂 No flame wars, please…
Regards,
Pawel
Hi depesz,
Thanks for yet another great post. I suggest one minor modification to the pg_vacation condition: If the header “Precedence: Bulk” is present, do not send the vacation message. I believe that this is the de facto standard header for identifying mail list posts and messages that should receive no autoreply.
Cheers,
Theory
@Pawel J. Sawicki, @Theory:
i will add those features (precedence:bulk, and send only once) in one of next posts in this series. thanks for idea.