1 #
2 # Postfix Admin
3 # by Mischa Peters <mischa at high5 dot net>
4 # Copyright (c) 2002 - 2005 High5!
5 # License Info: http://www.postfixadmin.com/?file=LICENSE.TXT
6 #
7 
8 # This is the complete MySQL database structure for Postfix Admin.
9 # If you are installing from scratch you can use this file otherwise you
10 # need to use the TABLE_CHANGES.TXT or TABLE_BACKUP_MX.TXT that comes with Postfix Admin.
11 #
12 # There are 2 entries for a database user in the file.
13 # One you can use for Postfix and one for Postfix Admin.
14 #
15 # If you run this file twice (2x) you will get an error on the user creation in MySQL.
16 # To go around this you can either comment the lines below "USE MySQL" until "USE postfix".
17 # Or you can remove the users from the database and run it again.
18 #
19 # You can create the database from the shell with:
20 #
21 # mysql -u root [-p] < DATABASE_MYSQL.TXT
22 
23 #
24 # Postfix / MySQL
25 #
26 USE mysql;
27 # Postfix user & password
28 INSERT INTO user (Host, User, Password) VALUES ('localhost','postfix',password('postfix'));
29 INSERT INTO db (Host, Db, User, Select_priv) VALUES ('localhost','postfix','postfix','Y');
30 # Postfix Admin user & password
31 INSERT INTO user (Host, User, Password) VALUES ('localhost','postfixadmin',password('postfixadmin'));
32 INSERT INTO db (Host, Db, User, Select_priv, Insert_priv, Update_priv, Delete_priv) VALUES ('localhost', 'postfix', 'postfixadmin', 'Y', 'Y', 'Y', 'Y');
33 FLUSH PRIVILEGES;
34 GRANT USAGE ON postfix.* TO postfix@localhost;
35 GRANT SELECT, INSERT, DELETE, UPDATE ON postfix.* TO postfix@localhost;
36 GRANT USAGE ON postfix.* TO postfixadmin@localhost;
37 GRANT SELECT, INSERT, DELETE, UPDATE ON postfix.* TO postfixadmin@localhost;
38 CREATE DATABASE postfix;
39 USE postfix;
40 
41 #
42 # Table structure for table admin
43 #
44 CREATE TABLE admin (
45   username varchar(255) NOT NULL default '',
46   password varchar(255) NOT NULL default '',
47   created datetime NOT NULL default '0000-00-00 00:00:00',
48   modified datetime NOT NULL default '0000-00-00 00:00:00',
49   active tinyint(1) NOT NULL default '1',
50   PRIMARY KEY  (username),
51   KEY username (username)
52 ) TYPE=MyISAM COMMENT='Postfix Admin - Virtual Admins';
53 
54 #
55 # Table structure for table alias
56 #
57 CREATE TABLE alias (
58   address varchar(255) NOT NULL default '',
59   goto text NOT NULL,
60   domain varchar(255) NOT NULL default '',
61   created datetime NOT NULL default '0000-00-00 00:00:00',
62   modified datetime NOT NULL default '0000-00-00 00:00:00',
63   active tinyint(1) NOT NULL default '1',
64   PRIMARY KEY  (address),
65   KEY address (address)
66 ) TYPE=MyISAM COMMENT='Postfix Admin - Virtual Aliases';
67 
68 #
69 # Table structure for table domain
70 #
71 CREATE TABLE domain (
72   domain varchar(255) NOT NULL default '',
73   description varchar(255) NOT NULL default '',
74   aliases int(10) NOT NULL default '0',
75   mailboxes int(10) NOT NULL default '0',
76   maxquota int(10) NOT NULL default '0',
77   transport varchar(255) default NULL,
78   backupmx tinyint(1) NOT NULL default '0',
79   created datetime NOT NULL default '0000-00-00 00:00:00',
80   modified datetime NOT NULL default '0000-00-00 00:00:00',
81   active tinyint(1) NOT NULL default '1',
82   PRIMARY KEY  (domain),
83   KEY domain (domain)
84 ) TYPE=MyISAM COMMENT='Postfix Admin - Virtual Domains';
85 
86 #
87 # Table structure for table domain_admins
88 #
89 CREATE TABLE domain_admins (
90   username varchar(255) NOT NULL default '',
91   domain varchar(255) NOT NULL default '',
92   created datetime NOT NULL default '0000-00-00 00:00:00',
93   active tinyint(1) NOT NULL default '1',
94   KEY username (username)
95 ) TYPE=MyISAM COMMENT='Postfix Admin - Domain Admins';
96 
97 #
98 # Table structure for table log
99 #
100 CREATE TABLE log (
101   timestamp datetime NOT NULL default '0000-00-00 00:00:00',
102   username varchar(255) NOT NULL default '',
103   domain varchar(255) NOT NULL default '',
104   action varchar(255) NOT NULL default '',
105   data varchar(255) NOT NULL default '',
106   KEY timestamp (timestamp)
107 ) TYPE=MyISAM COMMENT='Postfix Admin - Log';
108 
109 #
110 # Table structure for table mailbox
111 #
112 CREATE TABLE mailbox (
113   username varchar(255) NOT NULL default '',
114   password varchar(255) NOT NULL default '',
115   name varchar(255) NOT NULL default '',
116   maildir varchar(255) NOT NULL default '',
117   quota int(10) NOT NULL default '0',
118   domain varchar(255) NOT NULL default '',
119   created datetime NOT NULL default '0000-00-00 00:00:00',
120   modified datetime NOT NULL default '0000-00-00 00:00:00',
121   active tinyint(1) NOT NULL default '1',
122   PRIMARY KEY  (username),
123   KEY username (username)
124 ) TYPE=MyISAM COMMENT='Postfix Admin - Virtual Mailboxes';
125 
126 #
127 # Table structure for table vacation
128 #
129 CREATE TABLE vacation (
130   email varchar(255) NOT NULL default '',
131   subject varchar(255) NOT NULL default '',
132   body text NOT NULL,
133   cache text NOT NULL,
134   domain varchar(255) NOT NULL default '',
135   created datetime NOT NULL default '0000-00-00 00:00:00',
136   active tinyint(1) NOT NULL default '1',
137   PRIMARY KEY  (email),
138   KEY email (email)
139 ) TYPE=MyISAM COMMENT='Postfix Admin - Virtual Vacation';