|
1 -- $Id$ |
|
2 |
|
3 -- --- Information: |
|
4 -- This file contains some data types and functions these should speed up some |
|
5 -- operations. Read the comment on each data type/functions for more details. |
|
6 -- --- |
|
7 |
|
8 -- --- |
|
9 -- Data type for function postfix_smtpd_sender_login(varchar, varchar) |
|
10 -- --- |
|
11 CREATE TYPE sender_login AS ( |
|
12 sender varchar(320), |
|
13 login text |
|
14 ); |
|
15 |
|
16 -- --- |
|
17 -- Parameters (from _sender_ address (MAIL FROM) [localpart@the_domain]): |
|
18 -- varchar localpart |
|
19 -- varchar the_domain |
|
20 -- Returns: SASL _login_ names that own _sender_ addresses (MAIL FROM): |
|
21 -- set of sender_login records. |
|
22 -- |
|
23 -- Required access privileges for your postfix database user: |
|
24 -- GRANT SELECT ON domain_name, users, alias TO postfix; |
|
25 -- |
|
26 -- For more details see postconf(5) section smtpd_sender_login_maps |
|
27 -- --- |
|
28 CREATE OR REPLACE FUNCTION postfix_smtpd_sender_login( |
|
29 IN localpart varchar, IN the_domain varchar) RETURNS SETOF sender_login |
|
30 AS $$ |
|
31 DECLARE |
|
32 rec sender_login; |
|
33 did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); |
|
34 sender varchar(320) := localpart || '@' || the_domain; |
|
35 BEGIN |
|
36 -- Get all addresses for 'localpart' in the primary and aliased domains |
|
37 FOR rec IN |
|
38 SELECT sender, local_part || '@' || domainname |
|
39 FROM domain_name, users |
|
40 WHERE domain_name.gid = did |
|
41 AND users.local_part = localpart |
|
42 LOOP |
|
43 RETURN NEXT rec; |
|
44 END LOOP; |
|
45 IF NOT FOUND THEN |
|
46 -- Loop over the alias addresses for localpart@the_domain |
|
47 FOR rec IN |
|
48 SELECT DISTINCT sender, destination |
|
49 FROM alias |
|
50 LEFT JOIN domain_name USING (gid) |
|
51 WHERE alias.address = localpart |
|
52 LOOP |
|
53 RETURN NEXT rec; |
|
54 END LOOP; |
|
55 END IF; |
|
56 RETURN; |
|
57 END; |
|
58 $$ LANGUAGE plpgsql STABLE |
|
59 RETURNS NULL ON NULL INPUT |
|
60 EXTERNAL SECURITY INVOKER; |
|
61 |
|
62 -- ########################################################################## -- |
|
63 |
|
64 -- --- |
|
65 -- Data type for function postfix_virtual_mailbox(varchar, varchar) |
|
66 -- --- |
|
67 CREATE TYPE address_maildir AS ( |
|
68 address varchar(320), |
|
69 maildir text |
|
70 ); |
|
71 |
|
72 -- --- |
|
73 -- Parameters (from recipients address (MAIL TO) [localpart@the_domain]): |
|
74 -- varchar localpart |
|
75 -- varchar the_domain |
|
76 -- Returns: address_maildir records |
|
77 -- |
|
78 -- Required access privileges for your postfix database user: |
|
79 -- GRANT SELECT ON domain_data,domain_name,maillocation,users TO postfix; |
|
80 -- |
|
81 -- For more details see postconf(5) section virtual_mailbox_maps |
|
82 -- --- |
|
83 CREATE OR REPLACE FUNCTION postfix_virtual_mailbox_map( |
|
84 IN localpart varchar, IN the_domain varchar) RETURNS SETOF address_maildir |
|
85 AS $$ |
|
86 DECLARE |
|
87 rec address_maildir; |
|
88 did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); |
|
89 address varchar(320) := localpart || '@' || the_domain; |
|
90 BEGIN |
|
91 FOR rec IN |
|
92 SELECT address, domaindir||'/'||users.uid||'/'||maillocation||'/' |
|
93 FROM domain_data, users, maillocation |
|
94 WHERE domain_data.gid = did |
|
95 AND users.gid = did |
|
96 AND users.local_part = localpart |
|
97 AND maillocation.mid = users.mid |
|
98 LOOP |
|
99 RETURN NEXT rec; |
|
100 END LOOP; |
|
101 RETURN; |
|
102 END; |
|
103 $$ LANGUAGE plpgsql STABLE |
|
104 RETURNS NULL ON NULL INPUT |
|
105 EXTERNAL SECURITY INVOKER; |
|
106 |