News | About | Get Frugalware | Packages | Documentation | Discussion Forums | Bug Tracker | Wiki | Community | Development

Virtual Users and Domains with Postfix and MySQL

From FrugalWiki

Jump to: navigation, search

Contents

Introduction

This document is written for Postfix 2.2 and higher.

This document describes how to setup Virtual Domains (Aliases and Mailboxes) with Postfix and MySQL.

About the used software:

Postfix attempts to be fast, easy to administer, and secure, while at the same time being sendmail compatible enough to not upset existing users. Thus, the outside has a sendmail-ish flavor, but the inside is completely different.

The MySQL database server is the world's most popular open source database. Its architecture makes it extremely fast and easy to customize. Extensive reuse of code within the software and a minimalistic approach to producing functionally-rich features has resulted in a database management system unmatched in speed, compactness, stability and ease of deployment. The unique separation of the core server from the table handler makes it possible to run with strict transaction control or with ultra-fast transactionless disk access, whichever is most appropriate for the situation.

Postfix VDA enables quota support for Postfix.


If you are planning to use this howto as a basis for Postfix Admin, please be aware that there is some differences in the tables.

Please read the TABLE_CHANGES.TXT

Disclaimer

This document assumes that you have some knowledge on Postfix, and MySQL. At least enough to get everything installed. Installing the software is outside the scope of this document.

This document originally based on this article.

MySQL Install

Installation of MySQL is outside the scope of this document. We will use an out of the box MySQL install (pacman -S mysql).

MySQL Setup

Create the database

  • created
  • modified
  • active

These columns are used to make your life easier together with Postfix Admin. The "active" column is not used at the moment.

Create the Alias table

#
# Table structure for table alias
#
USE postfix;
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`)
) TYPE=MyISAM COMMENT='Postfix Admin - Virtual Aliases';

Postfix is using the "address" and "goto" column.

NOTE: This table can be used for virtual .forward files. This table is nothing more than /etc/aliases that you will find on any *nix OS. Multiple destination email addresses need to be separated by a "," (comma).

Create the Domain table

#
# Table structure for table domain
#
USE postfix;
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',
  `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`)
) TYPE=MyISAM COMMENT='Postfix Admin - Virtual Domains';

Postfix is using the "domain" and "description" column.

Create the Mailbox table

#
# Table structure for table mailbox
#
USE postfix;
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`)
) TYPE=MyISAM COMMENT='Postfix Admin - Virtual Mailboxes';

Postfix is using the "username" and "maildir" column.

Populate the tables

USE postfix;
INSERT INTO domain (domain,description) VALUES ('domain.tld','Test Domain');
INSERT INTO alias (address,goto) VALUES ('alias@domain.tld', 'user@domain.tld');
INSERT INTO mailbox (username,password,name,maildir) \
  VALUES ('user@domain.tld','secret','Mailbox User','user@domain.tld/');


The first INSERT is to let Postfix know that this domain is a virtual domain and should be handled by Postfix. It's also possible to have everything in one table but I think this is nicer.

The second INSERT is a virtual alias pointing to the third INSERT.

The third INSERT is an actual Virtual Mailbox.

To make sure that the new MySQL users are working, do the following from the command line.

% mysqladmin reload

Postfix Install

Frugalware comes with postifx-2.2.x and contains sasl and mysql support plus the -vda.patch, so you don't have to compile anything yourself.

After that you have to create a directory to have all your virtual users mail dropped in, this directory needs to be owned by Postfix.

% mkdir /var/mail
% chown -R postfix:postfix /var/mail
% chmod -R 771 /var/mail

Postfix Setup

main.cf

The example below is the part that goes into your main.cf file of Postfix. The path to the mysql files might be different on your setup. The same might be for uid_maps, gid_maps and minimum_uid values. These values should be the ones from the postfix user and group. You can find these in your /etc/passwd file.

virtual_alias_maps = mysql:/etc/postfix/mysql_virtual_alias_maps.cf
virtual_gid_maps = static:1001
virtual_mailbox_base = /var/mail
virtual_mailbox_domains = mysql:/etc/postfix/mysql_virtual_domains_maps.cf
virtual_mailbox_limit = 51200000
virtual_mailbox_maps = mysql:/etc/postfix/mysql_virtual_mailbox_maps.cf
virtual_minimum_uid = 1001
virtual_transport = virtual
virtual_uid_maps = static:1001
# Quota support
virtual_create_maildirsize = yes
virtual_mailbox_extended = yes
virtual_mailbox_limit_maps = mysql:/etc/postfix/mysql_virtual_mailbox_limit_maps.cf
virtual_mailbox_limit_override = yes
virtual_maildir_limit_message = Sorry, the user's maildir has overdrawn his diskspace quota, please try again later.
virtual_overquota_bounce = yes

mysql_virtual_alias_maps.cf

You will need to put this into a text file for postfix to pickup.

user = postfix
password = postfix
hosts = localhost
dbname = postfix
query = SELECT goto FROM alias WHERE address='%s'

mysql_virtual_domains_maps.cf

You will need to put this into a text file for postfix to pickup.

user = postfix
password = postfix
hosts = localhost
dbname = postfix
query = SELECT domain FROM domain WHERE domain='%s'
#optional query to use when relaying for backup MX
#query = SELECT domain FROM domain WHERE domain='%s' and backupmx = '0' and active = '1'

mysql_virtual_mailbox_maps.cf

You will need to put this into a text file for postfix to pickup.

user = postfix
password = postfix
hosts = localhost
dbname = postfix
query = SELECT maildir FROM mailbox WHERE username='%s'

mysql_virtual_mailbox_limit_maps.cf

You will need to put this into a text file for postfix to pickup.

user = postfix
password = postfix
hosts = localhost
dbname = postfix
query = SELECT quota FROM mailbox WHERE username='%s'

mysql_relay_domains_maps.cf

You will need to put this into a text file for postfix to pickup.

user = postfix
password = postfix
hosts = localhost
dbname = postfix
query = SELECT domain FROM domain WHERE domain='%s' and backupmx = '1'


Tips & tricks

  • Never list the virtual domains in $mydestination!
  • Always create at least one alias for each virtial user. The alias can point to itself, if necessary.
  • Try the PostfixAdmin administration system to manage your virtual domains and users.
Personal tools
Namespaces
Variants
Actions