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 mail.* 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
   1. mailpass must be in a format recognizable by dovecot (from dovecotpw)
   2. isactive - enables or disables the account entirely
   3. username should be valid, and not include the recipient_delimiter specified in Postfix
3. Alias management
   1. 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.
   2. The unique key allows us to specify an alias as belonging to multiple users. All such users will receive email on that alias.
   3. alias_ext is appended to the address given by the specified user id, for their own folder management convenience.