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
testlooks 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:
- 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.
- 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.
2 thoughts on “smtp + sql = more than it seems so (part 2)”
Comments are closed.