Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 6856
  • Last Modified:

mysql postfix and postfixadmin

Dear Sir/Madam:

I am having rhel5 server working as a mail server now the requirement is to configure the mail server for the virtual users and domains hence postfix is compiled for the mysql  feature and downloaded the postfixadmin and run the DATABASE_MYSQL script ( attached the same for your reference) after this edited the /var/www/html/postfixadmin/config.inc.php  like the following:
--------------/var/www/html/postfixadmin/config.inc.php----------------
$CONF['database_type'] = 'mysqli';
$CONF['database_host'] = '127.0.0.1';
$CONF['database_user'] = 'postfixadmin';
$CONF['database_password'] = 'ind080kar';
$CONF['database_name'] = 'postfix';
$CONF['database_prefix'] = '';
after this when tested by browsing http://localhost/postfixadmin/setup.php
it gives error , posted below for the reference :
Postfix Admin Setup Checker
Running software:
    * PHP version 5.1.6
    * Apache/2.2.3 (Red Hat)
Checking for dependencies:
    * Magic Quotes: Disabled - OK
    * Depends on: presence config.inc.php - OK
    * Checking $CONF['configured'] - OK
    * Depends on: MySQL 3.23, 4.0 - OK
    * Depends on: MySQL 4.1 - OK
    * Depends on: PostgreSQL - OK (change the database_type to 'pgsql' in config.inc.php!!)
    * Testing database connection - OK - mysqli://postfixadmin:xxxxx@127.0.0.1/postfix
    * Depends on: session - OK
    * Depends on: pcre - OK
    * Depends on: multibyte string - OK
    * Depends on: IMAP functions - OK
Everything seems fine... attempting to create/update database structure
DEBUG INFORMATION:
Invalid query: CREATE command denied to user 'postfixadmin'@'localhost' for table 'config'

Please help me to fix this above debug says invalid query , please help, thanks in advance.



DATABASE-MYSQL.TXT
0
D_wathi
Asked:
D_wathi
  • 10
  • 7
1 Solution
 
UmeshMySQL Principle Technical Support EngineerCommented:
Try this script..
DROP USER postfix@localhost;
DROP USER postfixadmin@localhost;
 
# Postfix Admin
# by Mischa Peters <mischa at high5 dot net>
# Copyright (c) 2002 - 2005 High5!
# License Info: http://www.postfixadmin.com/?file=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.
#
# 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('indkar@blr'));
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('indkar@blr'));
INSERT INTO db (Host, Db, User, Create_priv, Select_priv, Insert_priv, Update_priv, Delete_priv) VALUES ('localhost', 'postfix', 'postfixadmin','Y', '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, CREATE, 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 '',
  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',
  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,
  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)
) TYPE=MyISAM COMMENT='Postfix Admin - Virtual Vacation';

Open in new window

0
 
UmeshMySQL Principle Technical Support EngineerCommented:
You need one more flush privileges.. updated below script.. pls try thsi one
DROP USER postfix@localhost;
DROP USER postfixadmin@localhost;
 
# Postfix Admin
# by Mischa Peters <mischa at high5 dot net>
# Copyright (c) 2002 - 2005 High5!
# License Info: http://www.postfixadmin.com/?file=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.
#
# 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('indkar@blr'));
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('indkar@blr'));
INSERT INTO db (Host, Db, User, Create_priv, Select_priv, Insert_priv, Update_priv, Delete_priv) VALUES ('localhost', 'postfix', 'postfixadmin','Y', '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, CREATE, INSERT, DELETE, UPDATE ON postfix.* TO postfixadmin@localhost;
 
FLUSH PRIVILEGES;
 
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 '',
  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',
  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,
  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)
) TYPE=MyISAM COMMENT='Postfix Admin - Virtual Vacation';

Open in new window

0
 
D_wathiAuthor Commented:
Sir, Thank you very much for the reply , I will delete the previous database and execute the following script:
DROP USER postfix@localhost;
DROP USER postfixadmin@localhost;
 
# Postfix Admin
# by Mischa Peters <mischa at high5 dot net>
# Copyright (c) 2002 - 2005 High5!
# License Info: http://www.postfixadmin.com/?file=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.
#
# 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('indkar@blr'));
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('indkar@blr'));
INSERT INTO db (Host, Db, User, Create_priv, Select_priv, Insert_priv, Update_priv, Delete_priv) VALUES ('localhost', 'postfix', 'postfixadmin','Y', '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, CREATE, INSERT, DELETE, UPDATE ON postfix.* TO postfixadmin@localhost;
 
FLUSH PRIVILEGES;
 
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 '',
  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',
  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,
  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)
) TYPE=MyISAM COMMENT='Postfix Admin - Virtual Vacation';

after the execution of the above script then will edit  var/www/html/postfixadmin/config.inc.php  like the following:
--------------/var/www/html/postfixadmin/config.inc.php----------------
$CONF['database_type'] = 'mysqli';
$CONF['database_host'] = '127.0.0.1';
$CONF['database_user'] = 'postfixadmin';
$CONF['database_password'] = 'ind080kar';
$CONF['database_name'] = 'postfix';
$CONF['database_prefix'] = '';

please suggest me is this correct , thanks in advance.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
UmeshMySQL Principle Technical Support EngineerCommented:
Yes.. go ahead... only one doubt I have.. why database_type=mysqli shouldn't it be mysql... if that is come with the setup then leave it as is...
0
 
D_wathiAuthor Commented:
Thank you very much for the reply, i tried with database_type=mysql but this gives error and by setting to mysqli this works .
Sir , one doubt by setting it to the following in the /var/www/html/postfixadmin/config.inc.php
$CONF['database_user'] = 'postfixadmin';
$CONF['database_password'] = 'ind080kar'
script creates database postfix and the user postfixadmin , in the /var/www/html/postfixadmin/config.inc.php
 $CONF['database_user'] =postfixadmin
can you please tell me how this works , postfixadmin is logged into the database postfix through the postfixadmin application with this postfixadmin is able to do that database manupulations , please give an small explaination how this works ,this is a request please.
0
 
UmeshMySQL Principle Technical Support EngineerCommented:
>>can you please tell me how this works , postfixadmin is logged into the database postfix through the postfixadmin application with this postfixadmin is able to do that database manupulations , please give an small explaination how this works ,this is a request please.

I think this admin privileges are one-time say while creating DB and table details...once that is over may be normal/usage user would use these..

To be honest I have no idea about this application... :-)
0
 
D_wathiAuthor Commented:
Sir  i did run the script which you had given after that when i did the postfixadmin setup check i am getting the following error same is posted for your reference , please help to fix this.
Postfix Admin Setup Checker
Running software:
    * PHP version 5.1.6
    * Apache/2.2.3 (Red Hat)

Checking for dependencies:

    * Magic Quotes: Disabled - OK
    * Depends on: presence config.inc.php - OK
    * Checking $CONF['configured'] - OK
    * Depends on: MySQL 3.23, 4.0 - OK
    * Depends on: MySQL 4.1 - OK
    * Depends on: PostgreSQL - OK (change the database_type to 'pgsql' in config.inc.php!!)
    * Testing database connection - OK - mysqli://postfixadmin:xxxxx@127.0.0.1/postfix
    * Depends on: session - OK
    * Depends on: pcre - OK
    * Depends on: multibyte string - OK
    * Depends on: IMAP functions - OK
Everything seems fine... attempting to create/update database structure
Updating database:
old version: 317; target version: 397
updating to version 318 (MySQL)...
DEBUG INFORMATION:
Invalid query: Can't create table './postfix/vacation_notification.frm' (errno: 150)
0
 
UmeshMySQL Principle Technical Support EngineerCommented:
I appreciate if you can try it asap and let me know how it goes...
DROP USER postfix@localhost;
DROP USER postfixadmin@localhost;
DROP DATABASE postfix;
 
# Postfix Admin
# by Mischa Peters <mischa at high5 dot net>
# Copyright (c) 2002 - 2005 High5!
# License Info: http://www.postfixadmin.com/?file=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.
#
# 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
#
# Postfix user & password
 
# Postfix Admin user & password
GRANT SELECT, INSERT, DELETE, UPDATE ON postfix.* TO postfix@localhost IDENTIFIED BY 'indkar@blr';
GRANT ALL ON postfix.* TO postfixadmin@localhost IDENTIFIED BY 'indkar@blr';
FLUSH PRIVILEGES;
 
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 '',
  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',
  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,
  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)
) TYPE=MyISAM COMMENT='Postfix Admin - Virtual Vacation';

Open in new window

0
 
UmeshMySQL Principle Technical Support EngineerCommented:
Did you try?

Thanks,
Umesh
0
 
D_wathiAuthor Commented:
Sir, thanks for the reply , iam getting the following error when i execute the command : mysql -u root -p < DATABASE_MYSQL.TXT
Enter password:
ERROR 1396 (HY000) at line 1: Operation DROP USER failed for 'postfix'@'localhost'
----------------------------
attached the script for your reference , please help
DATABASE-MYSQL.TXT
0
 
UmeshMySQL Principle Technical Support EngineerCommented:
That means user doesn't exists..  did you drop the user?  ok..comment out DROP portion and re-run
DROP DATABASE postfix;
 
# Postfix Admin
# by Mischa Peters <mischa at high5 dot net>
# Copyright (c) 2002 - 2005 High5!
# License Info: http://www.postfixadmin.com/?file=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.
#
# 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
#
# Postfix user & password
 
# Postfix Admin user & password
GRANT SELECT, INSERT, DELETE, UPDATE ON postfix.* TO postfix@localhost IDENTIFIED BY 'indkar@blr';
GRANT ALL ON postfix.* TO postfixadmin@localhost IDENTIFIED BY 'indkar@blr';
FLUSH PRIVILEGES;
 
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 '',
  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',
  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,
  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)
) TYPE=MyISAM COMMENT='Postfix Admin - Virtual Vacation';

Open in new window

0
 
UmeshMySQL Principle Technical Support EngineerCommented:
Any updates on this?
0
 
D_wathiAuthor Commented:
Sorry for the delay , was out for some time, sir as per your latest script , i was able to run the script without any errors mysql -u root [-p] < DATABASE_MYSQL.TXT
also
postfixadmin setup check is successfull the same is posted below:

Postfix Admin Setup Checker

Running software:

    * PHP version 5.1.6
    * Apache/2.2.3 (Red Hat)

Checking for dependencies:

    * Magic Quotes: Disabled - OK
    * Depends on: presence config.inc.php - OK
    * Checking $CONF['configured'] - OK
    * Depends on: MySQL 3.23, 4.0 - OK
    * Depends on: MySQL 4.1 - OK
    * Depends on: PostgreSQL - OK (change the database_type to 'pgsql' in config.inc.php!!)
    * Testing database connection - OK - mysqli://postfixadmin:xxxxx@127.0.0.1/postfix
    * Depends on: session - OK
    * Depends on: pcre - OK
    * Depends on: multibyte string - OK
    * Depends on: IMAP functions - OK

Everything seems fine... attempting to create/update database structure
Database is up to date
-----------------------
can you please tell me what was the changes done other than removing the DROP USER , thanks in advance.







0
 
UmeshMySQL Principle Technical Support EngineerCommented:
Oh that's great so finally its done...

Changes I have done...

Earlier script has GRANT are as
============================
USE mysql;
# Postfix user & password
INSERT INTO user (Host, User, Password) VALUES ('localhost','postfix',password('indkar@blr'));
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('indkar@blr'));
INSERT INTO db (Host, Db, User, Create_priv, Select_priv, Insert_priv, Update_priv, Delete_priv) VALUES ('localhost', 'postfix', 'postfixadmin','Y', '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, CREATE, INSERT, DELETE, UPDATE ON postfix.* TO postfixadmin@localhost;



Changed To this
============================

GRANT SELECT, INSERT, DELETE, UPDATE ON postfix.* TO postfix@localhost IDENTIFIED BY 'indkar@blr';
GRANT ALL ON postfix.* TO postfixadmin@localhost IDENTIFIED BY 'indkar@blr';
FLUSH PRIVILEGES;



I ahve changed manual inserts INTO grant tables(user and db) coz the the following GRANTS were doing the same accept the password assigning stuff.. so I included them directly into GRANT statement....  coming to more privileges to "postfixadmin" ..this was necessary as it was causing lots of issues to due to full privileges so I granted them...  that's all..



I appreciate if you can mark the question as solved...

0
 
D_wathiAuthor Commented:
Sir, Thank you very much.
0
 
D_wathiAuthor Commented:
good solution at the right time.
0
 
UmeshMySQL Principle Technical Support EngineerCommented:
You are most welcome.

Thanks,
Umesh
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

  • 10
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now