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

last time i finished with working smtp system, with accounts, aliases and quota. now, let's add ability to get th
e mails for client (imap/pop3) and some smtp-auth.

first let's add client feature: getting mail from server.

since we have installer courier imap/pop3 servers, configuration is actually trivial.

courier works in a really cool way – all servers (pop3, pop3s, imap, imaps, ans possibly others) connect to central courier-authdaemon. so i have to configure only one program.

authdaemon configuration consist of 2 important files:

  • /etc/courier/authdaemonrc
  • /etc/courier/authpgsqlrc

their original version can be seen here: authdaemonrc and authpgsqlrc.

in first file (authdaemonrc) we have to find this line:

authmodulelist="authpam"

and change it to:

authmodulelist="authpgsql"

this basically switches authentication backend from pam (system account) to pgsql – which is database driven, and thus perfect for our needs 🙂

now for the second file: authpgsqlrc.

first, database connection information. originally it contains:

PGSQL_HOST      pgsql.example.com
PGSQL_PORT      5400
PGSQL_USERNAME      admin
PGSQL_PASSWORD      admin

since we are using the same database as exim (i hope it's not a surprise), our configuration should look like:

PGSQL_HOST      127.0.0.1
PGSQL_PORT      5432
PGSQL_USERNAME      eximtest
PGSQL_PASSWORD      riesahdoov

now, there is something missing in above info. database name. for whatever reason courier developers decided to put in separately in config:

PGSQL_DATABASE      template1

this value is of course nonsense. no sane person would ever put important information in template1 database, but for default value it is as good as any other.

in our case, it has to be modified:

PGSQL_DATABASE      eximtest

now. courier expects some specific info to be found in one table:

  • password (might be crypted)
  • username
  • system uid
  • system gid
  • home directory
  • maildir directory

the “problem" here is that we dont have any table with all of these. so, let's make a view.

in case you're not familiar – view is virtual table, used to rewrite query. view is defined using standard select statement. for example: if you'd make view:

CREATE VIEW x AS SELECT * FROM users WHERE uid > 1000;

and then query it using:

SELECT * FROM x WHERE name LIKE 'a%';

you would effectively do:

SELECT * FROM users WHERE uid > 1000 AND name LIKE 'a%';

of course this is very basical example. our view will be a bit more complex:

CREATE VIEW courier_data AS
SELECT
    a.username || '@' || d.fullname AS username,
    a.password AS password,
    d.system_uid AS system_uid,
    c.system_gid AS system_gid,
    get_account_homedir(a.username, d.fullname) AS home_dir,
    get_account_maildir(a.username, d.fullname) AS mail_dir
FROM
    accounts a
    JOIN domains d ON a.domain_id = d.id
    JOIN customers c ON d.customer_id = c.id
;

data from our example database:

eximtest=> SELECT * FROM courier_data ;
      username      |              password              | system_uid | system_gid |         home_dir          |             mail_dir
--------------------+------------------------------------+------------+------------+---------------------------+-----------------------------------
 depesz@exim.depesz | $1$dP8J.H9W$YOp2.N6HuulcJPlCaogu.0 |      10004 |      10002 | /mails/exim.depesz/depesz | /mails/exim.depesz/depesz/maildir
 test@exim.depesz   | $1$lJOwV79Y$HefCzh2oOSj/BM3RXDrZJ/ |      10004 |      10002 | /mails/exim.depesz/test   | /mails/exim.depesz/test/maildir
 depesz@localhost   | $1$dP8J.H9W$YOp2.N6HuulcJPlCaogu.0 |      10002 |      10002 | /mails/localhost/depesz   | /mails/localhost/depesz/maildir
 test@localhost     | $1$lJOwV79Y$HefCzh2oOSj/BM3RXDrZJ/ |      10002 |      10002 | /mails/localhost/test     | /mails/localhost/test/maildir
 depesz@badtest     | $1$dP8J.H9W$YOp2.N6HuulcJPlCaogu.0 |      10005 |      10002 | /mails/badtest/depesz     | /mails/badtest/depesz/maildir
 test@badtest       | $1$lJOwV79Y$HefCzh2oOSj/BM3RXDrZJ/ |      10005 |      10002 | /mails/badtest/test       | /mails/badtest/test/maildir
 depesz@example.com | $1$dP8J.H9W$YOp2.N6HuulcJPlCaogu.0 |      10003 |      10002 | /mails/example.com/depesz | /mails/example.com/depesz/maildir
 test@example.com   | $1$lJOwV79Y$HefCzh2oOSj/BM3RXDrZJ/ |      10003 |      10002 | /mails/example.com/test   | /mails/example.com/test/maildir
(8 ROWS)

now, let's finish configuration of courier.

in authpgsqlrc there are these options:

PGSQL_USER_TABLE        passwd
PGSQL_CRYPT_PWFIELD     crypt
PGSQL_UID_FIELD         uid
PGSQL_GID_FIELD         gid
PGSQL_LOGIN_FIELD       id
PGSQL_HOME_FIELD        home
PGSQL_NAME_FIELD        name
# PGSQL_MAILDIR_FIELD   maildir

now, let's change it to more correct values:

PGSQL_USER_TABLE        courier_data
PGSQL_CRYPT_PWFIELD     password
PGSQL_UID_FIELD         system_uid
PGSQL_GID_FIELD         system_gid
PGSQL_LOGIN_FIELD       username
PGSQL_HOME_FIELD        home_dir
#PGSQL_NAME_FIELD       name
PGSQL_MAILDIR_FIELD     mail_dir

name is hashed as we dont have field for this information (but if you do, feel free to add it 🙂

now. after all changes we have modified courier config (copy of my files can be found here: authdaemonrc and authpgsqlrc.

one restart:

=> /etc/init.d/courier-authdaemon restart
 * Stopping Courier authentication services authdaemond           [ OK ]
 * Starting Courier authentication services authdaemond           [ OK ]

and we can test it. first, let's check if we have any mails ready:

=> find /mails/ -type f -exec ls -l {} \;
-rw------- 1 10002 10002 3000295 2008-02-13 21:51 /mails/localhost/test/maildir/new/1202939484.H571096P10843.localhost
-rw------- 1 10003 10002 3000299 2008-02-13 21:51 /mails/example.com/test/maildir/new/1202939501.H955849P10858.localhost
-rw------- 1 10004 10002 1000299 2008-02-13 21:47 /mails/exim.depesz/test/maildir/new/1202939242.H271501P10726.localhost
-rw------- 1 10004 10002 2500303 2008-02-13 21:43 /mails/exim.depesz/depesz/maildir/new/1202939019.H61789P10607.localhost

ok. some mails are ready, but all of them are rather big. too big for my taste. so, let's send some small ones to depesz@localhost account:

=> for a in `seq 1 10`; do echo $a | mail -s test$a depesz@localhost; done
=> find /mails/localhost/depesz/ -type f -exec ls -l {} \;
-rw------- 1 10002 10002 301 2008-02-24 21:22 /mails/localhost/depesz/maildir/new/1203888173.H596275P2045.localhost
-rw------- 1 10002 10002 301 2008-02-24 21:22 /mails/localhost/depesz/maildir/new/1203888173.H779358P2064.localhost
-rw------- 1 10002 10002 301 2008-02-24 21:22 /mails/localhost/depesz/maildir/new/1203888174.H342336P2114.localhost
-rw------- 1 10002 10002 301 2008-02-24 21:22 /mails/localhost/depesz/maildir/new/1203888174.H288633P2108.localhost
-rw------- 1 10002 10002 303 2008-02-24 21:22 /mails/localhost/depesz/maildir/new/1203888174.H432100P2117.localhost
-rw------- 1 10002 10002 301 2008-02-24 21:22 /mails/localhost/depesz/maildir/new/1203888173.H673220P2053.localhost
-rw------- 1 10002 10002 301 2008-02-24 21:22 /mails/localhost/depesz/maildir/new/1203888173.H902191P2074.localhost
-rw------- 1 10002 10002 301 2008-02-24 21:22 /mails/localhost/depesz/maildir/new/1203888174.H217822P2098.localhost
-rw------- 1 10002 10002 301 2008-02-24 21:22 /mails/localhost/depesz/maildir/new/1203888174.H65040P2086.localhost
-rw------- 1 10002 10002 301 2008-02-24 21:22 /mails/localhost/depesz/maildir/new/1203888174.H30006P2091.localhost

much better 🙂

so, let's use old, trustworthy tool: telnet 🙂

but first. i dont remember password to depesz@localhost account, so:

eximtest=> UPDATE accounts SET password = 'depesz'
eximtest-> WHERE username = 'depesz' AND domain_id = (SELECT id FROM domains WHERE fullname = 'localhost');
UPDATE 1

so, on to telnet session 🙂

=> telnet 192.168.0.101 pop3
Trying 192.168.0.101...
Connected to 192.168.0.101.
Escape character is '^]'.
+OK Hello there.
USER depesz@localhost
+OK Password required.
PASS depesz
+OK logged in.
LIST
+OK POP3 clients that break here, they violate STD53.
1 312
2 312
3 312
4 312
5 312
6 312
7 312
8 312
9 314
10 312
.
RETR 1
+OK 312 octets follow.
Received: from root by localhost with local (Exim 4.67)
        (envelope-from <root@xxx>)
        id 1JTOJB-0000Wj-3b
        for depesz@localhost; Sun, 24 Feb 2008 21:22:53 +0000
To: depesz@localhost
Subject: test1
Message-Id: <E1JTOJB-0000Wj-3b@localhost>
From: root <root@xxx>
Date: Sun, 24 Feb 2008 21:22:53 +0000
 
1
.
RETR 8
+OK 312 octets follow.
Received: from root by localhost with local (Exim 4.67)
        (envelope-from <root@xxx>)
        id 1JTOJC-0000Xp-3M
        for depesz@localhost; Sun, 24 Feb 2008 21:22:54 +0000
To: depesz@localhost
Subject: test9
Message-Id: <E1JTOJC-0000Xp-3M@localhost>
From: root <root@xxx>
Date: Sun, 24 Feb 2008 21:22:54 +0000
 
9
.
quit
+OK Bye-bye.

sweet!

now, as i mentioned – all courier servers use authdaemon, so when i have pop3 working, all other will work as well. unfortunatelly i dont know imap well enough to show some sensible example – so you have to test yourself, or trust my word on it 🙂

now. second task for today. smtp auth.

at the moment i can send mails only using localhost interface (which i didn't actually setup in exim to avoid interference with my basic exim on this machine.

this means that any mail to any kind of remote domains will get rejected:

=> 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 Sun, 24 Feb 2008 21:37:04 +0000
EHLO ble
250-localhost Hello ble [192.168.0.101]
250-SIZE 52428800
250-PIPELINING
250 HELP
MAIL FROM: <president@whitehouse.gov>
250 OK
RCPT TO: <depesz@gmail.com>
550 relay not permitted
quit
221 localhost closing connection

and what if i'd try to send with From: being localuser?

=> 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 Sun, 24 Feb 2008 21:38:57 +0000
EHLO ble
250-localhost Hello ble [192.168.0.101]
250-SIZE 52428800
250-PIPELINING
250 HELP
MAIL FROM: <depesz@localhost>
250 OK
RCPT TO: <depesz@gmail.com>
550 relay not permitted
quit
221 localhost closing connection

same thing 🙂

this is actually “a good thing"™ – after all, nobody wants to have open relay. i hope 🙂

first thing to be done: add “authenticator".

at the very end of exim4.conf.template there is (quite long) part:

begin authenticators
...

everything that is there (unhashed) has to be commented out. after this, we add new authenticator:

login:
  driver = plaintext
  public_name = LOGIN
  server_prompts = "Username:: : Password::"
  server_condition = ${lookup pgsql {SELECT verify_password('${quote_pgsql:$1}', '${quote_pgsql:$2}') }}
  server_set_id = $1

please note quote_pgsql calls – these make it safe even in case somebody would enter “bad" username/password.

now we will need verify_password function. it is a bit complex as username is given as full name – together with domain name. but we can solve the problem with simple split_part():

CREATE OR REPLACE FUNCTION verify_password(in_user TEXT, in_password TEXT) RETURNS INT4 AS $BODY$
DECLARE
    use_user TEXT;
    use_domain TEXT;
    tempint TEXT;
BEGIN
    use_user   := split_part(in_user, '@', 1);
    use_domain := split_part(in_user, '@', 2);
    SELECT a.id INTO tempint
    FROM accounts a JOIN domains d ON a.domain_id = d.id
    WHERE a.username = use_user AND d.fullname = use_domain AND a.password = crypt(in_password, a.password);
    IF NOT FOUND THEN
        RETURN NULL;
    END IF;
    RETURN 1;
END;
$BODY$ LANGUAGE plpgsql;

of course after modification of exim4.conf.template i have to restart exim, and test authentication:

=> /etc/init.d/exim4 restart
 * Stopping MTA for restart                                                                                                                                                                               [ OK ]
 * Restarting MTA                                                                                                                                                                                         [ OK ]
=> 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 Sun, 24 Feb 2008 21:58:52 +0000
EHLO x
250-localhost Hello x [192.168.0.101]
250-SIZE 52428800
250-PIPELINING
250-AUTH LOGIN
250 HELP
AUTH LOGIN
334 VXNlcm5hbWU6
ZGVwZXN6QGxvY2FsaG9zdA==
334 UGFzc3dvcmQ6
ZGVwZXN6
235 Authentication succeeded
MAIL FROM: <depesz@localhost>
250 OK
RCPT TO: <depesz@gmail.com>
250 Accepted
quit
221 localhost closing connection

in case you dont know what all these “VXNlcm5hbWU6", “ZGVwZXN6QGxvY2FsaG9zdA==" and other “strange" strings here:

=> perl -le 'use MIME::Base64; print "$_ == " . decode_base64($_) for qw( VXNlcm5hbWU6 ZGVwZXN6QGxvY2FsaG9zdA== UGFzc3dvcmQ6 ZGVwZXN6 )'
VXNlcm5hbWU6 == Username:
ZGVwZXN6QGxvY2FsaG9zdA== == depesz@localhost
UGFzc3dvcmQ6 == Password:
ZGVwZXN6 == depesz

voilà. works 🙂

of course, as usual, actual copy of exim4.conf.template and config.autogenerated are available for your viewing pleasure 🙂

this part basically finishes the series. or it would finish if the title was “smtp + sql — howto". but there is a small addition: “more than it seems so" 🙂

so, from next part i will show what “cool" things can be done using presented setup.