GUI Admin for Postfix, MySQL

[OpenBSD 4.0, postfixadmin 2.1.0]

Postfixadmin is a web front end for managing a MySQL database, that is normally configured for use by your postfix installation.

This guide was tested on OpenBSD 4.0 release with postfixadmin 2.1.0 postfix 2.3.2 and dovecot 1.0rc15 and mysql 5.024a. Check with the release you are using whether you have the minimal software requirements before continuing.

If you have not already done so, please install and verify your mysql configuration with php support.

You can download postfixadmin from Sourceforge or grab the current source using svn, instructions at http://postfixadmin.sourceforge.net. If you can follow through the INSTALL.TXT file, go ahead and install from there. Otherwise, read that file first before continuing further.

For our purposes, we will assume that you have already unarchived the postfixadmin files available in the /var/www/htdocs/postfixadmin directory.

Configuring MySQL

Postfixadmin's installation instruction are in the INSTALL file.

The simplest way to configure your mysql server is to use the following DB configuration file.

The DB configuration is based on SVN release 2010.08.25 and may differ from your version of Postfixadmin.

Creating the Database

Obviously, the current release of postfixadmin is most likely to have the latest configuration of the database. For MySQL, the current schema is in the file DATABASE_MYSQL.TXT.

There are two basic changes I want to make for how I have installed postfixadmin:

  • Modify the database and mysql user names
  • Modify the superadmin user

You may not want to make the changes as I have made them, but may use this as the foundation for ensuring your set up is customised for your needs.

In our installation, I have decided to use different user account name for the postfix server, and a different database name. The standard release uses the database 'postfix' and a user account 'postfix' which I found confusing, so I've renamed the postfix user account to be 'postfixserver' and the database name will be 'mail.'

What we want to do is use the same installation files as postfixadmin with the following modifications.

  • Replace 'postfix' user to be 'postfixserver'
  • Replace 'postfix' database to be 'mail'

The major changes we will want to make is shown in the following file fragment that begins at around line 25.

File Fragment: ./DATABASE_MYSQL.TXT

25 #
26 # Postfix / MySQL
27 #
28 USE mysql;
29 # Postfix user & password
30 INSERT INTO user (Host, User, Password) VALUES ('localhost','postfix',password('postfix'));
31 INSERT INTO db (Host, Db, User, Select_priv) VALUES ('localhost','postfix','postfix','Y');
32 # Postfix Admin user & password
33 INSERT INTO user (Host, User, Password) VALUES ('localhost','postfixadmin',password('postfixadmin'));
34 INSERT INTO db (Host, Db, User, Select_priv, Insert_priv, Update_priv, Delete_priv) VALUES ('localhost','postfix', 'postfixadmin', 'Y', 'Y', 'Y', 'Y');
35 FLUSH PRIVILEGES;
36 GRANT USAGE ON postfix.* TO postfix@localhost;
37 GRANT SELECT, INSERT, DELETE, UPDATE ON postfix.* TO postfix@localhost;
38 GRANT USAGE ON postfix.* TO postfixadmin@localhost;
39 GRANT SELECT, INSERT, DELETE, UPDATE ON postfix.* TO postfixadmin@localhost;
40 CREATE DATABASE postfix;
41 USE postfix;

After we make changes to the above file, we should have something like the below, with the changes highlighted in blue

Updated File Fragment: ./DATABASE_MYSQL.TXT

25 #
26 # Postfix / MySQL
27 #
28 USE mysql;
29 # Postfix user & password
30 INSERT INTO user (Host, User, Password) VALUES ('localhost','postfixserver',password('postfixserver'));
31 INSERT INTO db (Host, Db, User, Select_priv) VALUES ('localhost','mail','postfixserver','Y');
32 # Postfix Admin user & password
33 INSERT INTO user (Host, User, Password) VALUES ('localhost','postfixadmin',password('postfixadmin'));
34 INSERT INTO db (Host, Db, User, Select_priv, Insert_priv, Update_priv, Delete_priv) VALUES ('localhost','mail', 'postfixadmin', 'Y', 'Y', 'Y', 'Y');
35 FLUSH PRIVILEGES;
36 GRANT USAGE ON mail.* TO postfixserver@localhost;
37 GRANT SELECT, INSERT, DELETE, UPDATE ON mail.* TO postfixserver@localhost;
38 GRANT USAGE ON mail.* TO postfixadmin@localhost;
39 GRANT SELECT, INSERT, DELETE, UPDATE ON mail.* TO postfixadmin@localhost;
40 CREATE DATABASE mail;
41 USE mail;

Details of the changes we want to make are as follows:

  • Line 30: Replace the user 'postfix' with 'postfixserver', and remember that the password 'postfixserver' should be something unique to your installation.
  • Line 31: Replace the Db 'postfix' with 'mail' and the User 'postfix' to be 'postfixserver'
  • Line 33: Replace the password for 'postfixadmin' with something unique for your installation.
  • Line 34: Replace the Db 'postfix' with 'mail'
  • Line 36: Replace  postfix.* with mail.* and replace postfix@localhost with postfixserver@localhost
  • Line 37: Replace postfix.* with mail.*
  • Line 38: Replace postfix.* with mail.*
  • Line 39: Replace postfix.* with mail.*
  • Line 40: Replace postfix; with mail;
  • Line 41: Replace postfix; with mail;
Modifying superadmin account

The default superadmin user account can be somewhat confusing as you are literally requested to enter 'admin@domain.tld' as the user account. This aesthetic change is to use 'admin' and allow setting the default password.

File Fragment: ./DATABASE_MYSQL.TXT

145 # superadmin user & password (login: admin@domain.tld, password: admin)
146 INSERT INTO domain_admins (username, domain, active) VALUES ('admin@domain.tld','ALL','1');
147 INSERT INTO admin (username, password, active) VALUES ('admin@domain.tld','$1$0fec9189$bgI6ncWrldPOsXnkUBIjl1','1');

We will change the default to something like the below

Updated File Fragment: ./DATABASE_MYSQL.TXT

145 # superadmin user & password (login: admin@domain.tld, password: admin)
146 INSERT INTO domain_admins (username, domain, active) VALUES ('admin','ALL','1');
147 INSERT INTO admin (username, password, active) VALUES ('admin','6dwLx9NTxhTjU','1');

Details of the changes we want to make are as follows:

  • Line 146: Replace 'admin@domain.tld' with 'admin'
  • Line 147: Replace 'admin@domain.tld' with 'admin'. Replace password with new password.

Create the Database

Now that we've completed the modifications to our database instructions, we can create our database with the following instructions.

# mysql -u root -p < ./DATABASE_MYSQL.TXT

Database Errors

You should not get any error messages, and if you do you may need to check the file changes above for any syntax errors.

Otherwise, copy the DATABASE_MYSQL.TXT shown later in this documentation, and recreate the database after executing the below mysql commands to clear out your previous installation.

Note: You may inadvertently destroy something on your server, so only perform these on a test server unless you know what you are doing.

To remove the previous, or any part of the previous database instructions that may have been added to the database server:

  • drop/delete the database mail and or postfix
  • delete user postfix
  • delete user postfixadmin
  • delete user postfixserver
  • delete database privileges (db) for postfix
  • delete database privileges (db) for postfixadmin
  • delete database privileges (db) for postfixserver
  • flush the privileges

Screen Session

$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 25 to server version: 5.0.24a-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> drop database mail;
Query OK, 7 rows affected (0.05 sec)
mysql> drop database postfix;
ERROR 1008 (HY000): Can't drop database 'postfix'; 
database doesn't exist
mysql> use mysql;
Database changed
mysql> delete from user where user='postfix';
Query OK, 0 rows affected (0.00 sec)
mysql> delete from user where user='postfixadmin';
Query OK, 1 row affected (0.00 sec)
mysql> delete from user where user='postfixserver';
Query OK, 1 row affected (0.00 sec)
mysql> delete from db where user='postfix';
Query OK, 0 rows affected (0.01 sec)
mysql> delete from db where user='postfixserver';
Query OK, 1 row affected (0.00 sec)
mysql> delete from db where user='postfixadmin';
Query OK, 1 row affected (0.00 sec)
mysql> flush privileges;

Remember that if you've changed the user names, then you need to remember to use those changes in the above instructions.

Summary

Your database should now be appropriately created for use with postfix and postfixadmin.

Configuring Files

There are two main changes you need to make with the files that are distributed with postfixadmin.

  • Set the correct permissions
  • Change config.inc.php

Changing File permissions

For security reasons, we will set the permissions for files in the postfixadmin directory. We can use a combination of the 'find' and 'chmod' programs to quickly achieve this

# cd /var/www/htdocs/postfixadmin
# find . -type f -exec chmod 640 "{}" ";"

If you observe some file permission problems after the above settings, then you may need to take a look at whether you also need to change the ownership of the files, as in the example below.

# find . -exec chown www:www "{}" ";"

Changing config.inc.php

postfixadmin's runtime behaviour is effected by initialisation settings in the config.inc.php file. Mostly, the changes are relevant to the users and passwords you've set in the above Database creation exercise, as well as issues relevant to your installation. Things that I changed include:

File Fragment: /var/www/htdocs/postfixadmin/config.inc.php

$CONF['postfix_admin_url'] = 'http://PRIVATE_IP/postfixadmin/';
$CONF['postfix_admin_path'] = '/admin/';
$CONF['database_host'] = '127.0.0.1';
$CONF['database_name'] = 'mail';
$CONF['admin_email'] = 'postmaster@example.org';
$CONF['smtp_server'] = '127.0.0.1';
$CONF['encrypt'] = 'system';
 $CONF['default_aliases'] = array (
 'abuse' => 'abuse@example.org',
 'hostmaster' => 'hostmaster@example.org',
 'postmaster' => 'postmaster@example.org',
 'webmaster' => 'webmaster@example.org'
);
$CONF['domain_path'] = 'YES';
$CONF['vacation_domain'] = 'autoreply.example.org';
$CONF['footer_text'] = 'Return to MYHOST';
$CONF['footer_link'] = 'http://PRIVATE_IP';

Configuring Postfix

Documentation for configuring Postfix for use with this MySQL database, is described at this link.

Start our Engines

Your postfixadmin installation should be installed and ready.

To start, open your browser to the url you specified in your configuration file, like:

$ lynx http://PRIVATE_IP/postfixadmin/

You can now use user 'admin' with password 'admin' for managing your virtual domains, or if you have changed the password in the above instructions, use that password.

DATABASE_MYSQL.TXT

Following is the modified DATABASE_MYSQL.TXT discussed above.

File: ./DATABASE_MYSQL.TXT

#
# Postfix Admin
# by Mischa Peters <mischa at high5 dot net>
# Copyright (c) 2002 - 2005 High5!
# Licensed under GPL for more info check GPL-LICENSE.TXT
# 
# 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.
# You can find these in DOCUMENTS/ 
#
# 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','postfixserver',password('postfixserver'));
INSERT INTO db (Host, Db, User, Select_priv) VALUES ('localhost','mail','postfixserver','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', 'mail', 'postfixadmin', 'Y', 'Y', 'Y', 'Y');
FLUSH PRIVILEGES;
GRANT USAGE ON mail.* TO postfixserver@localhost;
GRANT SELECT, INSERT, DELETE, UPDATE ON mail.* TO postfixserver@localhost;
GRANT USAGE ON mail.* TO postfixadmin@localhost;
GRANT SELECT, INSERT, DELETE, UPDATE ON mail.* TO postfixadmin@localhost;
CREATE DATABASE mail;
USE mail;
#
# 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 '',
`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'; 
#
# Table structure for table domain
#
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',
`quota` 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 '',
`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'; 
#
# Table structure for table vacation
#
CREATE TABLE `vacation` (
`email` varchar(255) NOT NULL default '',
`subject` varchar(255) NOT NULL default '',
`body` text NOT NULL default '',
`cache` text 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',
PRIMARY KEY (`email`),
KEY email (`email`)
) TYPE=MyISAM COMMENT='Postfix Admin - Virtual Vacation'; 
# superadmin user & password (login: admin@domain.tld, password: admin)
INSERT INTO domain_admins (username, domain, active) VALUES ('admin','ALL','1');
INSERT INTO admin (username, password, active) VALUES 
('admin','6dwLx9NTxhTjU','1');