Hits : 7127

! I am working on this – be patient !

Unknown action "a"

Dovecot Mail Server :: Mini SQL Auth How To

Contents

Dovecot
Basic Installation
SQL Installation
Sieve / Index / Allow Nets

Dovecot

http://wiki.dovecot.org

Dovecot Mail Server is one of the best and most secure software in the market. The use of dovecot is extremely easy and in this documentation i will present a mini and simply how to download – install – configure and use the dovecot mail server with an sql authentication mechanism. With this feature we can use the dovecot with virtual domains / virtual users without performing tasks as add/modify/remove to actual system users.

This documentation is based upon mercurial dovecot 1.1 version, and all the below scripts are fully tested on ubuntu 8.04.

For more information you should use the above wiki link (as i did).

top

Basic Installation

The below set of commands can be used as-is for a simply but fast installation of dovecot

YOU must be root !!!

Installation Process

hg clone http://hg.dovecot.org/dovecot-1.1/ dovecot-1.1.hg 
cd dovecot-1.1.hg 
./autogen.sh 
./configure 
make 
make install

At this point we can proceed with the configuration of dovecot

Configuration Process

As you can see below, we create a system user : dovecot
to run dovecot mail server. We cant use dovecot to login.

echo 'ssl_disable = yes' > /usr/local/etc/dovecot.conf 
egrep -v '#|^$' /usr/local/etc/dovecot-example.conf | sed -e 's/pam/shadow/g' >> /usr/local/etc/dovecot.conf 
chmod 644 /usr/local/etc/dovecot.conf 
useradd dovecot -g mail 
dovecot

Check Process

We can check if the dovecot mail server is running through PS command.

The below example of telnet make use of an actual user and the
USERNAME should be a real user of the system and
PASSWORD should be the real password of the user

ps -ef | grep dov[e]cot 

USERNAME@ubuntu:~$ telnet localhost imap 

Trying 127.0.0.1... 
Connected to localhost. 
Escape character is '^]'. 
* OK Dovecot ready. 
1 login USERNAME PASSWORD 
1 OK Logged in. 
^]

top

SQL Installation

The total of changes from the above installation process are very few. But first we should ensure that our system has all the necessaries headers and libraries
so that the dovecot mail server can be installed with sql support.

For my example i use the mysql from lampp installation but there wasnt any mysql headers for the dovecot to be installed. So i had to download the same version of mysql from mysql.com and copy the include directory inside lampp. If you have already MySQL installed from debs there shouldn't be any problem.

Here is the dovecot basic installation process with sql support:

SQL Basic Installation Process

I used the MySQL Database platform for my examples, there shouldn't be any problem with postgres.

hg clone http://hg.dovecot.org/dovecot-1.1/ dovecot-1.1.hg 
cd dovecot-1.1.hg 
./autogen.sh 
./configure --with-mysql 

make 
make install

if the configure command fails then check the MySQL libraries and includes
if the MySQL has been installed under /opt you can use something like this :

export -p CPPFLAGS="-I/opt/mysql/include/" 
export -p LDFLAGS="-L/opt/mysql/lib/"

and run again the configure command again.

SQL Table

For our examples we can use this SQL Statement to create the MySQL table which we will use for the authentication.

DROP TABLE IF EXISTS `users`; 
CREATE TABLE IF NOT EXISTS `users` ( 
  `userid` VARCHAR(255) NOT NULL, 
  `domain` VARCHAR(255) NOT NULL, 
  `password` VARCHAR(255) NOT NULL, 
  `mail_type` VARCHAR(10) NOT NULL, 
  `home` VARCHAR(255) NOT NULL, 
  `uid` INT(11) NOT NULL, 
  `gid` INT(11) NOT NULL, 
  PRIMARY KEY  (`userid`,`domain`) 
) ENGINE=MyISAM;

Description of fields

userid : Is the username (eg. USERNAME@mydomain.gr)
domain : Is the hostname of the domain (eg. username@MYDOMAIN.gr)
password: Is the password of the user. If we want to use a simple password without encryption, we should add the {PLAIN} before the actual password
mail_type: Is the Mailbox Format we want for this user, according to dovecot this should be : maildir, mbox or dbox
home : Is the home of the user, this should be something like /var/mail or /var/spool/mail
uid : This is the actual User ID that the virtual users can use as system user
gid : This is the actual Group ID that the virtual users can use as system user

As you can see i used as primary key both userid and domain so we can use severals virtual users with the same username at different virtual domains.
The uid and gid can be found if we run the bellow command:

uid dovecotvu

Configuration Process

At this point we can create the configuration files of dovecot. The dovecot needs two (2) files. One for the basic configuration (as the above example at the configuration process) and another for the sql authentication mechanism.

dovecot.conf

cat > /usr/local/etc/dovecot.conf << EOF 
 
ssl_disable = yes 
disable_plaintext_auth = no 
protocol imap { 
} 
 
protocol pop3 { 
} 
protocol lda { 
  postmaster_address = root@localhost 
} 
auth default { 
  mechanisms = plain 
passdb sql { 
  args = /usr/local/etc/dovecot-sql.conf 
} 
userdb sql { 
  args = /usr/local/etc/dovecot-sql.conf 
} 
  user = root 
} 
EOF

dovecot-sql.conf

cat > /usr/local/etc/dovecot-sql.conf << EOF 
driver = mysql 
connect = host=/var/mysql/mysql.sock dbname=dovecot user=root 
password_query = SELECT concat(userid, '@', domain) AS user, password \ 
  FROM users WHERE userid = '%n' AND domain = '%d' 
user_query = SELECT concat(userid, '@', domain) AS user, home, uid, gid \ 
  , concat( mail_type, ':', home, '%d/%n' ) AS mail \ 
  FROM users WHERE userid = '%n' AND domain = '%d' 
EOF

You should change the connect with values according to your mysql server.

Description of connect :

* host : The full path of MySQL socket
* dbname : The name of the MySQL Database
* user : The user of MySQL
* password : The password of the user

At the above example there is no password because i don't have any password for user root to my MySQL !!!

Add System and Virtual Users

At the end of the configuration process we should create two (2) system users.
One user should run the dovecot and should be an unprivileged user and another user that all the virtual users should imply as system user for dovecot.

useradd dovecot -g mail 
useradd dovecotvu -g mail

We will use the dovecot user to run the dovecot mail server and the dovecotvu (dovecot virtual user) so the virtual users can imply as a system user.

We give the group mail to the users so they can have write privileges at the default mail path of the system. Usually this is /var/mail or /var/spool/mail

And now we are ready to test our installation and configuration of dovecot mail server with sql authentication mechanism.

Before you run the dovecot we should at least create a user to the above sql table. Here are two – one with maildir mail format and the other with mbox mail format.

INSERT INTO `users` VALUES ('user1', 'example.com', '{PLAIN}test', 'maildir', '/var/mail/', 1002, 8); 
INSERT INTO `users` VALUES ('user2', 'example.com', '{PLAIN}test', 'mbox', '/var/mail/', 1002, 8);

The values 1002 and 8 are from uid dovecotvu

Check Process

We are ready to start our mail server :

dovecot 

ps -ef | grep dovecot

If everything are ok then with telnet we check the mail server:

telnet localhost imap 

Trying 127.0.0.1... 
Connected to localhost. 
Escape character is '^]'. 
* OK Dovecot ready. 

1 login user1@example.com test 
1 OK Logged in. 
^] 

telnet> clo 
Connection closed.

top

Sieve / Index / Allow Nets

The sieve plugin is an implementation of sieve mail filtering. More of sieve you can read at this link : http://tools.ietf.org/html/rfc5229. With sieve you can put your mail filters at the mail server and not have them local.

Basic Installation of Sieve

Stable version:

wget -c http://dovecot.org/releases/sieve/dovecot-sieve-1.1.5.tar.gz 
tar zxvf dovecot-sieve-1.1.5.tar.gz 
cd dovecot-sieve-1.1.5 
./configure --with-dovecot=../dovecot-1.1.hg 
make 
make install

From mercurial repository :

Requirements : bison

hg clone http://hg.dovecot.org/dovecot-sieve-1.1/ dovecot-sieve-1.1.hg 
cd dovecot-sieve-1.1.hg 
./autogen 
./configure --with-dovecot=../dovecot-1.1.hg 
make 
make install

MySQL Table

For this extra implementation we will use a slide different sql table.
The point of this table is to use as simply as it's get the dovecot.conf and put all the extra functionality and user variables at MySQL, as indexes, control files and allow nets.

CREATE TABLE IF NOT EXISTS `users` (
  `userid` VARCHAR(128) NOT NULL,
  `domain` VARCHAR(128) NOT NULL,
  `password` VARCHAR(64) NOT NULL,
  `username` VARCHAR(100) NOT NULL,
  `mail_type` VARCHAR(10) NOT NULL,
  `home` VARCHAR(255) NOT NULL,
  `sieve` VARCHAR(255) NOT NULL,
  `index` VARCHAR(100) NOT NULL,
  `control` VARCHAR(100) NOT NULL,
  `nets` VARCHAR(100) NOT NULL,
  `uid` INT(11) NOT NULL,
  `gid` INT(11) NOT NULL,
  PRIMARY KEY  (`userid`,`domain`)
) ENGINE=MyISAM;

Sample Users :

INSERT INTO `users` VALUES ('ebalaskas', 'ebalaskas.gr', 'test', 'Evaggelos Balaskas', 'maildir', '/var/mail/', '.dovecot.sieve', '.index/', '.control/', '127.0.0.1', 1002, 8);
INSERT INTO `users` VALUES ('ebal', 'ebalaskas.gr', 'test', 'Evaggelos Balaskas', 'mbox', '/var/mail/', '.dovecot.sieve', '.index/', '.control/', '127.0.0.1', 1002, 8);

Configuration Process

dovecot-sql.conf

cat > /usr/local/etc/dovecot-sql.conf << EOF 
driver = mysql 
connect = host=localhost dbname=dovecot user=root 
default_pass_scheme = PLAIN 
password_query = SELECT concat(userid, '@', domain) AS user, \ 
  password, nets AS allow_nets FROM users WHERE userid = '%n' AND domain = '%d' 
user_query = SELECT concat(userid, '@', domain) AS user, uid, gid, \ 
  concat( mail_type, ':', home, '%d/%n:INDEX=', home, `index`, '%d/%n:CONTROL=', home, control, '%d/%n' ) AS mail \ 
  FROM users WHERE userid = '%n' AND domain = '%d'
EOF

top

Referring pages:

dovecot