Mail Tables (MySQL)

This is a suggested table layout for a Postfix + Dovecot + MySQL setup.

The Tables
CREATE DATABASE mail; USE mail; GRANT USAGE ON *.* TO 'vmreader'@'localhost' IDENTIFIED BY 'passwordhere';

CREATE TABLE `mail_domains` (  `ID_DOMAIN` smallint(6) NOT NULL AUTO_INCREMENT,   `domain_name` varbinary(126) NOT NULL,   `active` tinyint(1) NOT NULL DEFAULT '1',   PRIMARY KEY (`ID_DOMAIN`),   UNIQUE KEY `domain_name` (`domain_name`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `mail_users` (  `ID_USER` int(11) NOT NULL AUTO_INCREMENT,   `ID_DOMAIN` smallint(6) NOT NULL,   `username` varbinary(63) NOT NULL,   `mailpass` varbinary(126) NOT NULL DEFAULT 'defaultmailpasswordhashhere',   `isactive` tinyint(1) NOT NULL DEFAULT '1',   PRIMARY KEY (`ID_USER`),   UNIQUE KEY `users_namedomain` (`ID_DOMAIN`,`username`),   CONSTRAINT `users_domainfk` FOREIGN KEY (`ID_DOMAIN`) REFERENCES `mail_domains` (`ID_DOMAIN`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `mail_aliases` (  `ID_ALIAS` int(11) NOT NULL AUTO_INCREMENT,   `ID_USER` int(11) NOT NULL,   `alias_ext` varbinary(126) NOT NULL DEFAULT '',   `alias_local` varbinary(126) NOT NULL,   `ID_DOMAIN` smallint(6) NOT NULL,   PRIMARY KEY (`ID_ALIAS`),   UNIQUE KEY `alias_unique` (`alias_local`,`ID_DOMAIN`,`ID_USER`),   CONSTRAINT `alias_domainfk` FOREIGN KEY (`ID_DOMAIN`) REFERENCES `mail_domains` (`ID_DOMAIN`),   CONSTRAINT `alias_userfk` FOREIGN KEY (`ID_USER`) REFERENCES `mail_users` (`ID_USER`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

GRANT SELECT ON `mail`.`mail_aliases` TO 'vmreader'@'localhost'; GRANT SELECT ON `mail`.`mail_domains` TO 'vmreader'@'localhost'; GRANT SELECT ON `mail`.`mail_users` TO 'vmreader'@'localhost';

Usage
I should probably setup a web interface script for this, but the vast majority of my addresses are semi-automated abuse/postmaster/webmaster addresses.


 * 1) Insert mailing domains as desired. If 'active' is false, effectively turns the domain off for most purposes in MTA/MDA configuration presented.
 * 2) Inserting users is somewhat more complex
 * 3) mailpass must be in a format recognizable by dovecot (from dovecotpw)
 * 4) isactive - enables or disables the account entirely
 * 5) username should be valid, and not include the recipient_delimiter specified in Postfix
 * 6) Alias management
 * 7) The configuration we specify later automatically considers characters from the first recipient_delimiter on to be valid aliases, and no configuration for these is required.
 * 8) The unique key allows us to specify an alias as belonging to multiple users. All such users will receive email on that alias.
 * 9) alias_ext is appended to the address given by the specified user id, for their own folder management convenience.