||<>|| = Introduction = This page shows you how to create a simple database structure for use as virtual user and domain information store for email systems such as Postfix and/or Dovecot. = MySQL Installation and Setup = You should already have MySQL setup and configured. If not you can follow the [[MySQL5FromSource]] or [[ApacheMySQLPHP]] guides or install a full LAMP using the command {{{ sudo apt-get install lamp-server^ }}} (remember the ^ at the end). The rest of this document will assume some familiarity with the MySQL command line and will just contain example commands, not step by step instructions. = Database Structure Overview = The database will consist of three tables: domains; mailboxes; and aliases; all linked together via a common value - a domain name. For the examples in this documentation, I am using a database called "postfix" and the user will be "postfixuser". {{attachment:dboverview.png}} = Creating the Database and Tables = Create a new database, user and switch to using it with the following MySQL commands. '''(You should use much better passwords!)''' {{{ CREATE DATABASE postfix; GRANT SELECT ON postfix.* to postfixuser@localhost IDENTIFIED BY 'reallybadpassword'; FLUSH PRIVILEGES; USE postfix; }}} The first table to create is "domains". This will hold a simple list of domain names and descriptions. This is an ideal table to use for Postfix to read a list of domain names that it is collecting or relaying emails for. {{{ CREATE TABLE domains ( domain VARCHAR(255) NOT NULL PRIMARY KEY, description VARCHAR(255) NOT NULL, active TINYINT(1) NOT NULL DEFAULT 1 ); }}} The second table provides most of the user account information. This table has a dual primary key of username and domain, so that you can have the same username for different domains if you wish. Unlike other examples I have chosen '''not''' to have a path to a maildor or mailbox - instead, these are generated by the config files using the other information in the table (see examples below). This should make the databse as flexible as possible. {{{ CREATE TABLE mailbox ( username VARCHAR(255) NOT NULL, password VARCHAR(100) NOT NULL, name VARCHAR(100) NOT NULL, domain VARCHAR(255) NOT NULL, active TINYINT(1) NOT NULL DEFAULT 1, PRIMARY KEY (username, domain) ); }}} And the final table is anothe simple one. Although not completely required, this table does provide a way of creating aliases and distrobution lists very easily. {{{ CREATE TABLE aliases ( address VARCHAR(255) NOT NULL PRIMARY KEY, goto TEXT NOT NULL, domain VARCHAR(255) ); }}} That's just about the minimum that is needed from the database side. See the examples below for ideas on howto configure Postfix or Dovecot to use these tables for user information. = Example Postfix configuration files = For this example I am going to create the files required to setup virtual user maps. For a more complete understanding of how postfix's virtual delivery systems work, you should visit the [[http://www.postfix.org|postfix homepage]] or read the [[Postfix]] and [[PostfixCompleteVirtualMailSystemHowto]] pages from this wiki. (Note: this last howto is not complete, but does provide some very useful information.) First we need to create a config file that tells postfix how to read information from our database. These files should normally be created in /etc/postfix. The file in this example (/etc/postfix/mysql_virtual_mailbox_maps.cf) will be used for looking up virtual mailbox locations. Postfix will provide an email address in the form username@domain, and expects a path to a mailbox location. Note: For "hosts", I am using the loopback IP address not "localhost", otherwise postfix will try to connect to the socket file in /var/run/mysql which it would find very difficult since postfix runs in a chroot jail in /var/spool/postfix. {{{ user = postfixuser password = password hosts = 127.0.0.1 dbname = postfix table = mailbox select_field = CONCAT(domain, '/', username, '/') where_field = CONCAT(username, '@', domain) #additional_conditions = and active = 1 }}} Now that this file has been created, you will need to tell postfix to use it for looking up mailboxed by editing /etc/postfix/main.cf and adding/updating the following lines:- {{{ virtual_mailbox_maps = mysql:/etc/postfix/mysql_virtual_mailbox_maps.cf virtual_mailbox_base = /home/vmail }}} = Example Dovecot configuration = Dovecot supports using SQL databases without the need for any other modules, and by reading the example configuration file (/etc/dovecot/dovecot-sql.conf) it should be easy to edit to suit your needs. The code below is my copy of this file with the comments removed. {{{ driver = mysql connect = dbname=postfix user=postfixuser password=password host=localhost default_pass_scheme = PLAIN password_query = SELECT CONCAT(username, '@', domain) as user, password FROM mailbox WHERE username='%n' AND domain='%d' }}} For more information see the [[Dovecot]] wiki page or the [[http://www.dovecot.org/|Dovecot Homepage]]. ---- Categories: CategoryEmail