summaryrefslogtreecommitdiffstats
path: root/DATABASE_PGSQL.TXT
blob: 665c3f3dd8aecd8f58ae2e7bdf4dbf9066e47a3d (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
-- Postfix Admin Release 2.x --
----------------------------------------------------------
--
-- Copyright (c) 2002 - 2005 High5!
-- Created by: Mischa Peters <mischa at high5 dot net>
--
-- This is the complete 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:
--      creatuser -P postfix
--      creatuser -P postfixadmin
--      createdb postfix
--      psql postfix
--      postfix=# \i postfix.sql
--      postfix=# \q
--      exit
--
-- Postfix / PgSQL

--
-- DROP TABLE admin,alias,domain,domain_admins,log,mailbox,vacation;
--

--
-- Table structure for table admin
--
CREATE TABLE "admin" (
  "username" character varying(255) NOT NULL default '',
  "password" character varying(255) NOT NULL default '',
  "created" timestamp with time zone default now(),
  "modified" timestamp with time zone default now(),
  "active" boolean NOT NULL default true,
  Constraint "admin_key" Primary Key ("username")
);
COMMENT ON TABLE admin IS 'Postfix Admin - Virtual Admins';
--
-- Table structure for table alias
--
CREATE TABLE alias (
 address character varying(255) NOT NULL default '',
 goto text NOT NULL,
 domain character varying(255) NOT NULL default '',
 created timestamp with time zone default now(),
 modified timestamp with time zone default now(),
 active boolean NOT NULL default true,
-- PRIMARY KEY ("address"),
-- KEY address ("address"),
 Constraint "alias_key" Primary Key ("address")
);
COMMENT ON TABLE alias IS 'Postfix Admin - Virtual Aliases';

--
-- Table structure for table domain
--
CREATE TABLE domain (
 domain character varying(255) NOT NULL default '',
 description character varying(255) NOT NULL default '',
 aliases integer NOT NULL default 0,
 mailboxes integer NOT NULL default 0,
 maxquota integer NOT NULL default 0,
 transport character varying(255) default NULL,
 backupmx boolean NOT NULL default false,
 created timestamp with time zone default now(),
 modified timestamp with time zone default now(),
 active boolean NOT NULL default true,
-- PRIMARY KEY ("domain"),
-- KEY domain ("domain"),
 Constraint "domain_key" Primary Key ("domain")
);
COMMENT ON TABLE domain IS 'Postfix Admin - Virtual Domains';

--
-- Table structure for table domain_admins
--
CREATE TABLE domain_admins (
 username character varying(255) NOT NULL default '',
 domain character varying(255) NOT NULL default '',
 created timestamp with time zone default now(),
 active boolean NOT NULL default true
-- KEY username ("username")
);
COMMENT ON TABLE domain_admins IS 'Postfix Admin - Domain Admins';

--
-- Table structure for table log
--
CREATE TABLE log (
 timestamp timestamp with time zone default now(),
 username character varying(255) NOT NULL default '',
 domain character varying(255) NOT NULL default '',
 action character varying(255) NOT NULL default '',
 data character varying(255) NOT NULL default ''
-- KEY timestamp ("timestamp")
);
COMMENT ON TABLE log IS 'Postfix Admin - Log';

--
-- Table structure for table mailbox
--
CREATE TABLE mailbox (
 username character varying(255) NOT NULL default '',
 password character varying(255) NOT NULL default '',
 name character varying(255) NOT NULL default '',
 maildir character varying(255) NOT NULL default '',
 quota integer NOT NULL default 0,
 domain character varying(255) NOT NULL default '',
 created timestamp with time zone default now(),
 modified timestamp with time zone default now(),
 active boolean NOT NULL default true,
-- PRIMARY KEY ("username"),
-- KEY username ("username"),
 Constraint "mailbox_key" Primary Key ("username")
);
COMMENT ON TABLE mailbox IS 'Postfix Admin - Virtual Mailboxes';

--
-- Table structure for table vacation
--
CREATE TABLE vacation (
 email character varying(255) NOT NULL default '',
 subject character varying(255) NOT NULL default '',
 body text NOT NULL,
 cache text NOT NULL,
 domain character varying(255) NOT NULL default '',
 created timestamp with time zone default now(),
 active boolean NOT NULL default true,
-- PRIMARY KEY ("email"),
-- KEY email ("email")
 Constraint "vacation_key" Primary Key ("email")
);
COMMENT ON TABLE vacation IS 'Postfix Admin - Virtual Vacation';

GRANT SELECT,INSERT,UPDATE,DELETE ON admin,alias,domain,domain_admins,log,mailbox,vacation TO postfixadmin_user;