smtp + sql = more than it seems so (part 6)

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 vacation

looks 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.

3 thoughts on “smtp + sql = more than it seems so (part 6)”

  1. 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

  2. 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

  3. @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.

Comments are closed.