<!-- GOAL ONE-LINER --> <p>Setup the MariaDB database and its table-structure.</p> <!-- RATIONALE --> <p>The database will be used by the SMTP server (Postfix) and IMAP server (Dovecot) to retrieve domain-, user-, and alias information from. Three tables are created: <code>domains</code>, <code>users</code>, <code>aliases</code>, the tables are created in relation to each other as depicted in the UML figure below:</p> <p><img src="https://user-images.githubusercontent.com/6718345/35768447-302d2a70-08fc-11e8-855b-6360f7281d45.png" alt="image" /></p> <blockquote> <blockquote> <blockquote> <blockquote> <blockquote> <blockquote> <p>The created relations are not mandatory for Postfix or Dovecot, but to increase database maintainability. E.g. removing an entry from one table will automatically remove related entries from other tables.</p> </blockquote> </blockquote> </blockquote> </blockquote> </blockquote> </blockquote> <!-- NUANCE --> <h2>Procedure</h2> <!-- NARRATIVE FORM --> <!-- STEP BY STEP --> <ol> <li> <p>Start the MySQL interpretive shell:</p> <pre><code class="language-shell">mysql</code></pre> </li> <li> <p>Create the database:</p> <pre><code class="language-sql">CREATE DATABASE `mailserver`;</code></pre> </li> <li> <p>Create the user and authorize user for database access:</p> <pre><code class="language-sql">CREATE USER 'mailadmin'@'localhost'; GRANT ALL ON `mailserver`.* TO 'mailadmin'@'localhost'; SET PASSWORD FOR 'mailadmin'@'localhost' = PASSWORD('<MYSQL-PASSWORD>');</code></pre> <p><code><MYSQL-PASSWORD></code> should be replaced by a stronk password of your choice and correspondingly be substituted throughout the whole guide as such).</p> <p>:warning: <strong>WARNING:</strong> The password may only contain <a href="https://en.wikipedia.org/wiki/Alphanumeric">alphanumeric</a> characters and not exceed a length of 32 characters!</p> </li> <li> <p>Create the <code>domains</code> table:</p> <pre><code class="language-sql">CREATE TABLE `mailserver`.`domains` ( `name` VARCHAR(63) COLLATE utf8_unicode_ci NOT NULL COMMENT 'The name of the domain you want to receive email for', PRIMARY KEY (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;</code></pre> </li> <li> <p>Create the <code>users</code> table and relate it to the domain table:</p> <pre><code class="language-sql">CREATE TABLE `mailserver`.`users` ( `username` VARCHAR(190) COLLATE utf8_unicode_ci NOT NULL, `domain` VARCHAR(63) COLLATE utf8_unicode_ci NOT NULL, `password` VARCHAR(200) COLLATE utf8_unicode_ci NOT NULL, `uid` SMALLINT(2) UNSIGNED NOT NULL DEFAULT 5000, `gid` SMALLINT(2) UNSIGNED NOT NULL DEFAULT 5000, PRIMARY KEY (`username`, `domain`), CONSTRAINT `users_domain_domains_name` FOREIGN KEY (`domain`) REFERENCES `domains` (`name`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;</code></pre> </li> <li> <p>Create the <code>aliases</code> table and relate it to the domain table:</p> <pre><code class="language-sql">CREATE TABLE `mailserver`.`aliases` ( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `domain` VARCHAR(63) COLLATE utf8_unicode_ci NOT NULL, `source` VARCHAR(253) COLLATE utf8_unicode_ci NOT NULL, `destination` VARCHAR(253) COLLATE utf8_unicode_ci NOT NULL, PRIMARY KEY (`id`), CONSTRAINT `aliases_domain_domains_name` FOREIGN KEY (`domain`) REFERENCES `domains` (`name`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;</code></pre> </li> <li> <p>Exit MariaDB:</p> <pre><code class="language-sql">quit</code></pre> </li> </ol> <!-- REFERENCES -->