Mail server_Dovecot MySQL configuration - SomethingWithHorizons/mailserver GitHub Wiki

Create a subset of configuration files required for IMAP server (Dovecot).

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

Procedure

Tell Dovecot to use the SQL-stored passwords instead of system credentials: Toggle prefetch method to enable lookup of a user and the corresponding password in one command: Inform Dovecot about how to see whether a user exist, and if so to fetch its settings (uid, gid, homedir etc.):

Edit /etc/dovecot/conf.d/10-auth.conf to make Dovecot stop using the configuration file that employs the OS facilitated user authentication (/etc/passwd) and start using the configuration file that employs SQL based authentication:
```
-  !include auth-system.conf.ext
+ #!include auth-system.conf.ext
```
```
- #!include auth-sql.conf.ext
+  !include auth-sql.conf.ext
```

Edit /etc/dovecot/conf.d/auth-sql.conf.ext to have Dovecot use prefetch for better performance (using one query instead of two as the data resides in the same DB) and spam filtering reasons:

  # "prefetch" user database means that the passdb already provided the
  # needed information and there's no need to do a separate userdb lookup.
  # <doc/wiki/UserDatabase.Prefetch.txt>
- #userdb {
- #  driver = prefetch
- #}
+  userdb {
+    driver = prefetch
+  }

Append /etc/dovecot/dovecot-sql.conf.ext with info required for storage information (location and userrights) and credential (user and password) data retrieval from the database:

+ default_pass_scheme = SHA512-CRYPT

+ driver = mysql
+ connect = "host=127.0.0.1 dbname=mailserver user=mailadmin password=<MYSQL PASSWORD>"
+ user_query = SELECT CONCAT('/var/vmail/', `domain`, '/', `username`) AS `home`, `uid`, `gid` FROM `users` WHERE `username` = '%n' AND `domain` = '%d' LIMIT 1;

+ password_query = SELECT \
+         CONCAT(`username`, '@', `domain`) AS `user`, \
+         `password`, \
+         CONCAT('/var/vmail/', `domain`, '/', `username`) AS `userdb_home`, \
+         `uid` AS `userdb_uid`, \
+         `gid` AS `userdb_gid` \
+         FROM `users` WHERE `username` = '%n' AND `domain` = '%d' LIMIT 1;

<MYSQL PASSWORD> being the password of choice defined during database preparation.

Reload the configuration files into Dovecot to effectuate the changes:
```
service dovecot reload
```