160 FROM domain_data |
160 FROM domain_data |
161 LEFT JOIN domain_name USING (gid) |
161 LEFT JOIN domain_name USING (gid) |
162 LEFT JOIN users USING (gid) |
162 LEFT JOIN users USING (gid) |
163 WHERE domain_name.is_primary |
163 WHERE domain_name.is_primary |
164 GROUP BY gid; |
164 GROUP BY gid; |
|
165 |
|
166 -- --- |
|
167 -- Drop all known v0.5 types (the dirty way) |
|
168 -- --- |
|
169 DROP TYPE address_maildir CASCADE; |
|
170 DROP TYPE dovecotpassword CASCADE; |
|
171 DROP TYPE dovecotuser CASCADE; |
|
172 DROP TYPE recipient_destination CASCADE; |
|
173 DROP TYPE recipient_transport CASCADE; |
|
174 DROP TYPE recipient_uid CASCADE; |
|
175 DROP TYPE sender_login CASCADE; |
|
176 |
|
177 -- ######################## TYPEs ########################################### -- |
|
178 |
|
179 -- --- |
|
180 -- Data type for function postfix_virtual_mailbox(varchar, varchar) |
|
181 -- --- |
|
182 CREATE TYPE address_maildir AS ( |
|
183 address varchar(320), |
|
184 maildir text |
|
185 ); |
|
186 -- --- |
|
187 -- Data type for function dovecotpassword(varchar, varchar) |
|
188 -- --- |
|
189 CREATE TYPE dovecotpassword AS ( |
|
190 userid varchar(320), |
|
191 password varchar(270), |
|
192 smtp boolean, |
|
193 pop3 boolean, |
|
194 imap boolean, |
|
195 managesieve boolean |
|
196 ); |
|
197 -- --- |
|
198 -- Data type for function dovecotquotauser(varchar, varchar) |
|
199 -- --- |
|
200 CREATE TYPE dovecotquotauser AS ( |
|
201 userid varchar(320), |
|
202 uid bigint, |
|
203 gid bigint, |
|
204 home text, |
|
205 mail text, |
|
206 quota_rule text |
|
207 ); |
|
208 -- --- |
|
209 -- Data type for function dovecotuser(varchar, varchar) |
|
210 -- --- |
|
211 CREATE TYPE dovecotuser AS ( |
|
212 userid varchar(320), |
|
213 uid bigint, |
|
214 gid bigint, |
|
215 home text, |
|
216 mail text |
|
217 ); |
|
218 -- --- |
|
219 -- Data type for functions: postfix_relocated_map(varchar, varchar) |
|
220 -- postfix_virtual_alias_map(varchar, varchar) |
|
221 -- --- |
|
222 CREATE TYPE recipient_destination AS ( |
|
223 recipient varchar(320), |
|
224 destination text |
|
225 ); |
|
226 -- --- |
|
227 -- Data type for function postfix_transport_map(varchar, varchar) |
|
228 -- --- |
|
229 CREATE TYPE recipient_transport AS ( |
|
230 recipient varchar(320), |
|
231 transport text |
|
232 ); |
|
233 -- --- |
|
234 -- Data type for function postfix_virtual_uid_map(varchar, varchar) |
|
235 -- --- |
|
236 CREATE TYPE recipient_uid AS ( |
|
237 recipient varchar(320), |
|
238 uid bigint |
|
239 ); |
|
240 -- --- |
|
241 -- Data type for function postfix_smtpd_sender_login_map(varchar, varchar) |
|
242 -- --- |
|
243 CREATE TYPE sender_login AS ( |
|
244 sender varchar(320), |
|
245 login text |
|
246 ); |
|
247 |
|
248 -- ######################## FUNCTIONs ####################################### -- |
|
249 |
|
250 -- --- |
|
251 -- Parameters (from login name [localpart@the_domain]): |
|
252 -- varchar localpart |
|
253 -- varchar the_domain |
|
254 -- Returns: dovecotpassword records |
|
255 -- --- |
|
256 CREATE OR REPLACE FUNCTION dovecotpassword( |
|
257 IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotpassword |
|
258 AS $$ |
|
259 DECLARE |
|
260 record dovecotpassword; |
|
261 userid varchar(320) := localpart || '@' || the_domain; |
|
262 BEGIN |
|
263 FOR record IN |
|
264 SELECT userid, passwd, smtp, pop3, imap, managesieve |
|
265 FROM users, service_set |
|
266 WHERE gid = (SELECT gid |
|
267 FROM domain_name |
|
268 WHERE domainname = the_domain) |
|
269 AND local_part = localpart |
|
270 AND service_set.ssid = users.ssid |
|
271 LOOP |
|
272 RETURN NEXT record; |
|
273 END LOOP; |
|
274 RETURN; |
|
275 END; |
|
276 $$ LANGUAGE plpgsql STABLE |
|
277 RETURNS NULL ON NULL INPUT |
|
278 EXTERNAL SECURITY INVOKER; |
|
279 -- --- |
|
280 -- Nearly the same as function dovecotuser below. It returns additionally the |
|
281 -- field quota_rule. |
|
282 -- --- |
|
283 CREATE OR REPLACE FUNCTION dovecotquotauser( |
|
284 IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotquotauser |
|
285 AS $$ |
|
286 DECLARE |
|
287 record dovecotquotauser; |
|
288 userid varchar(320) := localpart || '@' || the_domain; |
|
289 did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); |
|
290 BEGIN |
|
291 FOR record IN |
|
292 SELECT userid, uid, did, domaindir || '/' || uid AS home, |
|
293 format || ':~/' || directory AS mail, '*:bytes=' || |
|
294 bytes || ':messages=' || messages AS quota_rule |
|
295 FROM users, domain_data, mailboxformat, maillocation, quotalimit |
|
296 WHERE users.gid = did |
|
297 AND users.local_part = localpart |
|
298 AND maillocation.mid = users.mid |
|
299 AND mailboxformat.fid = maillocation.fid |
|
300 AND domain_data.gid = did |
|
301 AND quotalimit.qid = users.qid |
|
302 LOOP |
|
303 RETURN NEXT record; |
|
304 END LOOP; |
|
305 RETURN; |
|
306 END; |
|
307 $$ LANGUAGE plpgsql STABLE |
|
308 RETURNS NULL ON NULL INPUT |
|
309 EXTERNAL SECURITY INVOKER; |
|
310 -- --- |
|
311 -- Parameters (from login name [localpart@the_domain]): |
|
312 -- varchar localpart |
|
313 -- varchar the_domain |
|
314 -- Returns: dovecotuser records |
|
315 -- --- |
|
316 CREATE OR REPLACE FUNCTION dovecotuser( |
|
317 IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotuser |
|
318 AS $$ |
|
319 DECLARE |
|
320 record dovecotuser; |
|
321 userid varchar(320) := localpart || '@' || the_domain; |
|
322 did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); |
|
323 BEGIN |
|
324 FOR record IN |
|
325 SELECT userid, uid, did, domaindir || '/' || uid AS home, |
|
326 format || ':~/' || directory AS mail |
|
327 FROM users, domain_data, mailboxformat, maillocation |
|
328 WHERE users.gid = did |
|
329 AND users.local_part = localpart |
|
330 AND maillocation.mid = users.mid |
|
331 AND mailboxformat.fid = maillocation.fid |
|
332 AND domain_data.gid = did |
|
333 LOOP |
|
334 RETURN NEXT record; |
|
335 END LOOP; |
|
336 RETURN; |
|
337 END; |
|
338 $$ LANGUAGE plpgsql STABLE |
|
339 RETURNS NULL ON NULL INPUT |
|
340 EXTERNAL SECURITY INVOKER; |
|
341 -- --- |
|
342 -- Parameters (from recipients address (MAIL TO) [localpart@the_domain]): |
|
343 -- varchar localpart |
|
344 -- varchar the_domain |
|
345 -- Returns: recipient_destination records |
|
346 -- --- |
|
347 CREATE OR REPLACE FUNCTION postfix_relocated_map( |
|
348 IN localpart varchar, IN the_domain varchar) |
|
349 RETURNS SETOF recipient_destination |
|
350 AS $$ |
|
351 DECLARE |
|
352 record recipient_destination; |
|
353 recipient varchar(320) := localpart || '@' || the_domain; |
|
354 did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); |
|
355 BEGIN |
|
356 FOR record IN |
|
357 SELECT recipient, destination |
|
358 FROM relocated |
|
359 WHERE gid = did |
|
360 AND address = localpart |
|
361 LOOP |
|
362 RETURN NEXT record; |
|
363 END LOOP; |
|
364 RETURN; |
|
365 END; |
|
366 $$ LANGUAGE plpgsql STABLE |
|
367 RETURNS NULL ON NULL INPUT |
|
368 EXTERNAL SECURITY INVOKER; |
|
369 -- --- |
|
370 -- Parameters (from _sender_ address (MAIL FROM) [localpart@the_domain]): |
|
371 -- varchar localpart |
|
372 -- varchar the_domain |
|
373 -- Returns: SASL _login_ names that own _sender_ addresses (MAIL FROM): |
|
374 -- set of sender_login records. |
|
375 -- --- |
|
376 CREATE OR REPLACE FUNCTION postfix_smtpd_sender_login_map( |
|
377 IN localpart varchar, IN the_domain varchar) RETURNS SETOF sender_login |
|
378 AS $$ |
|
379 DECLARE |
|
380 rec sender_login; |
|
381 did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); |
|
382 sender varchar(320) := localpart || '@' || the_domain; |
|
383 BEGIN |
|
384 -- Get all addresses for 'localpart' in the primary and aliased domains |
|
385 FOR rec IN |
|
386 SELECT sender, local_part || '@' || domainname |
|
387 FROM domain_name, users |
|
388 WHERE domain_name.gid = did |
|
389 AND users.gid = did |
|
390 AND users.local_part = localpart |
|
391 LOOP |
|
392 RETURN NEXT rec; |
|
393 END LOOP; |
|
394 IF NOT FOUND THEN |
|
395 -- Loop over the alias addresses for localpart@the_domain |
|
396 FOR rec IN |
|
397 SELECT DISTINCT sender, destination |
|
398 FROM alias |
|
399 WHERE alias.gid = did |
|
400 AND alias.address = localpart |
|
401 LOOP |
|
402 RETURN NEXT rec; |
|
403 END LOOP; |
|
404 END IF; |
|
405 RETURN; |
|
406 END; |
|
407 $$ LANGUAGE plpgsql STABLE |
|
408 RETURNS NULL ON NULL INPUT |
|
409 EXTERNAL SECURITY INVOKER; |
|
410 -- --- |
|
411 -- Parameters (from recipients address (MAIL TO) [localpart@the_domain]): |
|
412 -- varchar localpart |
|
413 -- varchar the_domain |
|
414 -- Returns: recipient_transport records |
|
415 -- --- |
|
416 CREATE OR REPLACE FUNCTION postfix_transport_map( |
|
417 IN localpart varchar, IN the_domain varchar) |
|
418 RETURNS SETOF recipient_transport |
|
419 AS $$ |
|
420 DECLARE |
|
421 record recipient_transport; |
|
422 recipient varchar(320) := localpart || '@' || the_domain; |
|
423 BEGIN |
|
424 FOR record IN |
|
425 SELECT recipient, transport |
|
426 FROM transport |
|
427 WHERE tid = (SELECT tid |
|
428 FROM users |
|
429 WHERE gid = (SELECT gid |
|
430 FROM domain_name |
|
431 WHERE domainname = the_domain) |
|
432 AND local_part = localpart) |
|
433 LOOP |
|
434 RETURN NEXT record; |
|
435 END LOOP; |
|
436 RETURN; |
|
437 END; |
|
438 $$ LANGUAGE plpgsql STABLE |
|
439 RETURNS NULL ON NULL INPUT |
|
440 EXTERNAL SECURITY INVOKER; |
|
441 -- --- |
|
442 -- Parameters (from recipients address (MAIL TO) [localpart@the_domain]): |
|
443 -- varchar localpart |
|
444 -- varchar the_domain |
|
445 -- Returns: recipient_destination records |
|
446 -- --- |
|
447 CREATE OR REPLACE FUNCTION postfix_virtual_alias_map( |
|
448 IN localpart varchar, IN the_domain varchar) |
|
449 RETURNS SETOF recipient_destination |
|
450 AS $$ |
|
451 DECLARE |
|
452 record recipient_destination; |
|
453 recipient varchar(320) := localpart || '@' || the_domain; |
|
454 did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); |
|
455 BEGIN |
|
456 FOR record IN |
|
457 SELECT recipient, destination |
|
458 FROM alias |
|
459 WHERE gid = did |
|
460 AND address = localpart |
|
461 LOOP |
|
462 RETURN NEXT record; |
|
463 END LOOP; |
|
464 RETURN; |
|
465 END; |
|
466 $$ LANGUAGE plpgsql STABLE |
|
467 RETURNS NULL ON NULL INPUT |
|
468 EXTERNAL SECURITY INVOKER; |
|
469 -- --- |
|
470 -- Parameters (from recipients address (MAIL TO) [localpart@the_domain]): |
|
471 -- varchar localpart |
|
472 -- varchar the_domain |
|
473 -- Returns: address_maildir records |
|
474 -- --- |
|
475 CREATE OR REPLACE FUNCTION postfix_virtual_mailbox_map( |
|
476 IN localpart varchar, IN the_domain varchar) RETURNS SETOF address_maildir |
|
477 AS $$ |
|
478 DECLARE |
|
479 rec address_maildir; |
|
480 did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); |
|
481 address varchar(320) := localpart || '@' || the_domain; |
|
482 BEGIN |
|
483 FOR rec IN |
|
484 SELECT address, domaindir||'/'||users.uid||'/'||directory||'/' |
|
485 FROM domain_data, users, maillocation |
|
486 WHERE domain_data.gid = did |
|
487 AND users.gid = did |
|
488 AND users.local_part = localpart |
|
489 AND maillocation.mid = users.mid |
|
490 LOOP |
|
491 RETURN NEXT rec; |
|
492 END LOOP; |
|
493 RETURN; |
|
494 END; |
|
495 $$ LANGUAGE plpgsql STABLE |
|
496 RETURNS NULL ON NULL INPUT |
|
497 EXTERNAL SECURITY INVOKER; |
|
498 -- --- |
|
499 -- Parameters (from recipients address (MAIL TO) [localpart@the_domain]): |
|
500 -- varchar localpart |
|
501 -- varchar the_domain |
|
502 -- Returns: recipient_uid records |
|
503 -- --- |
|
504 CREATE OR REPLACE FUNCTION postfix_virtual_uid_map( |
|
505 IN localpart varchar, IN the_domain varchar) RETURNS SETOF recipient_uid |
|
506 AS $$ |
|
507 DECLARE |
|
508 record recipient_uid; |
|
509 recipient varchar(320) := localpart || '@' || the_domain; |
|
510 BEGIN |
|
511 FOR record IN |
|
512 SELECT recipient, uid |
|
513 FROM users |
|
514 WHERE gid = (SELECT gid |
|
515 FROM domain_name |
|
516 WHERE domainname = the_domain) |
|
517 AND local_part = localpart |
|
518 LOOP |
|
519 RETURN NEXT record; |
|
520 END LOOP; |
|
521 RETURN; |
|
522 END; |
|
523 $$ LANGUAGE plpgsql STABLE |
|
524 RETURNS NULL ON NULL INPUT |
|
525 EXTERNAL SECURITY INVOKER; |