Linux tips and tricks

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

Details

Category: Mail Server

Published: Thursday, 13 June 2019 13:48

Written by Super User

Hits: 2007

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 password “mail” 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/main.cf supporting MySQL data

virtual_alias_maps = hash:/etc/postfix/virtual mysql:/etc/postfix/aliase.cf

virtual_mailbox_maps = hash:/etc/postfix/vmailbox mysql:/etc/postfix/box-sql.cf

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/aliase.cf and write to it:

user = mail # Login

password = mail # Password

dbname = maildata # DB name

hosts = 127.0.0.1 # DB Host

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

Also, create a file /etc/postfix/box-sql.cf  for processing recipients addresses.

user = mail # Login

password = mail # Password

dbname = maildata # DB name

hosts = 127.0.0.1 # 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

• Prev
• Next