Switching…

To prepare the postfix database you need to have access to the MySQL database, start a Terminal session change to the root user and login to mysql using the mysql -p and type in the password. You are now connected to the database. Issue the following commands to add some users and a new database for Postfix or use the ready made sql script provided at the bottom of this page.

Change to the MySQL database administration and add the postfix user and password:

USE mysql;
INSERT INTO user (Host, User, Password) VALUES
     ('localhost','postfix',password('postfix'));
INSERT INTO db (Host, Db, User, Select_priv) VALUES
     ('localhost','postfix','postfix','Y');

Add the Postfix admin user and password:

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', 'postfix',
    'postfixadmin', 'Y', 'Y', 'Y', 'Y');

Setup the rights for the just created users:

FLUSH PRIVILEGES;
GRANT USAGE ON postfix.* TO postfix@localhost;
GRANT SELECT, INSERT, DELETE, UPDATE ON postfix.* TO
     postfix@localhost;
GRANT USAGE ON postfix.* TO postfixadmin@localhost;
GRANT SELECT, INSERT, DELETE, UPDATE ON postfix.* TO
     postfixadmin@localhost;

Next, create the database that we will use for the user administration:

CREATE DATABASE postfix;
USE postfix;

Now we can create the tables, first the table for the administrators:

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';

Then the table for the ‘alias’ administration, or which external e-mail address is routed to which mailbox.

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';

The table for the domains we are going to administer is next:

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),
  KEY domain (domain)
) TYPE=MyISAM COMMENT='Postfix Admin - Virtual Domains';

The table for the domain administrators if you want to use them.

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';

A table where the changes will be logged is very useful if some users report problems and you want to find out what has been done.

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';

The table structure for the actual mailboxes.

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';

When someone wants to send a reply when he is temporary unable to get to his mail.

CREATE TABLE vacation (
  email varchar(255) NOT NULL default '',
  subject varchar(255) NOT NULL default '',
  body text NOT NULL,
  cache text NOT NULL,
  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';

Resources:

create-postfix-db.sql, use it like:

mysql -p <create-postfix-db.sql