E-Mail/Server/Setup/Datenbank

Datenbank

Die Daten für die Benutzer (E-Mail-Adressen), Domänen und Aliasnamen des Mailservers werden in einer MySQL- (oder MariaDB-) Datenbank gespeichert.

• Sowohl Dovecot als auch Postfix interagieren mit diesen Daten.

Führen Sie die folgenden Schritte aus, um die Datenbank zu erstellen und Tabellen für virtuelle Benutzer, Domänen und Aliase hinzuzufügen:

1. Verwenden Sie das Tool mysql_secure_installation, um zusätzliche Sicherheitsoptionen zu konfigurieren.

• Dieses Tool wird Sie fragen, ob Sie ein neues Passwort für den MySQL-Root-Benutzer festlegen wollen, aber Sie können diesen Schritt überspringen:

# mysql_secure_installation

Antworten Sie Y auf die folgenden Fragen

• Remove anonymous users?
• Disallow root login remotely?
• Remove test database and access to it?
• Reload privilege tables now?

Log in to MySQL as a root user

# mysql -u root -p

Create a new database

CREATE DATABASE mailserver;

Create the MySQL user and grant the new user permissions over the database.

Replace password with a secure password for mailuser
CREATE USER 'mailuser'@'127.0.0.1' IDENTIFIED BY 'password';
GRANT SELECT ON mailserver.* TO 'mailuser'@'127.0.0.1';
# Run FLUSH to reload the MySQL database and apply the change:
FLUSH PRIVILEGES;

Switch to the new mailserver database

USE mailserver;

Create a table for the domains that will receive mail

CREATE TABLE `virtual_domains` (
 `id` int(11) NOT NULL auto_increment,
 `name` varchar(50) 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,
 `email` varchar(100) NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `email` (`email`),
 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,
 `source` varchar(100) NOT NULL,
 `destination` varchar(100) NOT NULL,
 PRIMARY KEY (`id`),
 FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Adding a Domain within MySQL

Verify that you are still logged into the MySQL shell.

If not, run

# mysql -u root -p to access MySQL.

Add the domains to the virtual_domains table.

• Replace the values for example.com and hostname with your own settings:

INSERT INTO mailserver.virtual_domains (name) VALUES ('example.com');

Verify the alias was added correctly by running a SELECT query on the virtual_domains table.

• Make a note of the corresponding id next to the domain as this will be used when adding emails and aliases.

SELECT * FROM mailserver.virtual_domains;

If needed, repeat this process to add another domain.

Adding an Email Address within MySQL

1. If you are still logged in to MySQL, return to your main Linux shell by typing exit and hitting enter.
2. Generate a hash using the SHA512-CRYPT encryption scheme by running the command below, replacing password with the password you’d like to use for the email user.

# doveadm pw -s SHA512-CRYPT -p "password" -r 5000

The output will look similar to {SHA512-CRYPT}$6$hvEwQ....

• Copy this output, ignoring the first 14 characters of {SHA512-CRYPT}.
• Since the SHA512-CRYPT scheme was used, the password should start with $6$.

1. Log back into MySQL as the root user:

# mysql -u root -p

1. Add the email address and password hash to the virtual_users table.

• The domain_id value (currently set to '1') references the virtual_domain table’s id value.
• If you added more than one domain, replace this value to correspond with the desired domain.
• Replace user@example.com with the email address that you wish to configure on the mail server.
• Replace hash with password hash generated in a previous step.

INSERT INTO mailserver.virtual_users (domain_id, password , email) VALUES ('1', 'hash', 'user@example.com');

1. Verify the email was added correctly by running a SELECT query on the virtual_users table.

SELECT * FROM mailserver.virtual_users;

1. If needed, repeat this process to add another email address.

Alternatively, the password hash can be generated directly within the MySQL INSERT statement above by replacing 'hash' (deleting the single quote characters as well) with one of the following:* Using the ENCRYPT() function: ENCRYPT('password', CONCAT('$6$', SUBSTRING(SHA(RAND()), -16))), replacing password with the plain text password desired for the email user.

• This function has been removed from MySQL 5.8 and above.
• Using the SHA2() function: TO_BASE64(UNHEX(SHA2('password', 512))), replacing password with the plain text password desired for the email user.
• This function generates the hash in a slightly different scheme.
• When configuring the Dovecot MYSQL settings (/etc/dovecot/dovecot-sql.conf.ext) in this guide, set default_pass_scheme to SHA512 instead of SHA512-CRYPT.

Adding an Alias within MySQL

An email alias forwards all emails it receives to another email address.

While not required, you can follow the steps below to add an email alias.

Verify that you are still logged into the MySQL shell.

• If not, run

# mysql -u root -p to access MySQL.

Add the alias to the virtual_aliases table.

• The domain_id value (currently set to '1') references the virtual_domain table’s id value.
• If you added more than one domain, replace this value to correspond with the desired domain.
• Replace alias@example.com with the desired alias.
• Replace user@example.com with the email address that you wish to forward email to.

INSERT INTO mailserver.virtual_aliases (domain_id, source, destination) VALUES ('1', 'alias@example.com', 'user@example.com');

Verify the alias was added correctly by running a SELECT query on the virtual_aliases table.

SELECT * FROM mailserver.virtual_aliases;

If needed, repeat this process to add another email alias.