Log in into MySQL:
mysql -uroot -p
And create a new database:
CREATE DATABASE mailserver;
USE mailserver;
Create a new user to handle the mail (this user just needs read permissions):
CREATE USER 'mail_user'@'localhost' IDENTIFIED BY 'mail_user_password';
GRANT SELECT ON mailserver.* TO 'mail_user'@'localhost';
And create an admin user that we’re going to use for the controlpanel:
CREATE USER 'mail_admin'@'%' IDENTIFIED BY 'mail_admin_password';
GRANT SELECT, INSERT, UPDATE, DELETE ON mailserver.* TO 'mail_admin'@'%';
Since the mailserver doesn’t allow to serve webpages, we use %
as the host instead of localhost
.
FLUSH PRIVILEGES;
Create the tables to hold the domain, user and alias informtion:
CREATE TABLE `domains` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `users` (
`id` int(11) NOT NULL auto_increment,
`domain_id` int(11) NOT NULL,
`email` varchar(100) NOT NULL,
`password` varchar(32) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `email` (`email`),
FOREIGN KEY (domain_id) REFERENCES domains(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `aliases` (
`id` int(11) NOT NULL auto_increment,
`domain_id` int(11) NOT NULL,
`source` varchar(100) NOT NULL,
`destination` varchar(100) NOT NULL,
PRIMARY KEY (`id`),
FOREIGN KEY (domain_id) REFERENCES domains(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Insert a new domain, user and alias:
INSERT INTO `domains` (`name`) VALUES ('example.com');
INSERT INTO `users` (`domain_id`, `email`, `password`) VALUES (1, '[email protected]', ENCRYPT('secret'));
INSERT INTO `aliases` (`domain_id`, `source`, `destination`) VALUES (1, '[email protected]', '[email protected]');