Exim with MySQL user authentication 6

Some time ago I’ve decided to write something in English, so to say make it readable/helpful for more people.

The thing i want to show you is how to authenticate virtual users in virtual domains on a Exim server with MySQL database. This tutorial shows only a testing enviorment , it’s not built for business purposes, neither is it safe (no password encryption). Feel free to go ahead if you just want to learn something or experiment with Exim.

First of all , the things you would need to get started:

• Ubuntu ( I’ve tested it on 8.10 on Vmware )
• some knowledge in the subject
• tpop3d server for POP3

Step 1

Installing exim and MySQL

to install MySQL in your Ubuntu just type:

sudo apt-­get install mysql­-server mysql-­client libmysqlclient15­-dev

and then let’s kick it off with Exim daemon:

sudo apt­-get install exim4­-daemon­-heavy

Step 2

Preparing the database

So now you need to login to your mysql database as root user and type your password

mysql -u root -p

then the next thing to do is to create your database for exim , f.e. :

create database mymail

then add some domains table

CREATE TABLE domains (name VARCHAR(64) NOT NULL);

and one more for the users

CREATE TABLE users (name VARCHAR(32) NOT NULL, domain
VARCHAR(64) NOT NULL, password VARCHAR(128) DEFAULT=” NOT NULL);

Step 3

Fill the base with some example data

INSERT INTO domains (name) VALUES (‘mydomain.com’);

INSERT INTO users (name, domain, password) VALUES
(‘testaccount’,'mydomain.com’,'testpass’);

Step 4

Configuring Exim

First of all create the file : /etc/exim4/exim4.conf

If you have no idea what to start off with , here is a simple example.

Now you need to add in the “EXIM CONFIGURATION” section

hide mysql_servers = “localhost/mymail/root/pass”

Where localhost is your server , mymail is your database , root is your mySQL user name and pass is your password for that user.

Then let’s build a rule for the local domains to be searched in the database

domainlist mysql_local_domains = ${lookup mysql {SELECT nazwa FROM domeny WHERE nazwa=”${domain}”}}

Now in the “ROUTERS CONFIGURATION” section let’s add a rule for a user to be recognized as local from our mySQL database:

mysql_localuser:
	driver = accept
	domains = +mysql_local_domains
condition = ${if eq{}{${lookup mysql {SELECT name FROM users WHERE \
name='${local_part}' AND domain='${domain}'}}}{no}{yes}}
	transport = mysql_delivery
	no_more

Now let’s add a delivery roul in the “TRANSPORTS CONFIGURATION” section:

mysql_delivery:
	driver = appendfile
	file = /var/mail/virtual/${domain}/${local_part}
	delivery_date_add
	envelope_to_add
	return_path_add
        mode 0666

The key here is that you have to create a folder for each domain for example /var/mail/virtual/mydomain.com and chmod it to 1777.

After making changes in the configuration file restart the exim server by typing

sudo /etc/init.d/exim4 restart

That’s about it for the exim server.

Step 5

installing tpop3d (a POP3 server)

Download the tar.gz archive from here. Unpack the archive and go into the folder. Then type:

./configure –enable-auth-mysql –disable-auth-pam

because we want the mySQL authorization and the PAM authorization causes problems to configure (the dependancies of this install are very old) so we won’t be needing that one. All we’ve got to type now is:

make install

After this is done, your new POP3 server should be installed and you should be able to check the manual by typing man tpop3d.

Step 6

configuring tpop3d

All that’s left is to configure our POP3 server now. To do that create a file /usr/local/etc/tpop3d.conf and make it look like so:

listen-address: 0.0.0.0
max-children: 10
timeout-seconds: 30

mailbox: /var/mail/$(user)

auth-mysql-enable: yes
auth-mysql-mail-group: mail
auth-mysql-hostname: localhost
auth-mysql-database: mymail
auth-mysql-username: root
auth-mysql-password: pass
auth-mysql-pass-query: SELECT CONCAT('/var/mail/virtual/',\
'$(domain)','/', '$(local_part)'), \
CONCAT('{plaintext}', password),\
 'yourUbuntuUser', 'bsd' FROM users WHERE \
name = '$(local_part)' AND domain = '$(domain)'

you should configure it with your own data, yourUbuntuUser should be the same as your login to Ubuntu.

Step 7

Testing .. let it rock !

run telnet on port 25 (smtp)

sudo telnet 0.0.0.0 25

> mail from: xx@yy.zz

> rcp to: virtualuser@mydomain.com

> data

>this is a TEST !

> .

> quit

Then run in a second terminal our POP3 server with debug :

sudo tpop3d -d

Now in the previous window run telnet again:

sudo telnet 0.0.0.0 110

>user virtualuser@mydomain.com

>pass my_password

After successful login you can type LIST to see the messages and RETR X where X is the message’s ID.

Hope it helps somebody to figure out the basics of Exim. Have fun and Good luck