Mail server_Postfix MySQL configuration - SomethingWithHorizons/mailserver GitHub Wiki

Create a subset of configuration files required for SMTP server (Postfix).

These configuration files enable Postfix to connect to MySQL to retrieve information about valid domains, users and aliases.

Procedure

Create the domain configuration file /etc/postfix/mysql-virtual-mailbox-domains.cf to enable domain existence checks using the database:
```
+ hosts = 127.0.0.1
+ dbname = mailserver
+ user = mailadmin
+ password = <MYSQL PASSWORD>
+ query = SELECT 1 FROM `domains` WHERE `name` = '%s' LIMIT 1;
```
<MYSQL PASSWORD> being the password of choice defined during database preparation.
Create the user configuration file /etc/postfix/mysql-virtual-mailbox-maps.cf to enable an e-mail address (user@domain) existence checks using the database:
```
+ hosts = 127.0.0.1
+ dbname = mailserver
+ user = mailadmin
+ password = <MYSQL PASSWORD>
+ query = SELECT 1 FROM `users` WHERE `username` = '%u' AND `domain` = '%d' LIMIT 1;
```
<MYSQL PASSWORD> being the password of choice defined during database preparation.
Create the aliases configuration file /etc/postfix/mysql-virtual-alias-maps.cf to enable alias destination retrieval from the database:
```
+ hosts = 127.0.0.1
+ dbname = mailserver
+ user = mailadmin
+ password = <MYSQL PASSWORD>
+ query = SELECT `destination` FROM `aliases` WHERE `source` = '%s';
```
<MYSQL PASSWORD> being the password of choice defined during database preparation.
Create the email to email configuration file /etc/postfix/mysql-email2email.cf to enable specific target e-mail address retrieval from the database:
```
+ hosts = 127.0.0.1
+ dbname = mailserver
+ user = mailadmin
+ password = <MYSQL PASSWORD>
+ query = SELECT '%s' FROM `users` WHERE `username` = '%u' AND `domain` = '%d' LIMIT 1;
```
<MYSQL PASSWORD> being the password of choice defined during database preparation.
Turn the created, human readable, configuration files into mapping-matrices of a format that postfix "understands". The last step is a trick; it combines the alias definition table and the user definition table to be dealt with as if it were one big operational alias table for Postfix*:
```
postconf -e "virtual_mailbox_domains=mysql:/etc/postfix/mysql-virtual-mailbox-domains.cf"
postconf -e "virtual_alias_maps=mysql:/etc/postfix/mysql-virtual-alias-maps.cf,mysql:/etc/postfix/mysql-email2email.cf"
postconf -e "virtual_mailbox_maps=mysql:/etc/postfix/mysql-virtual-mailbox-maps.cf"
```
:information_source: *Postfix prefers aliases (explicit redirection) over the actual existing user accounts. It does so by always checking the alias table before the user table when determining an e-mails destination (i.e. if it finds an alias source that corresponds with the "to-address" of the received e-mail, it will send it to its corresponding alias destination, rather than send it to a user that may have the same address). In other words: Alias rules overrule user (endpoint) definitions should they exist.

This is perfectly fine as long as only explicit user definitions are used in the alias table. However respecting the aforementioned preference model would make a 'catch all alias' definition indeed (always!) catch all e-mail, making no exceptions for specific e-mail addresses that may be present in the user table. Of course this could be countered by additionally including all specific user e-mail address definitions as a source and target in the alias table next to also being present in the user table to begin with. However, that would result in tedious user-management.

Instead this guide combines the alias and user definition (database) tables (in the last line of the final 'effectuation' step) as such that Postfix will effectively be presented with that combination as if it is one alias table. Using this "trickery"; specific users do not have to be added manually to the alias definition table any longer but are nevertheless encountered when postfix searches within the (combined) table that it is offered as its operational alias table.
Reload the configuration files into postfix to effectuate the changes:
```
service postfix reload
```