|
1 SET client_encoding = 'UTF8'; |
|
2 SET client_min_messages = warning; |
|
3 |
|
4 |
|
5 CREATE SEQUENCE transport_id; |
|
6 |
|
7 CREATE SEQUENCE maillocation_id; |
|
8 |
|
9 CREATE SEQUENCE domain_gid |
|
10 START WITH 70000 |
|
11 INCREMENT BY 1 |
|
12 MINVALUE 70000 |
|
13 MAXVALUE 4294967294 |
|
14 NO CYCLE; |
|
15 |
|
16 CREATE SEQUENCE users_uid |
|
17 START WITH 70000 |
|
18 INCREMENT BY 1 |
|
19 MINVALUE 70000 |
|
20 MAXVALUE 4294967294 |
|
21 NO CYCLE; |
|
22 |
|
23 |
|
24 CREATE TABLE transport ( |
|
25 tid bigint NOT NULL DEFAULT nextval('transport_id'), |
|
26 transport varchar(270) NOT NULL, -- smtps:[255-char.host.name:50025] |
|
27 CONSTRAINT pkey_transport PRIMARY KEY (tid), |
|
28 CONSTRAINT ukey_transport UNIQUE (transport) |
|
29 ); |
|
30 -- Insert default transport |
|
31 INSERT INTO transport(transport) VALUES ('dovecot:'); |
|
32 |
|
33 CREATE TABLE maillocation( |
|
34 mid bigint NOT NULL DEFAULT nextval('maillocation_id'), |
|
35 maillocation varchar(20) NOT NULL, |
|
36 CONSTRAINT pkey_maillocation PRIMARY KEY (mid), |
|
37 CONSTRAINT ukey_maillocation UNIQUE (maillocation) |
|
38 ); |
|
39 -- Insert default Maildir-folder name |
|
40 INSERT INTO maillocation(maillocation) VALUES ('Maildir'); |
|
41 |
|
42 CREATE TABLE domain_data ( |
|
43 gid bigint NOT NULL DEFAULT nextval('domain_gid'), |
|
44 tid bigint NOT NULL DEFAULT 1, -- defualt transport |
|
45 domaindir varchar(40) NOT NULL, --/srv/mail/$RAND/4294967294 |
|
46 CONSTRAINT pkey_domain_data PRIMARY KEY (gid), |
|
47 CONSTRAINT fkey_domain_data_tid_transport FOREIGN KEY (tid) |
|
48 REFERENCES transport (tid) |
|
49 ); |
|
50 |
|
51 CREATE TABLE domain_name ( |
|
52 domainname varchar(255) NOT NULL, |
|
53 gid bigint NOT NULL, |
|
54 is_primary boolean NOT NULL, |
|
55 CONSTRAINT pkey_domain_name PRIMARY KEY (domainname), |
|
56 CONSTRAINT fkey_domain_name_gid_domain_data FOREIGN KEY (gid) |
|
57 REFERENCES domain_data (gid) |
|
58 ); |
|
59 |
|
60 CREATE TABLE users ( |
|
61 local_part varchar(64) NOT NULL,-- only localpart w/o '@' |
|
62 passwd varchar(74) NOT NULL,-- {CRAM-MD5}+64hex numbers |
|
63 name varchar(128) NULL, |
|
64 uid bigint NOT NULL DEFAULT nextval('users_uid'), |
|
65 gid bigint NOT NULL, |
|
66 mid bigint NOT NULL DEFAULT 1, |
|
67 tid bigint NOT NULL DEFAULT 1, |
|
68 smtp boolean NOT NULL DEFAULT TRUE, |
|
69 pop3 boolean NOT NULL DEFAULT TRUE, |
|
70 imap boolean NOT NULL DEFAULT TRUE, |
|
71 managesieve boolean NOT NULL DEFAULT TRUE, |
|
72 CONSTRAINT pkey_users PRIMARY KEY (local_part, gid), |
|
73 CONSTRAINT ukey_users_uid UNIQUE (uid), |
|
74 CONSTRAINT fkey_users_gid_domain_data FOREIGN KEY (gid) |
|
75 REFERENCES domain_data (gid), |
|
76 CONSTRAINT fkey_users_mid_maillocation FOREIGN KEY (mid) |
|
77 REFERENCES maillocation (mid), |
|
78 CONSTRAINT fkey_users_tid_transport FOREIGN KEY (tid) |
|
79 REFERENCES transport (tid) |
|
80 ); |
|
81 |
|
82 CREATE TABLE alias ( |
|
83 gid bigint NOT NULL, |
|
84 address varchar(64) NOT NULL,-- only localpart w/o '@' |
|
85 destination varchar(320) NOT NULL, |
|
86 CONSTRAINT pkey_alias PRIMARY KEY (gid, address, destination), |
|
87 CONSTRAINT fkey_alias_gid_domain_data FOREIGN KEY (gid) |
|
88 REFERENCES domain_data (gid) |
|
89 ); |
|
90 |
|
91 CREATE TABLE relocated ( |
|
92 gid bigint NOT NULL, |
|
93 address varchar(64) NOT NULL, |
|
94 destination varchar(320) NOT NULL, |
|
95 CONSTRAINT pkey_relocated PRIMARY KEY (gid, address), |
|
96 CONSTRAINT fkey_relocated_gid_domain_data FOREIGN KEY (gid) |
|
97 REFERENCES domain_data (gid) |
|
98 ); |
|
99 |
|
100 CREATE OR REPLACE VIEW dovecot_password AS |
|
101 SELECT local_part || '@' || domain_name.domainname AS "user", |
|
102 passwd AS "password", smtp, pop3, imap, managesieve |
|
103 FROM users |
|
104 LEFT JOIN domain_name USING (gid); |
|
105 |
|
106 CREATE OR REPLACE VIEW dovecot_user AS |
|
107 SELECT local_part || '@' || domain_name.domainname AS userid, |
|
108 uid, gid, domain_data.domaindir || '/' || uid AS home, |
|
109 '~/' || maillocation.maillocation AS mail |
|
110 FROM users |
|
111 LEFT JOIN domain_data USING (gid) |
|
112 LEFT JOIN domain_name USING (gid) |
|
113 LEFT JOIN maillocation USING (mid); |
|
114 |
|
115 CREATE OR REPLACE VIEW postfix_gid AS |
|
116 SELECT gid, domainname |
|
117 FROM domain_name; |
|
118 |
|
119 CREATE OR REPLACE VIEW postfix_uid AS |
|
120 SELECT local_part || '@' || domain_name.domainname AS address, uid |
|
121 FROM users |
|
122 LEFT JOIN domain_name USING (gid); |
|
123 |
|
124 CREATE OR REPLACE VIEW postfix_maildir AS |
|
125 SELECT local_part || '@' || domain_name.domainname AS address, |
|
126 domain_data.domaindir||'/'||uid||'/'||maillocation.maillocation||'/' |
|
127 AS maildir |
|
128 FROM users |
|
129 LEFT JOIN domain_data USING (gid) |
|
130 LEFT JOIN domain_name USING (gid) |
|
131 LEFT JOIN maillocation USING (mid); |
|
132 |
|
133 CREATE OR REPLACE VIEW postfix_relocated AS |
|
134 SELECT address || '@' || domain_name.domainname AS address, destination |
|
135 FROM relocated |
|
136 LEFT JOIN domain_name USING (gid); |
|
137 |
|
138 CREATE OR REPLACE VIEW postfix_alias AS |
|
139 SELECT address || '@' || domain_name.domainname AS address, destination, gid |
|
140 FROM alias |
|
141 LEFT JOIN domain_name USING (gid); |
|
142 |
|
143 CREATE OR REPLACE VIEW postfix_transport AS |
|
144 SELECT local_part || '@' || domain_name.domainname AS address, |
|
145 transport.transport |
|
146 FROM users |
|
147 LEFT JOIN transport USING (tid) |
|
148 LEFT JOIN domain_name USING (gid); |
|
149 |
|
150 CREATE OR REPLACE VIEW vmm_domain_info AS |
|
151 SELECT gid, domainname, transport, domaindir, |
|
152 count(uid) AS accounts, |
|
153 (SELECT count(DISTINCT address) |
|
154 FROM alias |
|
155 WHERE alias.gid = domain_data.gid) AS aliases, |
|
156 (SELECT count(gid) |
|
157 FROM relocated |
|
158 WHERE relocated.gid = domain_data.gid) AS relocated, |
|
159 (SELECT count(gid) |
|
160 FROM domain_name |
|
161 WHERE domain_name.gid = domain_data.gid |
|
162 AND NOT domain_name.is_primary) AS aliasdomains |
|
163 FROM domain_data |
|
164 LEFT JOIN domain_name USING (gid) |
|
165 LEFT JOIN transport USING (tid) |
|
166 LEFT JOIN users USING (gid) |
|
167 WHERE domain_name.is_primary |
|
168 GROUP BY gid, domainname, transport, domaindir; |
|
169 |
|
170 |
|
171 CREATE LANGUAGE plpgsql; |
|
172 |
|
173 |
|
174 CREATE OR REPLACE FUNCTION domain_primary_trigger() RETURNS TRIGGER AS $$ |
|
175 DECLARE |
|
176 primary_count bigint; |
|
177 BEGIN |
|
178 SELECT INTO primary_count count(gid) + NEW.is_primary::integer |
|
179 FROM domain_name |
|
180 WHERE domain_name.gid = NEW.gid |
|
181 AND is_primary; |
|
182 |
|
183 IF (primary_count > 1) THEN |
|
184 RAISE EXCEPTION 'There can only be one domain marked as primary.'; |
|
185 END IF; |
|
186 |
|
187 RETURN NEW; |
|
188 END; |
|
189 $$ LANGUAGE plpgsql STABLE; |
|
190 |
|
191 |
|
192 CREATE TRIGGER primary_count_ins BEFORE INSERT ON domain_name |
|
193 FOR EACH ROW EXECUTE PROCEDURE domain_primary_trigger(); |
|
194 |
|
195 CREATE TRIGGER primary_count_upd AFTER UPDATE ON domain_name |
|
196 FOR EACH ROW EXECUTE PROCEDURE domain_primary_trigger(); |