View source for 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.

# Insert mailing domains as desired. If 'active' is false, effectively turns the domain off for most purposes in MTA/MDA configuration presented.
# Inserting users is somewhat more complex
## mailpass must be in a format recognizable by dovecot (from dovecotpw)
## isactive - enables or disables the account entirely
## username should be valid, and not include the recipient_delimiter specified in Postfix
# Alias management
## 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.
## The unique key allows us to specify an alias as belonging to multiple users. All such users will receive email on that alias.
## alias_ext is appended to the address given by the specified user id, for their own folder management convenience.

{{Bottom Buster}}