221 WHERE tid = (SELECT tid |
222 WHERE tid = (SELECT tid |
222 FROM users |
223 FROM users |
223 WHERE gid = (SELECT gid |
224 WHERE gid = (SELECT gid |
224 FROM domain_name |
225 FROM domain_name |
225 WHERE domainname = the_domain) |
226 WHERE domainname = the_domain) |
226 AND local_part = localpart) |
227 AND local_part = localpart) |
227 LOOP |
228 LOOP |
228 RETURN NEXT record; |
229 RETURN NEXT record; |
229 END LOOP; |
230 END LOOP; |
230 RETURN; |
231 RETURN; |
231 END; |
232 END; |
232 $$ LANGUAGE plpgsql STABLE |
233 $$ LANGUAGE plpgsql STABLE |
233 RETURNS NULL ON NULL INPUT |
234 RETURNS NULL ON NULL INPUT |
234 EXTERNAL SECURITY INVOKER; |
235 EXTERNAL SECURITY INVOKER; |
|
236 |
|
237 -- ########################################################################## -- |
|
238 |
|
239 -- --- |
|
240 -- Data type for function postfix_virtual_uid_map(varchar, varchar) |
|
241 -- --- |
|
242 CREATE TYPE recipient_uid AS ( |
|
243 recipient varchar(320), |
|
244 uid bigint |
|
245 ); |
|
246 |
|
247 -- --- |
|
248 -- Parameters (from recipients address (MAIL TO) [localpart@the_domain]): |
|
249 -- varchar localpart |
|
250 -- varchar the_domain |
|
251 -- Returns: recipient_uid records |
|
252 -- |
|
253 -- Required access privileges for your postfix database user: |
|
254 -- GRANT SELECT ON users, domain_name TO postfix; |
|
255 -- |
|
256 -- For more details see postconf(5) section virtual_uid_maps |
|
257 -- --- |
|
258 CREATE OR REPLACE FUNCTION postfix_virtual_uid_map( |
|
259 IN localpart varchar, IN the_domain varchar) RETURNS SETOF recipient_uid |
|
260 AS $$ |
|
261 DECLARE |
|
262 record recipient_uid; |
|
263 recipient varchar(320) := localpart || '@' || the_domain; |
|
264 BEGIN |
|
265 FOR record IN |
|
266 SELECT recipient, uid |
|
267 FROM users |
|
268 WHERE gid = (SELECT gid |
|
269 FROM domain_name |
|
270 WHERE domainname = the_domain) |
|
271 AND local_part = localpart |
|
272 LOOP |
|
273 RETURN NEXT record; |
|
274 END LOOP; |
|
275 RETURN; |
|
276 END; |
|
277 $$ LANGUAGE plpgsql STABLE |
|
278 RETURNS NULL ON NULL INPUT |
|
279 EXTERNAL SECURITY INVOKER; |
|
280 |
|
281 -- ########################################################################## -- |
|
282 |
|
283 -- --- |
|
284 -- Data type for function dovecotuser(varchar, varchar) |
|
285 -- --- |
|
286 CREATE TYPE dovecotuser AS ( |
|
287 userid varchar(320), |
|
288 uid bigint, |
|
289 gid bigint, |
|
290 home text, |
|
291 mail text |
|
292 ); |
|
293 |
|
294 -- --- |
|
295 -- Parameters (from login name [localpart@the_domain]): |
|
296 -- varchar localpart |
|
297 -- varchar the_domain |
|
298 -- Returns: dovecotuser records |
|
299 -- |
|
300 -- Required access privileges for your dovecot database user: |
|
301 -- GRANT SELECT ON users,domain_data,domain_name,maillocation TO dovecot; |
|
302 -- |
|
303 -- For more details see http://wiki.dovecot.org/UserDatabase |
|
304 -- --- |
|
305 CREATE OR REPLACE FUNCTION dovecotuser( |
|
306 IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotuser |
|
307 AS $$ |
|
308 DECLARE |
|
309 record dovecotuser; |
|
310 userid varchar(320) := localpart || '@' || the_domain; |
|
311 did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); |
|
312 BEGIN |
|
313 FOR record IN |
|
314 SELECT userid, uid, did, domaindir ||'/'|| uid AS home, |
|
315 '~/'|| maillocation AS mail |
|
316 FROM users, domain_data, maillocation |
|
317 WHERE users.gid = did |
|
318 AND users.local_part = localpart |
|
319 AND maillocation.mid = users.mid |
|
320 AND domain_data.gid = did |
|
321 LOOP |
|
322 RETURN NEXT record; |
|
323 END LOOP; |
|
324 RETURN; |
|
325 END; |
|
326 $$ LANGUAGE plpgsql STABLE |
|
327 RETURNS NULL ON NULL INPUT |
|
328 EXTERNAL SECURITY INVOKER; |
|
329 |
|
330 -- ########################################################################## -- |
|
331 |
|
332 -- --- |
|
333 -- Data type for function dovecotpassword(varchar, varchar) |
|
334 -- --- |
|
335 CREATE TYPE dovecotpassword AS ( |
|
336 userid varchar(320), |
|
337 password varchar(74), |
|
338 smtp boolean, |
|
339 pop3 boolean, |
|
340 imap boolean, |
|
341 managesieve boolean |
|
342 ); |
|
343 |
|
344 -- --- |
|
345 -- Parameters (from login name [localpart@the_domain]): |
|
346 -- varchar localpart |
|
347 -- varchar the_domain |
|
348 -- Returns: dovecotpassword records |
|
349 -- |
|
350 -- Required access privileges for your dovecot database user: |
|
351 -- GRANT SELECT ON users, domain_name TO dovecot; |
|
352 -- |
|
353 -- For more details see http://wiki.dovecot.org/AuthDatabase/SQL |
|
354 -- --- |
|
355 CREATE OR REPLACE FUNCTION dovecotpassword( |
|
356 IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotpassword |
|
357 AS $$ |
|
358 DECLARE |
|
359 record dovecotpassword; |
|
360 userid varchar(320) := localpart || '@' || the_domain; |
|
361 BEGIN |
|
362 FOR record IN |
|
363 SELECT userid, passwd, smtp, pop3, imap, managesieve |
|
364 FROM users |
|
365 WHERE gid = (SELECT gid |
|
366 FROM domain_name |
|
367 WHERE domainname = the_domain) |
|
368 AND local_part = localpart |
|
369 LOOP |
|
370 RETURN NEXT record; |
|
371 END LOOP; |
|
372 RETURN; |
|
373 END; |
|
374 $$ LANGUAGE plpgsql STABLE |
|
375 RETURNS NULL ON NULL INPUT |
|
376 EXTERNAL SECURITY INVOKER; |