-- Database objects for Postfix MTA; -- CREATE DATABASE mailserver; -- GRANT ALL ON mailserver.* TO postman@localhost identified BY 'stdpass'; CREATE TABLE virtual_domains ( id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) NOT NULL ) ENGINE = InnoDB; CREATE TABLE virtual_users ( id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, domain_id INT(11) NOT NULL, user VARCHAR(40) NOT NULL, password VARCHAR(32) NOT NULL, CONSTRAINT unique_email UNIQUE KEY (domain_id,user), CONSTRAINT virtual_users_fk1 FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE ) ENGINE = InnoDB; CREATE TABLE virtual_aliases ( id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, domain_id INT(11) NOT NULL, source VARCHAR(100) NOT NULL, destination VARCHAR(100) NOT NULL, CONSTRAINT virtual_aliases_fk1 FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE ) ENGINE = InnoDB; -- The following view looks like a table with 2 columns: email, password; CREATE VIEW users1 AS SELECT CONCAT(virtual_users.user, '@', virtual_domains.name) AS email, virtual_users.password FROM virtual_users LEFT JOIN virtual_domains ON virtual_users.domain_id = virtual_domains.id; -- The following view looks like a table with 2 columns: email, destination; CREATE VIEW aliases1 AS SELECT CONCAT(virtual_aliases.source, '@', virtual_domains.name) AS email, destination FROM virtual_aliases LEFT JOIN virtual_domains ON virtual_aliases.domain_id = virtual_domains.id; -- This view is used by dovecot; CREATE VIEW users2 AS SELECT t1.user AS user, t1.password AS password, t2.name AS domain FROM virtual_users t1, virtual_domains t2 WHERE t2.id = t1.domain_id;