1 -- --- Information: |
|
2 -- This file contains some data types and functions these should speed up some |
|
3 -- operations. Read the comment on each data type/functions for more details. |
|
4 -- --- |
|
5 |
|
6 -- --- |
|
7 -- Data type for function postfix_smtpd_sender_login_map(varchar, varchar) |
|
8 -- --- |
|
9 CREATE TYPE sender_login AS ( |
|
10 sender varchar(320), |
|
11 login text |
|
12 ); |
|
13 |
|
14 -- --- |
|
15 -- Parameters (from _sender_ address (MAIL FROM) [localpart@the_domain]): |
|
16 -- varchar localpart |
|
17 -- varchar the_domain |
|
18 -- Returns: SASL _login_ names that own _sender_ addresses (MAIL FROM): |
|
19 -- set of sender_login records. |
|
20 -- |
|
21 -- Required access privileges for your postfix database user: |
|
22 -- GRANT SELECT ON domain_name, users, alias TO postfix; |
|
23 -- |
|
24 -- For more details see postconf(5) section smtpd_sender_login_maps |
|
25 -- --- |
|
26 CREATE OR REPLACE FUNCTION postfix_smtpd_sender_login_map( |
|
27 IN localpart varchar, IN the_domain varchar) RETURNS SETOF sender_login |
|
28 AS $$ |
|
29 DECLARE |
|
30 rec sender_login; |
|
31 did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); |
|
32 sender varchar(320) := localpart || '@' || the_domain; |
|
33 BEGIN |
|
34 -- Get all addresses for 'localpart' in the primary and aliased domains |
|
35 FOR rec IN |
|
36 SELECT sender, local_part || '@' || domainname |
|
37 FROM domain_name, users |
|
38 WHERE domain_name.gid = did |
|
39 AND users.gid = did |
|
40 AND users.local_part = localpart |
|
41 LOOP |
|
42 RETURN NEXT rec; |
|
43 END LOOP; |
|
44 IF NOT FOUND THEN |
|
45 -- Loop over the alias addresses for localpart@the_domain |
|
46 FOR rec IN |
|
47 SELECT DISTINCT sender, destination |
|
48 FROM alias |
|
49 WHERE alias.gid = did |
|
50 AND alias.address = localpart |
|
51 LOOP |
|
52 RETURN NEXT rec; |
|
53 END LOOP; |
|
54 END IF; |
|
55 RETURN; |
|
56 END; |
|
57 $$ LANGUAGE plpgsql STABLE |
|
58 RETURNS NULL ON NULL INPUT |
|
59 EXTERNAL SECURITY INVOKER; |
|
60 |
|
61 -- ########################################################################## -- |
|
62 |
|
63 -- --- |
|
64 -- Data type for function postfix_virtual_mailbox(varchar, varchar) |
|
65 -- --- |
|
66 CREATE TYPE address_maildir AS ( |
|
67 address varchar(320), |
|
68 maildir text |
|
69 ); |
|
70 |
|
71 -- --- |
|
72 -- Parameters (from recipients address (MAIL TO) [localpart@the_domain]): |
|
73 -- varchar localpart |
|
74 -- varchar the_domain |
|
75 -- Returns: address_maildir records |
|
76 -- |
|
77 -- Required access privileges for your postfix database user: |
|
78 -- GRANT SELECT ON domain_data,domain_name,maillocation,users TO postfix; |
|
79 -- |
|
80 -- For more details see postconf(5) section virtual_mailbox_maps |
|
81 -- --- |
|
82 CREATE OR REPLACE FUNCTION postfix_virtual_mailbox_map( |
|
83 IN localpart varchar, IN the_domain varchar) RETURNS SETOF address_maildir |
|
84 AS $$ |
|
85 DECLARE |
|
86 rec address_maildir; |
|
87 did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); |
|
88 address varchar(320) := localpart || '@' || the_domain; |
|
89 BEGIN |
|
90 FOR rec IN |
|
91 SELECT address, domaindir||'/'||users.uid||'/'||directory||'/' |
|
92 FROM domain_data, users, maillocation |
|
93 WHERE domain_data.gid = did |
|
94 AND users.gid = did |
|
95 AND users.local_part = localpart |
|
96 AND maillocation.mid = users.mid |
|
97 LOOP |
|
98 RETURN NEXT rec; |
|
99 END LOOP; |
|
100 RETURN; |
|
101 END; |
|
102 $$ LANGUAGE plpgsql STABLE |
|
103 RETURNS NULL ON NULL INPUT |
|
104 EXTERNAL SECURITY INVOKER; |
|
105 |
|
106 -- ########################################################################## -- |
|
107 |
|
108 -- --- |
|
109 -- Data type for functions: postfix_relocated_map(varchar, varchar) |
|
110 -- postfix_virtual_alias_map(varchar, varchar) |
|
111 -- --- |
|
112 CREATE TYPE recipient_destination AS ( |
|
113 recipient varchar(320), |
|
114 destination text |
|
115 ); |
|
116 |
|
117 -- --- |
|
118 -- Parameters (from recipients address (MAIL TO) [localpart@the_domain]): |
|
119 -- varchar localpart |
|
120 -- varchar the_domain |
|
121 -- Returns: recipient_destination records |
|
122 -- |
|
123 -- Required access privileges for your postfix database user: |
|
124 -- GRANT SELECT ON alias, domain_name TO postfix; |
|
125 -- |
|
126 -- For more details see postconf(5) section virtual_alias_maps and virtual(5) |
|
127 -- --- |
|
128 CREATE OR REPLACE FUNCTION postfix_virtual_alias_map( |
|
129 IN localpart varchar, IN the_domain varchar) |
|
130 RETURNS SETOF recipient_destination |
|
131 AS $$ |
|
132 DECLARE |
|
133 record recipient_destination; |
|
134 recipient varchar(320) := localpart || '@' || the_domain; |
|
135 did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); |
|
136 BEGIN |
|
137 FOR record IN |
|
138 SELECT recipient, destination |
|
139 FROM alias |
|
140 WHERE gid = did |
|
141 AND address = localpart |
|
142 LOOP |
|
143 RETURN NEXT record; |
|
144 END LOOP; |
|
145 RETURN; |
|
146 END; |
|
147 $$ LANGUAGE plpgsql STABLE |
|
148 RETURNS NULL ON NULL INPUT |
|
149 EXTERNAL SECURITY INVOKER; |
|
150 |
|
151 -- ########################################################################## -- |
|
152 |
|
153 -- --- |
|
154 -- Parameters (from recipients address (MAIL TO) [localpart@the_domain]): |
|
155 -- varchar localpart |
|
156 -- varchar the_domain |
|
157 -- Returns: recipient_destination records |
|
158 -- |
|
159 -- Required access privileges for your postfix database user: |
|
160 -- GRANT SELECT ON domain_name, relocated TO postfix; |
|
161 -- |
|
162 -- For more details see postconf(5) section relocated_maps and relocated(5) |
|
163 -- --- |
|
164 CREATE OR REPLACE FUNCTION postfix_relocated_map( |
|
165 IN localpart varchar, IN the_domain varchar) |
|
166 RETURNS SETOF recipient_destination |
|
167 AS $$ |
|
168 DECLARE |
|
169 record recipient_destination; |
|
170 recipient varchar(320) := localpart || '@' || the_domain; |
|
171 did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); |
|
172 BEGIN |
|
173 FOR record IN |
|
174 SELECT recipient, destination |
|
175 FROM relocated |
|
176 WHERE gid = did |
|
177 AND address = localpart |
|
178 LOOP |
|
179 RETURN NEXT record; |
|
180 END LOOP; |
|
181 RETURN; |
|
182 END; |
|
183 $$ LANGUAGE plpgsql STABLE |
|
184 RETURNS NULL ON NULL INPUT |
|
185 EXTERNAL SECURITY INVOKER; |
|
186 |
|
187 -- ########################################################################## -- |
|
188 |
|
189 -- --- |
|
190 -- Data type for function postfix_transport_map(varchar, varchar) |
|
191 -- --- |
|
192 CREATE TYPE recipient_transport AS ( |
|
193 recipient varchar(320), |
|
194 transport text |
|
195 ); |
|
196 |
|
197 -- --- |
|
198 -- Parameters (from recipients address (MAIL TO) [localpart@the_domain]): |
|
199 -- varchar localpart |
|
200 -- varchar the_domain |
|
201 -- Returns: recipient_transport records |
|
202 -- |
|
203 -- Required access privileges for your postfix database user: |
|
204 -- GRANT SELECT ON users, transport, domain_name TO postfix; |
|
205 -- |
|
206 -- For more details see postconf(5) section transport_maps and transport(5) |
|
207 -- --- |
|
208 CREATE OR REPLACE FUNCTION postfix_transport_map( |
|
209 IN localpart varchar, IN the_domain varchar) |
|
210 RETURNS SETOF recipient_transport |
|
211 AS $$ |
|
212 DECLARE |
|
213 record recipient_transport; |
|
214 recipient varchar(320) := localpart || '@' || the_domain; |
|
215 BEGIN |
|
216 FOR record IN |
|
217 SELECT recipient, transport |
|
218 FROM transport |
|
219 WHERE tid = (SELECT tid |
|
220 FROM users |
|
221 WHERE gid = (SELECT gid |
|
222 FROM domain_name |
|
223 WHERE domainname = the_domain) |
|
224 AND local_part = localpart) |
|
225 LOOP |
|
226 RETURN NEXT record; |
|
227 END LOOP; |
|
228 RETURN; |
|
229 END; |
|
230 $$ LANGUAGE plpgsql STABLE |
|
231 RETURNS NULL ON NULL INPUT |
|
232 EXTERNAL SECURITY INVOKER; |
|
233 |
|
234 -- ########################################################################## -- |
|
235 |
|
236 -- --- |
|
237 -- Data type for function postfix_virtual_uid_map(varchar, varchar) |
|
238 -- --- |
|
239 CREATE TYPE recipient_uid AS ( |
|
240 recipient varchar(320), |
|
241 uid bigint |
|
242 ); |
|
243 |
|
244 -- --- |
|
245 -- Parameters (from recipients address (MAIL TO) [localpart@the_domain]): |
|
246 -- varchar localpart |
|
247 -- varchar the_domain |
|
248 -- Returns: recipient_uid records |
|
249 -- |
|
250 -- Required access privileges for your postfix database user: |
|
251 -- GRANT SELECT ON users, domain_name TO postfix; |
|
252 -- |
|
253 -- For more details see postconf(5) section virtual_uid_maps |
|
254 -- --- |
|
255 CREATE OR REPLACE FUNCTION postfix_virtual_uid_map( |
|
256 IN localpart varchar, IN the_domain varchar) RETURNS SETOF recipient_uid |
|
257 AS $$ |
|
258 DECLARE |
|
259 record recipient_uid; |
|
260 recipient varchar(320) := localpart || '@' || the_domain; |
|
261 BEGIN |
|
262 FOR record IN |
|
263 SELECT recipient, uid |
|
264 FROM users |
|
265 WHERE gid = (SELECT gid |
|
266 FROM domain_name |
|
267 WHERE domainname = the_domain) |
|
268 AND local_part = localpart |
|
269 LOOP |
|
270 RETURN NEXT record; |
|
271 END LOOP; |
|
272 RETURN; |
|
273 END; |
|
274 $$ LANGUAGE plpgsql STABLE |
|
275 RETURNS NULL ON NULL INPUT |
|
276 EXTERNAL SECURITY INVOKER; |
|
277 |
|
278 -- ########################################################################## -- |
|
279 |
|
280 -- --- |
|
281 -- Data type for function dovecotuser(varchar, varchar) |
|
282 -- --- |
|
283 CREATE TYPE dovecotuser AS ( |
|
284 userid varchar(320), |
|
285 uid bigint, |
|
286 gid bigint, |
|
287 home text, |
|
288 mail text |
|
289 ); |
|
290 -- --- |
|
291 -- Data type for function dovecotquotauser(varchar, varchar) |
|
292 -- --- |
|
293 CREATE TYPE dovecotquotauser AS ( |
|
294 userid varchar(320), |
|
295 uid bigint, |
|
296 gid bigint, |
|
297 home text, |
|
298 mail text, |
|
299 quota_rule text |
|
300 ); |
|
301 |
|
302 -- --- |
|
303 -- Parameters (from login name [localpart@the_domain]): |
|
304 -- varchar localpart |
|
305 -- varchar the_domain |
|
306 -- Returns: dovecotuser records |
|
307 -- |
|
308 -- Required access privileges for your dovecot database user: |
|
309 -- GRANT SELECT |
|
310 -- ON users, domain_data, domain_name, maillocation, mailboxformat |
|
311 -- TO dovecot; |
|
312 -- |
|
313 -- For more details see http://wiki.dovecot.org/UserDatabase |
|
314 -- --- |
|
315 CREATE OR REPLACE FUNCTION dovecotuser( |
|
316 IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotuser |
|
317 AS $$ |
|
318 DECLARE |
|
319 record dovecotuser; |
|
320 userid varchar(320) := localpart || '@' || the_domain; |
|
321 did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); |
|
322 BEGIN |
|
323 FOR record IN |
|
324 SELECT userid, uid, did, domaindir || '/' || uid AS home, |
|
325 format || ':~/' || directory AS mail |
|
326 FROM users, domain_data, mailboxformat, maillocation |
|
327 WHERE users.gid = did |
|
328 AND users.local_part = localpart |
|
329 AND maillocation.mid = users.mid |
|
330 AND mailboxformat.fid = maillocation.fid |
|
331 AND domain_data.gid = did |
|
332 LOOP |
|
333 RETURN NEXT record; |
|
334 END LOOP; |
|
335 RETURN; |
|
336 END; |
|
337 $$ LANGUAGE plpgsql STABLE |
|
338 RETURNS NULL ON NULL INPUT |
|
339 EXTERNAL SECURITY INVOKER; |
|
340 |
|
341 -- --- |
|
342 -- Nearly the same as function dovecotuser above. It returns additionally the |
|
343 -- field quota_rule. |
|
344 -- |
|
345 -- Required access privileges for your dovecot database user: |
|
346 -- GRANT SELECT |
|
347 -- ON users, domain_data, domain_name, maillocation, mailboxformat, |
|
348 -- quotalimit |
|
349 -- TO dovecot; |
|
350 -- --- |
|
351 CREATE OR REPLACE FUNCTION dovecotquotauser( |
|
352 IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotquotauser |
|
353 AS $$ |
|
354 DECLARE |
|
355 record dovecotquotauser; |
|
356 userid varchar(320) := localpart || '@' || the_domain; |
|
357 did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); |
|
358 BEGIN |
|
359 FOR record IN |
|
360 SELECT userid, uid, did, domaindir || '/' || uid AS home, |
|
361 format || ':~/' || directory AS mail, '*:bytes=' || |
|
362 bytes || ':messages=' || messages AS quota_rule |
|
363 FROM users, domain_data, mailboxformat, maillocation, quotalimit |
|
364 WHERE users.gid = did |
|
365 AND users.local_part = localpart |
|
366 AND maillocation.mid = users.mid |
|
367 AND mailboxformat.fid = maillocation.fid |
|
368 AND domain_data.gid = did |
|
369 AND quotalimit.qid = users.qid |
|
370 LOOP |
|
371 RETURN NEXT record; |
|
372 END LOOP; |
|
373 RETURN; |
|
374 END; |
|
375 $$ LANGUAGE plpgsql STABLE |
|
376 RETURNS NULL ON NULL INPUT |
|
377 EXTERNAL SECURITY INVOKER; |
|
378 |
|
379 -- ########################################################################## -- |
|
380 |
|
381 -- --- |
|
382 -- Data type for function dovecotpassword(varchar, varchar) |
|
383 -- --- |
|
384 CREATE TYPE dovecotpassword AS ( |
|
385 userid varchar(320), |
|
386 password varchar(270), |
|
387 smtp boolean, |
|
388 pop3 boolean, |
|
389 imap boolean, |
|
390 sieve boolean |
|
391 ); |
|
392 |
|
393 -- --- |
|
394 -- Parameters (from login name [localpart@the_domain]): |
|
395 -- varchar localpart |
|
396 -- varchar the_domain |
|
397 -- Returns: dovecotpassword records |
|
398 -- |
|
399 -- Required access privileges for your dovecot database user: |
|
400 -- GRANT SELECT ON users, domain_name, service_set TO dovecot; |
|
401 -- |
|
402 -- For more details see http://wiki.dovecot.org/AuthDatabase/SQL |
|
403 -- --- |
|
404 CREATE OR REPLACE FUNCTION dovecotpassword( |
|
405 IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotpassword |
|
406 AS $$ |
|
407 DECLARE |
|
408 record dovecotpassword; |
|
409 userid varchar(320) := localpart || '@' || the_domain; |
|
410 BEGIN |
|
411 FOR record IN |
|
412 SELECT userid, passwd, smtp, pop3, imap, sieve |
|
413 FROM users, service_set |
|
414 WHERE gid = (SELECT gid |
|
415 FROM domain_name |
|
416 WHERE domainname = the_domain) |
|
417 AND local_part = localpart |
|
418 AND service_set.ssid = users.ssid |
|
419 LOOP |
|
420 RETURN NEXT record; |
|
421 END LOOP; |
|
422 RETURN; |
|
423 END; |
|
424 $$ LANGUAGE plpgsql STABLE |
|
425 RETURNS NULL ON NULL INPUT |
|
426 EXTERNAL SECURITY INVOKER; |
|