RSS feedTo prepare the mailserver database you need to have access to the MySQL database, start a Terminal session and login to MySQL using the mysql command (with the admin user and password you configured earlier). You are now connected to the database. Issue the following commands to add some users and create a new database for the mailserver administration.
Change to the mysql database and add the postfix user and password:
Note: Please use a different password, or even a different username, than indicated. If you don’t other people know which password to use to enter your system. I will (for consistency) keep using this user and password throughout the documentation
INSERT INTO user (Host, User, Password) VALUES
('localhost','postfix',password('postfixpassword'));
INSERT INTO db (Host, Db, User, Select_priv) VALUES
('localhost','postfix','postfix','Y');
Add the Postfix admin user and password:
Setup the rights for the just created users: Next, create the database that we will use for the user administration: Now we can create the tables, first the table for the administrators: Then the table for the ‘alias’ administration, or which external e-mail address is routed to which mailbox. The table for the domains we are going to administer is next: The table for the domain administrators if you want to use them. A table where the changes will be logged is very useful if some users report problems and you want to find out what has been done. The table structure for the actual mailboxes. When someone wants to send a reply when he is temporary unable to get to his mail. Next step: Create the mailbox directories
('localhost','postfixadmin',password('postfixadminpassword'));
INSERT INTO db (Host, Db, User, Select_priv, Insert_priv,
Update_priv, Delete_priv) VALUES ('localhost', 'postfix',
'postfixadmin', 'Y', 'Y', 'Y', 'Y');
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;
USE postfix;
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)
) ENGINE=MyISAM COMMENT='Postfix Admin - Virtual Admins';
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)
) ENGINE=MyISAM COMMENT='Postfix Admin - Virtual Aliases';
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)
) ENGINE=MyISAM COMMENT='Postfix Admin - Virtual Domains';
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)
) ENGINE =MyISAM COMMENT='Postfix Admin - Domain Admins';
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)
) ENGINE =MyISAM COMMENT='Postfix Admin - Log';
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)
) ENGINE =MyISAM COMMENT='Postfix Admin - Virtual Mailboxes';
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)
) ENGINE =MyISAM COMMENT='Postfix Admin - Virtual Vacation';
8:49 am
first and foremost: Thank you so much for a great website! Very informative…
now a small prob I ran into: when creating the tables, I get this warning:
Warning (Code 1287): ‘TYPE=storage_engine’ is deprecated; use ‘ENGINE=storage_engine’ instead
should all occurences of TYPE= in your code be replaced by ENGINE= ? Or does that have some unwanted side-effects? I have created the tables like you indicated, with TYPE=. Can that be changed later, is that desirable, and if yes: how?
thanks for you help,
Marc
8:50 am
sorry, I omitted the version of MySQL I am using. It is 5.0.45
cheers,
Marc
8:23 pm
Marc, the create table scripts have been updated. Thanks for mentioning this.
9:46 pm
I’m stuck…
I can’t seem to add postfix user and password and postfix admin user and password without ERROR 1062 or 1064. I’ve tried a number of variations on your syntax above without success. (The only part of your documentation I’ve tried to change is the password). I have not “Setup the rights for the just created users” for any users I might have indirectly added to the mysql database. What follows are my first couple of attempts? How do I know if I’ve added any users? Can I clear these missteps and try again? Thanks in advance…
mysql> USE mysql;
INSERT INTO user (Host, User, Password) VALUES
 (’localhost’,'postfix’,password(’myinsertedpassword’));
INSERT INTO db (Host, Db, User, Select_priv) VALUES
 (’localhost’,'postfix’,'postfix’,'Y’);
Database changed
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘
INSERT INTO user (Host, User, Password) VALUES
 (’localhost’,'postfix’,pa’ at line 1
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘
INSERT INTO db (Host, Db, User, Select_priv) VALUES
 (’localhost’,'postfi’ at line 1
mysql> USE mysql;
Database changed
mysql> INSERT INTO user (Host, User, Password) VALUES
-> (’localhost’,'postfix’,password(’postfixpassword’));
ERROR 1062 (23000): Duplicate entry ‘localhost-postfix’ for key 1
mysql> INSERT INTO db (Host, Db, User, Select_priv) VALUES
-> (’localhost’,'postfix’,'postfix’,'Y’);
Query OK, 1 row affected (0.00 sec)
8:16 am
It looks like a copy paste error. Please try each line separate or type them over manually. The last line worked so you don’t need to do that one again.