1 0fd75c45 2022-07-30 mischa -- Postfix Admin Release 2.x --
  2 0fd75c45 2022-07-30 mischa ----------------------------------------------------------
  3 0fd75c45 2022-07-30 mischa --
  4 0fd75c45 2022-07-30 mischa -- Copyright (c) 2002 - 2005 High5!
  5 0fd75c45 2022-07-30 mischa -- Created by: Mischa Peters <mischa at high5 dot net>
  6 0fd75c45 2022-07-30 mischa --
  7 0fd75c45 2022-07-30 mischa -- This is the complete database structure for Postfix Admin.
  8 0fd75c45 2022-07-30 mischa -- If you are installing from scratch you can use this file otherwise you
  9 0fd75c45 2022-07-30 mischa -- need to use the TABLE_CHANGES.TXT or TABLE_BACKUP_MX.TXT that comes with Postfix Admin.
 10 0fd75c45 2022-07-30 mischa --
 11 0fd75c45 2022-07-30 mischa -- There are 2 entries for a database user in the file.
 12 0fd75c45 2022-07-30 mischa -- One you can use for Postfix and one for Postfix Admin.
 13 0fd75c45 2022-07-30 mischa --
 14 0fd75c45 2022-07-30 mischa -- If you run this file twice (2x) you will get an error on the user creation in MySQL.
 15 0fd75c45 2022-07-30 mischa -- To go around this you can either comment the lines below "USE MySQL" until "USE postfix".
 16 0fd75c45 2022-07-30 mischa -- Or you can remove the users from the database and run it again.
 17 0fd75c45 2022-07-30 mischa --
 18 0fd75c45 2022-07-30 mischa -- You can create the database from the shell with:
 19 0fd75c45 2022-07-30 mischa --      creatuser -P postfix
 20 0fd75c45 2022-07-30 mischa --      creatuser -P postfixadmin
 21 0fd75c45 2022-07-30 mischa --      createdb postfix
 22 0fd75c45 2022-07-30 mischa --      psql postfix
 23 0fd75c45 2022-07-30 mischa --      postfix=# \i postfix.sql
 24 0fd75c45 2022-07-30 mischa --      postfix=# \q
 25 0fd75c45 2022-07-30 mischa --      exit
 26 0fd75c45 2022-07-30 mischa --
 27 0fd75c45 2022-07-30 mischa -- Postfix / PgSQL
 28 0fd75c45 2022-07-30 mischa 
 29 0fd75c45 2022-07-30 mischa --
 30 0fd75c45 2022-07-30 mischa -- DROP TABLE admin,alias,domain,domain_admins,log,mailbox,vacation;
 31 0fd75c45 2022-07-30 mischa --
 32 0fd75c45 2022-07-30 mischa 
 33 0fd75c45 2022-07-30 mischa --
 34 0fd75c45 2022-07-30 mischa -- Table structure for table admin
 35 0fd75c45 2022-07-30 mischa --
 36 0fd75c45 2022-07-30 mischa CREATE TABLE "admin" (
 37 0fd75c45 2022-07-30 mischa   "username" character varying(255) NOT NULL default '',
 38 0fd75c45 2022-07-30 mischa   "password" character varying(255) NOT NULL default '',
 39 0fd75c45 2022-07-30 mischa   "created" timestamp with time zone default now(),
 40 0fd75c45 2022-07-30 mischa   "modified" timestamp with time zone default now(),
 41 0fd75c45 2022-07-30 mischa   "active" boolean NOT NULL default true,
 42 0fd75c45 2022-07-30 mischa   Constraint "admin_key" Primary Key ("username")
 43 0fd75c45 2022-07-30 mischa );
 44 0fd75c45 2022-07-30 mischa COMMENT ON TABLE admin IS 'Postfix Admin - Virtual Admins';
 45 0fd75c45 2022-07-30 mischa --
 46 0fd75c45 2022-07-30 mischa -- Table structure for table alias
 47 0fd75c45 2022-07-30 mischa --
 48 0fd75c45 2022-07-30 mischa CREATE TABLE alias (
 49 0fd75c45 2022-07-30 mischa  address character varying(255) NOT NULL default '',
 50 0fd75c45 2022-07-30 mischa  goto text NOT NULL,
 51 0fd75c45 2022-07-30 mischa  domain character varying(255) NOT NULL default '',
 52 0fd75c45 2022-07-30 mischa  created timestamp with time zone default now(),
 53 0fd75c45 2022-07-30 mischa  modified timestamp with time zone default now(),
 54 0fd75c45 2022-07-30 mischa  active boolean NOT NULL default true,
 55 0fd75c45 2022-07-30 mischa -- PRIMARY KEY ("address"),
 56 0fd75c45 2022-07-30 mischa -- KEY address ("address"),
 57 0fd75c45 2022-07-30 mischa  Constraint "alias_key" Primary Key ("address")
 58 0fd75c45 2022-07-30 mischa );
 59 0fd75c45 2022-07-30 mischa COMMENT ON TABLE alias IS 'Postfix Admin - Virtual Aliases';
 60 0fd75c45 2022-07-30 mischa 
 61 0fd75c45 2022-07-30 mischa --
 62 0fd75c45 2022-07-30 mischa -- Table structure for table domain
 63 0fd75c45 2022-07-30 mischa --
 64 0fd75c45 2022-07-30 mischa CREATE TABLE domain (
 65 0fd75c45 2022-07-30 mischa  domain character varying(255) NOT NULL default '',
 66 0fd75c45 2022-07-30 mischa  description character varying(255) NOT NULL default '',
 67 0fd75c45 2022-07-30 mischa  aliases integer NOT NULL default 0,
 68 0fd75c45 2022-07-30 mischa  mailboxes integer NOT NULL default 0,
 69 0fd75c45 2022-07-30 mischa  maxquota integer NOT NULL default 0,
 70 0fd75c45 2022-07-30 mischa  transport character varying(255) default NULL,
 71 0fd75c45 2022-07-30 mischa  backupmx boolean NOT NULL default false,
 72 0fd75c45 2022-07-30 mischa  created timestamp with time zone default now(),
 73 0fd75c45 2022-07-30 mischa  modified timestamp with time zone default now(),
 74 0fd75c45 2022-07-30 mischa  active boolean NOT NULL default true,
 75 0fd75c45 2022-07-30 mischa -- PRIMARY KEY ("domain"),
 76 0fd75c45 2022-07-30 mischa -- KEY domain ("domain"),
 77 0fd75c45 2022-07-30 mischa  Constraint "domain_key" Primary Key ("domain")
 78 0fd75c45 2022-07-30 mischa );
 79 0fd75c45 2022-07-30 mischa COMMENT ON TABLE domain IS 'Postfix Admin - Virtual Domains';
 80 0fd75c45 2022-07-30 mischa 
 81 0fd75c45 2022-07-30 mischa --
 82 0fd75c45 2022-07-30 mischa -- Table structure for table domain_admins
 83 0fd75c45 2022-07-30 mischa --
 84 0fd75c45 2022-07-30 mischa CREATE TABLE domain_admins (
 85 0fd75c45 2022-07-30 mischa  username character varying(255) NOT NULL default '',
 86 0fd75c45 2022-07-30 mischa  domain character varying(255) NOT NULL default '',
 87 0fd75c45 2022-07-30 mischa  created timestamp with time zone default now(),
 88 0fd75c45 2022-07-30 mischa  active boolean NOT NULL default true
 89 0fd75c45 2022-07-30 mischa -- KEY username ("username")
 90 0fd75c45 2022-07-30 mischa );
 91 0fd75c45 2022-07-30 mischa COMMENT ON TABLE domain_admins IS 'Postfix Admin - Domain Admins';
 92 0fd75c45 2022-07-30 mischa 
 93 0fd75c45 2022-07-30 mischa --
 94 0fd75c45 2022-07-30 mischa -- Table structure for table log
 95 0fd75c45 2022-07-30 mischa --
 96 0fd75c45 2022-07-30 mischa CREATE TABLE log (
 97 0fd75c45 2022-07-30 mischa  timestamp timestamp with time zone default now(),
 98 0fd75c45 2022-07-30 mischa  username character varying(255) NOT NULL default '',
 99 0fd75c45 2022-07-30 mischa  domain character varying(255) NOT NULL default '',
100 0fd75c45 2022-07-30 mischa  action character varying(255) NOT NULL default '',
101 0fd75c45 2022-07-30 mischa  data character varying(255) NOT NULL default ''
102 0fd75c45 2022-07-30 mischa -- KEY timestamp ("timestamp")
103 0fd75c45 2022-07-30 mischa );
104 0fd75c45 2022-07-30 mischa COMMENT ON TABLE log IS 'Postfix Admin - Log';
105 0fd75c45 2022-07-30 mischa 
106 0fd75c45 2022-07-30 mischa --
107 0fd75c45 2022-07-30 mischa -- Table structure for table mailbox
108 0fd75c45 2022-07-30 mischa --
109 0fd75c45 2022-07-30 mischa CREATE TABLE mailbox (
110 0fd75c45 2022-07-30 mischa  username character varying(255) NOT NULL default '',
111 0fd75c45 2022-07-30 mischa  password character varying(255) NOT NULL default '',
112 0fd75c45 2022-07-30 mischa  name character varying(255) NOT NULL default '',
113 0fd75c45 2022-07-30 mischa  maildir character varying(255) NOT NULL default '',
114 0fd75c45 2022-07-30 mischa  quota integer NOT NULL default 0,
115 0fd75c45 2022-07-30 mischa  domain character varying(255) NOT NULL default '',
116 0fd75c45 2022-07-30 mischa  created timestamp with time zone default now(),
117 0fd75c45 2022-07-30 mischa  modified timestamp with time zone default now(),
118 0fd75c45 2022-07-30 mischa  active boolean NOT NULL default true,
119 0fd75c45 2022-07-30 mischa -- PRIMARY KEY ("username"),
120 0fd75c45 2022-07-30 mischa -- KEY username ("username"),
121 0fd75c45 2022-07-30 mischa  Constraint "mailbox_key" Primary Key ("username")
122 0fd75c45 2022-07-30 mischa );
123 0fd75c45 2022-07-30 mischa COMMENT ON TABLE mailbox IS 'Postfix Admin - Virtual Mailboxes';
124 0fd75c45 2022-07-30 mischa 
125 0fd75c45 2022-07-30 mischa --
126 0fd75c45 2022-07-30 mischa -- Table structure for table vacation
127 0fd75c45 2022-07-30 mischa --
128 0fd75c45 2022-07-30 mischa CREATE TABLE vacation (
129 0fd75c45 2022-07-30 mischa  email character varying(255) NOT NULL default '',
130 0fd75c45 2022-07-30 mischa  subject character varying(255) NOT NULL default '',
131 0fd75c45 2022-07-30 mischa  body text NOT NULL,
132 0fd75c45 2022-07-30 mischa  cache text NOT NULL,
133 0fd75c45 2022-07-30 mischa  domain character varying(255) NOT NULL default '',
134 0fd75c45 2022-07-30 mischa  created timestamp with time zone default now(),
135 0fd75c45 2022-07-30 mischa  active boolean NOT NULL default true,
136 0fd75c45 2022-07-30 mischa -- PRIMARY KEY ("email"),
137 0fd75c45 2022-07-30 mischa -- KEY email ("email")
138 0fd75c45 2022-07-30 mischa  Constraint "vacation_key" Primary Key ("email")
139 0fd75c45 2022-07-30 mischa );
140 0fd75c45 2022-07-30 mischa COMMENT ON TABLE vacation IS 'Postfix Admin - Virtual Vacation';
141 0fd75c45 2022-07-30 mischa 
142 0fd75c45 2022-07-30 mischa GRANT SELECT,INSERT,UPDATE,DELETE ON admin,alias,domain,domain_admins,log,mailbox,vacation TO postfixadmin_user;