Part 6. Alternative methods for storing user settings. Using MySQL.

The list of articles cycle


User accounts can also be stored in the database. You can use PostgreSQL or Mysql for it.


Let’s use MySQL as a more popular option


Mysql configuring

Login to MySQL as root

$ mysql -uroot -p


Create database named maildata for our mail system

mysql> Create DATABASE maildata;


Create user named mail with passwordmail” and give him full access to our database

mysql> GRANT all ON maildata.* TO 'mail'@'localhost' IDENTIFIED BY 'mail';


Let's switch on to the newly created database

mysql> use maildata;


Create users table , with email addresses (it will be login too), passwords and the last part of the path to the mail folder.

mysql> CREATE TABLE users (email varchar(80) NOT NULL, password varchar(20) NOT NULL, path varchar(80) NOT NULL, PRIMARY KEY(email));


Also, create a table with aliases

mysql> CREATE TABLE aliase (source varchar(80) NOT NULL, destination TEXT NOT NULL, PRIMARY KEY(source));


Field values are similar to those in the files

Now fill these tables with test data


mysql> insert into users (email,password,path) values (This email address is being protected from spambots. You need JavaScript enabled to view it.','admin','study.local/admin/');

mysql> insert into aliase (source,destination) values ('postmaster@study .local','admin@study .local');


You do not have to do all the previous settings from the command line. You can do all this with a web interface, such as phpMyAdmin.


Postfix configuring

Let install MySQLsupport for the Postfix

# apt install postfix-mysql


Add to the file /etc/postfix/ supporting MySQL data


virtual_alias_maps = hash:/etc/postfix/virtual mysql:/etc/postfix/

virtual_mailbox_maps = hash:/etc/postfix/vmailbox mysql:/etc/postfix/


Note that it is not necessary to switch off previous data files. Postfix can use multiple data sources at the same time.


Create a file for processing aliases /etc/postfix/ and write to it:

user = mail # Login

password = mail # Password

dbname = maildata # DB name

hosts = # DB Host

query = SELECT destination FROM aliase WHERE source = '%s' # SQL request for processing aliases


Also, create a file /etc/postfix/  for processing recipients addresses.


user = mail # Login

password = mail # Password

dbname = maildata # DB name

hosts = # DB Host

query = SELECT path FROM users WHERE email = '%s' # SQL SQL request for processing mailbox addresses.



Dovecot configuring

You must install an additional package because Dovecot must have access to the users data in MySQL.

# apt install dovecot-mysql


In file /etc/dovecot/conf.d/10-auth.conf enable line:

!include auth-sql.conf.ext

Note that Dovecot, as well as Postfix, can simultaneously use multiple user databases.


Next you must add lines to the file /etc/dovecot/dovecot-sql.conf.ext:


driver = mysql

# connection settings with the database server

connect = host=localhost dbname= maildata user=mail password=mail

default_pass_scheme = PLAIN #passwords are stored in cleartext

# SQL query strings

password_query = SELECT email AS email,password FROM users WHERE email = '%u'

user_query = SELECT email AS user, '5000' AS uid, '5000' AS gid FROM users WHERE email = '%u'


Now you can use the MySQL database as a user data repository and email aliases.


The list of articles cycle

You can buy the book

"Mail server based on Postfix,

Dovecot and RoundCube"

in electronic form in the store