postfix-dovecot-mysql/postfix.sql

------------------------------------------
-- ALTERNATIVE TABLE LAYOUT FOR TUTORIAL:
-- Email with Postfix, Dovecot, and MySQL
--	* https://www.linode.com/docs/email/postfix/email-with-postfix-dovecot-and-mysql
--	* Source: https://github.com/linode/docs/blob/master/docs/email/postfix/email-with-postfix-dovecot-and-mysql.md
--
-- This layout is more consistence and wont require as much maintanance
-- as the one in the tutorial, e.g will the setup in the tutorial
-- require
-- * update on every table if the domain name changes, even though this happens rarely.
-- * update on aliases if the main email changes, even though this happens rarely.
--
-- It also ensures that 
-- 	* the child (aliases) is deleted if a user is deleted, and not just the domain. 
-- 	  No "dangling" aliases...
------------------------------------------
-- Layout

-- | virtual_domains	|
-- |++++++++++++++++++++|
-- | id | name		|
-- | int| <domain name>	|
-- e.g
-- | 1 	| domain.tld 	|

-- | virtual_user							|
-- |++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++|
-- | id	| domain_id	| password 	| username			|
-- | int| int		| <password>	| <username withouth domain>	|
-- e.g ------------------------------------------------------------------
-- | 1 	| 1		| y9w2..fudh8h9	| admin				|

-- | virtual_aliases							|
-- |++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++|
-- | id | domain_id 	| user_id	| alias 			|
-- | int| int		| int		| <alias without domain>	|
-- e.g ------------------------------------------------------------------
-- | 1 	| 1		| 1		| adminalias			|

-- Create table for virtual domains
CREATE TABLE `virtual_domains` (
	`id` int(11) NOT NULL auto_increment,
	`name` varchar(190) NOT NULL,
	PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Create a table for all of the email addresses and passwords:
CREATE TABLE `virtual_users` (
	`id` int(11) NOT NULL auto_increment,
	`domain_id` int(11) NOT NULL,
	`password` varchar(106) NOT NULL,
	`username` varchar(64) NOT NULL,
	PRIMARY KEY (`id`),
	UNIQUE KEY `email` (`username`, `domain_id`),
	FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Create a table for the email aliases:
CREATE TABLE `virtual_aliases` (
	`id` int(11) NOT NULL auto_increment,
	`domain_id` int(11) NOT NULL,
	`user_id` int(11) NOT NULL,
	`alias` varchar(100) NOT NULL,
	PRIMARY KEY (`id`),
	UNIQUE KEY `email` (`alias`, `domain_id`),
	FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE,
	FOREIGN KEY (user_id) REFERENCES virtual_users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Insert hostname
INSERT INTO `mailserver`.`virtual_domains` (`name`) VALUES ('domain.tld')

-- Insert a user
INSERT INTO `mailserver`.`virtual_users` (`domain_id`, `password`, `username`)
VALUES ('1', ENCRYPT('password', CONCAT('$6$', SUBSTRING(SHA(RAND()), -16))), 'user');

-- Insert an alias for user
INSERT INTO `mailserver`.`virtual_aliases` (`domain_id`, `user_id`, `alias`)
VALUES ('1', '1', 'alias');