1 SET client_encoding = 'UTF8'; |
|
2 SET client_min_messages = warning; |
|
3 |
|
4 -- --- |
|
5 -- Create the new service_set table and insert all possible combinations |
|
6 -- -- |
|
7 CREATE SEQUENCE service_set_id; |
|
8 |
|
9 CREATE TABLE service_set ( |
|
10 ssid bigint NOT NULL DEFAULT nextval('service_set_id'), |
|
11 smtp boolean NOT NULL DEFAULT TRUE, |
|
12 pop3 boolean NOT NULL DEFAULT TRUE, |
|
13 imap boolean NOT NULL DEFAULT TRUE, |
|
14 sieve boolean NOT NULL DEFAULT TRUE, |
|
15 CONSTRAINT pkey_service_set PRIMARY KEY (ssid), |
|
16 CONSTRAINT ukey_service_set UNIQUE (smtp, pop3, imap, sieve) |
|
17 ); |
|
18 |
|
19 COPY service_set (smtp, pop3, imap, sieve) FROM stdin; |
|
20 TRUE TRUE TRUE TRUE |
|
21 FALSE TRUE TRUE TRUE |
|
22 TRUE FALSE TRUE TRUE |
|
23 FALSE FALSE TRUE TRUE |
|
24 TRUE TRUE FALSE TRUE |
|
25 FALSE TRUE FALSE TRUE |
|
26 TRUE FALSE FALSE TRUE |
|
27 FALSE FALSE FALSE TRUE |
|
28 TRUE TRUE TRUE FALSE |
|
29 FALSE TRUE TRUE FALSE |
|
30 TRUE FALSE TRUE FALSE |
|
31 FALSE FALSE TRUE FALSE |
|
32 TRUE TRUE FALSE FALSE |
|
33 FALSE TRUE FALSE FALSE |
|
34 TRUE FALSE FALSE FALSE |
|
35 FALSE FALSE FALSE FALSE |
|
36 \. |
|
37 |
|
38 -- --- |
|
39 -- Drop the obsolete VIEWs, we've functions now. |
|
40 -- --- |
|
41 DROP VIEW dovecot_user; |
|
42 DROP VIEW dovecot_password; |
|
43 DROP VIEW postfix_alias; |
|
44 DROP VIEW postfix_maildir; |
|
45 DROP VIEW postfix_relocated; |
|
46 DROP VIEW postfix_transport; |
|
47 DROP VIEW postfix_uid; |
|
48 -- the vmm_domain_info view will be restored later |
|
49 DROP VIEW vmm_domain_info; |
|
50 |
|
51 CREATE SEQUENCE mailboxformat_id; |
|
52 CREATE SEQUENCE quotalimit_id; |
|
53 |
|
54 CREATE TABLE mailboxformat ( |
|
55 fid bigint NOT NULL DEFAULT nextval('mailboxformat_id'), |
|
56 format varchar(20) NOT NULL, |
|
57 CONSTRAINT pkey_mailboxformat PRIMARY KEY (fid), |
|
58 CONSTRAINT ukey_mailboxformat UNIQUE (format) |
|
59 ); |
|
60 -- Insert supported mailbox formats |
|
61 INSERT INTO mailboxformat(format) VALUES ('maildir'); |
|
62 INSERT INTO mailboxformat(format) VALUES ('mdbox'); |
|
63 INSERT INTO mailboxformat(format) VALUES ('sdbox'); |
|
64 |
|
65 -- Adjust maillocation table |
|
66 ALTER TABLE maillocation DROP CONSTRAINT ukey_maillocation; |
|
67 ALTER TABLE maillocation RENAME COLUMN maillocation TO directory; |
|
68 ALTER TABLE maillocation |
|
69 ADD COLUMN fid bigint NOT NULL DEFAULT 1, |
|
70 ADD COLUMN extra varchar(1024); |
|
71 ALTER TABLE maillocation ADD CONSTRAINT fkey_maillocation_fid_mailboxformat |
|
72 FOREIGN KEY (fid) REFERENCES mailboxformat (fid); |
|
73 |
|
74 ALTER TABLE users ALTER COLUMN passwd TYPE varchar(270); |
|
75 |
|
76 -- --- |
|
77 -- Add quota stuff |
|
78 -- --- |
|
79 CREATE TABLE quotalimit ( |
|
80 qid bigint NOT NULL DEFAULT nextval('quotalimit_id'), |
|
81 bytes bigint NOT NULL, |
|
82 messages integer NOT NULL DEFAULT 0, |
|
83 CONSTRAINT pkey_quotalimit PRIMARY KEY (qid), |
|
84 CONSTRAINT ukey_quotalimit UNIQUE (bytes, messages) |
|
85 ); |
|
86 -- Insert default (non) quota limit |
|
87 INSERT INTO quotalimit(bytes, messages) VALUES (0, 0); |
|
88 |
|
89 -- Adjust tables (quota) |
|
90 ALTER TABLE domain_data ADD COLUMN qid bigint NOT NULL DEFAULT 1; |
|
91 ALTER TABLE domain_data ADD CONSTRAINT fkey_domain_data_qid_quotalimit |
|
92 FOREIGN KEY (qid) REFERENCES quotalimit (qid); |
|
93 |
|
94 ALTER TABLE users ADD COLUMN qid bigint NULL DEFAULT NULL; |
|
95 ALTER TABLE users ADD CONSTRAINT fkey_users_qid_quotalimit |
|
96 FOREIGN KEY (qid) REFERENCES quotalimit (qid); |
|
97 |
|
98 CREATE TABLE userquota ( |
|
99 uid bigint NOT NULL, |
|
100 bytes bigint NOT NULL DEFAULT 0, |
|
101 messages integer NOT NULL DEFAULT 0, |
|
102 CONSTRAINT pkey_userquota PRIMARY KEY (uid), |
|
103 CONSTRAINT fkey_userquota_uid_users FOREIGN KEY (uid) |
|
104 REFERENCES users (uid) ON DELETE CASCADE |
|
105 ); |
|
106 |
|
107 CREATE OR REPLACE FUNCTION merge_userquota() RETURNS TRIGGER AS $$ |
|
108 BEGIN |
|
109 IF NEW.messages < 0 OR NEW.messages IS NULL THEN |
|
110 IF NEW.messages IS NULL THEN |
|
111 NEW.messages = 0; |
|
112 ELSE |
|
113 NEW.messages = -NEW.messages; |
|
114 END IF; |
|
115 RETURN NEW; |
|
116 END IF; |
|
117 LOOP |
|
118 UPDATE userquota |
|
119 SET bytes = bytes + NEW.bytes, messages = messages + NEW.messages |
|
120 WHERE uid = NEW.uid; |
|
121 IF found THEN |
|
122 RETURN NULL; |
|
123 END IF; |
|
124 BEGIN |
|
125 IF NEW.messages = 0 THEN |
|
126 INSERT INTO userquota VALUES (NEW.uid, NEW.bytes, NULL); |
|
127 ELSE |
|
128 INSERT INTO userquota VALUES (NEW.uid, NEW.bytes, -NEW.messages); |
|
129 END IF; |
|
130 RETURN NULL; |
|
131 EXCEPTION |
|
132 WHEN unique_violation THEN |
|
133 -- do nothing, and loop to try the UPDATE again |
|
134 WHEN foreign_key_violation THEN |
|
135 -- break the loop: a non matching uid means no such user |
|
136 RETURN NULL; |
|
137 END; |
|
138 END LOOP; |
|
139 END; |
|
140 $$ LANGUAGE plpgsql; |
|
141 |
|
142 CREATE TRIGGER mergeuserquota BEFORE INSERT ON userquota |
|
143 FOR EACH ROW EXECUTE PROCEDURE merge_userquota(); |
|
144 |
|
145 -- Adjust tables (services) |
|
146 ALTER TABLE domain_data ADD COLUMN ssid bigint NOT NULL DEFAULT 1; |
|
147 ALTER TABLE domain_data ADD CONSTRAINT fkey_domain_data_ssid_service_set |
|
148 FOREIGN KEY (ssid) REFERENCES service_set (ssid); |
|
149 |
|
150 ALTER TABLE users ADD COLUMN ssid bigint NULL DEFAULT NULL; |
|
151 -- save current service sets |
|
152 UPDATE users u |
|
153 SET ssid = ss.ssid |
|
154 FROM service_set ss |
|
155 WHERE ss.smtp = u.smtp |
|
156 AND ss.pop3 = u.pop3 |
|
157 AND ss.imap = u.imap |
|
158 AND ss.sieve = u.sieve; |
|
159 |
|
160 ALTER TABLE users DROP COLUMN smtp; |
|
161 ALTER TABLE users DROP COLUMN pop3; |
|
162 ALTER TABLE users DROP COLUMN imap; |
|
163 ALTER TABLE users DROP COLUMN sieve; |
|
164 ALTER TABLE users ADD CONSTRAINT fkey_users_ssid_service_set |
|
165 FOREIGN KEY (ssid) REFERENCES service_set (ssid); |
|
166 |
|
167 -- --- |
|
168 -- Catchall |
|
169 -- --- |
|
170 |
|
171 CREATE TABLE catchall ( |
|
172 gid bigint NOT NULL, |
|
173 destination varchar(320) NOT NULL, |
|
174 CONSTRAINT pkey_catchall PRIMARY KEY (gid, destination), |
|
175 CONSTRAINT fkey_catchall_gid_domain_data FOREIGN KEY (gid) |
|
176 REFERENCES domain_data (gid) |
|
177 ); |
|
178 |
|
179 -- --- |
|
180 -- Quota/Service/Transport inheritance |
|
181 -- --- |
|
182 ALTER TABLE users ALTER COLUMN tid DROP NOT NULL; |
|
183 ALTER TABLE users ALTER COLUMN tid SET DEFAULT NULL; |
|
184 -- The qid and ssid columns have already been defined accordingly above. |
|
185 -- The rest of the logic will take place in the functions. |
|
186 |
|
187 -- While qid and ssid are new and it's perfectly okay for existing users to |
|
188 -- get NULL values (i.e. inherit from the domain's default), tid existed in |
|
189 -- vmm 0.5.x. A sensible way forward seems thus to NULL all user records' tid |
|
190 -- fields where the tid duplicates the value stored in the domain's record. |
|
191 UPDATE users |
|
192 SET tid = NULL |
|
193 WHERE tid = (SELECT tid |
|
194 FROM domain_data |
|
195 WHERE domain_data.gid = users.gid); |
|
196 |
|
197 -- --- |
|
198 -- Account/domain notes |
|
199 -- --- |
|
200 |
|
201 ALTER TABLE users ADD COLUMN note text NULL DEFAULT NULL; |
|
202 ALTER TABLE domain_data ADD COLUMN note text NULL DEFAULT NULL; |
|
203 |
|
204 -- --- |
|
205 -- Restore view |
|
206 -- --- |
|
207 CREATE VIEW vmm_domain_info AS |
|
208 SELECT gid, count(uid) AS accounts, |
|
209 (SELECT count(DISTINCT address) |
|
210 FROM alias |
|
211 WHERE alias.gid = domain_data.gid) AS aliases, |
|
212 (SELECT count(gid) |
|
213 FROM relocated |
|
214 WHERE relocated.gid = domain_data.gid) AS relocated, |
|
215 (SELECT count(gid) |
|
216 FROM domain_name |
|
217 WHERE domain_name.gid = domain_data.gid |
|
218 AND NOT domain_name.is_primary) AS aliasdomains, |
|
219 (SELECT count(gid) |
|
220 FROM catchall |
|
221 WHERE catchall.gid = domain_data.gid) AS catchall |
|
222 FROM domain_data |
|
223 LEFT JOIN domain_name USING (gid) |
|
224 LEFT JOIN users USING (gid) |
|
225 WHERE domain_name.is_primary |
|
226 GROUP BY gid; |
|
227 |
|
228 -- --- |
|
229 -- Drop all known v0.5 types (the dirty way) |
|
230 -- --- |
|
231 DROP TYPE address_maildir CASCADE; |
|
232 DROP TYPE dovecotpassword CASCADE; |
|
233 DROP TYPE dovecotuser CASCADE; |
|
234 DROP TYPE recipient_destination CASCADE; |
|
235 DROP TYPE recipient_transport CASCADE; |
|
236 DROP TYPE recipient_uid CASCADE; |
|
237 DROP TYPE sender_login CASCADE; |
|
238 |
|
239 -- ######################## TYPEs ########################################### -- |
|
240 |
|
241 -- --- |
|
242 -- Data type for function postfix_virtual_mailbox(varchar, varchar) |
|
243 -- --- |
|
244 CREATE TYPE address_maildir AS ( |
|
245 address varchar(320), |
|
246 maildir text |
|
247 ); |
|
248 -- --- |
|
249 -- Data type for function dovecotpassword(varchar, varchar) |
|
250 -- --- |
|
251 CREATE TYPE dovecotpassword AS ( |
|
252 userid varchar(320), |
|
253 password varchar(270), |
|
254 smtp boolean, |
|
255 pop3 boolean, |
|
256 imap boolean, |
|
257 sieve boolean |
|
258 ); |
|
259 -- --- |
|
260 -- Data type for function dovecotquotauser(varchar, varchar) |
|
261 -- --- |
|
262 CREATE TYPE dovecotquotauser AS ( |
|
263 userid varchar(320), |
|
264 uid bigint, |
|
265 gid bigint, |
|
266 home text, |
|
267 mail text, |
|
268 quota_rule text |
|
269 ); |
|
270 -- --- |
|
271 -- Data type for function dovecotuser(varchar, varchar) |
|
272 -- --- |
|
273 CREATE TYPE dovecotuser AS ( |
|
274 userid varchar(320), |
|
275 uid bigint, |
|
276 gid bigint, |
|
277 home text, |
|
278 mail text |
|
279 ); |
|
280 -- --- |
|
281 -- Data type for functions: postfix_relocated_map(varchar, varchar) |
|
282 -- postfix_virtual_alias_map(varchar, varchar) |
|
283 -- --- |
|
284 CREATE TYPE recipient_destination AS ( |
|
285 recipient varchar(320), |
|
286 destination text |
|
287 ); |
|
288 -- --- |
|
289 -- Data type for function postfix_transport_map(varchar, varchar) |
|
290 -- --- |
|
291 CREATE TYPE recipient_transport AS ( |
|
292 recipient varchar(320), |
|
293 transport text |
|
294 ); |
|
295 -- --- |
|
296 -- Data type for function postfix_virtual_uid_map(varchar, varchar) |
|
297 -- --- |
|
298 CREATE TYPE recipient_uid AS ( |
|
299 recipient varchar(320), |
|
300 uid bigint |
|
301 ); |
|
302 -- --- |
|
303 -- Data type for function postfix_smtpd_sender_login_map(varchar, varchar) |
|
304 -- --- |
|
305 CREATE TYPE sender_login AS ( |
|
306 sender varchar(320), |
|
307 login text |
|
308 ); |
|
309 |
|
310 -- ######################## FUNCTIONs ####################################### -- |
|
311 |
|
312 -- --- |
|
313 -- Parameters (from login name [localpart@the_domain]): |
|
314 -- varchar localpart |
|
315 -- varchar the_domain |
|
316 -- Returns: dovecotpassword records |
|
317 -- --- |
|
318 CREATE OR REPLACE FUNCTION dovecotpassword( |
|
319 IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotpassword |
|
320 AS $$ |
|
321 DECLARE |
|
322 record dovecotpassword; |
|
323 userid varchar(320) := localpart || '@' || the_domain; |
|
324 BEGIN |
|
325 FOR record IN |
|
326 SELECT userid, passwd, smtp, pop3, imap, sieve |
|
327 FROM users, service_set, domain_data |
|
328 WHERE users.gid = (SELECT gid |
|
329 FROM domain_name |
|
330 WHERE domainname = the_domain) |
|
331 AND local_part = localpart |
|
332 AND users.gid = domain_data.gid |
|
333 AND CASE WHEN |
|
334 users.ssid IS NOT NULL |
|
335 THEN |
|
336 service_set.ssid = users.ssid |
|
337 ELSE |
|
338 service_set.ssid = domain_data.ssid |
|
339 END |
|
340 LOOP |
|
341 RETURN NEXT record; |
|
342 END LOOP; |
|
343 RETURN; |
|
344 END; |
|
345 $$ LANGUAGE plpgsql STABLE |
|
346 RETURNS NULL ON NULL INPUT |
|
347 EXTERNAL SECURITY INVOKER; |
|
348 -- --- |
|
349 -- Nearly the same as function dovecotuser below. It returns additionally the |
|
350 -- field quota_rule. |
|
351 -- --- |
|
352 CREATE OR REPLACE FUNCTION dovecotquotauser( |
|
353 IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotquotauser |
|
354 AS $$ |
|
355 DECLARE |
|
356 record dovecotquotauser; |
|
357 userid varchar(320) := localpart || '@' || the_domain; |
|
358 did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); |
|
359 BEGIN |
|
360 FOR record IN |
|
361 SELECT userid, uid, did, domaindir || '/' || uid AS home, |
|
362 format || ':~/' || directory AS mail, '*:bytes=' || |
|
363 bytes || ':messages=' || messages AS quota_rule |
|
364 FROM users, domain_data, mailboxformat, maillocation, quotalimit |
|
365 WHERE users.gid = did |
|
366 AND users.local_part = localpart |
|
367 AND maillocation.mid = users.mid |
|
368 AND mailboxformat.fid = maillocation.fid |
|
369 AND domain_data.gid = did |
|
370 AND CASE WHEN |
|
371 users.qid IS NOT NULL |
|
372 THEN |
|
373 quotalimit.qid = users.qid |
|
374 ELSE |
|
375 quotalimit.qid = domain_data.qid |
|
376 END |
|
377 LOOP |
|
378 RETURN NEXT record; |
|
379 END LOOP; |
|
380 RETURN; |
|
381 END; |
|
382 $$ LANGUAGE plpgsql STABLE |
|
383 RETURNS NULL ON NULL INPUT |
|
384 EXTERNAL SECURITY INVOKER; |
|
385 -- --- |
|
386 -- Parameters (from login name [localpart@the_domain]): |
|
387 -- varchar localpart |
|
388 -- varchar the_domain |
|
389 -- Returns: dovecotuser records |
|
390 -- --- |
|
391 CREATE OR REPLACE FUNCTION dovecotuser( |
|
392 IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotuser |
|
393 AS $$ |
|
394 DECLARE |
|
395 record dovecotuser; |
|
396 userid varchar(320) := localpart || '@' || the_domain; |
|
397 did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); |
|
398 BEGIN |
|
399 FOR record IN |
|
400 SELECT userid, uid, did, domaindir || '/' || uid AS home, |
|
401 format || ':~/' || directory AS mail |
|
402 FROM users, domain_data, mailboxformat, maillocation |
|
403 WHERE users.gid = did |
|
404 AND users.local_part = localpart |
|
405 AND maillocation.mid = users.mid |
|
406 AND mailboxformat.fid = maillocation.fid |
|
407 AND domain_data.gid = did |
|
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 recipients address (MAIL TO) [localpart@the_domain]): |
|
418 -- varchar localpart |
|
419 -- varchar the_domain |
|
420 -- Returns: recipient_destination records |
|
421 -- --- |
|
422 CREATE OR REPLACE FUNCTION postfix_relocated_map( |
|
423 IN localpart varchar, IN the_domain varchar) |
|
424 RETURNS SETOF recipient_destination |
|
425 AS $$ |
|
426 DECLARE |
|
427 record recipient_destination; |
|
428 recipient varchar(320) := localpart || '@' || the_domain; |
|
429 did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); |
|
430 BEGIN |
|
431 FOR record IN |
|
432 SELECT recipient, destination |
|
433 FROM relocated |
|
434 WHERE gid = did |
|
435 AND address = localpart |
|
436 LOOP |
|
437 RETURN NEXT record; |
|
438 END LOOP; |
|
439 RETURN; |
|
440 END; |
|
441 $$ LANGUAGE plpgsql STABLE |
|
442 RETURNS NULL ON NULL INPUT |
|
443 EXTERNAL SECURITY INVOKER; |
|
444 -- --- |
|
445 -- Parameters (from _sender_ address (MAIL FROM) [localpart@the_domain]): |
|
446 -- varchar localpart |
|
447 -- varchar the_domain |
|
448 -- Returns: SASL _login_ names that own _sender_ addresses (MAIL FROM): |
|
449 -- set of sender_login records. |
|
450 -- --- |
|
451 CREATE OR REPLACE FUNCTION postfix_smtpd_sender_login_map( |
|
452 IN localpart varchar, IN the_domain varchar) RETURNS SETOF sender_login |
|
453 AS $$ |
|
454 DECLARE |
|
455 rec sender_login; |
|
456 did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); |
|
457 sender varchar(320) := localpart || '@' || the_domain; |
|
458 BEGIN |
|
459 -- Get all addresses for 'localpart' in the primary and aliased domains |
|
460 FOR rec IN |
|
461 SELECT sender, local_part || '@' || domainname |
|
462 FROM domain_name, users |
|
463 WHERE domain_name.gid = did |
|
464 AND users.gid = did |
|
465 AND users.local_part = localpart |
|
466 LOOP |
|
467 RETURN NEXT rec; |
|
468 END LOOP; |
|
469 IF NOT FOUND THEN |
|
470 -- Loop over the alias addresses for localpart@the_domain |
|
471 FOR rec IN |
|
472 SELECT DISTINCT sender, destination |
|
473 FROM alias |
|
474 WHERE alias.gid = did |
|
475 AND alias.address = localpart |
|
476 LOOP |
|
477 RETURN NEXT rec; |
|
478 END LOOP; |
|
479 END IF; |
|
480 RETURN; |
|
481 END; |
|
482 $$ LANGUAGE plpgsql STABLE |
|
483 RETURNS NULL ON NULL INPUT |
|
484 EXTERNAL SECURITY INVOKER; |
|
485 -- --- |
|
486 -- Parameters (from recipients address (MAIL TO) [localpart@the_domain]): |
|
487 -- varchar localpart |
|
488 -- varchar the_domain |
|
489 -- Returns: recipient_transport records |
|
490 -- --- |
|
491 CREATE OR REPLACE FUNCTION postfix_transport_map( |
|
492 IN localpart varchar, IN the_domain varchar) |
|
493 RETURNS SETOF recipient_transport |
|
494 AS $$ |
|
495 DECLARE |
|
496 record recipient_transport; |
|
497 recipient varchar(320) := localpart || '@' || the_domain; |
|
498 did bigint := (SELECT gid FROM domain_name WHERE domainname = the_domain); |
|
499 transport_id bigint; |
|
500 BEGIN |
|
501 IF did IS NULL THEN |
|
502 RETURN; |
|
503 END IF; |
|
504 |
|
505 SELECT tid INTO transport_id |
|
506 FROM users |
|
507 WHERE gid = did AND local_part = localpart; |
|
508 |
|
509 IF transport_id IS NULL THEN |
|
510 SELECT tid INTO STRICT transport_id |
|
511 FROM domain_data |
|
512 WHERE gid = did; |
|
513 END IF; |
|
514 |
|
515 FOR record IN |
|
516 SELECT recipient, transport |
|
517 FROM transport |
|
518 WHERE tid = transport_id |
|
519 LOOP |
|
520 RETURN NEXT record; |
|
521 END LOOP; |
|
522 RETURN; |
|
523 END; |
|
524 $$ LANGUAGE plpgsql STABLE |
|
525 RETURNS NULL ON NULL INPUT |
|
526 EXTERNAL SECURITY INVOKER; |
|
527 -- --- |
|
528 -- Parameters (from recipients address (MAIL TO) [localpart@the_domain]): |
|
529 -- varchar localpart |
|
530 -- varchar the_domain |
|
531 -- Returns: recipient_destination records |
|
532 -- --- |
|
533 CREATE OR REPLACE FUNCTION _interpolate_destination( |
|
534 IN destination varchar, localpart varchar, IN the_domain varchar) |
|
535 RETURNS varchar |
|
536 AS $$ |
|
537 DECLARE |
|
538 result varchar(320); |
|
539 BEGIN |
|
540 IF position('%' in destination) = 0 THEN |
|
541 RETURN destination; |
|
542 END IF; |
|
543 result := replace(destination, '%n', localpart); |
|
544 result := replace(result, '%d', the_domain); |
|
545 result := replace(result, '%=', localpart || '=' || the_domain); |
|
546 RETURN result; |
|
547 END; |
|
548 $$ LANGUAGE plpgsql STABLE |
|
549 RETURNS NULL ON NULL INPUT |
|
550 EXTERNAL SECURITY INVOKER; |
|
551 |
|
552 CREATE OR REPLACE FUNCTION postfix_virtual_alias_map( |
|
553 IN localpart varchar, IN the_domain varchar) |
|
554 RETURNS SETOF recipient_destination |
|
555 AS $$ |
|
556 DECLARE |
|
557 recordc recipient_destination; |
|
558 record recipient_destination; |
|
559 catchall_cursor refcursor; |
|
560 recipient varchar(320) := localpart || '@' || the_domain; |
|
561 did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); |
|
562 BEGIN |
|
563 FOR record IN |
|
564 SELECT recipient, |
|
565 _interpolate_destination(destination, localpart, the_domain) |
|
566 FROM alias |
|
567 WHERE gid = did |
|
568 AND address = localpart |
|
569 LOOP |
|
570 RETURN NEXT record; |
|
571 END LOOP; |
|
572 |
|
573 IF NOT FOUND THEN |
|
574 -- There is no matching virtual_alias. If there are no catchall |
|
575 -- records for this domain, we can just return NULL since Postfix |
|
576 -- will then later consult mailboxes/relocated itself. But if |
|
577 -- there is a catchall destination, then it would take precedence |
|
578 -- over mailboxes/relocated, which is not what we want. Therefore, |
|
579 -- we must first find out if the query is for an existing mailbox |
|
580 -- or relocated entry and return the identity mapping if that is |
|
581 -- the case |
|
582 OPEN catchall_cursor FOR |
|
583 SELECT recipient, |
|
584 _interpolate_destination(destination, localpart, the_domain) |
|
585 FROM catchall |
|
586 WHERE gid = did; |
|
587 FETCH NEXT FROM catchall_cursor INTO recordc; |
|
588 |
|
589 IF recordc IS NOT NULL THEN |
|
590 -- Since there are catchall records for this domain |
|
591 -- check the mailbox and relocated records and return identity |
|
592 -- if a matching record exists. |
|
593 FOR record IN |
|
594 SELECT recipient, recipient as destination |
|
595 FROM users |
|
596 WHERE gid = did |
|
597 AND local_part = localpart |
|
598 UNION SELECT recipient, recipient as destination |
|
599 FROM relocated |
|
600 WHERE gid = did |
|
601 AND address = localpart |
|
602 LOOP |
|
603 RETURN NEXT record; |
|
604 END LOOP; |
|
605 |
|
606 IF NOT FOUND THEN |
|
607 -- There were no records found for mailboxes/relocated, |
|
608 -- so now we can actually iterate the cursor and populate |
|
609 -- the return set |
|
610 LOOP |
|
611 RETURN NEXT recordc; |
|
612 FETCH NEXT FROM catchall_cursor INTO recordc; |
|
613 EXIT WHEN recordc IS NULL; |
|
614 END LOOP; |
|
615 END IF; |
|
616 END IF; |
|
617 CLOSE catchall_cursor; |
|
618 END IF; |
|
619 RETURN; |
|
620 END; |
|
621 $$ LANGUAGE plpgsql STABLE |
|
622 RETURNS NULL ON NULL INPUT |
|
623 EXTERNAL SECURITY INVOKER; |
|
624 -- --- |
|
625 -- Parameters (from recipients address (MAIL TO) [localpart@the_domain]): |
|
626 -- varchar localpart |
|
627 -- varchar the_domain |
|
628 -- Returns: address_maildir records |
|
629 -- --- |
|
630 CREATE OR REPLACE FUNCTION postfix_virtual_mailbox_map( |
|
631 IN localpart varchar, IN the_domain varchar) RETURNS SETOF address_maildir |
|
632 AS $$ |
|
633 DECLARE |
|
634 rec address_maildir; |
|
635 did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); |
|
636 address varchar(320) := localpart || '@' || the_domain; |
|
637 BEGIN |
|
638 FOR rec IN |
|
639 SELECT address, domaindir||'/'||users.uid||'/'||directory||'/' |
|
640 FROM domain_data, users, maillocation |
|
641 WHERE domain_data.gid = did |
|
642 AND users.gid = did |
|
643 AND users.local_part = localpart |
|
644 AND maillocation.mid = users.mid |
|
645 LOOP |
|
646 RETURN NEXT rec; |
|
647 END LOOP; |
|
648 RETURN; |
|
649 END; |
|
650 $$ LANGUAGE plpgsql STABLE |
|
651 RETURNS NULL ON NULL INPUT |
|
652 EXTERNAL SECURITY INVOKER; |
|
653 -- --- |
|
654 -- Parameters (from recipients address (MAIL TO) [localpart@the_domain]): |
|
655 -- varchar localpart |
|
656 -- varchar the_domain |
|
657 -- Returns: recipient_uid records |
|
658 -- --- |
|
659 CREATE OR REPLACE FUNCTION postfix_virtual_uid_map( |
|
660 IN localpart varchar, IN the_domain varchar) RETURNS SETOF recipient_uid |
|
661 AS $$ |
|
662 DECLARE |
|
663 record recipient_uid; |
|
664 recipient varchar(320) := localpart || '@' || the_domain; |
|
665 BEGIN |
|
666 FOR record IN |
|
667 SELECT recipient, uid |
|
668 FROM users |
|
669 WHERE gid = (SELECT gid |
|
670 FROM domain_name |
|
671 WHERE domainname = the_domain) |
|
672 AND local_part = localpart |
|
673 LOOP |
|
674 RETURN NEXT record; |
|
675 END LOOP; |
|
676 RETURN; |
|
677 END; |
|
678 $$ LANGUAGE plpgsql STABLE |
|
679 RETURNS NULL ON NULL INPUT |
|
680 EXTERNAL SECURITY INVOKER; |
|