1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
|
Dictionary quota
================
The /dictionary/ quota backend supports both *storage* and *messages* quota
limits. The current quota is kept in a dictionary. The available dictionaries
are:
* SQL
* Redis
* Flat file
The quota root format is:
---%<-------------------------------------------------------------------------
quota = dict:<quota root name>:<username>[:<option>[...]]:<dictionary URI>
---%<-------------------------------------------------------------------------
If /username/ is left empty, the logged in username is used (this is typically
what you want). Another useful username is '%d' for supporting domain-wide
quotas.
The supported options are:
* noenforcing: Don't enforce quota limits, only track them.
* ignoreunlimited: If user has unlimited quota, don't track it.
* ns=<prefix>: This quota root is tracked only for the given namespace.
NOTE: The dictionary stores only the current quota usage. The quota limits are
still configured in userdb the same way as with other quota backends.
NOTE2: The quota dict may delete rows from the database when it wants to
rebuild the quota. You must use a separate table that contains only the quota
information, or you'll lose the other data.
Examples
--------
---%<-------------------------------------------------------------------------
plugin {
# SQL backend:
quota = dict:User quota::proxy::sqlquota
# Redis backend (v2.1.9+):
quota = dict:User quota::redis:host=127.0.0.1:prefix=user/
# file backend:
quota = dict:User quota::file:%h/Maildir/dovecot-quota
quota_rule = *:storage=10M:messages=1000
}
dict {
sqlquota = mysql:/etc/dovecot/dovecot-dict-sql.conf.ext
}
---%<-------------------------------------------------------------------------
The above SQL example uses dictionary proxy process (see below), because SQL
libraries aren't linked to all Dovecot binaries. The file and Redis examples
use direct access.
Example 'dovecot-dict-sql.conf.ext':
---%<-------------------------------------------------------------------------
connect = host=localhost dbname=mails user=sqluser password=sqlpass
map {
pattern = priv/quota/storage
table = quota
username_field = username
value_field = bytes
}
map {
pattern = priv/quota/messages
table = quota
username_field = username
value_field = messages
}
---%<-------------------------------------------------------------------------
Create the table like this:
---%<-------------------------------------------------------------------------
CREATE TABLE quota (
username varchar(100) not null,
bytes bigint not null default 0,
messages integer not null default 0,
primary key (username)
);
---%<-------------------------------------------------------------------------
MySQL uses the following queries to update the quota. You need suitable
privileges.
---%<-------------------------------------------------------------------------
INSERT INTO table (bytes,username) VALUES ('112497180','foo@example.com') ON
DUPLICATE KEY UPDATE bytes='112497180';
INSERT INTO table (messages,username) VALUES ('1743','foo@example.com') ON
DUPLICATE KEY UPDATE messages='1743';
UPDATE table SET bytes=bytes-14433,messages=messages-2 WHERE username =
'foo@example.com';
DELETE FROM table WHERE username = 'foo@example.com';
---%<-------------------------------------------------------------------------
If you're using PostgreSQL, you'll need a trigger:
---%<-------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION merge_quota() RETURNS TRIGGER AS $$
BEGIN
IF NEW.messages < 0 OR NEW.messages IS NULL THEN
-- ugly kludge: we came here from this function, really do try to insert
IF NEW.messages IS NULL THEN
NEW.messages = 0;
ELSE
NEW.messages = -NEW.messages;
END IF;
return NEW;
END IF;
LOOP
UPDATE quota SET bytes = bytes + NEW.bytes,
messages = messages + NEW.messages
WHERE username = NEW.username;
IF found THEN
RETURN NULL;
END IF;
BEGIN
IF NEW.messages = 0 THEN
INSERT INTO quota (bytes, messages, username)
VALUES (NEW.bytes, NULL, NEW.username);
ELSE
INSERT INTO quota (bytes, messages, username)
VALUES (NEW.bytes, -NEW.messages, NEW.username);
END IF;
return NULL;
EXCEPTION WHEN unique_violation THEN
-- someone just inserted the record, update it
END;
END LOOP;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER mergequota BEFORE INSERT ON quota
FOR EACH ROW EXECUTE PROCEDURE merge_quota();
---%<-------------------------------------------------------------------------
Dictionary proxy server
-----------------------
To avoid each process making a new SQL connection, you can make all dictionary
communications go through a dictionary server process which keeps the
connections permanently open.
The dictionary server is referenced with URI 'proxy:<dictionary server socket
path>:<dictionary name>'. The socket path may be left empty if you haven't
changed 'base_dir' setting in 'dovecot.conf'. Otherwise set it to
'<base_dir>/dict-server'. The dictionary names are configured in
'dovecot.conf'. For example:
---%<-------------------------------------------------------------------------
dict {
quota = mysql:/etc/dovecot/dovecot-dict-sql.conf.ext
expire = mysql:/etc/dovecot/dovecot-dict-sql.conf.ext
}
---%<-------------------------------------------------------------------------
See <Dict.txt> for more information, especially about permission issues.
(This file was created from the wiki on 2013-11-24 04:42)
|
