28 CONSTRAINT ukey_transport UNIQUE (transport) |
34 CONSTRAINT ukey_transport UNIQUE (transport) |
29 ); |
35 ); |
30 -- Insert default transport |
36 -- Insert default transport |
31 INSERT INTO transport(transport) VALUES ('dovecot:'); |
37 INSERT INTO transport(transport) VALUES ('dovecot:'); |
32 |
38 |
33 CREATE TABLE maillocation( |
39 CREATE TABLE mailboxformat ( |
34 mid bigint NOT NULL DEFAULT nextval('maillocation_id'), |
40 fid bigint NOT NULL DEFAULT nextval('mailboxformat_id'), |
35 maillocation varchar(20) NOT NULL, |
41 format varchar(20) NOT NULL, |
|
42 CONSTRAINT pkey_mailboxformat PRIMARY KEY (fid), |
|
43 CONSTRAINT ukey_mailboxformat UNIQUE (format) |
|
44 ); |
|
45 -- Insert supported mailbox formats |
|
46 INSERT INTO mailboxformat(format) VALUES ('maildir'); |
|
47 INSERT INTO mailboxformat(format) VALUES ('mdbox'); |
|
48 INSERT INTO mailboxformat(format) VALUES ('sdbox'); |
|
49 |
|
50 CREATE TABLE maillocation ( |
|
51 mid bigint NOT NULL DEFAULT nextval('maillocation_id'), |
|
52 fid bigint NOT NULL DEFAULT 1, |
|
53 directory varchar(20) NOT NULL, |
|
54 extra varchar(1024), |
36 CONSTRAINT pkey_maillocation PRIMARY KEY (mid), |
55 CONSTRAINT pkey_maillocation PRIMARY KEY (mid), |
37 CONSTRAINT ukey_maillocation UNIQUE (maillocation) |
56 CONSTRAINT fkey_maillocation_fid_mailboxformat FOREIGN KEY (fid) |
|
57 REFERENCES mailboxformat (fid) |
38 ); |
58 ); |
39 -- Insert default Maildir-folder name |
59 -- Insert default Maildir-folder name |
40 INSERT INTO maillocation(maillocation) VALUES ('Maildir'); |
60 INSERT INTO maillocation(directory) VALUES ('Maildir'); |
|
61 |
|
62 CREATE TABLE quotalimit ( |
|
63 qid bigint NOT NULL DEFAULT nextval('quotalimit_id'), |
|
64 bytes bigint NOT NULL, |
|
65 messages integer NOT NULL DEFAULT 0, |
|
66 CONSTRAINT pkey_quotalimit PRIMARY KEY (qid), |
|
67 CONSTRAINT ukey_quotalimit UNIQUE (bytes, messages) |
|
68 ); |
|
69 -- Insert default (non) quota limit |
|
70 INSERT INTO quotalimit(bytes, messages) VALUES (0, 0); |
|
71 |
|
72 CREATE TABLE service_set ( |
|
73 ssid bigint NOT NULL DEFAULT nextval('service_set_id'), |
|
74 smtp boolean NOT NULL DEFAULT TRUE, |
|
75 pop3 boolean NOT NULL DEFAULT TRUE, |
|
76 imap boolean NOT NULL DEFAULT TRUE, |
|
77 managesieve boolean NOT NULL DEFAULT TRUE, |
|
78 CONSTRAINT pkey_service_set PRIMARY KEY (ssid), |
|
79 CONSTRAINT ukey_service_set UNIQUE (smtp, pop3, imap, managesieve) |
|
80 ); |
|
81 -- Insert all possible service combinations |
|
82 COPY service_set (smtp, pop3, imap, managesieve) FROM stdin; |
|
83 TRUE TRUE TRUE TRUE |
|
84 FALSE TRUE TRUE TRUE |
|
85 TRUE FALSE TRUE TRUE |
|
86 FALSE FALSE TRUE TRUE |
|
87 TRUE TRUE FALSE TRUE |
|
88 FALSE TRUE FALSE TRUE |
|
89 TRUE FALSE FALSE TRUE |
|
90 FALSE FALSE FALSE TRUE |
|
91 TRUE TRUE TRUE FALSE |
|
92 FALSE TRUE TRUE FALSE |
|
93 TRUE FALSE TRUE FALSE |
|
94 FALSE FALSE TRUE FALSE |
|
95 TRUE TRUE FALSE FALSE |
|
96 FALSE TRUE FALSE FALSE |
|
97 TRUE FALSE FALSE FALSE |
|
98 FALSE FALSE FALSE FALSE |
|
99 \. |
41 |
100 |
42 CREATE TABLE domain_data ( |
101 CREATE TABLE domain_data ( |
43 gid bigint NOT NULL DEFAULT nextval('domain_gid'), |
102 gid bigint NOT NULL DEFAULT nextval('domain_gid'), |
44 tid bigint NOT NULL DEFAULT 1, -- defualt transport |
103 qid bigint NOT NULL DEFAULT 1, -- default quota limit |
|
104 ssid bigint NOT NULL DEFAULT 1, -- default service_set |
|
105 tid bigint NOT NULL DEFAULT 1, -- default transport |
45 domaindir varchar(40) NOT NULL, --/srv/mail/$RAND/4294967294 |
106 domaindir varchar(40) NOT NULL, --/srv/mail/$RAND/4294967294 |
|
107 note text NULL DEFAULT NULL, |
46 CONSTRAINT pkey_domain_data PRIMARY KEY (gid), |
108 CONSTRAINT pkey_domain_data PRIMARY KEY (gid), |
|
109 CONSTRAINT fkey_domain_data_qid_quotalimit FOREIGN KEY (qid) |
|
110 REFERENCES quotalimit (qid), |
|
111 CONSTRAINT fkey_domain_data_ssid_service_set FOREIGN KEY (ssid) |
|
112 REFERENCES service_set (ssid), |
47 CONSTRAINT fkey_domain_data_tid_transport FOREIGN KEY (tid) |
113 CONSTRAINT fkey_domain_data_tid_transport FOREIGN KEY (tid) |
48 REFERENCES transport (tid) |
114 REFERENCES transport (tid) |
49 ); |
115 ); |
50 |
116 |
51 CREATE TABLE domain_name ( |
117 CREATE TABLE domain_name ( |
57 REFERENCES domain_data (gid) |
123 REFERENCES domain_data (gid) |
58 ); |
124 ); |
59 |
125 |
60 CREATE TABLE users ( |
126 CREATE TABLE users ( |
61 local_part varchar(64) NOT NULL,-- only localpart w/o '@' |
127 local_part varchar(64) NOT NULL,-- only localpart w/o '@' |
62 passwd varchar(74) NOT NULL,-- {CRAM-MD5}+64hex numbers |
128 passwd varchar(270) NOT NULL, |
63 name varchar(128) NULL, |
129 name varchar(128) NULL, |
64 uid bigint NOT NULL DEFAULT nextval('users_uid'), |
130 uid bigint NOT NULL DEFAULT nextval('users_uid'), |
65 gid bigint NOT NULL, |
131 gid bigint NOT NULL, |
66 mid bigint NOT NULL DEFAULT 1, |
132 mid bigint NOT NULL DEFAULT 1, |
67 tid bigint NOT NULL DEFAULT 1, |
133 qid bigint NULL DEFAULT NULL, |
68 smtp boolean NOT NULL DEFAULT TRUE, |
134 ssid bigint NULL DEFAULT NULL, |
69 pop3 boolean NOT NULL DEFAULT TRUE, |
135 tid bigint NULL DEFAULT NULL, |
70 imap boolean NOT NULL DEFAULT TRUE, |
136 note text NULL DEFAULT NULL, |
71 managesieve boolean NOT NULL DEFAULT TRUE, |
|
72 CONSTRAINT pkey_users PRIMARY KEY (local_part, gid), |
137 CONSTRAINT pkey_users PRIMARY KEY (local_part, gid), |
73 CONSTRAINT ukey_users_uid UNIQUE (uid), |
138 CONSTRAINT ukey_users_uid UNIQUE (uid), |
74 CONSTRAINT fkey_users_gid_domain_data FOREIGN KEY (gid) |
139 CONSTRAINT fkey_users_gid_domain_data FOREIGN KEY (gid) |
75 REFERENCES domain_data (gid), |
140 REFERENCES domain_data (gid), |
76 CONSTRAINT fkey_users_mid_maillocation FOREIGN KEY (mid) |
141 CONSTRAINT fkey_users_mid_maillocation FOREIGN KEY (mid) |
77 REFERENCES maillocation (mid), |
142 REFERENCES maillocation (mid), |
|
143 CONSTRAINT fkey_users_qid_quotalimit FOREIGN KEY (qid) |
|
144 REFERENCES quotalimit (qid), |
|
145 CONSTRAINT fkey_users_ssid_service_set FOREIGN KEY (ssid) |
|
146 REFERENCES service_set (ssid), |
78 CONSTRAINT fkey_users_tid_transport FOREIGN KEY (tid) |
147 CONSTRAINT fkey_users_tid_transport FOREIGN KEY (tid) |
79 REFERENCES transport (tid) |
148 REFERENCES transport (tid) |
|
149 ); |
|
150 |
|
151 CREATE TABLE userquota_11 ( |
|
152 uid bigint NOT NULL, |
|
153 path varchar(16) NOT NULL, |
|
154 current bigint NOT NULL DEFAULT 0, |
|
155 CONSTRAINT pkey_userquota_11 PRIMARY KEY (uid, path), |
|
156 CONSTRAINT fkey_userquota_11_uid_users FOREIGN KEY (uid) |
|
157 REFERENCES users (uid) ON DELETE CASCADE |
80 ); |
158 ); |
81 |
159 |
82 CREATE TABLE alias ( |
160 CREATE TABLE alias ( |
83 gid bigint NOT NULL, |
161 gid bigint NOT NULL, |
84 address varchar(64) NOT NULL,-- only localpart w/o '@' |
162 address varchar(64) NOT NULL,-- only localpart w/o '@' |
95 CONSTRAINT pkey_relocated PRIMARY KEY (gid, address), |
173 CONSTRAINT pkey_relocated PRIMARY KEY (gid, address), |
96 CONSTRAINT fkey_relocated_gid_domain_data FOREIGN KEY (gid) |
174 CONSTRAINT fkey_relocated_gid_domain_data FOREIGN KEY (gid) |
97 REFERENCES domain_data (gid) |
175 REFERENCES domain_data (gid) |
98 ); |
176 ); |
99 |
177 |
100 CREATE OR REPLACE VIEW dovecot_password AS |
178 CREATE TABLE catchall ( |
101 SELECT local_part || '@' || domain_name.domainname AS "user", |
179 gid bigint NOT NULL, |
102 passwd AS "password", smtp, pop3, imap, managesieve |
180 destination varchar(320) NOT NULL, |
103 FROM users |
181 CONSTRAINT pkey_catchall PRIMARY KEY (gid, destination), |
104 LEFT JOIN domain_name USING (gid); |
182 CONSTRAINT fkey_catchall_gid_domain_data FOREIGN KEY (gid) |
105 |
183 REFERENCES domain_data (gid) |
106 CREATE OR REPLACE VIEW dovecot_user AS |
184 ); |
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 |
185 |
115 CREATE OR REPLACE VIEW postfix_gid AS |
186 CREATE OR REPLACE VIEW postfix_gid AS |
116 SELECT gid, domainname |
187 SELECT gid, domainname |
117 FROM domain_name; |
188 FROM domain_name; |
118 |
189 |
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 |
190 CREATE OR REPLACE VIEW vmm_domain_info AS |
151 SELECT gid, domainname, transport, domaindir, |
191 SELECT gid, count(uid) AS accounts, |
152 count(uid) AS accounts, |
|
153 (SELECT count(DISTINCT address) |
192 (SELECT count(DISTINCT address) |
154 FROM alias |
193 FROM alias |
155 WHERE alias.gid = domain_data.gid) AS aliases, |
194 WHERE alias.gid = domain_data.gid) AS aliases, |
156 (SELECT count(gid) |
195 (SELECT count(gid) |
157 FROM relocated |
196 FROM relocated |
158 WHERE relocated.gid = domain_data.gid) AS relocated, |
197 WHERE relocated.gid = domain_data.gid) AS relocated, |
159 (SELECT count(gid) |
198 (SELECT count(gid) |
160 FROM domain_name |
199 FROM domain_name |
161 WHERE domain_name.gid = domain_data.gid |
200 WHERE domain_name.gid = domain_data.gid |
162 AND NOT domain_name.is_primary) AS aliasdomains |
201 AND NOT domain_name.is_primary) AS aliasdomains, |
|
202 (SELECT count(gid) |
|
203 FROM catchall |
|
204 WHERE catchall.gid = domain_data.gid) AS catchall |
163 FROM domain_data |
205 FROM domain_data |
164 LEFT JOIN domain_name USING (gid) |
206 LEFT JOIN domain_name USING (gid) |
165 LEFT JOIN transport USING (tid) |
|
166 LEFT JOIN users USING (gid) |
207 LEFT JOIN users USING (gid) |
167 WHERE domain_name.is_primary |
208 WHERE domain_name.is_primary |
168 GROUP BY gid, domainname, transport, domaindir; |
209 GROUP BY gid; |
169 |
210 |
|
211 -- ########################################################################## -- |
170 |
212 |
171 CREATE LANGUAGE plpgsql; |
213 CREATE LANGUAGE plpgsql; |
172 |
214 |
|
215 -- ######################## TYPEs ########################################### -- |
|
216 |
|
217 -- --- |
|
218 -- Data type for function postfix_virtual_mailbox(varchar, varchar) |
|
219 -- --- |
|
220 CREATE TYPE address_maildir AS ( |
|
221 address varchar(320), |
|
222 maildir text |
|
223 ); |
|
224 -- --- |
|
225 -- Data type for function dovecotpassword(varchar, varchar) |
|
226 -- --- |
|
227 CREATE TYPE dovecotpassword AS ( |
|
228 userid varchar(320), |
|
229 password varchar(270), |
|
230 smtp boolean, |
|
231 pop3 boolean, |
|
232 imap boolean, |
|
233 managesieve boolean |
|
234 ); |
|
235 -- --- |
|
236 -- Data type for function dovecotquotauser(varchar, varchar) |
|
237 -- --- |
|
238 CREATE TYPE dovecotquotauser AS ( |
|
239 userid varchar(320), |
|
240 uid bigint, |
|
241 gid bigint, |
|
242 home text, |
|
243 mail text, |
|
244 quota_rule text |
|
245 ); |
|
246 -- --- |
|
247 -- Data type for function dovecotuser(varchar, varchar) |
|
248 -- --- |
|
249 CREATE TYPE dovecotuser AS ( |
|
250 userid varchar(320), |
|
251 uid bigint, |
|
252 gid bigint, |
|
253 home text, |
|
254 mail text |
|
255 ); |
|
256 -- --- |
|
257 -- Data type for functions: postfix_relocated_map(varchar, varchar) |
|
258 -- postfix_virtual_alias_map(varchar, varchar) |
|
259 -- --- |
|
260 CREATE TYPE recipient_destination AS ( |
|
261 recipient varchar(320), |
|
262 destination text |
|
263 ); |
|
264 -- --- |
|
265 -- Data type for function postfix_transport_map(varchar, varchar) |
|
266 -- --- |
|
267 CREATE TYPE recipient_transport AS ( |
|
268 recipient varchar(320), |
|
269 transport text |
|
270 ); |
|
271 -- --- |
|
272 -- Data type for function postfix_virtual_uid_map(varchar, varchar) |
|
273 -- --- |
|
274 CREATE TYPE recipient_uid AS ( |
|
275 recipient varchar(320), |
|
276 uid bigint |
|
277 ); |
|
278 -- --- |
|
279 -- Data type for function postfix_smtpd_sender_login_map(varchar, varchar) |
|
280 -- --- |
|
281 CREATE TYPE sender_login AS ( |
|
282 sender varchar(320), |
|
283 login text |
|
284 ); |
|
285 |
|
286 -- ######################## TRIGGERs ######################################## -- |
173 |
287 |
174 CREATE OR REPLACE FUNCTION domain_primary_trigger() RETURNS TRIGGER AS $$ |
288 CREATE OR REPLACE FUNCTION domain_primary_trigger() RETURNS TRIGGER AS $$ |
175 DECLARE |
289 DECLARE |
176 primary_count bigint; |
290 primary_count bigint; |
177 BEGIN |
291 BEGIN |
192 CREATE TRIGGER primary_count_ins BEFORE INSERT ON domain_name |
306 CREATE TRIGGER primary_count_ins BEFORE INSERT ON domain_name |
193 FOR EACH ROW EXECUTE PROCEDURE domain_primary_trigger(); |
307 FOR EACH ROW EXECUTE PROCEDURE domain_primary_trigger(); |
194 |
308 |
195 CREATE TRIGGER primary_count_upd AFTER UPDATE ON domain_name |
309 CREATE TRIGGER primary_count_upd AFTER UPDATE ON domain_name |
196 FOR EACH ROW EXECUTE PROCEDURE domain_primary_trigger(); |
310 FOR EACH ROW EXECUTE PROCEDURE domain_primary_trigger(); |
|
311 |
|
312 |
|
313 CREATE OR REPLACE FUNCTION merge_userquota_11() RETURNS TRIGGER AS $$ |
|
314 BEGIN |
|
315 UPDATE userquota_11 |
|
316 SET current = current + NEW.current |
|
317 WHERE uid = NEW.uid AND path = NEW.path; |
|
318 IF found THEN |
|
319 RETURN NULL; |
|
320 ELSE |
|
321 RETURN NEW; |
|
322 END IF; |
|
323 END; |
|
324 $$ LANGUAGE plpgsql; |
|
325 |
|
326 CREATE TRIGGER mergeuserquota_11 BEFORE INSERT ON userquota_11 |
|
327 FOR EACH ROW EXECUTE PROCEDURE merge_userquota_11(); |
|
328 |
|
329 -- ######################## FUNCTIONs ####################################### -- |
|
330 |
|
331 -- --- |
|
332 -- Parameters (from login name [localpart@the_domain]): |
|
333 -- varchar localpart |
|
334 -- varchar the_domain |
|
335 -- Returns: dovecotpassword records |
|
336 -- |
|
337 -- Required access privileges for your dovecot database user: |
|
338 -- GRANT SELECT ON users, domain_name, service_set TO dovecot; |
|
339 -- |
|
340 -- For more details see http://wiki.dovecot.org/AuthDatabase/SQL |
|
341 -- --- |
|
342 CREATE OR REPLACE FUNCTION dovecotpassword( |
|
343 IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotpassword |
|
344 AS $$ |
|
345 DECLARE |
|
346 record dovecotpassword; |
|
347 userid varchar(320) := localpart || '@' || the_domain; |
|
348 BEGIN |
|
349 FOR record IN |
|
350 SELECT userid, passwd, smtp, pop3, imap, managesieve |
|
351 FROM users, service_set, domain_data |
|
352 WHERE users.gid = (SELECT gid |
|
353 FROM domain_name |
|
354 WHERE domainname = the_domain) |
|
355 AND local_part = localpart |
|
356 AND service_set.ssid = users.ssid |
|
357 AND users.gid = domain_data.gid |
|
358 AND CASE WHEN |
|
359 users.ssid IS NOT NULL |
|
360 THEN |
|
361 service_set.ssid = users.ssid |
|
362 ELSE |
|
363 service_set.ssid = domain_data.ssid |
|
364 END |
|
365 LOOP |
|
366 RETURN NEXT record; |
|
367 END LOOP; |
|
368 RETURN; |
|
369 END; |
|
370 $$ LANGUAGE plpgsql STABLE |
|
371 RETURNS NULL ON NULL INPUT |
|
372 EXTERNAL SECURITY INVOKER; |
|
373 -- --- |
|
374 -- Nearly the same as function dovecotuser below. It returns additionally the |
|
375 -- field quota_rule. |
|
376 -- |
|
377 -- Required access privileges for your dovecot database user: |
|
378 -- GRANT SELECT |
|
379 -- ON users, domain_data, domain_name, maillocation, mailboxformat, |
|
380 -- quotalimit |
|
381 -- TO dovecot; |
|
382 -- --- |
|
383 CREATE OR REPLACE FUNCTION dovecotquotauser( |
|
384 IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotquotauser |
|
385 AS $$ |
|
386 DECLARE |
|
387 record dovecotquotauser; |
|
388 userid varchar(320) := localpart || '@' || the_domain; |
|
389 did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); |
|
390 BEGIN |
|
391 FOR record IN |
|
392 SELECT userid, uid, did, domaindir || '/' || uid AS home, |
|
393 format || ':~/' || directory AS mail, '*:bytes=' || |
|
394 bytes || ':messages=' || messages AS quota_rule |
|
395 FROM users, domain_data, mailboxformat, maillocation, quotalimit |
|
396 WHERE users.gid = did |
|
397 AND users.local_part = localpart |
|
398 AND maillocation.mid = users.mid |
|
399 AND mailboxformat.fid = maillocation.fid |
|
400 AND domain_data.gid = did |
|
401 AND CASE WHEN |
|
402 users.qid IS NOT NULL |
|
403 THEN |
|
404 quotalimit.qid = users.qid |
|
405 ELSE |
|
406 quotalimit.qid = domain_data.qid |
|
407 END |
|
408 LOOP |
|
409 RETURN NEXT record; |
|
410 END LOOP; |
|
411 RETURN; |
|
412 END; |
|
413 $$ LANGUAGE plpgsql STABLE |
|
414 RETURNS NULL ON NULL INPUT |
|
415 EXTERNAL SECURITY INVOKER; |
|
416 -- --- |
|
417 -- Parameters (from login name [localpart@the_domain]): |
|
418 -- varchar localpart |
|
419 -- varchar the_domain |
|
420 -- Returns: dovecotuser records |
|
421 -- |
|
422 -- Required access privileges for your dovecot database user: |
|
423 -- GRANT SELECT |
|
424 -- ON users, domain_data, domain_name, maillocation, mailboxformat |
|
425 -- TO dovecot; |
|
426 -- |
|
427 -- For more details see http://wiki.dovecot.org/UserDatabase |
|
428 -- --- |
|
429 CREATE OR REPLACE FUNCTION dovecotuser( |
|
430 IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotuser |
|
431 AS $$ |
|
432 DECLARE |
|
433 record dovecotuser; |
|
434 userid varchar(320) := localpart || '@' || the_domain; |
|
435 did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); |
|
436 BEGIN |
|
437 FOR record IN |
|
438 SELECT userid, uid, did, domaindir || '/' || uid AS home, |
|
439 format || ':~/' || directory AS mail |
|
440 FROM users, domain_data, mailboxformat, maillocation |
|
441 WHERE users.gid = did |
|
442 AND users.local_part = localpart |
|
443 AND maillocation.mid = users.mid |
|
444 AND mailboxformat.fid = maillocation.fid |
|
445 AND domain_data.gid = did |
|
446 LOOP |
|
447 RETURN NEXT record; |
|
448 END LOOP; |
|
449 RETURN; |
|
450 END; |
|
451 $$ LANGUAGE plpgsql STABLE |
|
452 RETURNS NULL ON NULL INPUT |
|
453 EXTERNAL SECURITY INVOKER; |
|
454 -- --- |
|
455 -- Parameters (from recipients address (MAIL TO) [localpart@the_domain]): |
|
456 -- varchar localpart |
|
457 -- varchar the_domain |
|
458 -- Returns: recipient_destination records |
|
459 -- |
|
460 -- Required access privileges for your postfix database user: |
|
461 -- GRANT SELECT ON domain_name, relocated TO postfix; |
|
462 -- |
|
463 -- For more details see postconf(5) section relocated_maps and relocated(5) |
|
464 -- --- |
|
465 CREATE OR REPLACE FUNCTION postfix_relocated_map( |
|
466 IN localpart varchar, IN the_domain varchar) |
|
467 RETURNS SETOF recipient_destination |
|
468 AS $$ |
|
469 DECLARE |
|
470 record recipient_destination; |
|
471 recipient varchar(320) := localpart || '@' || the_domain; |
|
472 did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); |
|
473 BEGIN |
|
474 FOR record IN |
|
475 SELECT recipient, destination |
|
476 FROM relocated |
|
477 WHERE gid = did |
|
478 AND address = localpart |
|
479 LOOP |
|
480 RETURN NEXT record; |
|
481 END LOOP; |
|
482 RETURN; |
|
483 END; |
|
484 $$ LANGUAGE plpgsql STABLE |
|
485 RETURNS NULL ON NULL INPUT |
|
486 EXTERNAL SECURITY INVOKER; |
|
487 -- --- |
|
488 -- Parameters (from _sender_ address (MAIL FROM) [localpart@the_domain]): |
|
489 -- varchar localpart |
|
490 -- varchar the_domain |
|
491 -- Returns: SASL _login_ names that own _sender_ addresses (MAIL FROM): |
|
492 -- set of sender_login records. |
|
493 -- |
|
494 -- Required access privileges for your postfix database user: |
|
495 -- GRANT SELECT ON domain_name, users, alias TO postfix; |
|
496 -- |
|
497 -- For more details see postconf(5) section smtpd_sender_login_maps |
|
498 -- --- |
|
499 CREATE OR REPLACE FUNCTION postfix_smtpd_sender_login_map( |
|
500 IN localpart varchar, IN the_domain varchar) RETURNS SETOF sender_login |
|
501 AS $$ |
|
502 DECLARE |
|
503 rec sender_login; |
|
504 did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); |
|
505 sender varchar(320) := localpart || '@' || the_domain; |
|
506 BEGIN |
|
507 -- Get all addresses for 'localpart' in the primary and aliased domains |
|
508 FOR rec IN |
|
509 SELECT sender, local_part || '@' || domainname |
|
510 FROM domain_name, users |
|
511 WHERE domain_name.gid = did |
|
512 AND users.gid = did |
|
513 AND users.local_part = localpart |
|
514 LOOP |
|
515 RETURN NEXT rec; |
|
516 END LOOP; |
|
517 IF NOT FOUND THEN |
|
518 -- Loop over the alias addresses for localpart@the_domain |
|
519 FOR rec IN |
|
520 SELECT DISTINCT sender, destination |
|
521 FROM alias |
|
522 WHERE alias.gid = did |
|
523 AND alias.address = localpart |
|
524 LOOP |
|
525 RETURN NEXT rec; |
|
526 END LOOP; |
|
527 END IF; |
|
528 RETURN; |
|
529 END; |
|
530 $$ LANGUAGE plpgsql STABLE |
|
531 RETURNS NULL ON NULL INPUT |
|
532 EXTERNAL SECURITY INVOKER; |
|
533 -- --- |
|
534 -- Parameters (from recipients address (MAIL TO) [localpart@the_domain]): |
|
535 -- varchar localpart |
|
536 -- varchar the_domain |
|
537 -- Returns: recipient_transport records |
|
538 -- |
|
539 -- Required access privileges for your postfix database user: |
|
540 -- GRANT SELECT ON users, transport, domain_name TO postfix; |
|
541 -- |
|
542 -- For more details see postconf(5) section transport_maps and transport(5) |
|
543 -- --- |
|
544 CREATE OR REPLACE FUNCTION postfix_transport_map( |
|
545 IN localpart varchar, IN the_domain varchar) |
|
546 RETURNS SETOF recipient_transport |
|
547 AS $$ |
|
548 DECLARE |
|
549 record recipient_transport; |
|
550 recipient varchar(320) := localpart || '@' || the_domain; |
|
551 did bigint := (SELECT gid FROM domain_name WHERE domainname = the_domain); |
|
552 transport_id bigint; |
|
553 BEGIN |
|
554 IF did IS NULL THEN |
|
555 RETURN; |
|
556 END IF; |
|
557 |
|
558 SELECT tid INTO transport_id |
|
559 FROM users |
|
560 WHERE gid = did AND local_part = localpart; |
|
561 |
|
562 IF transport_id IS NULL THEN |
|
563 SELECT tid INTO STRICT transport_id |
|
564 FROM domain_data |
|
565 WHERE gid = did; |
|
566 END IF; |
|
567 |
|
568 FOR record IN |
|
569 SELECT recipient, transport |
|
570 FROM transport |
|
571 WHERE tid = transport_id |
|
572 LOOP |
|
573 RETURN NEXT record; |
|
574 END LOOP; |
|
575 RETURN; |
|
576 END; |
|
577 $$ LANGUAGE plpgsql STABLE |
|
578 RETURNS NULL ON NULL INPUT |
|
579 EXTERNAL SECURITY INVOKER; |
|
580 -- --- |
|
581 -- Parameters (from recipients address (MAIL TO) [localpart@the_domain]): |
|
582 -- varchar localpart |
|
583 -- varchar the_domain |
|
584 -- Returns: recipient_destination records |
|
585 -- |
|
586 -- Required access privileges for your postfix database user: |
|
587 -- GRANT SELECT ON alias, domain_name TO postfix; |
|
588 -- |
|
589 -- For more details see postconf(5) section virtual_alias_maps and virtual(5) |
|
590 -- --- |
|
591 CREATE OR REPLACE FUNCTION _interpolate_destination( |
|
592 IN destination varchar, localpart varchar, IN the_domain varchar) |
|
593 RETURNS varchar |
|
594 AS $$ |
|
595 DECLARE |
|
596 result varchar(320); |
|
597 BEGIN |
|
598 IF position('%' in destination) = 0 THEN |
|
599 RETURN destination; |
|
600 END IF; |
|
601 result := replace(destination, '%n', localpart); |
|
602 result := replace(result, '%d', the_domain); |
|
603 result := replace(result, '%=', localpart || '=' || the_domain); |
|
604 RETURN result; |
|
605 END; |
|
606 $$ LANGUAGE plpgsql STABLE |
|
607 RETURNS NULL ON NULL INPUT |
|
608 EXTERNAL SECURITY INVOKER; |
|
609 |
|
610 CREATE OR REPLACE FUNCTION postfix_virtual_alias_map( |
|
611 IN localpart varchar, IN the_domain varchar) |
|
612 RETURNS SETOF recipient_destination |
|
613 AS $$ |
|
614 DECLARE |
|
615 recordc recipient_destination; |
|
616 record recipient_destination; |
|
617 catchall_cursor refcursor; |
|
618 recipient varchar(320) := localpart || '@' || the_domain; |
|
619 did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); |
|
620 BEGIN |
|
621 FOR record IN |
|
622 SELECT recipient, |
|
623 _interpolate_destination(destination, localpart, the_domain) |
|
624 FROM alias |
|
625 WHERE gid = did |
|
626 AND address = localpart |
|
627 LOOP |
|
628 RETURN NEXT record; |
|
629 END LOOP; |
|
630 |
|
631 IF NOT FOUND THEN |
|
632 -- There is no matching virtual_alias. If there are no catchall |
|
633 -- records for this domain, we can just return NULL since Postfix |
|
634 -- will then later consult mailboxes/relocated itself. But if |
|
635 -- there is a catchall destination, then it would take precedence |
|
636 -- over mailboxes/relocated, which is not what we want. Therefore, |
|
637 -- we must first find out if the query is for an existing mailbox |
|
638 -- or relocated entry and return the identity mapping if that is |
|
639 -- the case |
|
640 OPEN catchall_cursor FOR |
|
641 SELECT recipient, |
|
642 _interpolate_destination(destination, localpart, the_domain) |
|
643 FROM catchall |
|
644 WHERE gid = did; |
|
645 FETCH NEXT FROM catchall_cursor INTO recordc; |
|
646 |
|
647 IF recordc IS NOT NULL THEN |
|
648 -- Since there are catchall records for this domain |
|
649 -- check the mailbox and relocated records and return identity |
|
650 -- if a matching record exists. |
|
651 FOR record IN |
|
652 SELECT recipient, recipient as destination |
|
653 FROM users |
|
654 WHERE gid = did |
|
655 AND local_part = localpart |
|
656 UNION SELECT recipient, recipient as destination |
|
657 FROM relocated |
|
658 WHERE gid = did |
|
659 AND address = localpart |
|
660 LOOP |
|
661 RETURN NEXT record; |
|
662 END LOOP; |
|
663 |
|
664 IF NOT FOUND THEN |
|
665 -- There were no records found for mailboxes/relocated, |
|
666 -- so now we can actually iterate the cursor and populate |
|
667 -- the return set |
|
668 LOOP |
|
669 RETURN NEXT recordc; |
|
670 FETCH NEXT FROM catchall_cursor INTO recordc; |
|
671 EXIT WHEN recordc IS NULL; |
|
672 END LOOP; |
|
673 END IF; |
|
674 END IF; |
|
675 CLOSE catchall_cursor; |
|
676 END IF; |
|
677 RETURN; |
|
678 END; |
|
679 $$ LANGUAGE plpgsql STABLE |
|
680 RETURNS NULL ON NULL INPUT |
|
681 EXTERNAL SECURITY INVOKER; |
|
682 -- --- |
|
683 -- Parameters (from recipients address (MAIL TO) [localpart@the_domain]): |
|
684 -- varchar localpart |
|
685 -- varchar the_domain |
|
686 -- Returns: address_maildir records |
|
687 -- |
|
688 -- Required access privileges for your postfix database user: |
|
689 -- GRANT SELECT ON domain_data,domain_name,maillocation,users TO postfix; |
|
690 -- |
|
691 -- For more details see postconf(5) section virtual_mailbox_maps |
|
692 -- --- |
|
693 CREATE OR REPLACE FUNCTION postfix_virtual_mailbox_map( |
|
694 IN localpart varchar, IN the_domain varchar) RETURNS SETOF address_maildir |
|
695 AS $$ |
|
696 DECLARE |
|
697 rec address_maildir; |
|
698 did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); |
|
699 address varchar(320) := localpart || '@' || the_domain; |
|
700 BEGIN |
|
701 FOR rec IN |
|
702 SELECT address, domaindir||'/'||users.uid||'/'||directory||'/' |
|
703 FROM domain_data, users, maillocation |
|
704 WHERE domain_data.gid = did |
|
705 AND users.gid = did |
|
706 AND users.local_part = localpart |
|
707 AND maillocation.mid = users.mid |
|
708 LOOP |
|
709 RETURN NEXT rec; |
|
710 END LOOP; |
|
711 RETURN; |
|
712 END; |
|
713 $$ LANGUAGE plpgsql STABLE |
|
714 RETURNS NULL ON NULL INPUT |
|
715 EXTERNAL SECURITY INVOKER; |
|
716 -- --- |
|
717 -- Parameters (from recipients address (MAIL TO) [localpart@the_domain]): |
|
718 -- varchar localpart |
|
719 -- varchar the_domain |
|
720 -- Returns: recipient_uid records |
|
721 -- |
|
722 -- Required access privileges for your postfix database user: |
|
723 -- GRANT SELECT ON users, domain_name TO postfix; |
|
724 -- |
|
725 -- For more details see postconf(5) section virtual_uid_maps |
|
726 -- --- |
|
727 CREATE OR REPLACE FUNCTION postfix_virtual_uid_map( |
|
728 IN localpart varchar, IN the_domain varchar) RETURNS SETOF recipient_uid |
|
729 AS $$ |
|
730 DECLARE |
|
731 record recipient_uid; |
|
732 recipient varchar(320) := localpart || '@' || the_domain; |
|
733 BEGIN |
|
734 FOR record IN |
|
735 SELECT recipient, uid |
|
736 FROM users |
|
737 WHERE gid = (SELECT gid |
|
738 FROM domain_name |
|
739 WHERE domainname = the_domain) |
|
740 AND local_part = localpart |
|
741 LOOP |
|
742 RETURN NEXT record; |
|
743 END LOOP; |
|
744 RETURN; |
|
745 END; |
|
746 $$ LANGUAGE plpgsql STABLE |
|
747 RETURNS NULL ON NULL INPUT |
|
748 EXTERNAL SECURITY INVOKER; |