Mail server_Database preparation - SomethingWithHorizons/mailserver GitHub Wiki

Setup the MariaDB database and its table-structure.

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: domains, users, aliases, the tables are created in relation to each other as depicted in the UML figure below:

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.

Procedure

Start the MySQL interpretive shell:
```
mysql
```
Create the database:
```
CREATE DATABASE `mailserver`;
```
Create the user and authorize user for database access:
```
CREATE USER 'mailadmin'@'localhost';
GRANT ALL ON `mailserver`.* TO 'mailadmin'@'localhost';

SET PASSWORD FOR 'mailadmin'@'localhost' = PASSWORD('<MYSQL-PASSWORD>');
```
<MYSQL-PASSWORD> should be replaced by a stronk password of your choice and correspondingly be substituted throughout the whole guide as such).

:warning: WARNING: The password may only contain alphanumeric characters and not exceed a length of 32 characters!

Create the domains table:

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;

Create the users table and relate it to the domain table:

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;

Create the aliases table and relate it to the domain table:

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;

Exit MariaDB:
```
quit
```