|
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 sieve 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, sieve |
|
73 FROM users |
|
74 WHERE gid = (SELECT gid |
|
75 FROM domain_name |
|
76 WHERE domainname = the_domain) |
|
77 AND local_part = localpart |
|
78 LOOP |
|
79 RETURN NEXT record; |
|
80 END LOOP; |
|
81 RETURN; |
|
82 END; |
|
83 $$ LANGUAGE plpgsql STABLE |
|
84 RETURNS NULL ON NULL INPUT |
|
85 EXTERNAL SECURITY INVOKER; |
|
86 -- --- |
|
87 -- Parameters (from login name [localpart@the_domain]): |
|
88 -- varchar localpart |
|
89 -- varchar the_domain |
|
90 -- Returns: dovecotuser records |
|
91 -- |
|
92 -- Required access privileges for your dovecot database user: |
|
93 -- GRANT SELECT |
|
94 -- ON users, domain_data, domain_name, maillocation, mailboxformat |
|
95 -- TO dovecot; |
|
96 -- |
|
97 -- For more details see http://wiki.dovecot.org/UserDatabase |
|
98 -- --- |
|
99 CREATE OR REPLACE FUNCTION dovecotuser( |
|
100 IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotuser |
|
101 AS $$ |
|
102 DECLARE |
|
103 record dovecotuser; |
|
104 userid varchar(320) := localpart || '@' || the_domain; |
|
105 did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); |
|
106 BEGIN |
|
107 FOR record IN |
|
108 SELECT userid, uid, did, domaindir || '/' || uid AS home, |
|
109 format || ':~/' || directory AS mail |
|
110 FROM users, domain_data, mailboxformat, maillocation |
|
111 WHERE users.gid = did |
|
112 AND users.local_part = localpart |
|
113 AND maillocation.mid = users.mid |
|
114 AND mailboxformat.fid = maillocation.fid |
|
115 AND domain_data.gid = did |
|
116 LOOP |
|
117 RETURN NEXT record; |
|
118 END LOOP; |
|
119 RETURN; |
|
120 END; |
|
121 $$ LANGUAGE plpgsql STABLE |
|
122 RETURNS NULL ON NULL INPUT |
|
123 EXTERNAL SECURITY INVOKER; |