CentOS 5.5, exim, dovecot with MySQL & SquirrelMail

So this is a little guide I pieced together from various sources on the internet, hopefully it helps someoone when making a similar setup.

Could not find these packages in the centOS repository, so grabbed these from atrpms http://packages.atrpms.net/dist/el5/exim/

# rpm --import http://packages.atrpms.net/RPM-GPG-KEY.atrpms
# yum install dovecot squirrelmail;

Setup mysql database and create tables

# mysql -u root -p
mysql> CREATE DATABASE maildb;
mysql> grant all on maildb.* to mail@localhost identified by 'mail'
mysql> flush privileges;

CREATE MYSQL TABLES

CREATE TABLE domains (
id INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
fqdn VARCHAR(250) NOT NULL,
type ENUM('local','relay') NOT NULL DEFAULT 'local',
description VARCHAR(250) NULL,
active TINYINT(1) NOT NULL DEFAULT 0,
created TIMESTAMP(14) NOT NULL DEFAULT NOW(),
modified TIMESTAMP(14) NULL
);
CREATE TABLE mailboxes (
id INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
domain_id INT(10) NOT NULL,
local_part VARCHAR(250) NOT NULL,
password VARCHAR(50) NULL,
description VARCHAR(250) NULL,
active TINYINT(1) NOT NULL DEFAULT 0,
created TIMESTAMP(14) NOT NULL DEFAULT NOW(),
modified TIMESTAMP(14) NULL
);
CREATE TABLE aliases (
id INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
domain_id INT(10) NOT NULL,
local_part VARCHAR(250) NOT NULL,
goto VARCHAR(250) NOT NULL,
description VARCHAR(250) NULL,
active TINYINT(1) NOT NULL DEFAULT 0,
created TIMESTAMP(14) NOT NULL DEFAULT NOW(),
modified TIMESTAMP(14) NULL
);
CREATE TABLE vacations (
id INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
mailbox_id INT(10) NOT NULL,
subject VARCHAR(250) NOT NULL,
body TEXT NOT NULL,
description VARCHAR(250) NULL,
active TINYINT(1) NOT NULL DEFAULT 0,
created TIMESTAMP(14) NOT NULL DEFAULT NOW(),
modified TIMESTAMP(14) NULL
);

Exim Configuration

# Auth params for mysql
hide mysql_servers = localhost/MAILDB/MYSQLUSER/MYSQLPASS
# local and relay to domains settings from mysql
domainlist local_domains = ${lookup mysql{SELECT fqdn AS domain FROM domains WHERE fqdn='${quote_mysql:$domain}' AND type='local' AND active=1}}
domainlist relay_to_domains = ${lookup mysql{SELECT fqdn AS domain FROM domains WHERE fqdn='${quote_mysql:$domain}' AND type='relay' AND active=1}}
local_delivery:
driver = appendfile
maildir_format = true
directory = /var/spool/mail/$domain/$local_part
create_directory = true
directory_mode = 0770
mode_fail_narrower = false
message_prefix =
message_suffix =
delivery_date_add
envelope_to_add
return_path_add
group = mail
mode = 0660
dovecot_delivery:
driver = appendfile
maildir_format = true
directory = /var/spool/mail/$domain/$local_part
create_directory = true
directory_mode = 0770
mode_fail_narrower = false
message_prefix =
message_suffix =
delivery_date_add
envelope_to_add
return_path_add
user = mail
group = mail
mode = 0660
auth_plain:
driver = plaintext
public_name = PLAIN
server_condition = ${lookup mysql{SELECT CONCAT(mailboxes.local_part,'@',domains.fqdn) FROM mailboxes,domains WHERE \
mailboxes.local_part=SUBSTRING_INDEX('${quote_mysql:$auth2}','@',1) AND \
mailboxes.password=MD5('${quote_mysql:$auth3}') AND \
mailboxes.active=1 AND \
mailboxes.domain_id=domains.id AND \
domains.fqdn=SUBSTRING_INDEX('${quote_mysql:$auth2}','@',-1) AND \
domains.active=1}{yes}{no}}
server_prompts = :
server_set_id = $auth2
auth_login:
driver = plaintext
public_name = LOGIN
server_condition = ${lookup mysql{SELECT CONCAT(mailboxes.local_part,'@',domains.fqdn) FROM mailboxes,domains WHERE \
mailboxes.local_part=SUBSTRING_INDEX('${quote_mysql:$auth1}','@',1) AND \
mailboxes.password=MD5('${quote_mysql:$auth2}') AND \
mailboxes.active=1 AND \
mailboxes.domain_id=domains.id AND \
domains.fqdn=SUBSTRING_INDEX('${quote_mysql:$auth1}','@',-1) AND \
domains.active=1}{yes}{no}}
server_prompts = Username:: : Password::
server_set_id = $auth1

Dovecot configuration - /etc/dovecot.conf


passdb sql {
args = /etc/dovecot-sql.conf
}
userdb passwd {
}
userdb SQL {
args = /etc/dovecot-sql.conf
}

/etc/dovecot-sql.conf

driver = mysql
connect = host=localhost dbname=maildb user=MYSQLUSER password=MYSQLPASS
default_pass_scheme = PLAIN-MD5
password_query = SELECT CONCAT(mailboxes.local_part,'@',domains.fqdn) as `user`,mailboxes.password AS `password`,'/var/spool/mail/%d/%n' AS `userdb_home`, 8 AS `userdb_uid`, 12 AS `userdb_gid` FROM `mailboxes`, `domains` WHERE mailboxes.local_part = '%n' AND mailboxes.active = 1 AND mailboxes.domain_id = domains.id AND domains.fqdn = '%d' AND domains.active = 1
user_query = SELECT '/var/spool/mail/%d/%n' AS `home`, 8 AS `uid`, 12 AS `gid`

Populating our mysql tables
INSERT INTO domains (fqdn,type,active) VALUES('my-test-site.com','local',1);
INSERT INTO mailboxes VALUES(NULL,1,'dummy',MD5('dummy'),'test',1,NOW(),NOW());

Testing configuration

# telnet 10.3.0.204 143
Trying 10.3.0.204...
Connected to 10.3.0.204.
Escape character is '^]'.
* OK Dovecot ready.
a003 LOGIN dummy@my-test-site.com dummy
a003 OK Logged in.
a004 SELECT INBOX
* FLAGS (\Answered \Flagged \Deleted \Seen \Draft)
* OK [PERMANENTFLAGS (\Answered \Flagged \Deleted \Seen \Draft \*)] Flags permitted.
* 7 EXISTS
* 7 RECENT
* OK [UNSEEN 1] First unseen.
* OK [UIDVALIDITY 1296735311] UIDs valid
* OK [UIDNEXT 8] Predicted next UID
a004 OK [READ-WRITE] Select completed.

SENDING A TEST EMAIL FROM ANOTHER BOX

# telnet 10.3.0.204
HELO my-test-box.com
250 devmail Hello my-test-box.com [10.3.0.201]
MAIL FROM:me@my-test-site.com
250 OK
RCPT TO:dummy@my-test-site.com
250 Accepted
DATA
354 Enter message, ending with “.” on a line by itself
TEST MESSAGE DATA
.
250 OK id=1Pkyad-0001b7-AF

ACCESSING WEBMAIL

Load up a web browswer and type in the following address.

http://10.3.0.204/webmail