Mail Tables (MySQL)

This is a suggested table layout for a Postfix + Dovecot + MySQL setup. I tend to manage entries manually, if only because I do not have a whole lot of users. Most of this is for webmaster and abuse accounts that need to handle bounces and such. If there is interest I could whip together a script to help manage this over the web.

The Tables

CREATE DATABASE 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',
  `privilegedemail` tinyint(1) NOT NULL DEFAULT '1',
  `publicemail` 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';