I'm going to upgrade my home server to Etch in the next few weeks (more or less, most likely more...), and my current mail setup is... well, it works. I thought about a virtual-users-in-a-database setup instead of reusing the old one, but I don't want to install one of the existing solutions like vexim.

Some basic thoughts:
All mails are stored in Maildir format under /var/mail//maildir/, owner of everything should be a separate user/group called vmail:vmail.

The (MySQL) database should contain three tables:

• one for the users including username, password and per-user settings
• one that contains the domains the server should handle (+ per domain settings)
• one for the actual mail addresses (+ per address settings)

These are the create statements (for now, more features and therefore more comlumns and/or tables will be added later):

CREATE TABLE IF NOT EXISTS users (
    id              SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    username        VARCHAR(255) NOT NULL UNIQUE,
    password        CHAR(32) NOT NULL,
    password_plain  VARCHAR(255) DEFAULT NULL,
    enabled         TINYINT NOT NULL DEFAULT 0,
    created         TIMESTAMP DEFAULT NOW(),
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS domains (
    id           SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    domain       VARCHAR(255) NOT NULL UNIQUE,
    enabled      TINYINT NOT NULL DEFAULT 0,
    postmaster   SMALLINT UNSIGNED NOT NULL,
    created      TIMESTAMP DEFAULT NOW(),
    FOREIGN KEY  (postmaster) REFERENCES users(id) ON DELETE RESTRICT
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS addresses (
    id           MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    local_part   VARCHAR(255) NOT NULL,
    domain       SMALLINT UNSIGNED NOT NULL,
    enabled      TINYINT UNSIGNED NOT NULL DEFAULT 0,
    user         SMALLINT UNSIGNED NOT NULL,
    UNIQUE       (local_part, domain),
    FOREIGN KEY  (domain) REFERENCES domains(id) ON DELETE CASCADE,
    FOREIGN KEY  (user) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

This is actually not to difficult to configure. Some snippets from my /etc/exim4/exim4.conf (yes, I don't use the Debian configuration scheme):

Q_LOCAL_DOMAINS = SELECT GROUP_CONCAT(DISTINCT domain SEPARATOR ' : ') FROM domains WHERE enabled=1

domainlist local_domains = @ : localhost : ${lookup mysql{Q_LOCAL_DOMAINS}}

Two basic routers:

mail4postmaster:
    debug_print = "R: mail4postmaster for $local_part@$domain"
    driver = accept
    domains = +local_domains
    local_parts = postmaster
    transport = mail4postmaster

mail4user:
    debug_print = "R: mail4user for $local_part@$domain"
    driver = accept
    domains = +local_domains
    condition = ${lookup mysql{Q_ADDRESS_EXISTS_ACTIVE_RCPT}}
    transport = mail4user

with the macro Q_ADDRESS_ACTIVE_RCPT defined as:

Q_ADDRESS_EXISTS_ACTIVE_RCPT = SELECT COUNT(*) FROM addresses JOIN domains ON \
    addresses.domain=domains.id JOIN users ON addresses.user=users.id WHERE \
    domains.enabled != 0 AND addresses.enabled != 0 AND users.enabled != 0 \
    AND domains.domain="${quote_mysql:$domain}" AND \
    addresses.local_part="${quote_mysql:$local_part}"

The two required transports are:

Q_ADDRESS_USERNAME = SELECT username FROM users JOIN addresses ON \
    addresses.user=users.id JOIN domains ON addresses.domain=domains.id WHERE \
    domains.domain="${quote_mysql:$domain}" AND \
    addresses.local_part="${quote_mysql:$local_part}"

Q_POSTMASTER_USERNAME = SELECT username FROM users JOIN domains ON \
    domains.postmaster=users.id WHERE domains.domain="${quote_mysql:$domain}"

mail4user:
    debug_print = "T: mail4user for $local_part@$domain"
    driver = appendfile
    directory = /var/mail/${lookup mysql{Q_ADDRESS_USERNAME}}/maildir
    user = vmail
    group = vmail
    mode_fail_narrower = false
    delivery_date_add = true
    envelope_to_add = true
    return_path_add = true
    maildir_format = true

mail4postmaster:
    debug_print = "T: mail4postmaster for $local_part@$domain"
    driver = appendfile
    directory = /var/mail/${lookup mysql{Q_POSTMASTER_USERNAME}}/maildir
    user = vmail
    group = vmail
    mode_fail_narrower = false
    delivery_date_add = true
    envelope_to_add = true
    return_path_add = true
    maildir_format = true

This should allow incoming messages, since no authenticators are defined, sending mail is not possible yet.

I will use Dovecot as IMAP server, it should work without problems. My next blog entry will cover this. After that I will do the exim authentication setup, quota support, filters & forwarders, auto reply, spam & malware scanning...

Any comments and suggestions are welcome...