To prepare the mailserver database you need to have access to the MySQL database, start a Terminal session and login to MySQL using the mysql command (with the admin user and password you configured earlier). You are now connected to the database. Issue the following commands to add some users and create a new database for the mailserver administration.

Change to the mysql database and add the postfix user and password:

Note: Please use a different password, or even a different username, than indicated. If you don’t other people know which password to use to enter your system. I will (for consistency) keep using this user and password throughout the documentation

USE mysql;
INSERT INTO user (Host, User, Password) VALUES
  ('localhost','postfix',password('postfixpassword'));
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('postfixadminpassword'));
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)
) ENGINE=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)
) ENGINE=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)
) ENGINE=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)
) ENGINE =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)
) ENGINE =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)
) ENGINE =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)
) ENGINE =MyISAM COMMENT='Postfix Admin - Virtual Vacation';

Next step: Create the mailbox directories