Set up MySQL-database to use with Postfix

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