HOWTO: Convert to courier imap using mysql

B

ballyn

Verified User
Joined
Feb 7, 2005
Messages
266
Location
Boston, MA
Quick and dirty guide to using courier-imap with cram-md5 and mysql under DirectAdmin

Motivation

The imap and imap ssl functionality provided by DA is not as robust as other solutions I've configured, and I much prefer the maildir folder hierarchy for mail storage as opposed to single mbox files. So, I went about determining how to convert my DA system to use courier-imap as the MDA (mail delivery agent).

The challenge
DA has two kinds of users, "real" and "virtual". "real" users currently do their authentication through PAM or through the OS shadow mechanism, while "virtual" users are authenticated against a custom file in DA's directory. Both real and virtual mail users must be able to do a number of things, including get delivery, do smtp-auth, do pop, do imap and utlize pop-before-smtp. The biggest hurdle to the migration is that under courier, the path to the maildir is provided by the authentication layer. So, we need to first use an auth method supported by courier that will provide password checking as well as the path to the maildir for that user. Then, the smtp auth facility must also be told to use that mechanism. The courier pop and imap daemons will take care of delivery.

Courier-imap's most common form of authentication is through a shadow-like dbm userdb system called authuserdb. Username, password, etc. is stored in a flat text file and then converted to a db file by a script included with courier. Unfortunately, on my system, I couldn't get exim to read the shadow file that is created by courier (which appears to be a v1 file while exim's support is for v3/4). The problem, then, is that we can't use the same password file for smtp-auth that we use for pop/imap.

Rather than struggle with versioning and compatibility issues, I decided to take a slightly more complex (but more scalable) approach of storing user data in a mysql table. However, this approach also made it simple to further support CRAM-MD5 authentication for both pop/imap and smtp-auth.

Note:There is additional functionality to be added and more testing to be done before this conversion can be "complete". For example, it is currently impossible to change a user's e-mail password via the DA console. Furthermore, I can't get pop3d-ssl to correctly negotiate an sslv3 session...


Required packages:

Note: This is tested under Centos 3.3... you should be able to use similar or identical packages under RHE 3 flavors. Notes regarding BSD are inline regarding possible issues I'm aware of...

Courier-IMAP:
tested courier-imap-3.0.3-1.i386.rpm

Courier-IMAP mysql authentication:
tested courier-imap-mysql-3.0.3-1

MySQL Compatibility libraries:
tested MySQL-shared-compat-4.0.23-0

Exim with mysql support
tested 4.44-1 RPM from DA by installing the src rpm and editing the makefile

DRAC
http://mail.cc.umanitoba.ca/drac/

Configuration:
Code: 
INSTALL = install
EBIN = /usr/local/sbin
MAN = /usr/local/man/man
DEFS = -DSOCK_RPC -DFCNTL_LOCK -DGETHOST -DDASH_C -DREQ_HASH -DCIDR_KEY -DTERM_KD
CC = gcc
RANLIB = :
CFLAGS = $(DEFS) -g
LDLIBS = -ldb
TSTLIBS = -L. -ldrac
RPCGENFLAGS = -C -I
MANLIB = 3
MANADM = 8

drac-add
http://mail.cc.umanitoba.ca/drac/courier-exec.txt


Setting up MySql:

Choose a user that will own the "mail" database. Create a database called "mail" and create the user table.
Code: 
CREATE TABLE users (
id                    char(128) DEFAULT  NOT NULL,
crypt                 char(128) DEFAULT  NOT NULL,
clear                 char(128) DEFAULT  NOT NULL,
name                  char(128) DEFAULT  NOT NULL,
uid                   int(10) unsigned DEFAULT '65534' NOT NULL,
gid                   int(10) unsigned DEFAULT '65534' NOT NULL,
home                  char(255) DEFAULT  NOT NULL,
maildir               char(255) DEFAULT  NOT NULL,
quota                 char(255) DEFAULT '' NOT NULL,
KEY id (id(128))
);

Insert a test user into it:
Code: 
INSERT INTO users (id, crypt, clear, name, uid, gid, home, maildir) VALUES (
  "[email protected]", encrypt("asecretpass"), "asecretpass", "atestuser", "8", "12",
  "/var/spool/virtual/domain.com/user",
  "/var/spool/virtual/domain.com/user/Maildir")

Note: we keep a cleartext version of the password in this table for CRAM-MD5 authentication

Note: MySql's encrypt() function requires crypt() support from the OS. No clue if BSD builds support this.

Note: The uid/gid fields for real users should match the os uid/gid since courier will use them to access the users maildir.



Configuring the MTA (Exim) for delivery:

Exim must be configured in two ways:
  • delivery of mail to a maildir folder
  • allow smtp-auth for real and virtual users (see below)
We solve the first issue by modifying the local_delivery and virtual_localdelivery directives in exim.conf:
Code: 
local_delivery:
  driver = appendfile
  delivery_date_add
  envelope_to_add
  #file = /var/mail/$local_part
  directory=${home}/Maildir
  maildir_format = true
  #prefix = ""
  group = mail
  mode = 0660
  return_path_add
  user = ${local_part}

## for delivering virtual domains to their own mail spool

virtual_localdelivery:
  driver = appendfile
  create_directory
  delivery_date_add
  directory_mode = 700
  envelope_to_add
  #file = /var/spool/virtual/${domain}/${local_part}
  directory=/var/spool/virtual/${domain}/${local_part}/Maildir
  maildir_format = true
  group = mail
  mode = 660
  return_path_add
  user = "${lookup{$domain}lsearch*{/etc/virtual/domainowners}{$value}}"
  quota = ${if exists{/etc/virtual/${domain}/quota}{${lookup{$local_part} \
lsearch*{/etc/virtual/${domain}/quota}{$value}{0}}}{0}}

As you can see, we've told exim to deliver mail to a Maildir "directory" for real users as well as virtual users instead of the mbox "file" described previously.. Now we need to set up the maildirs. You'll want to check permissions on this, too... I think I just set "mail" the owner of the virtual maildirs.

Code: 
mv /var/spool/virtual/<domain>/<user> /var/spool/virtual/<domain>/<user>.bak
maildirmake /var/spool/virtual/<domain>/<user>/Maildir
or
maildirmake /home/<user>/Maildir

There are a number of scripts to convert an mbox format mail file into a maildir...

On restart, exim will begin delivering mail for real users into /home/<user>/Maildir and virtual users into /var/spool/virtual/<domain>/<user>/Maildir. Try sending a message to a user and then checking in the ~user/Maildir/new directory for the file.

It would be a good idea at this point to create a maildir for your /etc/skel folder with a "Sent", "Drafts", "Trash" structure, etc.


Configuring the MDA (courier-imap) for delivery:

Most courier-imap 3.x versions require fam which requires portmap. If you're using an RPM repository, fam should be configured as a prerequisite and installed. Fam typically runs out of inetd or xineted and chkconfig --list should show you the service. Both portmap and fam should be running.

edit /usr/lib/courier-imap/etc/authdaemonrc to auth against mysql:
Code: 
authmodulelist="authmysql authcram"

edit /usr/lib/courier-imap/etc/authmysqlrc to contain information about your databse, etc.
Code: 
MYSQL_SERVER            localhost
MYSQL_USERNAME          admin_mail
MYSQL_PASSWORD          asecretpass
MYSQL_SOCKET           /var/lib/mysql/mysql.sock
MYSQL_DATABASE        admin_mail
MYSQL_USER_TABLE        users
MYSQL_CRYPT_PWFIELD     crypt
MYSQL_CLEAR_PWFIELD     clear
MYSQL_UID_FIELD         uid
MYSQL_GID_FIELD         gid
MYSQL_LOGIN_FIELD       id
MYSQL_HOME_FIELD        home
MYSQL_NAME_FIELD        name
MYSQL_MAILDIR_FIELD     maildir

While we're here, let's edit imapd and pop3d to support SSL, TLS and CRAM-MD5:

/usr/lib/courier-imap/etc/imapd:
Code: 
IMAP_CAPABILITY="IMAP4rev1 UIDPLUS CHILDREN NAMESPACE THREAD=ORDEREDSUBJECT \
THREAD=REFERENCES SORT QUOTA AUTH=CRAM-MD5 AUTH=CRAM-SHA1 IDLE"
IMAP_CAPABILITY_TLS="$IMAP_CAPABILITY AUTH=PLAIN"
/usr/lib/courier-imap/etc/imapd-ssl:
Code: 
IMAPDSSLSTART=YES
IMAPDSTARTTLS=YES
/usr/lib/courier-imap/etc/pop3d:
Code: 
POP3AUTH="LOGIN CRAM-MD5 CRAM-SHA1"
POP3AUTH_TLS="LOGIN PLAIN"

Also make sure to set the IMAPD/POP3DSTART=YES flags for the services you want to enable and enable TLS, etc. in pop3d-ssl. You'll probably also want to generate some new ssl certificates... see the /usr/lib/courier-imap/share folder for some scripts.

Now we can shutdown imap (via xinetd) and vm-pop3d (via DA's console) and start courier-imap (service courier-imap start).

At this point, assuming you've entered an email user's information into mysql, you should be able to receive mail into a maildir via exim and use pop and imap to retrieve those messages via courier. Remember that /var/log/maillog and /var/log/exim/mainlog are your friends. Also remember that all users are now getting delivery to maildir folders and they might have to exist before exim will deliver to them and that all authentication is now being done through mysql, so if you don't have a user/pass in the mysql db, you won't be able to authenticate to the mail system.


Configuring SMTP-AUTH:

To use SMTP-AUTH against mysql, modify your exim.conf to contain lines like this:
Code: 
MYSQL_AUTH_PLAIN = SELECT crypt FROM users WHERE id = '$2'
MYSQL_AUTH_LOGIN = SELECT crypt FROM users WHERE id = '$1'
MYSQL_AUTH_CRAM = SELECT clear FROM users WHERE id = '$1'

plain:
  server_condition = "${if and {{!eq{$2}{}}{!eq{$3}{}}{crypteq{$3}{ \
${lookup mysql {MYSQL_AUTH_PLAIN} {$value}fail}}}}{1}{0}}"
login:
  server_condition = "${if and {{!eq{$1}{}}{!eq{$2}{}}{crypteq{$2}{ \
${lookup mysql {MYSQL_AUTH_LOGIN} {$value}fail}}}}{1}{0}}"

cram:
  driver = cram_md5
  public_name = CRAM-MD5
  server_secret = "${lookup mysql{MYSQL_AUTH_CRAM}}"
  server_set_id = $1

Restart exim and test smtp-authentication... you should be able to use PLAIN, LOGIN and CRAM-MD5 authentication. Make sure that you are not an open relay!

Configure pop-before-smtp:

pop-before-smtp is currently provided by the popb4smtp process which watches the maillog and writes IPs that authenticate against a flat file. Unfortunately, courier's logging breaks this solution. So, for this guide, we'll use two programs, drac and drac-add, which should actually scale much better than a log scrubbing solution. drac is a process that requires portmap and listens for rpc calls. drac-add is added to courier's pop authentication system.

So, what happens is that when someone authenticates over pop, they go through courier's normal auth sequence and then also pass through drac-add. If they are authenticated, drac-add makes an RPC call to drac which adds the IP address in a database (I edited drac's makefile to use /etc/drac/dracd.db). Exim then consults this database when it consults the relay_hosts. This could probably be done for imap as well, but I figure if you have an imap client you probably have smtp-auth as well.

  • Ensure that portmap is running and that you have a firewall rule protecting it
  • Start drac, /usr/local/sbin/rpc.dracd
  • mv the drac-add binary into /usr/lib/courier-imap/libexec/authlib/
  • add drac-add the authmodulelist for /usr/lib/courier-imap/etc/pop3d:
    Code: 
    AUTHMODULES="authdaemon drac-add"
  • edit exim.conf:
    Code: 
    hostlist relay_hosts = net32-dbm;/etc/drac/dracd.db : 127.0.0.1


Notes and TODO

This solution requires that ALL users (real and virtual) store a crypted and a cleartext version of their password in a mysql table. The danger of storing the cleartext value in the database is outweighed, IMHO, by the advantage of never sending a password by utilizing hashes in CRAM-MD5. However, the security of the database as well as the limited support for the scheme must be considered. If you're uncomfortable with storing cleartext passwords, just remove everything related to cram-md5 in this howto.

Requiring all users to exist in this database also requires that DirectAdmin have the ability to alter this data (during account creation/deletion, password change, etc.). I have a plugin and a couple of custom scripts that I'm using successfully so far. The one issue I had was storing the mysql info for the plugin in a file that must be executable (and thus readable) by any user... At John's suggestion, I ended up using a small wrapper that is suid diradmin which calls the actual script that does the work and contains the mysql information.

Forwarding, autoresponders, vacation messages, etc. are lightly tested (and should function).

Quotas for virtual users should be fine since we're using the same filename for the maildir's enclosing folder that was used for the mbox. However, real users mail will now be stored in "~/Maildir" instead of "/var/spool/mail/<username>" and "~/mail". I'm not sure if mail quotas are ever calculated for real users, and I don't know if this affects them.

Since we're already using mysql, it should be simple to replace drac and drac-add with a script that will replace drac-add as a courier authentication process and simply take the user's current IP address and write it to a mysql table that we can then use an exim mysql lookup for pop-before-smtp.

Comments, suggestions, etc. are welcome!
 
Last edited:
Wow you have come along way. I hope to test this shortly on a test server. Getting closer.
 
ballyn is correct about the challenge. I am spening some of my free time trying to create and authentication module for use with Direct Admin. That should be all that is needed to have a fully functional courier-imap with DA, and still support changing passwords from the Control Panel, virtual and system users. Their layout for creating a new authentiaction module is a little complex, but I think I can massage a lot of the code changes that were made to UW into Courier.

The UW changes use a lot of global variables that are UW specific, but I think I can replace them with local variables and come up with a workable module.

The nice thing about courier, is they separated the authentication and the IMAP server. The authentication part is where you set the Maildir and authenticate the user, and the IMAP server remains untouched.
 
So I've looked at skins and plugins... it's pretty straightforward to modify the process to change an email (virtual) user's password. I have a prototype but I need to look at security, etc.

If you don't want to use mysql for real users passwords, it's simple to add "AUTHPAM" to the authmysqlrc file and then use that in addition to mysql. That change, along with a custom skin/plugin for pop accounts (and some quota work) should complete the conversion with the exception of smtp-auth for real users.

However, I would personally prefer to have different passwords for email than I do for ssh. By using SSL for DA, the only way in which a cleartext password could be transmitted is via pop/imap, so using different passwords for the mail accounts adds a lot more security to the system.

The skinning/plugin work becomes much greater at that point, and I'd like to hear the general opinion about using mysql vs. some other method (and DA's thoughts, maybe) before I get into it.
 
Last edited:
I was thinking about this some more and another thing that would need to be done, is to use Courier's POP3 service, since the current DA POP3 server relys on mbox mail files.

I personally would prefer to have a MySQL based system, it would really scale a lot better than parsing passwd files. But would need to be integrated with the skins.
 
toml said:
I personally would prefer to have a MySQL based system, it would really scale a lot better than parsing passwd files. But would need to be integrated with the skins.
Click to expand...

I don't speak for DirectAdmin, but my gut feeling is they won't like this if they ever decide to implement this in the future since that would complicate the installation process as well as make the webmail service dependent on MySQl. Whereas, at the moment, most services are pretty compartmentalized.
 
jmstacey said:
I don't speak for DirectAdmin, but my gut feeling is they won't like this if they ever decide to implement this in the future since that would complicate the installation process as well as make the webmail service dependent on MySQl. Whereas, at the moment, most services are pretty compartmentalized.
Click to expand...

That is the reason I was posting it. I do think MySql authentication would be the best thing, but for little to no impact on DA, I think the current /etc/passwd and ~/.shadow system would be the easiest to support for DA.

As I said before the other thing that would need changing is POP3. I have never used the Courier POP3 server, but I assume they use the same authentication server as IMAP, so it should be able to be just dropped in.
 
This guide covers using courier's pop3 daemons and should function for using pop3 over ssl as well... I just can't get the SSL part working right now. :) Regular POP using CRAM-MD5 works fine, however, and also uses drac to allow pop-before-smtp.

While I agree that converting to mysql appears to be a more complicated/risky solution, the current mail solution relies on:

exim, vm-pop3d, popb4smtp, xinetd, uw-imap plus PAM and a flatfile for every domain with email accounts

While assuming we can get smtp-auth working using mysql, this solution relies on:

exim, courier-imap, mysql

The scalability and performance is undoubtedly much higher as well.
 
Whatever is done should also anticipate the ability to have the mail server on it's own server. Whatever configuration would best lend itself to a seperate mail server should be used. Obviously we are looking for speed, stability and scalability. Either way, I believe this is far less complicated then the current mail system. DA can easily implement any changes needed for the DA skin. It is good to see this has come a long way already and I am sure this is very encouraging news to all DA users as well as DA themselves.
 
I posted some code to allow courier to use the existing /etc/passwd and /etc/virtual/$domain/passwd files. I modifed the custom auth module from the 0.53 version of courier-authlib. You can see the file that was changed HERE . There may still need to be a litte more work done to it, but it was authenticating fine for me on my Solaris box with some dummy virtual domain files in place.
 
I'm almost done putting together a plugin that allows users to change email passwords in a courier-mysql environment. Essentially, I'm completely separating DA/OS passwords from email passwords and adding a "Change Email Password" hook to DA. Admins can change all passwords, Resellers can change their users' passwords and users can change their own password. "Virtual" (pop3) accounts are handled as before and I'm using the custom scripts to create/destroy user and pop3 accounts.

I have noticed, however, the courier does use the UID/GID flags in the mysql table I described when logging in a user. Courier will use that uid to access the users Maildir. So, I have to figure out how to get the UIDs right for the table.

So, my question... when is the users' mbox file created? I haven't touched any of DA's processes regarding account/pop3 creation, but I can't figure out where in the process the mbox file was being created. Will Exim create it when it receives a message for it? Wouldn't this cause a client (like squirrelmail) to fail if the user tried to check the account before a message was sent to it?

Thanks...
 
So I think my plugin/scripts are complete and functional. I ended up grabbing the uid/gid from a stat on the homedir for real users and using mail for pop3 accounts. I also tweaked exim.conf to create the maildir folders if they don't exist with the correct permissions.

I've also tested autoresponders and a few other things which all worked as expected (since we haven't really touched that code). I'm still stuck on pop3-ssl, but since I don't even use pop I haven't spent much time on it... I'm thinking it's a certificate thing or something broken in the rpm build I used vs. the openssl library it's linking against...

Anyways, if anyone gets to the point of getting courier et errata installed and wants to look at the plugin/scripts, let me know.
 
How about submitting this to DA for review?

How does this affect current existing mail accounts?
 
Is someone planning to make this into a full-fledge DA plugin? If so, I know *a lot* of people here would be eternally thankful!

Keep up the great work!
 
It would be REALLY difficult to make this a full plugin, especially if you wanted to support more than one OS.

I'm in the process of writing a script that will try to install the pieces, but it will likely only work with RH/Centos in the first iteration.

I'm currently working on how to migrate accounts... I'd like to make sure that it's possible before I get into automating the install.
 
ballyn said:
It would be REALLY difficult to make this a full plugin, especially if you wanted to support more than one OS.

I'm in the process of writing a script that will try to install the pieces, but it will likely only work with RH/Centos in the first iteration.

I'm currently working on how to migrate accounts... I'd like to make sure that it's possible before I get into automating the install.
Click to expand...

We'll wait then :)
Thank's for your effort, really appreciate it.
 
I have a preliminary script ready for testing that will install and configure courier, mysql and exim (including per-user spam boxes). I'm kind of waiting on a new exim RPM, but if you have a RHE 3 or Centos 3 DA box that you can use to test the script on (i.e might blow up), please let me know via email or PM.

Thanks!
 
Hey Ballyn,

Just a suggestion - I don't like the idea of using MySQL to power email services, unless it's a fully redundant external MySQL server being used.

How about using an SQLite database, which is more self-contained?

Matt
 
Haven't even considered it. I chose MySql since it already exists on all DA boxes.

The SQL is pretty portable in the scripts, but I don't know if courier has an auth plugin for it.
 
You must log in or register to reply here.
Back
Top