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||'/'||maillocation||'/' |
|
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 -- --- |
|
113 CREATE TYPE recipient_destination AS ( |
|
114 recipient varchar(320), |
|
115 destination text |
|
116 ); |
|
117 |
|
118 -- --- |
|
119 -- Parameters (from recipients address (MAIL TO) [localpart@the_domain]): |
|
120 -- varchar localpart |
|
121 -- varchar the_domain |
|
122 -- Returns: recipient_destination records |
|
123 -- |
|
124 -- Required access privileges for your postfix database user: |
|
125 -- GRANT SELECT ON alias, domain_name TO postfix; |
|
126 -- |
|
127 -- For more details see postconf(5) section virtual_alias_maps and virtual(5) |
|
128 -- --- |
|
129 CREATE OR REPLACE FUNCTION postfix_virtual_alias_map( |
|
130 IN localpart varchar, IN the_domain varchar) |
|
131 RETURNS SETOF recipient_destination |
|
132 AS $$ |
|
133 DECLARE |
|
134 record recipient_destination; |
|
135 recipient varchar(320) := localpart || '@' || the_domain; |
|
136 did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); |
|
137 BEGIN |
|
138 FOR record IN |
|
139 SELECT recipient, destination |
|
140 FROM alias |
|
141 WHERE gid = did |
|
142 AND address = localpart |
|
143 LOOP |
|
144 RETURN NEXT record; |
|
145 END LOOP; |
|
146 RETURN; |
|
147 END; |
|
148 $$ LANGUAGE plpgsql STABLE |
|
149 RETURNS NULL ON NULL INPUT |
|
150 EXTERNAL SECURITY INVOKER; |
|
151 |
|
152 -- ########################################################################## -- |
|
153 |
|
154 -- --- |
|
155 -- Parameters (from recipients address (MAIL TO) [localpart@the_domain]): |
|
156 -- varchar localpart |
|
157 -- varchar the_domain |
|
158 -- Returns: recipient_destination records |
|
159 -- |
|
160 -- Required access privileges for your postfix database user: |
|
161 -- GRANT SELECT ON domain_name, relocated TO postfix; |
|
162 -- |
|
163 -- For more details see postconf(5) section relocated_maps and relocated(5) |
|
164 -- --- |
|
165 CREATE OR REPLACE FUNCTION postfix_relocated_map( |
|
166 IN localpart varchar, IN the_domain varchar) |
|
167 RETURNS SETOF recipient_destination |
|
168 AS $$ |
|
169 DECLARE |
|
170 record recipient_destination; |
|
171 recipient varchar(320) := localpart || '@' || the_domain; |
|
172 did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); |
|
173 BEGIN |
|
174 FOR record IN |
|
175 SELECT recipient, destination |
|
176 FROM relocated |
|
177 WHERE gid = did |
|
178 AND address = localpart |
|
179 LOOP |
|
180 RETURN NEXT record; |
|
181 END LOOP; |
|
182 RETURN; |
|
183 END; |
|
184 $$ LANGUAGE plpgsql STABLE |
|
185 RETURNS NULL ON NULL INPUT |
|
186 EXTERNAL SECURITY INVOKER; |
|
187 |
|
188 -- ########################################################################## -- |
|
189 |
|
190 -- --- |
|
191 -- Data type for function postfix_transport_map(varchar, varchar) |
|
192 -- --- |
|
193 CREATE TYPE recipient_transport AS ( |
|
194 recipient varchar(320), |
|
195 transport text |
|
196 ); |
|
197 |
|
198 -- --- |
|
199 -- Parameters (from recipients address (MAIL TO) [localpart@the_domain]): |
|
200 -- varchar localpart |
|
201 -- varchar the_domain |
|
202 -- Returns: recipient_transport records |
|
203 -- |
|
204 -- Required access privileges for your postfix database user: |
|
205 -- GRANT SELECT ON users, transport, domain_name TO postfix; |
|
206 -- |
|
207 -- For more details see postconf(5) section transport_maps and transport(5) |
|
208 -- --- |
|
209 CREATE OR REPLACE FUNCTION postfix_transport_map( |
|
210 IN localpart varchar, IN the_domain varchar) |
|
211 RETURNS SETOF recipient_transport |
|
212 AS $$ |
|
213 DECLARE |
|
214 record recipient_transport; |
|
215 recipient varchar(320) := localpart || '@' || the_domain; |
|
216 BEGIN |
|
217 FOR record IN |
|
218 SELECT recipient, transport |
|
219 FROM transport |
|
220 WHERE tid = (SELECT tid |
|
221 FROM users |
|
222 WHERE gid = (SELECT gid |
|
223 FROM domain_name |
|
224 WHERE domainname = the_domain) |
|
225 AND local_part = localpart) |
|
226 LOOP |
|
227 RETURN NEXT record; |
|
228 END LOOP; |
|
229 RETURN; |
|
230 END; |
|
231 $$ LANGUAGE plpgsql STABLE |
|
232 RETURNS NULL ON NULL INPUT |
|
233 EXTERNAL SECURITY INVOKER; |
|
234 |
|
235 -- ########################################################################## -- |
|
236 |
|
237 -- --- |
|
238 -- Data type for function postfix_virtual_uid_map(varchar, varchar) |
|
239 -- --- |
|
240 CREATE TYPE recipient_uid AS ( |
|
241 recipient varchar(320), |
|
242 uid bigint |
|
243 ); |
|
244 |
|
245 -- --- |
|
246 -- Parameters (from recipients address (MAIL TO) [localpart@the_domain]): |
|
247 -- varchar localpart |
|
248 -- varchar the_domain |
|
249 -- Returns: recipient_uid records |
|
250 -- |
|
251 -- Required access privileges for your postfix database user: |
|
252 -- GRANT SELECT ON users, domain_name TO postfix; |
|
253 -- |
|
254 -- For more details see postconf(5) section virtual_uid_maps |
|
255 -- --- |
|
256 CREATE OR REPLACE FUNCTION postfix_virtual_uid_map( |
|
257 IN localpart varchar, IN the_domain varchar) RETURNS SETOF recipient_uid |
|
258 AS $$ |
|
259 DECLARE |
|
260 record recipient_uid; |
|
261 recipient varchar(320) := localpart || '@' || the_domain; |
|
262 BEGIN |
|
263 FOR record IN |
|
264 SELECT recipient, uid |
|
265 FROM users |
|
266 WHERE gid = (SELECT gid |
|
267 FROM domain_name |
|
268 WHERE domainname = the_domain) |
|
269 AND local_part = localpart |
|
270 LOOP |
|
271 RETURN NEXT record; |
|
272 END LOOP; |
|
273 RETURN; |
|
274 END; |
|
275 $$ LANGUAGE plpgsql STABLE |
|
276 RETURNS NULL ON NULL INPUT |
|
277 EXTERNAL SECURITY INVOKER; |
|
278 |
|
279 -- ########################################################################## -- |
|
280 |
|
281 -- --- |
|
282 -- Data type for function dovecotuser(varchar, varchar) |
|
283 -- --- |
|
284 CREATE TYPE dovecotuser AS ( |
|
285 userid varchar(320), |
|
286 uid bigint, |
|
287 gid bigint, |
|
288 home text, |
|
289 mail text |
|
290 ); |
|
291 |
|
292 -- --- |
|
293 -- Parameters (from login name [localpart@the_domain]): |
|
294 -- varchar localpart |
|
295 -- varchar the_domain |
|
296 -- Returns: dovecotuser records |
|
297 -- |
|
298 -- Required access privileges for your dovecot database user: |
|
299 -- GRANT SELECT ON users,domain_data,domain_name,maillocation TO dovecot; |
|
300 -- |
|
301 -- For more details see http://wiki.dovecot.org/UserDatabase |
|
302 -- --- |
|
303 CREATE OR REPLACE FUNCTION dovecotuser( |
|
304 IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotuser |
|
305 AS $$ |
|
306 DECLARE |
|
307 record dovecotuser; |
|
308 userid varchar(320) := localpart || '@' || the_domain; |
|
309 did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); |
|
310 BEGIN |
|
311 FOR record IN |
|
312 SELECT userid, uid, did, domaindir ||'/'|| uid AS home, |
|
313 '~/'|| maillocation AS mail |
|
314 FROM users, domain_data, maillocation |
|
315 WHERE users.gid = did |
|
316 AND users.local_part = localpart |
|
317 AND maillocation.mid = users.mid |
|
318 AND domain_data.gid = did |
|
319 LOOP |
|
320 RETURN NEXT record; |
|
321 END LOOP; |
|
322 RETURN; |
|
323 END; |
|
324 $$ LANGUAGE plpgsql STABLE |
|
325 RETURNS NULL ON NULL INPUT |
|
326 EXTERNAL SECURITY INVOKER; |
|
327 |
|
328 -- ########################################################################## -- |
|
329 |
|
330 -- --- |
|
331 -- Data type for function dovecotpassword(varchar, varchar) |
|
332 -- --- |
|
333 CREATE TYPE dovecotpassword AS ( |
|
334 userid varchar(320), |
|
335 password varchar(74), |
|
336 smtp boolean, |
|
337 pop3 boolean, |
|
338 imap boolean, |
|
339 sieve boolean |
|
340 ); |
|
341 |
|
342 -- --- |
|
343 -- Parameters (from login name [localpart@the_domain]): |
|
344 -- varchar localpart |
|
345 -- varchar the_domain |
|
346 -- Returns: dovecotpassword records |
|
347 -- |
|
348 -- Required access privileges for your dovecot database user: |
|
349 -- GRANT SELECT ON users, domain_name TO dovecot; |
|
350 -- |
|
351 -- For more details see http://wiki.dovecot.org/AuthDatabase/SQL |
|
352 -- --- |
|
353 CREATE OR REPLACE FUNCTION dovecotpassword( |
|
354 IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotpassword |
|
355 AS $$ |
|
356 DECLARE |
|
357 record dovecotpassword; |
|
358 userid varchar(320) := localpart || '@' || the_domain; |
|
359 BEGIN |
|
360 FOR record IN |
|
361 SELECT userid, passwd, smtp, pop3, imap, sieve |
|
362 FROM users |
|
363 WHERE gid = (SELECT gid |
|
364 FROM domain_name |
|
365 WHERE domainname = the_domain) |
|
366 AND local_part = localpart |
|
367 LOOP |
|
368 RETURN NEXT record; |
|
369 END LOOP; |
|
370 RETURN; |
|
371 END; |
|
372 $$ LANGUAGE plpgsql STABLE |
|
373 RETURNS NULL ON NULL INPUT |
|
374 EXTERNAL SECURITY INVOKER; |
|