MySQL

We'll be using the MySQL database for storing our user information. The messages themselves will be stored in Maildir format files. As we mentioned before, the default MySQL install on Fedora Core will work nicely for our system. If you want to install MySQL from source or if you're trying to set this system up on another distribution, you'll have to alter certain configuration options accordingly.

Creating a database and tables

First, as the MySQL 'root' user (that's not the system root user), you should create a new database for our mail system. Let's call it 'postfix'.

mysqladmin -u root -p create postfix

Now, in a shell, create a user 'postfix' and give it permissions to use our database:

GRANT ALL on postfix.* postfix@localhost 
IDENTIFIED BY 'your-password';

Next, create the following SQL script to generate the tables:

CREATE TABLE postfix_access (id int(10) unsigned NOT NULL auto_increment, 
  source varchar(128) NOT NULL default '',
  access varchar(128) NOT NULL default '',
  type enum('recipient','sender','client') NOT NULL default 'recipient',
  PRIMARY KEY  (id));

CREATE TABLE postfix_alias ( 
  id int(11) unsigned NOT NULL auto_increment, 
  alias varchar(128) NOT NULL default '', 
  destination varchar(128) NOT NULL default '', 
  PRIMARY KEY  (id));

 CREATE TABLE postfix_relocated ( 
  id int(11) unsigned NOT NULL auto_increment, 
  email varchar(128) NOT NULL default '', 
  destination varchar(128) NOT NULL default '', 
  PRIMARY KEY  (id));

 CREATE TABLE postfix_smtp ( 
  id int(11) NOT NULL auto_increment, 
  email varchar(100) NOT NULL default '', 
  clear varchar(20) NOT NULL default '', 
  PRIMARY KEY  (id));

 CREATE TABLE postfix_transport ( 
  id int(11) unsigned NOT NULL auto_increment, 
  domain varchar(128) NOT NULL default '', 
  destination varchar(128) NOT NULL default '', 
  PRIMARY KEY  (id), 
  UNIQUE KEY domain (domain));

 CREATE TABLE postfix_users ( 
  id int(11) unsigned NOT NULL auto_increment, 
  email varchar(128) NOT NULL default '', 
  clear varchar(128) NOT NULL default '', 
  uid int(11) unsigned NOT NULL default '0', 
  gid int(11) unsigned NOT NULL default '0', 
  homedir tinytext NOT NULL, 
  maildir tinytext NOT NULL, 
  quota tinytext NOT NULL, 
  access enum('Y','N') NOT NULL default 'Y', 
  postfix enum('Y','N') NOT NULL default 'Y', 
  PRIMARY KEY  (id), 
  UNIQUE KEY email (email));

 CREATE TABLE postfix_virtual ( 
  id int(11) unsigned NOT NULL auto_increment, 
  email varchar(128) NOT NULL default '', 
  destination varchar(128) NOT NULL default '', 
  PRIMARY KEY  (id));

Now you can enter some values for a test email address that we'll use when it's all set up:

INSERT INTO postfix_smtp VALUES (1,'mike@linux.ork','mike');
INSERT INTO postfix_users VALUES (1,'mike@linux.ork','mike', 1004, 1100,'/home/vmail','linux.ork/mike/','','Y','Y');
INSERT INTO postfix_virtual VALUES (1,'mike@linux.ork','mike');

INSERT INTO postfix_transport VALUES (1,'mail.linux.ork','local:');
INSERT INTO postfix_transport VALUES (2,'linux.ork','virtual:');
INSERT INTO postfix_alias VALUES (1,'root','mike');

These are the values we need to later test our system. When you need to add more users, the values will be more or less the same.