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