February 6th, 2008 by depesz | Tags: , | 2 comments »
Did it help? If yes - maybe you can help me?

in first part of this text i described how i installed base ubuntu system in chroot, and then got exim, courier and postgresql in there. now to some configuration.

first things that have to be changed is to switch exim from standalone mode into fully fledged internet-capable smtp server (default debian/ubuntu setting basically disable all remote connections in exim).

to do so, i'll edit update-exim4.conf.conf file, and make following changes:

  • dc_eximconfig_configtype : changed from ‘local' to ‘internet'
  • dc_local_interfaces : changed from ‘127.0.0.1' to ‘192.168.0.101' – as i dont want my test exim to interfere with main, working smtp on my laptop 🙂
  • dc_localdelivery : from ‘mail_spool' to ‘maildir_home' – maildir is much better anyway 🙂

now, with this changes, i do:

/etc/init.d/exim4 restart

regenerated /var/lib/exim4/config.autogenerated looks like this.

quick check if everything works:

=> telnet 192.168.0.101 25
Trying 192.168.0.101...
Connected to 192.168.0.101.
Escape character is '^]'.
220 localhost ESMTP Exim 4.67 Mon, 28 Jan 2008 15:11:19 +0000
EHLO localhost
250-localhost Hello localhost [192.168.0.101]
250-SIZE 52428800
250-PIPELINING
250 HELP
MAIL FROM: <depesz@depesz.com>
250 OK
RCPT TO: <root>
501 <root>: recipient address must contain a domain
RCPT TO: <root@localhost>
250 Accepted
DATA
354 Enter message, ending with "." on a line by itself
test
.
250 OK id=1JJVeA-000555-Cy
quit
221 localhost closing connection
Connection closed by foreign host.

now, did it work?

=> exigrep 1JJVeA-000555-Cy /var/log/exim4/mainlog
2008-01-28 15:11:47 1JJVeA-000555-Cy <= depesz@depesz.com H=(localhost) [192.168.0.101] P=esmtp S=203
2008-01-28 15:11:47 1JJVeA-000555-Cy => /var/mail/mail <root@localhost> R=mail4root T=address_file
2008-01-28 15:11:47 1JJVeA-000555-Cy Completed
=> ls -l /var/mail/mail
-rw------- 1 mail mail 360 2008-01-28 15:11 /var/mail/mail
=> cat /var/mail/mail
From depesz@depesz.com Mon Jan 28 15:11:47 2008
Return-path: <depesz@depesz.com>
Envelope-to: root@localhost
Delivery-date: Mon, 28 Jan 2008 15:11:47 +0000
Received: from [192.168.0.101] (helo=localhost)
        by localhost with esmtp (Exim 4.67)
        (envelope-from <depesz@depesz.com>)
        id 1JJVeA-000555-Cy
        for root@localhost; Mon, 28 Jan 2008 15:11:47 +0000
 
test

looks ok.

now, let's start with real exim-pgsql integration. first thing to do is to learn exim to know which domains are “local" – i.e. should not be relayed.

at the moment our testing exim shows:

=> exim4 -bt root@localhost
R: lowuid_aliases for root@localhost (UID 0)
R: system_aliases for root@localhost
R: userforward for root@localhost
R: procmail for root@localhost
R: maildrop for root@localhost
R: mail4root for root@localhost
root@localhost -> /var/mail/mail
  transport = address_file

which is ok, but this:

=> exim4 -bt root@example.com
R: dnslookup for root@example.com
root@example.com
  router = dnslookup, transport = remote_smtp
  host example.com [208.77.188.166]

which basically is also ok, but let's say we will tell it to use example.com as local domain.

same thing (turn it into local domain) will be done for “exim.depesz" domain, which now shows (correctly):

=> exim4 -bt root@exim.depesz
R: dnslookup for root@exim.depesz
root@exim.depesz is undeliverable: Unrouteable address

so, to make this work, we will need some database, and table to store domain information. let's use this schema:

CREATE user eximtest with password 'riesahdoov';
CREATE DATABASE eximtest with owner eximtest;
\connect eximtest eximtest
CREATE TABLE domains (
    id       SERIAL,
    fullname TEXT NOT NULL DEFAULT '' UNIQUE,
    PRIMARY KEY (id)
);
CREATE UNIQUE INDEX ui_domains_fullname ON domains (fullname);

now, since i use ubuntu as my sandbox, i have to take care about one “misfeature" of debian – default “ident sameuser" in pg_hba.conf (which is located in /etc/postgresql/8.2/main/pg_hba.conf).

i modified the file to have only this information inside:

local   all         all                               trust
host    all         all         0.0.0.0/0          md5

you might want to use some other configuration, but for me – it's just test 🙂

now, that i created necessary user, database and table, let's add config to exim.

at the very beginning (just below header of “MAIN CONFIGURATION SETTINGS") of exim4.conf.template i add:

hide pgsql_servers = 127.0.0.1::5432/eximtest/eximtest/riesahdoov

this line tells exim where to connect to when postgresql connection will be necessary.

format of this line is very simple:

  • hide – means don't show in exim4 -bP output 🙂
  • pgsql_servers – config variable to store information about database server(s) connection string(s)
  • 127.0.0.1… – connection string in format: host:port/database/user/password

now, that i have this let's find information about “which domain is local, and which is not" …

here it is:

domainlist local_domains = MAIN_LOCAL_DOMAINS

where MAIN_LOCAL_DOMAINS is taken from debian way of handling config file, and is later on changed to something like this:

.ifndef MAIN_LOCAL_DOMAINS
MAIN_LOCAL_DOMAINS=@:localhost:localhost:localhost
.endif
...
domainlist local_domains = MAIN_LOCAL_DOMAINS

so, knowing this, let's change local_domains to something better:

domainlist local_domains = ${lookup pgsql {SELECT fullname FROM domains WHERE fullname = '${domain}' }}
 
now, you might say - whoa, there is sql injection in here.
 
theoretically - it's true. but fortunately exim does a lot of checking itself, and when it finds given string contains characters which cannot be part of email address - is doesn't even call the query:
 
<code>=> exim4 -bt "root@localhost';--"
syntax error: malformed address: ';-- may not follow root@localhost

so, right now, since our table is empty, i shouldn't even be able to try to deliver to localhost. does it work that way?

=> exim4 -bt root@localhost
R: dnslookup for root@localhost
root@localhost is undeliverable: Unrouteable address

while remote addresses should work the same way, they did:

=> exim4 -bt root@example.com
R: dnslookup for root@example.com
root@example.com
  router = dnslookup, transport = remote_smtp
  host example.com [208.77.188.166]

so far, so good. but will it work with some data inside?

eximtest=> insert into domains (fullname) values ('localhost'), ('example.com'), ('exim.depesz');
INSERT 0 3
eximtest=> select * from domains;
 id |  fullname
----+-------------
  1 | localhost
  2 | example.com
  3 | exim.depesz
(3 rows)

and how about now checking the delivery?

=> exim4 -bt root@localhost
R: lowuid_aliases for root@localhost (UID 0)
R: system_aliases for root@localhost
R: userforward for root@localhost
R: procmail for root@localhost
R: maildrop for root@localhost
R: mail4root for root@localhost
root@localhost -> /var/mail/mail
  transport = address_file
=> exim4 -bt root@example.com
R: lowuid_aliases for root@example.com (UID 0)
R: system_aliases for root@example.com
R: userforward for root@example.com
R: procmail for root@example.com
R: maildrop for root@example.com
R: mail4root for root@example.com
root@example.com -> /var/mail/mail
  transport = address_file
=> exim4 -bt root@exim.depesz
R: lowuid_aliases for root@exim.depesz (UID 0)
R: system_aliases for root@exim.depesz
R: userforward for root@exim.depesz
R: procmail for root@exim.depesz
R: maildrop for root@exim.depesz
R: mail4root for root@exim.depesz
root@exim.depesz -> /var/mail/mail
  transport = address_file

works great!

of course, for domains that are not listed as local, plain old, standard remote_smtp will be used:

=> exim4 -bt root@depesz.com
R: dnslookup for root@depesz.com
root@depesz.com
  router = dnslookup, transport = remote_smtp
  host depesz.com [88.198.37.40] MX=5

now, one more thing.

the way i showed it. it works, but is not really expandable. since i want my system to work in a best possible way, i will add 2 important changes:

  1. add some triggers to keep the data sane. i mean – somebody will insert (perhaps using some kind of web-admin tool) data, but we need it to be 100% ok.
  2. since i can imagine situation when i will want to change some behavior of mail delivery without changing exim configuration – i'll write all exim-called queries as functions.

first case might be not obvious until you'll see:

eximtest=> insert into domains (fullname) values ('BadTest');
INSERT 0 1

and then:

=> exim4 -bt root@BadTest
R: dnslookup for root@badtest
root@BadTest is undeliverable: Unrouteable address

why is that so?

postgresql logs tell the truth:

2008-01-28 16:37:50 UTC LOG:  duration: 3.044 ms  statement: SELECT fullname FROM domains WHERE fullname = 'badtest'

so, exim automatically lowercases domain names. in this case, let's make our domains also always-lowercase, and (since we are at it) remove any leadng/trailing whitespace:

CREATE OR REPLACE FUNCTION cleanup_domains() RETURNS TRIGGER AS
$BODY$
DECLARE
BEGIN
    NEW.fullname := trim(both FROM lower(NEW.fullname));
    RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql';
CREATE TRIGGER cleanup_domains BEFORE INSERT OR UPDATE ON domains FOR EACH ROW EXECUTE PROCEDURE cleanup_domains();

simple update to fix already-inserted-data:

eximtest=> update domains set fullname = fullname;
UPDATE 4
eximtest=> select * from domains;
 id |  fullname
----+-------------
  1 | localhost
  2 | example.com
  3 | exim.depesz
  4 | badtest
(4 rows)

and now, the delivery will succeed:

=> exim4 -bt root@BadTest
R: lowuid_aliases for root@badtest (UID 0)
R: system_aliases for root@badtest
R: userforward for root@badtest
R: procmail for root@badtest
R: maildrop for root@badtest
R: mail4root for root@badtest
root@BadTest -> /var/mail/mail
  transport = address_file

now to the function for getting the data. it will be dead-simple, but it shouldn't be complex – it is there just in case we will want to change something in local-domain-finding rules.

CREATE OR REPLACE FUNCTION is_local_domain(in_domain TEXT) RETURNS TEXT as $BODY$
DECLARE
    tempint INT4;
BEGIN
    SELECT id INTO tempint FROM domains WHERE fullname = trim(both FROM lower(in_domain));
    IF NOT FOUND THEN
        RETURN NULL;
    END IF;
    RETURN in_domain;
END;
$BODY$ LANGUAGE plpgsql;

and now small change in exim config:

domainlist local_domains = ${lookup pgsql {SELECT is_local_domain('${domain}') }}

quick glance in postgresql logs show that the query really is called using function, and of course domains are still visible as local.

copy of exim4.conf.template with changes from this post is available here. regenerated config from /var/lib/exim4 is available here.