# Our addresses table contains the username@DomainID information we're looking for
	CREATE TABLE Address (
	  AddressID bigint(64) unsigned NOT NULL auto_increment,
	  Target varchar(255) NOT NULL default '',
	  DomainID int(16) unsigned NOT NULL default '0',
	  AddressTypeID int(8) unsigned NOT NULL default '0',
	  DataField text NOT NULL,
	  PRIMARY KEY  (Target,DomainID),
	  UNIQUE KEY AddressID (AddressID)
	) TYPE=MyISAM;
	# --------------------------------------------------------

	#
	# Table structure for table `AddressType`
	#

	CREATE TABLE AddressType (
	  AddressTypeID int(8) unsigned NOT NULL auto_increment,
	  AddressType varchar(255) NOT NULL default '',
	  PRIMARY KEY  (AddressTypeID),
	  UNIQUE KEY AddressTypeID (AddressTypeID),
	  KEY AddressTypeID_2 (AddressTypeID)
	) TYPE=MyISAM;
	# --------------------------------------------------------

	#
	# Table structure for table `Domain`
	#

	CREATE TABLE Domain (
	  DomainID int(32) unsigned NOT NULL auto_increment,
	  Domain varchar(255) NOT NULL default '',
	  UsernamePrefix varchar(255) NOT NULL default '',
	  PRIMARY KEY  (DomainID),
	  UNIQUE KEY Domain (Domain),
	  KEY Domain_2 (Domain)
	) TYPE=MyISAM;
	# --------------------------------------------------------

	#
	# Table structure for table `UserData`
	#

	CREATE TABLE UserData (
	  UserID bigint(64) unsigned NOT NULL auto_increment,
	  Username varchar(64) NOT NULL default '',
	  Password varchar(64) NOT NULL default '',
	  DomainID int(32) unsigned NOT NULL default '0',
	  Enabled tinyint(1) unsigned NOT NULL default '0',
	  DomainMgr tinyint(1) unsigned NOT NULL default '0',
	  Comment text NOT NULL,
	  PRIMARY KEY  (UserID),
	  UNIQUE KEY Username (Username),
	  KEY Username_2 (Username),
	  KEY DomainID (DomainID)
	) TYPE=MyISAM;

	INSERT INTO AddressType VALUES (1, 'Alias');
	INSERT INTO AddressType VALUES (2, 'Forwarder');
	INSERT INTO AddressType VALUES (3, 'CatchAll');
	INSERT INTO AddressType VALUES (4, 'Multi Recipient');
	INSERT INTO AddressType VALUES (5, 'Bounce');
	INSERT INTO AddressType VALUES (6, 'DevNull');

	

I mentioned that PAM allows you to bridge systems. What I mean by this in example is that you could install mod_auth_pam to apache and have it poll the same table in order to allow the email customer to have access to a control panel to do things like change their password. Although, I personally would probably use mod_auth_mysql for such a task, it was an easy example.

To test this system, add a domain (that has it's MX record pointed to your server) to the domains table. Write down the id number that you get. This is what the other tables will reference. Add a username/password (don't forget to use the PASSWORD() function in mySQL to encrypt the password) and set the domain to the number you got from the other table. Then set the enabled flag to 1. Lastly set the domainmgr flag to 1. We'll use this flag to tell us (via web-management) who has access to change the domain and who doesn't. You'll want to add an entry to the addresses table as well so we can test the recieving of mail. "Target" will be whatever is left of the @ sign of an email address. So maybe we can use "test", then set the domain to the number you have from the domain table. Set the addresstype to 1. We'll be using 1 to indicate a local email address, 2 to indicate an forwarder, 3 to indicate a "catchall", 4 to indicate an address we'd like to have "bounce", and 5 for one that just silently gets discarded. The datafield information will change based on the addresstype. For a type 1, it'll be the local username. For type 2 it will be the email address we intend to forward to, and for type 3 it'll be the address we want mail to goto when the left side doesn't match any users we have set up (thus the name "catchall"). The right side of the email address of course is generated from the domainID you put in. Type 4 & 5 require no datafield.

	imap    auth    optional        pam_mysql.so    db=mailserver table=userdata \
		usercolumn=username passwdcolumn=password crypt=2 user=mailserver where=enabled=1
	imap    account required        pam_mysql.so    db=mailserver table=userdata \
		usercolumn=username passwdcolumn=password crypt=2 user=mailserver where=enabled=1
	

While the README to pam-mysql should be adequate in describing these lines, the important parts are the "crypt=2" which tells PAM that we want to use mySQL PASSWORD() type fields in the database, and the line that says "where=enabled=1" which says that we only want positive results for those records that not only match our username/password combination we pass to it, but also have the enabled tick set to 1. This will allow us to control access later in the game without having to delete the account.

What we need to do now is set the default password for the administrative account.

As a side note, to delete said user, you will set the "c" flag to their account

Now edit your /etc/rc.conf file. Edit/Replace the lines with these corresponding entries.

	daily_status_mail_rejects_enable="NO"
	daily_status_include_submit_mailq="NO"
	daily_submit_queuerun="NO"
	sendmail_enable="YES"
	sendmail_flags="-bd"
	sendmail_outbound_enable="NO"
	sendmail_submit_enable="NO"
	sendmail_msp_queue_enable="NO"
	

Now we'll move on to the postfix setup.

Allow it to "replace sendmail in /etc/mailer.conf"
With the mailbox created, we can now generate the mysql rows that'll tell postfix what to do with the mail. We'll have to make a virtuals file for postfix using that same data.

Put a domain name into the domains table. Generate a "test" username record in the username table and specify that it's enabled and that it's domain is the ID number you got from the domains table. Then goto the address table and generate a type 1 email address of info with a datafield of "test". All this will cause email for info@yourdomain.com to goto the user "test" 's mailbox. Run the utility to write out the file.

Now it is possible to compile in mySQL support into postfix and have it generate virtuals tables from that, but I didn't see where those plugins allowed for quite as detailed a virtual-hosting as my method, nor did it allow for as many types of email addresses as we'll be able to support (regular, forwarders, multi-recipients, catch-alls).

	InvalidAddress	|"exit 67"
	DEVNULL		/dev/null
	

	my+old_email_addy @ lostinfo.com     InvalidAddress
	throwaway @ lostinfo.com        DEVNULL
	

Now go into /usr/local/etc/postfix/master.cf and find the line after the one that defines "cyrus". It should have an "argv=/cyrus/bin/deliver". Well, clearly cyrus is not in the root partition. Change this to say :

	user=cyrus argv=/usr/local/cyrus/bin/deliver -e -r ${sender} -m ${extension} ${user}
	

Now open main.cf and uncomment the line that says "mynetworks_sytle = host". Now find a line that mentions "virtual_maps" and add a line under it that says "virtual_maps = hash:/usr/local/etc/postfix/virtual". This will let us use the virtual users file we'll create. Search for "alias_maps =" and add this line- "alias_maps = hash:/usr/local/etc/postfix/aliases". Scroll down a bit and find alias_database and use this line- "alias_database = hash:/usr/local/etc/postfix/aliases". Now go uncomment the line that says "#mailbox_transport = cyrus".