Hits :
7350
! I am working on this – be patient !
Unknown action "a"
Dovecot Mail Server :: Mini SQL Auth How To
Dovecot
http://dovecot.org
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
Formatter "highlight/html" not found
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