Complete Virtual Mail Server/Postfix to Postgres

From Gentoo Linux Wiki

Jump to: navigation, search

Complete Virtual Mail Server

Getting Started

Basic Mail Setup

Enhanced Mail Services

Anti-Spam Configuration

Anti-Virus Configuration

Log Analyzer

Wrapping it Up

edit

[edit] Postfix to Postgres

We now have Postfix and Postgres running and the necessary tables created so it is time to tie these two together. For this we will write a number of access files and adjust the postfix parameters so that it all hangs together. In a lame attempt to keep things at least a little on the neat side, I put all the access files into their own subdirectory.

Code: Create Directory 
# mkdir /etc/postfix/pgsql

First things first. The initial install of Postgres will not allow us to connect from the postfix server until we go in and create an allowed host entry. I did this using the Postgres module in webmin. The “allowed hosts” page will allow you to specify who is allowed to connect and restrict access to specific users.


Code: webmin PostgreSQL Settings 
PostgreSQL client authentication details

Host address:                   Single Host – (specify your IP Address)
Users:                          Listed users …  postfix, postfixadmin
Authentication Mode:            Plaintext password

Please note that using PLAINTEXT PASSWORDS ACROSS NETWORKS IS VERY INSECURE. --Peaceful 00:07, 8 November 2006 (UTC)

If you skip this step, or it was not successful, you will know further down when we test our new email user we created. In you /var/log/messages file you will get a message along the lines of:

Code: Error Message 
FATAL:  no pg_hba.conf entry for host “xxx.xxx.xxx.xxx", user "postfix", database "postfix", SSL off

[edit] Creating Mail Users

Before we can setup any users, we need to list all of the domains that postfix will be the final destination for. In our database, this information is maintained in the domain table. I decided that I would host all domains as virtual domains. This way I do not need to setup any linux user accounts for non-virtual mailboxes. Create the following access file.

File: /etc/postfix/pgsql/pgsql-virtual-domains.cf 
#
# pgsql-virtual-domains.cf
#
# Postfix virtual_mailbox_domains
# Postfix is final destination for the specified list of domains. Mail
# is delivered via the virtual_transport mail delivery transport.
#

user         	= postfix
password     	= $password
dbname       	= postfix
hosts       	= $dbServerhostname
table        	= domain
select_field 	= description
where_field  	= domain
additional_conditions = and backupmx = 'false' and active = 'true'

Next we will link postfix to the mailbox table which defines all of the valid addresses for the domains specified.

File: /etc/postfix/pgsql/pgsql-virtual-maps.cf 
#
# pgsql-virtual-maps.cf
#
# Postfix virtual_mailbox_maps
# Lookup table with all the valid addresses in the domains that
# match virtal_mailbox_domains
#

user            = postfix
password        = $password
dbname          = postfix
hosts           = $dbServerhostname
table           = mailbox
select_field    = maildir
where_field     = email
additional_conditions = and active = 'true'

To clear some of the errors we received on the dnsreport test, we will create an access file to the virtual table. This will allow us to specify the alias addresses or domains to be forwarded to a local or remote address. This will be used to clear some of the errors we received on the dnsreport test.

File: /etc/postfix/pgsql/pgsql-virtual.cf 
#
# pgsql-virtual.cf
#
# Postfix virtual_alias_maps
# Postfix is final destination for the specified list of domains. Mail
# is delivered via the virtual_transport mail delivery transport.
#

user            = postfix
password        = $password
dbname          = postfix
hosts           = $dbServerhostname
table           = alias
select_field    = goto
where_field     = address
additional_conditions = and active = 'true'

Postfix will want to be able to look up the uid and gid that will be used for the incoming mail. In our case this will be the vmail user that we setup earlier which I have assigned a uid and gid of 1000. In order to keep this solution as flexible as possible, we will include access files that will go look this up from the mailbox table. This will allow you to use different uid and gid later if you so desire.

File: /etc/postfix/pgsql/pgsql-virtual-gid.cf 
#
# pgsql-virtual-gid.cf
#
# Postfix virtual_gid_maps
# Look up the group ID for virtual mailbox delivery by recipient.
#

user            = postfix
password        = $password
dbname          = postfix
hosts           = $dbServerhostname
table           = mailbox
select_field    = gid
where_field     = email
additional_conditions = and active = 'true'


File: /etc/postfix/pgsql/pgsql-virtual-uid.cf 
#
# pgsql-virtual-uid.cf
#
# Postfix virtual_uid_maps
# Look up the user ID for virtual mailbox delivery by recipient.
#

user            = postfix
password        = $password
dbname          = postfix
hosts           = $dbServerhostname
table           = mailbox
select_field    = uid
where_field     = email
additional_conditions = and active = 'true'

[edit] MySQL virtual-*.cf files note

When using MySQL, make sure you omit the single quotes around 'true' and 'false' in the "additional_conditions" statement (see example below). when using tinyint(1), values can be matched with true or false without the quotes, when using quotes you'll never get a match.

File: /etc/postfix/mysql/mysql-virtual-domains.cf 
#
# mysql-virtual-domains.cf
#
# Postfix virtual_mailbox_domains
# Postfix is final destination for the specified list of domains. Mail
# is delivered via the virtual_transport mail delivery transport.
#

user         	= postfix
password     	= $password
dbname       	= postfix
hosts       	= dbServerhostname
table        	= domain
select_field 	= description
where_field  	= domain
additional_conditions = and backupmx = false and active = true

[edit] Configuring main.cf

At this point, we will adjust the postfix main.cf config file so that we can hook these tables up, insert some data and give it a test. None of the parameters that need to be set were included in the version of main.cf that came with the Postfix distribution. That said, you should still do a search on the file before adding them in to avoid duplicates. In my case, since there were not there, I added them to the end of the main.cf file so that I had them all in a single place. Note: If you are not using local delivery, make sure you comment out 'mydestination'. See $mydestination as what will be deliverd to local unix accounts. Since we are doing everything virtually, comment out mydestination in main.cf.

Note: I've found that instead of commenting out mydestination, I had to leave it defined with an empty value. When it was empty it defaulted to my domain name which prevented mail being delivered - Tim Crockford (21/05/06)

File: /etc/postfix/main.cf 
#
# Settings required to support virtual mail delivery using lookups in
# the Postgres database.
#

# Set the base address for all virtual mailboxes
virtual_mailbox_base = /home/vmail

# A list of all virtual domains serviced by this instance of postfix.
virtual_mailbox_domains = pgsql:/etc/postfix/pgsql/pgsql-virtual-domains.cf

# Look up the mailbox location, uid and gid based on the email address
# received.
virtual_mailbox_maps = pgsql:/etc/postfix/pgsql/pgsql-virtual-maps.cf
virtual_uid_maps = pgsql:/etc/postfix/pgsql/pgsql-virtual-uid.cf
virtual_gid_maps = pgsql:/etc/postfix/pgsql/pgsql-virtual-gid.cf


# Any aliases that are supported by this system
virtual_alias_maps = pgsql:/etc/postfix/pgsql/pgsql-virtual.cf

All the connections are now in so we need to go add some test data to the tables. I will be using the webmin postgres interface, which is very straight forward so below I have provided sample data and will leave it to you to load it whatever way you want. Note: webmin will specify every value in the insert SQL statement using NULL for any fields you have not entered. This means you must enter the default values manually for any field that is specified as NOT NULL or the insert will fail.

Note: Notice the trailing slash after the maildir: entry. This tells postfix we are using maildir instead of mailbox for this entry. Any sane person will want maildir's so just make sure you have a trailing slash.

File: Test Data 
Domain Table
   Domain:    	example.com
   Description: Test Domain
   Aliases:   	0
   Mailboxes: 	0
   Maxquota:  	0
   Transport:	
   Backupmx:  	false
   Created:   	now()
   Modified:  	now()
   Active:    	true

Mailbox Table
   username:    username
   email:       user@example.com
   Password:  	$1$.2213700$AOdx3nlEm3dKANLVkAjim0
   Name:	Users Name
   uid:		1000
   gid:		1000
   maildir:   	example.com/user/
   homedir:   	/home/vmail
   Quota:     	0
   Domain:    	example.com
   Created:   	now()
   Modified:  	now()
   Active:    	true


Note: The valued entered in the Password field is the encrypted result for “secret” as
      encrypted with the crypt() function.}}


Alias Table
   Address:           postmaster@example.com
   Goto:              user@example.com
   Domain             example.com
   Created:           now()
   Modified:          now()
   Active:            true

If you don't want to enter this by hand, here's some straight forward sql or use the gentoo-wiki-virtmail-pgsql.sql script below:

File: Test SQL 
insert into domain (domain, description, aliases, mailboxes, maxquota, backupmx, created, modified, active) values ('example.com', 'Test domain', 0, 0, 0, false, now(), now(), true);

insert into mailbox (username, email, password, name, uid, gid, maildir, homedir, quota, domain, created, modified, active) values ('username', 'user@example.com', '$1$.2213700$AOdx3nlEm3dKANLVkAjim0', 'Users Name', 1000, 1000, 'example.com/user/', '/home/vmail', 0, 'example.com', now(), now(), true);

insert into alias(address, goto, domain, created, modified, active) values ('postmaster@example.com', 'user@example.com', 'example.com', now(), now(), true);

If you would prefer a complete complete sql script use the example below

File: Postfix and Postfixadmin gentoo-wiki-virtmail-pgsql.sql 
-- Postfix Admin Release 2.x --
----------------------------------------------------------
--
-- Copyright (c) 2002 - 2005 High5!
-- Created by: Mischa Peters <mischa at high5 dot net>
--
-- Modified for use with the gentoo-wiki vmail howto
-- by Michael Crawford <m.crawford at eliteitminds dot com>
--
-- This is the complete database structure for Postfix Admin.
-- If you are installing from scratch you can use this file otherwise you
-- need to use the TABLE_CHANGES.TXT or TABLE_BACKUP_MX.TXT that comes with Postfix Admin.
--
-- There are 2 entries for a database user in the file.
-- One you can use for Postfix and one for Postfix Admin.
--
-- If you run this file twice (2x) you will get an error on the user creation in MySQL.
-- To go around this you can either comment the lines below "USE MySQL" until "USE postfix".
-- Or you can remove the users from the database and run it again.
--
-- You can create the database from the shell with:
--      creatuser -P postfix
--      creatuser -P postfixadmin
--      createdb postfix
--      psql postfix
--      postfix=# \i postfix.sql
--      postfix=# \q
--      exit
--
-- Postfix / PgSQL

--
-- DROP TABLE admin,alias,domain,domain_admins,log,mailbox,vacation;
--

--
-- Table structure for table admin
--
CREATE TABLE "admin" (
  "username" character varying(255) NOT NULL default '',
  "password" character varying(255) NOT NULL default '',
  "created" timestamp with time zone default now(),
  "modified" timestamp with time zone default now(),
  "active" boolean NOT NULL default true,
  Constraint "admin_key" Primary Key ("username")
);
COMMENT ON TABLE admin IS 'Postfix Admin - Virtual Admins';
--
-- Table structure for table alias
--
CREATE TABLE alias (
 address character varying(255) NOT NULL default 'postmaster@example.com',
 goto text NOT NULL,
 domain character varying(255) NOT NULL default 'example.com',
 destination character varying(255) NOT NULL default 'user@example.com',
 created timestamp with time zone default now(),
 modified timestamp with time zone default now(),
 active boolean NOT NULL default true,
-- PRIMARY KEY ("address"),
-- KEY address ("address"),
 Constraint "alias_key" Primary Key ("address")
);
COMMENT ON TABLE alias IS 'Postfix Admin - Virtual Aliases';

--
-- Table structure for table domain
--
CREATE TABLE domain (
 domain character varying(255) NOT NULL default 'example.com',
 description character varying(255) NOT NULL default 'Test Domain',
 aliases integer NOT NULL default 0,
 mailboxes integer NOT NULL default 0,
 maxquota integer NOT NULL default 0,
 transport character varying(255) default NULL,
 backupmx boolean NOT NULL default false,
 created timestamp with time zone default now(),
 modified timestamp with time zone default now(),
 active boolean NOT NULL default true,
-- PRIMARY KEY ("domain"),
-- KEY domain ("domain"),
 Constraint "domain_key" Primary Key ("domain")
);
COMMENT ON TABLE domain IS 'Postfix Admin - Virtual Domains';

--
-- Table structure for table domain
--
CREATE TABLE relocated (
 email character varying(255) NOT NULL default 'user2@example.com',
 desination character varying(255) NOT NULL default 'user2@newdomain.com',
 created timestamp with time zone default now(),
 modified timestamp with time zone default now(),
 active boolean NOT NULL default true,
-- PRIMARY KEY ("email"),
-- KEY email ("email"),
 Constraint "relocated_key" Primary Key ("email")
);
COMMENT ON TABLE relocated IS 'Postfix Admin - Virtual Relocated Domains';

--
-- Table structure for table domain_admins
--
CREATE TABLE domain_admins (
 username character varying(255) NOT NULL default '',
 domain character varying(255) NOT NULL default '',
 created timestamp with time zone default now(),
 active boolean NOT NULL default true
-- KEY username ("username")
);
COMMENT ON TABLE domain_admins IS 'Postfix Admin - Domain Admins';

--
-- Table structure for table log
--
CREATE TABLE log (
 timestamp timestamp with time zone default now(),
 username character varying(255) NOT NULL default '',
 domain character varying(255) NOT NULL default '',
 action character varying(255) NOT NULL default '',
 data character varying(255) NOT NULL default ''
-- KEY timestamp ("timestamp")
);
COMMENT ON TABLE log IS 'Postfix Admin - Log';

--
-- Table structure for table mailbox
--
CREATE TABLE mailbox (
 username character varying(255) NOT NULL default 'username',
 email character varying(255) NOT NULL default 'user@example.com',
 password character varying(255) NOT NULL default '$1$.2213700$AOdx3nlEm3dKANLVkAjim0',
 name character varying(255) NOT NULL default 'John Doe',
 uid integer NOT NULL default 1000,
 gid integer NOT NULL default 1000,
 maildir character varying(255) NOT NULL default 'example.com/user/',
 homedir character varying(255) NOT NULL default '/home/vmail',
 quota integer NOT NULL default 0,
 domain character varying(255) NOT NULL default 'example.com',
 created timestamp with time zone default now(),
 modified timestamp with time zone default now(),
 active boolean NOT NULL default true,
-- PRIMARY KEY ("username"),
-- KEY username ("username"),
 Constraint "mailbox_key" Primary Key ("username")
);
COMMENT ON TABLE mailbox IS 'Postfix Admin - Virtual Mailboxes';

--
-- Table structure for table vacation
--
CREATE TABLE vacation (
 email character varying(255) NOT NULL default '',
 subject character varying(255) NOT NULL default '',
 body text NOT NULL,
 cache text NOT NULL,
 domain character varying(255) NOT NULL default '',
 created timestamp with time zone default now(),
 active boolean NOT NULL default true,
-- PRIMARY KEY ("email"),
-- KEY email ("email")
 Constraint "vacation_key" Primary Key ("email")
);
COMMENT ON TABLE vacation IS 'Postfix Admin - Virtual Vacation';

Here's the same script for mysql if anyone should wish to port this howto for their own use to the widely used mysql database:

File: Postfix and Postfixadmin gentoo-wiki-virtmail-mysql.sql 
#
# Postfix Admin
# by Mischa Peters <mischa at high5 dot net>
# Copyright (c) 2002 - 2005 High5!
# License Info: http://www.postfixadmin.com/?file=LICENSE.TXT

# Modified for use with the gentoo-wiki vmail howto
# by Michael Crawford <ali3nx at eliteitminds dot com>

# This is the complete MySQL database structure for Postfix Admin.
# If you are installing from scratch you can use this file otherwise you
# need to use the TABLE_CHANGES.TXT or TABLE_BACKUP_MX.TXT that comes with Postfix Admin.
#
# There are 2 entries for a database user in the file.
# One you can use for Postfix and one for Postfix Admin.
#
# If you run this file twice (2x) you will get an error on the user creation in MySQL.
# To go around this you can either comment the lines below "USE MySQL" until "USE postfix".
# Or you can remove the users from the database and run it again.
#
# You can create the database from the shell with:
#
# mysql -u root [-p] < DATABASE_MYSQL.TXT

#
# Postfix / MySQL
#
USE mysql;
# Postfix user & password
INSERT INTO user (Host, User, Password) VALUES ('localhost','postfix',password('postfix'));
INSERT INTO db (Host, Db, User, Select_priv) VALUES ('localhost','postfix','postfix','Y');
# Postfix Admin user & 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');
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;
CREATE DATABASE postfix;
USE postfix;

#
# Table structure for table admin
#
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';

#
# Table structure for table alias
#
CREATE TABLE alias (
  address varchar(255) NOT NULL default 'postmaster@example.com',
  goto text NOT NULL,
  domain varchar(255) NOT NULL default 'example.com',
  destination varchar(255) NOT NULL default 'user@example.com',
  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';

#
# Table structure for table domain
#
CREATE TABLE domain (
  domain varchar(255) NOT NULL default 'example.com',
  description varchar(255) NOT NULL default 'Test Domain',
  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';

#
# Table structure for table domain_admins
#
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';

#
# Table structure for table log
#
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';

#
# Table structure for table mailbox
#
CREATE TABLE mailbox (
  username varchar(255) NOT NULL default '',
  email varchar(255) NOT NULL default 'user@example.com',
  password varchar(255) NOT NULL default '$1$.2213700$AOdx3nlEm3dKANLVkAjim0',
  name varchar(255) NOT NULL default 'John Doe',
  uid int(10) NOT NULL default '1000',
  gid int(10) NOT NULL default '1000',
  maildir varchar(255) NOT NULL default 'example.com/user/',
  homedir varchar(255) NOT NULL default '/home/vmail',
  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';

#
# Table structure for table relocated
#
CREATE TABLE relocated (
 email varchar(255) NOT NULL default 'user2@example.com',
 destination varchar(255) NOT NULL default 'user2@newdomain.com',
 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 (email),
 KEY email (email)
) TYPE=MyISAM COMMENT='Postfix Admin - Virtual Vacation';

 !!! THIS MYSQL STATEMENT WON'T WORK !!! Can anybody post a working and complete statement? the last line seems to be cutoff...

added 2007/04/15 by b2c:

This is what I used on my mysql5-db and it applies and works fine. However, if you use postfixadmin, be warned, it fills some tables not in the way you'd expect it to, and you'll have troubles to login to your mailserver later on. So double-check your settings AND the postfixadmin-settings!

For convenience, i granted basic rights to postfix AND postfixadmin to all the tables and also create the database 'postfix'.

Please check this config before you apply it! It work's for me, but no guarantees.

Don't forget to adjust username/password/hostname.

OK, here you go:

File: Postfix and Postfixadmin gentoo-wiki-virtmail-mysql5.sql 
#
# Postfix / MySQL
#
USE mysql;
# Postfix user & password
INSERT INTO user (Host, User, Password) VALUES ('your_hostname_here','postfix',password('yourpasswordhere'));
INSERT INTO db (Host, Db, User, Select_priv) VALUES ('your_hostname_here','postfix','postfix','Y');

# Postfix Admin user & password
INSERT INTO user (Host, User, Password) VALUES ('your_hostname_here','postfixadmin',password('yourpasswordhere'));
INSERT INTO db (Host, Db, User, Select_priv, Insert_priv, Update_priv, Delete_priv) VALUES ('your_hostname_here', 'postfix', 'postfixadmin', 'Y', 'Y', 'Y', 'Y');

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;

# ONLY if your database does NOT LISTEN TO localhost
#GRANT USAGE ON postfix.* TO postfix@your_hostname/ip_here;
#GRANT SELECT, INSERT, DELETE, UPDATE ON postfix.* TO postfix@your_hostname/ip_here;
#GRANT USAGE ON postfix.* TO postfixadmin@your_hostname/ip_here;
#GRANT SELECT, INSERT, DELETE, UPDATE ON postfix.* TO postfixadmin@your_hostname/ip_here;

CREATE DATABASE postfix;
USE postfix;

#
# Table structure for table admin
#
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';
GRANT SELECT, INSERT, UPDATE, DELETE ON admin to postfixadmin, postfix;

#
# Table structure for table alias
#
CREATE TABLE alias (
  address varchar(255) NOT NULL default 'postmaster@your_hostname_here',
  goto text NOT NULL,
  domain varchar(255) NOT NULL default 'your_hostname_here',  # eg.:mail.yourhost.com
  destination varchar(255) NOT NULL default 'user@example.com',
  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';
GRANT SELECT, INSERT, UPDATE, DELETE ON alias to postfixadmin, postfix;

#
# Table structure for table domain
#
CREATE TABLE domain (
  domain varchar(255) NOT NULL default 'your_hostname_here', # eg.:mail.yourhost.com
  description varchar(255) NOT NULL default 'Mailserver2',
  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';
GRANT SELECT, INSERT, UPDATE, DELETE ON domain to postfixadmin, postfix;

#
# Table structure for table domain_admins
#
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';
GRANT SELECT, INSERT, UPDATE, DELETE ON domain_admins to postfixadmin, postfix;

#
# Table structure for table log
#
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';
GRANT SELECT, INSERT, UPDATE, DELETE ON log to postfixadmin, postfix;

#
# Table structure for table mailbox
#
CREATE TABLE mailbox (
  username varchar(255) NOT NULL default '',
  email varchar(255) NOT NULL default 'user@example.com',
  password varchar(255) NOT NULL default '$1$.2213700$AOdx3nlEm3dKANLVkAjim0',
  name varchar(255) NOT NULL default 'John Doe',
  uid int(10) NOT NULL default '1000',
  gid int(10) NOT NULL default '1000',
  maildir varchar(255) NOT NULL default 'example.com/user/',
  homedir varchar(255) NOT NULL default '/home/vmail',
  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';
GRANT SELECT, INSERT, UPDATE, DELETE ON mailbox to postfixadmin, postfix;


#
# Table structure for table relocated, reloaded!
#
CREATE TABLE relocated (
 email varchar(255) NOT NULL default '',
 destination varchar(255) NOT NULL default '',
 created timestamp NOT NULL default '0000-00-00 00:00:00',
 modified timestamp NOT NULL default '0000-00-00 00:00:00',
 active boolean NOT NULL default true,
 PRIMARY KEY (email)
)
TYPE=MyISAM COMMENT='Postfix Admin - Relocated Mail Table';
GRANT SELECT, INSERT, UPDATE, DELETE ON relocated to postfixadmin, postfix;

#
# Table Structures for table transport
#
CREATE TABLE transport (
 domain varchar(255) NOT NULL default '',
 destination varchar(255) NOT NULL default '',
 created timestamp NOT NULL default '0000-00-00 00:00:00',
 modified timestamp NOT NULL default '0000-00-00 00:00:00',
 active boolean NOT NULL default true,
 PRIMARY KEY (domain)
)
TYPE=MyISAM COMMENT='Postfix Admin - Transport Table';
GRANT SELECT, INSERT, UPDATE, DELETE ON transport to postfixadmin, postfix;
Retrieved from "http://www.gentoo-wiki.com/Complete_Virtual_Mail_Server/Postfix_to_Postgres"
Personal tools