1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980 |
- ------------------------------------------
- -- 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');
|