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

at the end of last part of this text, i finished having ready postgresql and exim (pop/imap installed but not configured), with exim being able to ask postgresql whether given domain is local. now, let the saga continue.

now, since we know local domains, let's check if we can teach our dream team (exim + postgresql) to know accounts and aliases.

first aliases.

in our test database, let's create this table:

CREATE TABLE aliases (
    id          SERIAL,
    domain_id   INT4 NOT NULL DEFAULT 0 REFERENCES domains (id),
    username    TEXT NOT NULL DEFAULT '',
    destination TEXT NOT NULL DEFAULT '',
    PRIMARY KEY (id)
);

it will store our aliases. basic idea is that to have email address “depesz@depesz.com" which redirects to “president@whitehouse.gov", i add this record:

INSERT INTO aliases (domain_id, username, destination) SELECT id, 'depesz', 'president@whitehouse.gov' FROM domains WHERE fullname = 'depesz.com';

again, i'd add trigger to keep data in this table clean:

CREATE OR REPLACE FUNCTION cleanup_aliases() RETURNS TRIGGER AS
$BODY$
DECLARE
BEGIN
    NEW.username := TRIM(BOTH FROM LOWER(NEW.username));
    NEW.destination := TRIM(BOTH FROM LOWER(NEW.destination));
    RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql';
CREATE TRIGGER cleanup_aliases BEFORE INSERT OR UPDATE ON aliases FOR EACH ROW EXECUTE PROCEDURE cleanup_aliases();

and now, obligatory function for exim to use.

now, i have to think about it. get_alias_destinations() function will accept destination email as argument, but can return multiple rows! (we might have alias like all@company.com, which forwards to many emails – kind of mailing list).

so, our function will be:

CREATE OR REPLACE FUNCTION get_alias_destinations(in_username TEXT, in_domain TEXT) RETURNS SETOF TEXT AS '
DECLARE
    use_username text := trim(both FROM lower(in_username));
    use_domain   text := trim(both FROM lower(in_domain));
    temprec      record;
BEGIN
    FOR temprec in
        SELECT a.*
        FROM aliases a JOIN domains d ON a.domain_id = d.id
        WHERE a.username = use_username AND d.fullname = use_domain
    LOOP
        RETURN NEXT temprec.destination;
    END LOOP;
    RETURN;
END;
' LANGUAGE 'plpgsql';

so, how does it work in psql?

eximtest=> SELECT * FROM domains;
 id |  fullname
----+-------------
  1 | localhost
  2 | example.com
  3 | exim.depesz
  4 | badtest
(4 ROWS)
eximtest=> SELECT * FROM aliases;
 id | domain_id | username |    destination
----+-----------+----------+-------------------
  1 |         2 | depesz   | depesz@depesz.com
  2 |         3 | test     | test1@depesz.com
  3 |         3 | test     | test2@depesz.com
(3 ROWS)
eximtest=> SELECT * FROM get_alias_destinations('test', 'depesz.com');
 get_alias_destinations
------------------------
(0 ROWS)
eximtest=> SELECT * FROM get_alias_destinations('test', 'exim.depesz');
 get_alias_destinations
------------------------
 test1@depesz.com
 test2@depesz.com
(2 ROWS)
eximtest=> SELECT * FROM get_alias_destinations('depesz', 'example.com');
 get_alias_destinations
------------------------
 depesz@depesz.com
(1 ROW)

looks fine. now for exim configuration.

in my exim4.conf.template i find entry for: “system_aliases:" router. it looks like this:

system_aliases:
  debug_print = "R: system_aliases for $local_part@$domain"
  driver = redirect
  domains = +local_domains
  allow_fail
  allow_defer
  data = ${lookup{$local_part}lsearch{/etc/aliases}}
  .ifdef SYSTEM_ALIASES_USER
  user = SYSTEM_ALIASES_USER
  .endif
  .ifdef SYSTEM_ALIASES_GROUP
  group = SYSTEM_ALIASES_GROUP
  .endif
  .ifdef SYSTEM_ALIASES_FILE_TRANSPORT
  file_transport = SYSTEM_ALIASES_FILE_TRANSPORT
  .endif
  .ifdef SYSTEM_ALIASES_PIPE_TRANSPORT
  pipe_transport = SYSTEM_ALIASES_PIPE_TRANSPORT
  .endif
  .ifdef SYSTEM_ALIASES_DIRECTORY_TRANSPORT
  directory_transport = SYSTEM_ALIASES_DIRECTORY_TRANSPORT
  .endif

as you can see, it looks for aliases in /etc/aliases. since we dont want this, we can easily remove it from config (or comment it out), and instead put this there:

pg_aliases:
  debug_print = "R: pg_aliases for $local_part@$domain"
  driver = redirect
  allow_fail
  allow_defer
  DATA = ${lookup pgsql{ SELECT * FROM get_alias_destinations('${local_part}', '${domain}') }}

now, obligatory exim restart (to make it rebuild config), and let's test it:

=> exim4 -bt test@exim.depesz
R: pg_aliases for test@exim.depesz
R: dnslookup for test1@depesz.com
R: dnslookup for test2@depesz.com
test2@depesz.com
    <-- test@exim.depesz
  router = dnslookup, transport = remote_smtp
  host depesz.com [88.198.37.40] MX=5
test1@depesz.com
    <-- test@exim.depesz
  router = dnslookup, transport = remote_smtp
  host depesz.com [88.198.37.40] MX=5
=> exim4 -bt depesz@example.com
R: pg_aliases for depesz@example.com
R: dnslookup for depesz@depesz.com
depesz@depesz.com
    <-- depesz@example.com
  router = dnslookup, transport = remote_smtp
  host depesz.com [88.198.37.40] MX=5

nice. looks cool.

now, let's test alias leading to another alias:

eximtest=> INSERT INTO aliases (domain_id, username, destination) VALUES (2, 'test', 'depesz@example.com');
INSERT 0 1
eximtest=> SELECT * FROM get_alias_destinations('test', 'example.com');
 get_alias_destinations
------------------------
 depesz@example.com
(1 ROW)

testing it from shell shows that it really works ok:

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

sweet 🙂

now, let's add some real accounts.

first let's think a bit about filesystem layout. where will all those emails go? i mean – mail accounts will not be related in any way to system accounts, so we have to find a special place for them.

let's say i will put all those mails in /mails directory (yes, i know it should be in /var, but this is mail server. i will put my mails wherever i want 🙂

now, i will use this schema:

  • /mails/DOMAIN – directory which will hold data for all accounts in given domain.
  • /mails/DOMAIN/USERNAME – virtual-user home directory. basically users will have only emails, but if we will (and yes, we will) extend our exim to do cool things, we will need a place to store per-user files.
  • /mails/DOMAIN/USERNAME/maildir – maildir for user

since full path to user directory can be “deduced" from username and domain, we dont have to store this information. the only thing we need to “store" is top-path.

for this, let's add setup table:

CREATE TABLE setup (
    id    SERIAL,
    param TEXT NOT NULL DEFAULT '' UNIQUE,
    VALUE TEXT NOT NULL DEFAULT '',
    PRIMARY KEY (id)
);

since we're doing everything using functions, let's add functions to store and retrieve param values:

CREATE OR REPLACE FUNCTION setup_set(in_param TEXT, in_value TEXT) RETURNS BOOL AS '
DECLARE
BEGIN
    LOOP
        UPDATE setup SET value = in_value WHERE param = in_param;
        IF found THEN
            RETURN true;
        END IF;
        BEGIN
            INSERT INTO setup(param, value) VALUES (in_param, in_value);
            RETURN false;
        EXCEPTION WHEN unique_violation THEN
        END;
    END LOOP;
END;
' LANGUAGE 'plpgsql';

now, if you're wondering why it is so complicated, please check the docs for postgresql.

quick check if it works:

eximtest=> SELECT setup_set('mails_top_dir', '/m');
 setup_set
-----------
 f
(1 ROW)
eximtest=> SELECT setup_set('mails_top_dir', '/mails');
 setup_set
-----------
 t
(1 ROW)
eximtest=> SELECT * FROM setup;
 id |     param     | VALUE
----+---------------+--------
  1 | mails_top_dir | /mails
(1 ROW)

looks fine.

now, let's write the getter function. it will be simpler:

CREATE OR REPLACE FUNCTION setup_get(in_param TEXT) RETURNS TEXT AS '
DECLARE
    reply text;
BEGIN
    select value into reply from setup where param = in_param;
    return reply;
END;
' LANGUAGE 'plpgsql';

quick check:

eximtest=> SELECT setup_get('mails_top_dir');
 setup_get
-----------
 /mails
(1 ROW)
eximtest=> SELECT setup_get('mails_dasdsf');
 setup_get
-----------
 [NULL]
(1 ROW)

works as expected.

now, let's add accounts table. for starters, it will be rather simple:

CREATE TABLE accounts (
    id        BIGSERIAL,
    username  TEXT NOT NULL DEFAULT '',
    domain_id INT4 NOT NULL DEFAULT 0 REFERENCES domains (id),
    password  TEXT NOT NULL DEFAULT '',
    PRIMARY KEY (id)
);
CREATE UNIQUE INDEX ui_accounts_udi ON accounts (username, domain_id);

please not the composite unique index – thanks to this, we will have the guarantee that there can be only one account with given name in given domain.

cleanup function could be as simple as in aliases or it might have also another feature – automatical encryption of passwords. let's add it 🙂

first, i'll need to add cryptographic functions to postgresql:

=> dpkg -L postgresql-contrib-8.2 | grep pgcrypto.sql
/usr/share/postgresql/8.2/contrib/pgcrypto.sql
/usr/share/postgresql/8.2/contrib/uninstall_pgcrypto.sql

and now, in psql:

eximtest=> \c - postgres
You are now connected TO DATABASE "eximtest" AS USER "postgres".
eximtest=# \i /usr/share/postgresql/8.2/contrib/pgcrypto.sql
SET
CREATE FUNCTION
...
CREATE FUNCTION
eximtest=# \c - eximtest
You are now connected TO DATABASE "eximtest" AS USER "eximtest".

ok. now we have it. let's check previous post from my blog, about password encryption.

CREATE OR REPLACE FUNCTION cleanup_and_crypt_accounts() RETURNS TRIGGER AS
$BODY$
DECLARE
BEGIN
    NEW.username := TRIM(BOTH FROM LOWER(NEW.username));
    IF substr(NEW.password, 1, 3) <> '$1$' THEN
        NEW.password := crypt( NEW.password, gen_salt('md5') );
    END IF;
    RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql';
CREATE TRIGGER cleanup_and_crypt_accounts BEFORE INSERT OR UPDATE ON accounts FOR EACH ROW EXECUTE PROCEDURE cleanup_and_crypt_accounts();

in the article about crypted password i mentioned also a way to make it even more friendly with custom domain, and operators, but since it is not neccessary to have it, let's skip it for simplicity sake.

so, let's create test account:

eximtest=> INSERT INTO accounts (username, domain_id, password) SELECT 'depesz', id, 'pass' FROM domains WHERE fullname = 'exim.depesz';
INSERT 0 1
mtest=> SELECT * FROM accounts;
 id | username | domain_id |              password
----+----------+-----------+------------------------------------
  1 | depesz   |         3 | $1$dP8J.H9W$YOp2.N6HuulcJPlCaogu.0
(1 ROW)

now. before i will modify exim config, let's check what shows now when i try to deliver mail to depesz@exim.depesz:

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

this looks ok.

so now, let's add appropriate things to exim.

no config there is router “local_user". since it is no longer important, let's remove it, and add new one:

pg_user:
  debug_print = "R: pg_user for $local_part@$domain"
  driver = accept
  condition = ${lookup pgsql {SELECT is_local_user('${local_part}', '${domain}')}}
  transport = pg_delivery

please note 2 things:

  • i refer to function which i didn't wrote yet (is_local_user())
  • i refer to transport which also doesn't exist yet

first part is simple to fix:

CREATE OR REPLACE FUNCTION is_local_user(in_username TEXT, in_domain TEXT) RETURNS TEXT AS $BODY$
DECLARE
    use_username text := TRIM(BOTH FROM LOWER(in_username));
    use_domain   text := TRIM(BOTH FROM LOWER(in_domain));
    tempint INT4;
BEGIN
    SELECT a.id INTO tempint FROM accounts a JOIN domains d ON a.domain_id = d.id WHERE d.fullname = use_domain AND a.username = use_username;
    IF NOT FOUND THEN
        RETURN NULL;
    END IF;
    RETURN in_username || '@' || in_domain;
END;
$BODY$ LANGUAGE plpgsql;

quick sanity check:

eximtest=> SELECT is_local_user('depesz', 'exim.depesz');
   is_local_user
--------------------
 depesz@exim.depesz
(1 ROW)
eximtest=> SELECT is_local_user('depesz', 'exim.depesz.com');
 is_local_user
---------------
 [NULL]
(1 ROW)

ok. now for the transport.

in transports part of config, i find (and remove) two transports: mail_spool and maildir_home.

they are not important, and will/should never be used in pure-virtual accounts situation.

instead of these two, let's add our new transport:

pg_delivery:
  debug_print = "T: pg_delivery for $local_part@$domain"
  driver = appendfile
  maildir_format
  directory = ${lookup pgsql{SELECT get_account_maildir('${local_part}', '${domain}')}}

now, i need to supply it with get_account_maildir function. so, here it is:

CREATE OR REPLACE FUNCTION get_account_maildir(in_username TEXT, in_domain TEXT) RETURNS TEXT AS $BODY$
DECLARE
    use_username text := TRIM(BOTH FROM LOWER(in_username));
    use_domain   text := TRIM(BOTH FROM LOWER(in_domain));
    temptext TEXT;
BEGIN
    temptext := is_local_user(in_username, in_domain);
    IF temptext IS NULL THEN
        RETURN NULL;
    END IF;
    RETURN setup_get('mails_top_dir') || '/' || use_domain || '/' || use_username || '/maildir';
END;
$BODY$ LANGUAGE plpgsql;

thortically i dont have to check if given user really exists (call to is_local_user), but i want to be safe in case somebody would call it directly, and not through exim.

sanity check:

eximtest=> SELECT get_account_maildir('a', 'b');
 get_account_maildir
---------------------
 [NULL]
(1 ROW)
eximtest=> SELECT get_account_maildir('depesz', 'exim.depesz');
        get_account_maildir
-----------------------------------
 /mails/exim.depesz/depesz/maildir
(1 ROW)

now, obligatory exim restart, and let's check if i can deliver mail to depesz@exim.depesz 🙂

=> exim4 -bt depesz@exim.depesz
R: pg_aliases for depesz@exim.depesz
R: pg_user for depesz@exim.depesz
depesz@exim.depesz
  router = pg_user, transport = pg_delivery

looks ok. so let's try to get real delivery:

=> 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 Sat, 02 Feb 2008 23:55:42 +0000
EHLO x
250-localhost Hello x [192.168.0.101]
250-SIZE 52428800
250-PIPELINING
250 HELP
MAIL FROM: <x@x.com>
250 OK
RCPT TO: <depesz@exim.depesz>
250 Accepted
DATA
354 Enter message, ending with "." on a line by itself
will it work?
.
250 OK id=1JLSDJ-0002NJ-ES
quit
221 localhost closing connection

wow. looks ok. does it?

=> exigrep 1JLSDJ-0002NJ-ES /var/log/exim4/mainlog
+++ 1JLSDJ-0002NJ-ES has NOT completed +++
2008-02-02 23:56:06 1JLSDJ-0002NJ-ES <= x@x.com H=(x) [192.168.0.101] P=esmtp S=198
2008-02-02 23:56:06 1JLSDJ-0002NJ-ES == depesz@exim.depesz R=pg_user T=pg_delivery defer (13): Permission denied: cannot CREATE /mails/exim.depesz/depesz/maildir

oops. so, let's create the directory, and retry to deliver:

=> mkdir -p /mails/exim.depesz/depesz/maildir; exim4 -qff -v
LOG: queue_run MAIN
  START queue run: pid=9204 -qff
delivering 1JLSDJ-0002NJ-ES (queue run pid 9204)
R: pg_aliases FOR depesz@exim.depesz
R: pg_user FOR depesz@exim.depesz
T: pg_delivery FOR depesz@exim.depesz
LOG: MAIN
  == depesz@exim.depesz R=pg_user T=pg_delivery defer (13): Permission denied: cannot CREATE /mails/exim.depesz/depesz/maildir/tmp
LOG: queue_run MAIN
  END queue run: pid=9204 -qff

hmm … why? it's simple – exim runs as non-root user. we have to change mode for this directory:

=> chown Debian-exim:Debian-exim /mails/exim.depesz/depesz/maildir; exim4 -qff -v
LOG: queue_run MAIN
  Start queue run: pid=9247 -qff
delivering 1JLSDJ-0002NJ-ES (queue run pid 9247)
R: pg_aliases for depesz@exim.depesz
R: pg_user for depesz@exim.depesz
T: pg_delivery for depesz@exim.depesz
LOG: MAIN
  => depesz <depesz@exim.depesz> R=pg_user T=pg_delivery
LOG: MAIN
  Completed
LOG: queue_run MAIN
  End queue run: pid=9247 -qff

yes! it looks like delivered:

=> find /mails/exim.depesz/depesz/maildir
/mails/exim.depesz/depesz/maildir
/mails/exim.depesz/depesz/maildir/cur
/mails/exim.depesz/depesz/maildir/tmp
/mails/exim.depesz/depesz/maildir/new
/mails/exim.depesz/depesz/maildir/new/1201996733.H657456P9250.localhost
!!!!!!!!!!!!! EXIM !!!!!!!!!!!!!!!
[23:59:24] root@xxx:/
=> cat /mails/exim.depesz/depesz/maildir/new/1201996733.H657456P9250.localhost
Received: from [192.168.0.101] (helo=x)
        by localhost with esmtp (Exim 4.67)
        (envelope-from <x@x.com>)
        id 1JLSDJ-0002NJ-ES
        for depesz@exim.depesz; Sat, 02 Feb 2008 23:56:06 +0000
 
will it work?

cool. now let's finish this part. in next part we'll add quota for email accounts.

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

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

  1. Pingback: </depesz> » Blog Archive » smtp + sql = more than it seems so (part 4)

  2. I am implementing it in Debian Etch and everything works just as described. I have found one bug so far – in the statement creating trigger for password hashing function, there should be “ON accounts” instead of “ON aliases”. Great howto, cheers!

Comments are closed.