Mailadmin and beyond

First, install Drupal7 on a web server equipped with a PostgreSQL database.
Then, install and activate Mail admin which comes with the "Mail admin shared domain list" sub-module.

For this, you may have to intervene on your database to actually finish the installation. If you run into "PDOException : SQLSTATE[42704]: Undefined object: 7 ERROR language "plpgsql" doesn't exists ; HINT : use […] to load the language in the db" during the module activation, you'll need to log on your PostgreSQL Drupal database with sufficient privileges to pass this command :

Then, you'll be able to play again the module database setup script :

After that, your module will be installed. You'll got a new E-mail administration link (/mailadmin) in your admin user profile page.

From here, you'll be able to do nothing, until you first set at least one domain in your "shared domain list".

Happily, the second module is dedicated to the management of this list, and you can reach its interfaces typing this address : /mailadmin/shareddomain/

Once you set some domains, they fill the lists of the main module, allowing you via some clicks in Drupal to define :

• mailboxes ;
• mail forwards ;
• domain aliases.

Thank you Mailadmin.

The module records these settings in a clean database, and that's all. It prevents evident unworkable settings to be input, but nothing more. It's just a good graphical web user interface on top of a database. To receive and consult e-mails, some work remains. We need to setup a mail transport agent to receive mails, and a pop/imap server to consult them from an e-mail client.

Mailadmin is known to work with Exim (MTA) and Dovecot (IMAP), with PostreSQL between the 3.

My example is based on Debian 6.

I assume that you successfully added a working-test-domain.fr in your shared domains list, and then a test-user@working-test-domain.fr mailbox.

I also assume that you are in a configuration where working-test-domain.fr is routable, and configured with an MX record pointing to the machine we work on. Get you a domain.

It may be a strange idea to try to consult e-mails before receiving them, but still, I unrolled things this way. So first : install dovecot.

I typed : aptitude install dovecot-imapd (as IMAP access would be enough to start with something).

As there is no magic in the universe, once dovecot is installed on your box, it requires some settings to run…

So I edited /etc/dovecot/dovecot.conf to uncomment some lines :

protocols = imap imaps
disable_plaintext_auth = no
mail_debug = yes
auth_verbose = yes
auth_debug = yes
auth_debug_passwords = yes
auth default {
    mechanisms = plain
[…]
    passdb sql {
        # Path for SQL configuration file
        args = /etc/dovecot/dovecot-sql.conf
    }
[…]
    userdb static {
        args = uid=5000 gid=5000 home=/var/vmail/%d/%n allow_all_users=yes
    }
[…]
}

Ok, that was fun until this last line : args = uid=5000 gid=5000 home=/var/vmail/%d/%n allow_all_users=yes

But your mails need to be stored somewhere on your machine hard-drive, and you have to choose where in order to tell dovecot where to find them.

So I followed this tutorial : http://workaround.org/ispmail/lenny/configure-dovecot

Which explains that you may create a "vmail" user (with uid=5000) on your system to store your mail in his special home directory :

$ groupadd -g 5000 vmail
# useradd -g vmail -u 5000 vmail -d /var/vmail -m

So did I, and following the tutorial I also set up in dovecot a mail_location variable defining sub-directories to store everyone e-mails in, with a simple domain/user_name/e-mails tree structure :

So, now, you might understand this previous last line, which states that users can statistically be found with the permissions of user and group 5000 in a dynamically constructed path :

Else, all the debug and verbose related stuff is mention here just for you to find out in your logs why it refuses to work when its broken. If everything works, you may comment it out to improve performances in production configuration.

But we're not done yet, as you noticed, this previous configuration file loads the dovecot-sql.conf file. There are three useful lines in my file :

# read your Drupal site settings.php to find the good values
connect = host=localhost dbname=[drupal-db-name] user=[drupal-db-user] \
    password=[drupal-db-passwd]
# to be true, this one might not be useful is our case
default_pass_scheme = SSHA256
password_query = \
  SELECT local_part AS username, domain_name AS domain, password, \
  home AS userdb_home, uid AS userdb_uid, gid AS userdb_gid \
  FROM mailadmin_mailboxes WHERE domain_name = '%d' AND local_part = '%n'

Hum, I know, the complexity always appears in the last line… This one is an SQL statement, which aims to allow dovecot to retrieve the passwords of the existing users when someone tries to login and consult their mails. You did set these passwords up via Mailadmin, which stored it in your Drupal database. The fields need to be retrieved with these exact names. They are took from the "mailadmin_mailboxes" table (which is one of the 4 tables of the module). In fact, only the three fields of the first line are needed, the others are here because they exists in the database, and may be used in a prefetch configuration, but the Mailadmin module don't seem to manage them (at least yet…). The last part of the query ensure that you pick the right password.

Then, reload your dovecot configuration with :

# /etc/init.d/dovecot reload
Reloading IMAP/POP3 mail server: dovecot.

And you can test your connectivity from every machine connected to internet via, for instance :

Because, yes, mutt needs twice the @domain part to first know where to connect to, and then connect via a full e-mail address, for dovecot to split it into domain and local_part.

mutt may ask you to accept a certificate (for TLS authentication is you didn't set better certificates), and then your password. The level is complete when you successfully log on your new account.

As explained in the first part, your domain(s) have to route traffic to your machine. It's easy to verify it once you got a running exim4 instance on your box :

We chose to install the heavy version of the daemon because the light one don't have SQL support.

Then, at this point we know that we'll have a good time trying to tame exim to read deliver mails to our database-defined virtual local users.

Exim configuration can be divided into a lot of small files, or concatenated all in one. But in any cases, Debian also provide a "update-exim4.conf" system, based on the /etc/exim4/update-exim4.conf.conf file (not a joke !).

In this 1st file, it's possible configure some big parts :

dc_eximconfig_configtype='internet'
dc_local_interfaces='127.0.0.1 ; ::1 ; '
dc_use_split_config='false'

In my configuration, I let the rest in default state. So the main points are :

• I chose the "internet" flavor of Debian auto-configuration script of exim4 (ran during the installation) ;
• I added the IP address of the internet door of the machine in the listened interfaces of exim, so he will get mails coming from the wild outside ;
• I told exim to read his configuration from the one-file version.

After modifications, run :

It's a script which manage to write the now configuration item where exim4 expects them.

Once exim is focused on the one-file conf, lets personalize it. Ok, it's a ~2000 lines file, but reading a first and modest tutorial : http://www.janoszen.com/2010/03/22/the-big-exim-tutorial/ you can start to understand how it works… It's divided in kinda chapters :

• begin acl
• begin routers
• begin transports
• begin retry
• begin rewrite
• begin authenticators

Before the first chapter, there are ~400 lines of "introduction settings", in which I added on top :

It should work also with the socket version of exim/postgresql possible connexion :

But authentication failed this way in my case.

Then, we must explain to exim how to get the domains to accept e-mails for :

domainlist local_domains = ${tr { ${lookup pgsql{SELECT DISTINCT domain_name \
	FROM mailadmin_shareddomains} } } {"\n"} {:} }

The inner SQL statement just retrieve the domains from the corresponding dedicated Mailadmin table (mailadmin_shareddomains), and present them in a colon-separated list. Other tutorials usually don't bother with this detail, but I didn't manage to avoid it. The trick comes from : http://gablog.eu/online/node/34.html

Then, even if Mailadmin does his best to avoid you entering twice the same domain in the list, it seemed prudent to enforce the DISTINCT SQL clause.

To finish here, it seems you need a primary_hostname :

Then, I jumped to the end of the section routers. In this section, named router rules are defined. Debian comes with a lot of its own rules, but we have to add one to connect Exim with the database. Router rules work like coral polyps, trying to grab the e-mails received by Exim, deciding if they fit their delivering conditions.

mailadmin_domains:
  debug_print = "R: mailadmin_domains $local_part@$domain"
  driver = accept
  domains = +local_domains
  condition = ${lookup pgsql{SELECT local_part || '@' || domain_name \
    FROM mailadmin_mailboxes \
    WHERE domain_name = '${quote_pgsql:$domain}' \
    AND local_part = '${quote_pgsql:$local_part}'}}
  self = send
  transport = vmail_users

The three important things here are :

• the accepted domain list, only mails from domains took from our previous SQL query are kept here ;
• the local_part condition, with an SQL statement ensuring that we know the exact required e-mail address ;
• the transport statement which branch the e-mail handling to one of the named transport rules.

Our vmail_users transport rule, defined after the "begin transports" statement looks like :

vmail_users:
  driver = appendfile
  maildir_format = true
  directory = /var/vmail/$domain/$local_part/Maildir
  create_directory
  delivery_date_add
  envelope_to_add
  return_path_add
  user = vmail
  group = vmail
  mode = 0600

Important parts are :

• directory, matching the tree structure dovecot expects ;
• user, group and mode, compliant with unix right management.

My inspiration for this part came from :

• http://www.tq01.com/configuring-exim-with-virtual-users-on-debian
• http://www.tty1.net/virtual_domains_en.html

Once you edited the config file, reload exim with :

If it doesn't complain, you can try sending your first e-mail, and wait in your e-mail client to watch at its arrival.

But things can go wrong. When exim4 fails to reload, it writes a paniclog, that you have to remove yourself in order to avoid warnings about it's existence.

And, instead of waiting for your e-mail to come in your e-mail client, you may :

In order to read how the e-mail is handled by Exim, and if he is rejected for any reason.

Then, we still have two Mailadmin database tables unused :

• mailadmin_forwards
• mailadmin_domain_aliases

At least, to instruct Exim what to do with theses information, we wont need transport rules. Lets defined two new sets of domains :

domainlist source_domain_aliases = ${tr { ${lookup pgsql{SELECT DISTINCT source_domain_name \
        FROM mailadmin_domain_aliases} } } {"\n"} {:} }
domainlist source_domain_forwards = ${tr { ${lookup pgsql{SELECT DISTINCT domain_name \
        FROM mailadmin_forwards} } } {"\n"} {:} }

And two forwarding routers using them :

mailadmin_domain_aliases:
  debug_print = "R: mailadmin_domain_aliases $local_part@$domain"
  driver = redirect
  allow_defer
  allow_fail
  domains = +source_domain_aliases
  data = ${local_part}@${lookup pgsql{SELECT FIRST (target_domain_name) \
    FROM mailadmin_domain_aliases WHERE source_domain_name = '${quote_pgsql:$domain}' } }
  file_transport = address_file
  pipe_transport = address_pipe

mailadmin_forwards:
  debug_print = "R: mailadmin_domain_aliases $local_part@$domain"
  driver = redirect
  allow_defer
  allow_fail
  domains = +source_domain_forwards
  data = ${lookup pgsql{SELECT FIRST (destination) FROM mailadmin_forwards \
    WHERE domain_name = '${quote_pgsql:$domain}' \
    AND local_part = '${quote_pgsql:$local_part}' } }
  file_transport = address_file
  pipe_transport = address_pipe

Mailadmin database is made to ease this part, giving the right information on the right shape…

Reload Exim conf once again, and we're done.