Configuring a Virtual Email Service - MySQL

If we've succesfully confirmed Virtual E-mail accounts are functioning, as in the previous configurations, then the next stage is to set up client accounts (including the ability for clients to grab mail from our mail server.) This requires that we enable the password authentication for the client connections (which we have not addressed thus far.) We will be continuing by using Dovecot as the client-side connection, and we'll use Postfixadmin to configure the SQL backend connections for connecting the user.

PostfixAdmin by Mischa Peters is a great tool for managing virtual user email accounts based on Postfix. We will install and testing the foundation database configuration here.

To minimise tools being reviewed for debugging, we're going to attempt installing MySQL support, using the postfixadmin data tables, but without installing or using postfixadmin.

Configuring MySQL

The following notes differs from a standard postfixadmin install in how it uses usernames, largely because it simplifies things for my understanding. The whole process has helped me to better understand the interactions between these different applications, finding methods for debugging installation problems. I hope it also simplifies for our understanding.

Please refer to our MySQL notes for how to install MySQL for OpenBSD.

Following Mischa's instructions at Postfix Wiki, Virtual Users and Domains we'll take a look at:

  • Creating the database
  • Creating the Alias table
  • Creating the Domain table
  • Creating the Mailbox table
  • Populating the tables

Much of these database settings are straight out of the postfixadmin/DATABASE_MYSQL.TXT file with slight/inane modifications where it helps me find things more legible.

The key differentiators between these database instructions than the default install are as follows:

  • database name is: mail instead of postfix
  • postfix user account is: postfixserver instead of postfix
  • populated sample data: username is different, password is different

Minor quibbles but makes the install instructions slightly more legible?

Creating the database

We will first log into the mysql server with an account that has root/administrator privileges and insert (copy/paste) sql commands below.

Screen Session

$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 41 to server version: 5.0.24a-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

The rest of the 'greyed' instructions can be copy/pasted into your MySQL monitor above. Be sure to change your usernames and passwords as appropriate.

The first thing we want is to tell mysql that we want to modify the records relating to user accounts for the database server.

mysql client session

USE mysql;
Next, we want to create some new settings for a new user 'postfixserver' that we want to designate for use by the postfix server. mysql client session
INSERT INTO user (Host, User, Password) 
    VALUES ('localhost','postfixserver',
    password('postfixserver'));
INSERT INTO db (Host, Db, User, Select_priv)
    VALUES ('localhost','mail','postfixserver','Y');

Next, we want to create a new user 'postfixadmin' that we want to designate for use by the postfixadmin application.

mysql client session

INSERT INTO user (Host, User, Password) 
    VALUES ('localhost','postfixadmin',
    password('postfixadmin'));
INSERT INTO db (Host, Db, User, Select_priv, 
    Insert_priv, Update_priv, Delete_priv) 
    VALUES ('localhost', 'mail', 'postfixadmin',
    'Y', 'Y', 'Y', 'Y');

To ensure that these new user settings have been loaded into use, we flush the settings.

mysql client session

FLUSH PRIVILEGES;

Now, we want to set privileges for the database that we will be using.

mysql client session

GRANT USAGE ON mail.* TO postfixserver@localhost;
GRANT SELECT, INSERT, DELETE, UPDATE ON mail.* TO
    postfixserver@localhost;
GRANT USAGE ON mail.* TO postfixadmin@localhost;
GRANT SELECT, INSERT, DELETE, UPDATE ON mail.* TO
    postfixadmin@localhost;

Next, we create the database itself.

mysql client session

CREATE DATABASE mail;

The next stage is to create the relevant tables and some dummy/sample data.

Creating the Alias table

The alias table will store/retrieve our virtual aliases (which I have not yet

mysql client session

USE mail;
CREATE TABLE `alias` (
        `address` varchar(255) NOT NULL default '',
        `goto` text NOT NULL,
        `domain` varchar(255) NOT NULL default '',
        `created` datetime NOT NULL default '0000-00-00 00:00:00',
        `modified` datetime NOT NULL default '0000-00-00 00:00:00',
        `active` tinyint(1) NOT NULL default '1',
        PRIMARY KEY (address),
        KEY address (`address`) 
    ) TYPE=MyISAM 
    COMMENT='Postfix Admin - Virtual Aliases';

Creating the Domain table

The domain table will store/retrieve our virtual domains

mysql client session

CREATE TABLE `domain` (
        `domain` varchar(255) NOT NULL default '',
        `description` varchar(255) NOT NULL default '',
        `aliases` int(10) NOT NULL default '0',
        `mailboxes` int(10) NOT NULL default '0',
        `maxquota` int(10) NOT NULL default '0',
        `quota` int(10) NOT NULL default '0',
        `transport` varchar(255) default NULL,
        `backupmx` tinyint(1) NOT NULL default '0',
        `created` datetime NOT NULL default '0000-00-00 00:00:00',
        `modified` datetime NOT NULL default '0000-00-00 00:00:00',
        `active` tinyint(1) NOT NULL default '1',
        PRIMARY KEY (`domain`),
        KEY domain (`domain`)
    ) TYPE=MyISAM COMMENT='Postfix Admin - Virtual Domains';

Creating the Mailbox table

The mailbox table will store/retrieve the usernames, passwords, and file directories

mysql client session

USE mail;
CREATE TABLE `mailbox` (
        `username` varchar(255) NOT NULL default '',
        `password` varchar(255) NOT NULL default '',
        `name` varchar(255) NOT NULL default '',
        `maildir` varchar(255) NOT NULL default '',
        `quota` int(10) NOT NULL default '0',
        `domain` varchar(255) NOT NULL default '',
        `created` datetime NOT NULL default '0000-00-00 00:00:00',
        `modified` datetime NOT NULL default '0000-00-00 00:00:00',
        `active` tinyint(1) NOT NULL default '1',
        PRIMARY KEY (`username`),
        KEY username (`username`)
    ) TYPE=MyISAM 
    COMMENT='Postfix Admin - Virtual Mailboxes';

Other Tables for postfixadmin

The database is now created, and we might as well configure the other tables used by postfixadmin

mysql client session

USE mail;
CREATE TABLE `admin` (
        `username` varchar(255) NOT NULL default '',
        `password` varchar(255) NOT NULL default '',
        `created` datetime NOT NULL default '0000-00-00 00:00:00',
        `modified` datetime NOT NULL default '0000-00-00 00:00:00',
        `active` tinyint(1) NOT NULL default '1',
        PRIMARY KEY (`username`),
        KEY username (`username`)
    ) TYPE=MyISAM 
    COMMENT='Postfix Admin - Virtual Admins';

mysql client session

USE mail;
CREATE TABLE `domain_admins` (
        `username` varchar(255) NOT NULL default '',
        `domain` varchar(255) NOT NULL default '',
        `created` datetime NOT NULL default '0000-00-00 00:00:00',
        `active` tinyint(1) NOT NULL default '1',
        KEY username (`username`)
    ) TYPE=MyISAM 
    COMMENT='Postfix Admin - Domain Admins';
USE mail;
CREATE TABLE `log` (
        `timestamp` datetime NOT NULL default '0000-00-00 00:00:00',
        `username` varchar(255) NOT NULL default '',
        `domain` varchar(255) NOT NULL default '',
        `action` varchar(255) NOT NULL default '',
        `data` varchar(255) NOT NULL default '',
        KEY timestamp (`timestamp`)
    ) TYPE=MyISAM 
    COMMENT='Postfix Admin - Log';

mysql client session

USE mail;
#
# Table structure for table vacation
#
CREATE TABLE `vacation` (
        `email` varchar(255) NOT NULL default '',
        `subject` varchar(255) NOT NULL default '',
        `body` text NOT NULL default '',
        `cache` text NOT NULL default '',
        `domain` varchar(255) NOT NULL default '',
        `created` datetime NOT NULL default '0000-00-00 00:00:00',
        `active` tinyint(1) NOT NULL default '1',
        PRIMARY KEY (`email`),
        KEY email (`email`)
    ) TYPE=MyISAM 
    COMMENT='Postfix Admin - Virtual Vacation';

Populating the tables

We will populate our database with some test data that you can easily remove later using GUI Admin. By using our sample data below we avoid having to configure postfixadmin to have a working test server.

  • Populate an administrator account for postfixadmin, so you can follow through and make your own changes to the data as the testing continues.
  • Populate Virtual Domains data
  • Populate Virtual Mailbox data (virtual users)
  • Verify our Settings

We will replicate our virtual user system used with the above hash files into our MySQL database.

Super Administrator Account.

This is largely relevant only if you will be installing postfixadmin, and can be skipped.

Now, here's one part where the standard documentation always got me lost. The standard instructions provides the below image which will work for logging into the system, but will cause other problems. Instead of the following instructions

superadmin user & password (login: admin@domain.tld, password: admin)

INSERT INTO domain_admins 
    (username, domain, active) 
    VALUES ('admin@domain.tld','ALL','1');
INSERT INTO admin 
    (username, password, active) VALUES 
    ('admin@domain.tld','$1$0fec9189$bgI6ncWrldPOsXnkUBIjl1','1');

We will be using the following instructions which uses CRYPT instead of postfixadmin's md5crypt for encrypting the password to:

  • Create the administrator account 'admin' and using 'admin' as the password.
  • make the administrator account a 'Super Administrator' with powers over all virtual domains.

mysql client session

USE mail;
INSERT INTO admin (username, password, active) 
    VALUES 
    ('admin','6dwLx9NTxhTjU','1');
INSERT INTO domain_admins (username, domain, active) 
    VALUES ('admin','ALL','1');

When installing postfixadmin, from the above settings, we set:

File: /var/www/htdocs/postfixadmin/config.inc.php

$CONF['encrypt'] = 'system';

Virtual Domains

We will be creating virtual domains for our three sample domain:

  • alpha.example.org

Creating our virtual domains<

mysql client session

USE mail;
INSERT INTO domain 
    (domain,description,aliases,mailboxes,maxquota,
        quota,transport,backupmx,active) 
    VALUES ('alpha.example.org', 'Alpha 
        Tester','10','10', '0','0','virtual', '0','1');

We can verify that the data has been entered correctly with the following simple test. From the command prompt, start mysql.

Screen Session

# mysql -u root -p
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 94 to server version: 5.0.24a-log 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer. 
mysql> use mail;
Database changed
mysql> select domain, transport from domain;
+--------------+-----------+
| domain | transport |
+--------------+-----------+
| alpha.example.org | virtual |
+--------------+-----------+
3 rows in set (0.00 sec)
Virtual Mailbox

As from our example above, we will be creating virtual mailboxes (virtual user accounts) for our above three sample domains:

  • Virtual Users for alpha.example.org

Virtual Users for alpha.example.org

Creating our virtual users for alpha.example.org : password is username

mysql client session

USE mail;

INSERT INTO mailbox 
    (username,password,name,maildir,quota,domain,active) 
    VALUES ('alfred@alpha.example.org',
        '82fU0EHEzA6wo', 'Alfred',
        'alpha.example.org/alfred@alpha.example.org/', 
        '0','alpha.example.org','1'
     );
INSERT INTO mailbox 
    (username,password,name,maildir,quota,domain,active) 
    VALUES ('bob@alpha.example.org','1bdyGcAE/JC0I', 
        'Bob','alpha.example.org/bob@alpha.example.org/', 
        '0','alpha.example.org','1'
    );
INSERT INTO mailbox 
    (username,password,name,maildir,quota,domain,active) 
    VALUES ('charlie@alpha.example.org','048qvFjqS3zBc', 
        'Charlie','alpha.example.org/charlie@alpha.example.org/', 
        '0','alpha.example.org','1'
    );

Verify our settings

Remember to change the user domains in the above to your specific virtual domain(s). You can use an sql query such as the below to help verify that you are not using the *.example. domains from this document.

mysql client session

use mail;
select username from mailbox;
select domain from domain;

Create the system user account for managing virtual mail

[Ref: virtualuidmaps, virtualgidmaps]

If you've skipped the hash virtual user instructions, then you will need to create the System user Account for Postfix to use for delivering 'virtual' mail.

Mail delivery happens with the recipient's UID/GID privileges specified with virtualuidmaps and virtualgidmaps, therefore the virtual mailbox files must be owned by a system user account and associated with a group on your system. Fortunately Postfix is flexible to allow each mailbox to be owned by a unique system user account or by a single system user account for all domains, and even one system user account per domain. This is set by using the virtualuidmaps and virtualgidmaps setting.

virtual_uid_maps = static:901
virtual_gid_maps = static:901

The 'static' map type tells Postfix that you want the uid/gid to be for all accounts.

We can now create the system user account to manage virtual email mailboxes.

useradd -d /var/spool/postfix/vmail -g=uid -u 901 \
    -s /sbin/nologin -m -c 'Virtual Mailbox Owner' _vmail
chmod -R 770 /var/spool/postfix/vmail

A by-product of the user/group creation is that the 'base' directory will also be created with the correct permissions.

If we wanted to use different users, groups for managing mailboxes, then we could have used a lookup file instead.

virtual_uid_maps = hash:/etc/postfix/virtual/uids
virtual_gid_maps = hash:/etc/postfix/virtual/gids

Ensure the standard (non-virtual) alias file is built by using Postfix's newaliases.

/usr/local/sbin/newaliases

Restart Postfix

/usr/local/sbin/postfix stop
/usr/local/sbin/postfix start

Configuring Postfix

Postfix can read it's configuration data from hash files, text files, and from databases. To tell Postfix that data will be obtained from a MySQL database, we use the 'mysql:' prefix to a text file that contains the relevant information for postfix to extract that data.

For our example, modify the above /etc/postfix/main.cf. We can work by just removing the additions made above and replacing them with the following.

File Fragment: /etc/postfix/main.cf

virtual_alias_maps = mysql:/etc/postfix/mysql/alias_maps.cf
virtual_gid_maps = static:901
virtual_mailbox_base = /var/spool/postfix/vmail
virtual_mailbox_domains = mysql:/etc/postfix/mysql/domains_maps.cf
virtual_mailbox_maps = mysql:/etc/postfix/mysql/mailbox_maps.cf
virtual_minimum_uid = 900
virtual_transport = virtual
virtual_uid_maps = static:901
parent_domain_matches_subdomains =

The /etc/postfix/mysql/*.cf files contain the login information for postfix to access and retrieve the MySQL database/table.

Verify that what we've set above is actually what postfix will recognise.

Screen Session

/usr/local/sbin/postfix reload
/usr/local/sbin/postconf -n | grep ^virtual
virtual_alias_maps = mysql:/etc/postfix/mysql/alias_maps.cf
virtual_gid_maps = static:901
virtual_mailbox_base = /var/spool/postfix/vmail
virtual_mailbox_domains = mysql:/etc/postfix/mysql/domains_maps.cf
virtual_mailbox_maps = mysql:/etc/postfix/mysql/mailbox_maps.cf
virtual_minimum_uid = 900
virtual_transport = virtual
virtual_uid_maps = static:901

Key things to watch out for is that we are using the file type: 'mysql' and that the file locations specified above will be correct to what we are creating below.

Creating the postfix to mysql settings file

Before we create our text *.cf files, we'll need to make the directory.

mkdir -p /etc/postfix/mysql

Create the current mysql instruction/configuration files for postfix.

Virtual Domains

virtual_mailbox_domains = mysql:/etc/postfix/mysql/domains_maps.cf

domains_maps.cf provide SQL login information, and instructions for listing domains.

Note: serves the same function as File:/etc/postfix/virtual/domains

File:/etc/postfix/mysql/domains_maps.cf

user = postfixserver
password = postfixserver
hosts = 127.0.0.1
dbname = mail
table = domain
select_field = domain
where_field = domain
additional_conditions = and backupmx = '0' and active = '1'

Virtual Mailbox

virtual_mailbox_maps = mysql:/etc/postfix/mysql/mailbox_maps.cf

mailbox_maps.cf provide SQL login information, and instructions for listing mailbox accounts. Serves the same purpose as /etc/postfix/virtual/accounts

File:/etc/postfix/mysql/mailbox_maps.cf

user = postfixserver
password = postfixserver
hosts = 127.0.0.1
dbname = mail
table = mailbox
select_field = maildir
where_field = username
additional_conditions = and active = '1'
Virtual Aliases
virtual_alias_maps = mysql:/etc/postfix/mysql/alias_maps.cf

alias_maps.cf will be used for virtual_alias_maps

File: /etc/postfix/mysql/alias_maps.cf

user = postfixserver
password = postfixserver
hosts = 127.0.0.1
dbname = mail
table = alias
select_field = goto
where_field = address

Restart Postfix

Once you've created all these mysql files, we can stop and restart postfix and should be working together with postfixadmin for managing virtual user accounts.

/usr/local/sbin/postfix stop
/usr/local/sbin/postfix start

Testing

We can now provide some sample testing of mail routing through to our virtual accounts, using MySQL as the database.

telnet localhost smtp

Screen Session

$ telnet localhost smtp
Trying ::1...
Connected to localhost.
Escape character is '^]'.
220 myhost.example.org ESMTP Postfix (2.3.2)
ehlo example.org
250-myhost.example.org
250-PIPELINING
250-SIZE 10240000
250-VRFY
250-ETRN
250-ENHANCEDSTATUSCODES
250-8BITMIME
250 DSN
mail from: <samt@example.org>
250 2.1.0 Ok
rcpt to: <charlie@alpha.example.org>
250 2.1.5 Ok
data
354 End data with <CR><LF>.<CR><LF>
Subject: Welcome MySQL based virtual users

Hopefully you've received this email message without fault ?


.
250 2.0.0 Ok: queued as 357E65A950
quit
221 2.0.0 Bye
Connection closed by foreign host.

Mail Log

With the following results showing in our log file: /var/log/maillog

File: /var/log/maillog

connect from unknown[::1]
client=unknown[::1]
message-id=<20070209010215.45BCC5A956@myhost.example.org>
from=<samt@example.org>, size=402, nrcpt=3 (queue active)
disconnect from unknown[::1]
to=<charlie@alpha.example.org>, relay=virtual, delay=54,
delays=52/0.01/0/1.6, dsn=2.0.0, status=sent (delivered to maildir)
removed

Mail Store

We should now have email in the user directories

  • ./alpha.example.org/charlie@alpha.example.org/new as well as

with the same content as below:

File: /var/spool/postfix/vmail/alpha.example.org/charlie@alpha.example.org

59984.myhost.example.org <
Return-Path: <samt@example.org>
X-Original-To: charlie@alpha.example.org
Delivered-To: charlie@alpha.example.org
Received: from example.org (unknown [IPv6:::1])
by myhost.example.org (Postfix) with ESMTP id 45BCC5A956;
Fri, 9 Feb 2007 14:02:08 +1300 (TOT)
Subject: Welcome MySQL based virtual users
Message-Id: <20070209010215.45BCC5A956@myhost.example.org>
Date: Fri, 9 Feb 2007 14:02:08 +1300 (TOT)
From: samt@example.org
To: undisclosed-recipients:; 
 
Hopefully you've received this email message without 
fault ?

MySQL Log file

If you're having problems then you can also take a look at the sql log files at /var/mysql/myhost.log

File: /var/mysql/myhost.log

20 Connect postfixserver@localhost on mail
20 Query SELECT goto FROM alias WHERE address='example.org'
21 Connect postfixserver@localhost on mail
21 Query SELECT domain FROM domain WHERE domain='example.org' 070209 13:50:37 20 Query SELECT goto FROM alias WHERE address='alpha.example.org'
21 Query SELECT domain FROM domain WHERE domain='alpha.example.org'
22 Connect postfixserver@localhost on mail
22 Query SELECT goto FROM alias WHERE address='charlie@alpha.example.org'
22 Query SELECT goto FROM alias WHERE address='@alpha.example.org'
23 Connect postfixserver@localhost on mail
23 Query SELECT maildir FROM mailbox WHERE username='charlie@alpha.example.org' and active = '1'
24 Connect postfixserver@localhost on mail
24 Query SELECT goto FROM alias WHERE address='charlie@alpha.example.org'
24 Query SELECT goto FROM alias WHERE address='@alpha.example.org'070209 13:50:44 
20 Query SELECT goto FROM alias WHERE address='alpha.example.org'
21 Query SELECT domain FROM domain WHERE domain='alpha.example.org'
25 Connect postfixserver@localhost on mail
25 Query SELECT maildir FROM mailbox WHERE username='charlie@alpha.example.org' and active = '1'

So, if some of the above are not working properly then you at least can get some clues from the above two log files of where you can begin debugging your installation.

Remember that postconf returns what Postfix actually understands from your changes to the ./postfix/main.cf file so it is always a good point to start here to ensure that what you thought you typed in, is actually what postfix is reading.

The next logical step in configuring your email server with Postfix, is to set up an imap/pop3 server. For this exercise, we've reviewed instructions to use dovecot.

Configuring a Virtual Email Service - MySQL high load server

Below are just collections from other people's notes, since I haven't got a 'high load' server for testing as yet (otherwise known as machines and ram are dealing well currently.)

You can improve performance in high load environments by sharing database/mysql connections among Postfix smtpd connections.

virtual_alias_maps      = proxy:mysql:/etc/postfix/mysql/alias_maps.cf
virtual_mailbox_domains = proxy:mysql:/etc/postfix/mysql/domains_maps.cf
virtual_mailbox_maps    = proxy:mysql:/etc/postfix/mysql/mailbox_maps.cf