1 SET client_encoding = 'UTF8'; |
|
2 SET client_min_messages = warning; |
|
3 |
|
4 |
|
5 -- --- |
|
6 -- Parameters (from recipients address (MAIL TO) [localpart@the_domain]): |
|
7 -- varchar localpart |
|
8 -- varchar the_domain |
|
9 -- Returns: address_maildir records |
|
10 -- |
|
11 -- Required access privileges for your postfix database user: |
|
12 -- GRANT SELECT ON domain_data,domain_name,maillocation,users TO postfix; |
|
13 -- |
|
14 -- For more details see postconf(5) section virtual_mailbox_maps |
|
15 -- --- |
|
16 CREATE OR REPLACE FUNCTION postfix_virtual_mailbox_map( |
|
17 IN localpart varchar, IN the_domain varchar) RETURNS SETOF address_maildir |
|
18 AS $$ |
|
19 DECLARE |
|
20 rec address_maildir; |
|
21 did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); |
|
22 address varchar(320) := localpart || '@' || the_domain; |
|
23 BEGIN |
|
24 FOR rec IN |
|
25 SELECT address, domaindir||'/'||users.uid||'/'||directory||'/' |
|
26 FROM domain_data, users, maillocation |
|
27 WHERE domain_data.gid = did |
|
28 AND users.gid = did |
|
29 AND users.local_part = localpart |
|
30 AND maillocation.mid = users.mid |
|
31 LOOP |
|
32 RETURN NEXT rec; |
|
33 END LOOP; |
|
34 RETURN; |
|
35 END; |
|
36 $$ LANGUAGE plpgsql STABLE |
|
37 RETURNS NULL ON NULL INPUT |
|
38 EXTERNAL SECURITY INVOKER; |
|
39 |
|
40 |
|
41 DROP TYPE dovecotpassword CASCADE; |
|
42 -- --- |
|
43 -- Data type for function dovecotpassword(varchar, varchar) |
|
44 -- --- |
|
45 CREATE TYPE dovecotpassword AS ( |
|
46 userid varchar(320), |
|
47 password varchar(270), |
|
48 smtp boolean, |
|
49 pop3 boolean, |
|
50 imap boolean, |
|
51 managesieve boolean |
|
52 ); |
|
53 -- --- |
|
54 -- Parameters (from login name [localpart@the_domain]): |
|
55 -- varchar localpart |
|
56 -- varchar the_domain |
|
57 -- Returns: dovecotpassword records |
|
58 -- |
|
59 -- Required access privileges for your dovecot database user: |
|
60 -- GRANT SELECT ON users, domain_name TO dovecot; |
|
61 -- |
|
62 -- For more details see http://wiki.dovecot.org/AuthDatabase/SQL |
|
63 -- --- |
|
64 CREATE OR REPLACE FUNCTION dovecotpassword( |
|
65 IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotpassword |
|
66 AS $$ |
|
67 DECLARE |
|
68 record dovecotpassword; |
|
69 userid varchar(320) := localpart || '@' || the_domain; |
|
70 BEGIN |
|
71 FOR record IN |
|
72 SELECT userid, passwd, smtp, pop3, imap, managesieve |
|
73 FROM users, service_set |
|
74 WHERE gid = (SELECT gid |
|
75 FROM domain_name |
|
76 WHERE domainname = the_domain) |
|
77 AND local_part = localpart |
|
78 AND service_set.ssid = users.ssid |
|
79 LOOP |
|
80 RETURN NEXT record; |
|
81 END LOOP; |
|
82 RETURN; |
|
83 END; |
|
84 $$ LANGUAGE plpgsql STABLE |
|
85 RETURNS NULL ON NULL INPUT |
|
86 EXTERNAL SECURITY INVOKER; |
|
87 -- --- |
|
88 -- Parameters (from login name [localpart@the_domain]): |
|
89 -- varchar localpart |
|
90 -- varchar the_domain |
|
91 -- Returns: dovecotuser records |
|
92 -- |
|
93 -- Required access privileges for your dovecot database user: |
|
94 -- GRANT SELECT |
|
95 -- ON users, domain_data, domain_name, maillocation, mailboxformat |
|
96 -- TO dovecot; |
|
97 -- |
|
98 -- For more details see http://wiki.dovecot.org/UserDatabase |
|
99 -- --- |
|
100 CREATE OR REPLACE FUNCTION dovecotuser( |
|
101 IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotuser |
|
102 AS $$ |
|
103 DECLARE |
|
104 record dovecotuser; |
|
105 userid varchar(320) := localpart || '@' || the_domain; |
|
106 did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); |
|
107 BEGIN |
|
108 FOR record IN |
|
109 SELECT userid, uid, did, domaindir || '/' || uid AS home, |
|
110 format || ':~/' || directory AS mail |
|
111 FROM users, domain_data, mailboxformat, maillocation |
|
112 WHERE users.gid = did |
|
113 AND users.local_part = localpart |
|
114 AND maillocation.mid = users.mid |
|
115 AND mailboxformat.fid = maillocation.fid |
|
116 AND domain_data.gid = did |
|
117 LOOP |
|
118 RETURN NEXT record; |
|
119 END LOOP; |
|
120 RETURN; |
|
121 END; |
|
122 $$ LANGUAGE plpgsql STABLE |
|
123 RETURNS NULL ON NULL INPUT |
|
124 EXTERNAL SECURITY INVOKER; |
|
125 -- --- |
|
126 -- Data type for function dovecotquotauser(varchar, varchar) |
|
127 -- --- |
|
128 CREATE TYPE dovecotquotauser AS ( |
|
129 userid varchar(320), |
|
130 uid bigint, |
|
131 gid bigint, |
|
132 home text, |
|
133 mail text, |
|
134 quota_rule text |
|
135 ); |
|
136 -- --- |
|
137 -- Nearly the same as function dovecotuser above. It returns additionally the |
|
138 -- field quota_rule. |
|
139 -- |
|
140 -- Required access privileges for your dovecot database user: |
|
141 -- GRANT SELECT |
|
142 -- ON users, domain_data, domain_name, maillocation, mailboxformat, |
|
143 -- quotalimit |
|
144 -- TO dovecot; |
|
145 -- --- |
|
146 CREATE OR REPLACE FUNCTION dovecotquotauser( |
|
147 IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotquotauser |
|
148 AS $$ |
|
149 DECLARE |
|
150 record dovecotquotauser; |
|
151 userid varchar(320) := localpart || '@' || the_domain; |
|
152 did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); |
|
153 BEGIN |
|
154 FOR record IN |
|
155 SELECT userid, uid, did, domaindir || '/' || uid AS home, |
|
156 format || ':~/' || directory AS mail, '*:bytes=' || |
|
157 bytes || ':messages=' || messages AS quota_rule |
|
158 FROM users, domain_data, mailboxformat, maillocation, quotalimit |
|
159 WHERE users.gid = did |
|
160 AND users.local_part = localpart |
|
161 AND maillocation.mid = users.mid |
|
162 AND mailboxformat.fid = maillocation.fid |
|
163 AND domain_data.gid = did |
|
164 AND quotalimit.qid = users.qid |
|
165 LOOP |
|
166 RETURN NEXT record; |
|
167 END LOOP; |
|
168 RETURN; |
|
169 END; |
|
170 $$ LANGUAGE plpgsql STABLE |
|
171 RETURNS NULL ON NULL INPUT |
|
172 EXTERNAL SECURITY INVOKER; |
|