This documentation is for Dovecot v2.x, see wiki1 for v1.x documentation.
Differences between revisions 20 and 49 (spanning 29 versions)
Revision 20 as of 2009-03-15 22:35:21
Size: 4932
Editor: localhost
Comment: converted to 1.6 markup
Revision 49 as of 2017-04-12 09:00:54
Size: 5973
Editor: TimoSirainen
Comment:
Deletions are marked like this. Additions are marked like this.
Line 3: Line 3:
The ''dictionary'' quota backend supports both '''storage''' and '''messages''' quota limits. The current quota is kept in a dictionary. The available dictionaries are: '''NOTE:''' Using the [[Quota/Count|count]] backend (possibly together with [[Plugins/QuotaClone|quota_clone]] plugin) is now preferred to using this backend. It has less of a chance of quota usage becoming wrong.
Line 5: Line 5:
 * MySQL (v1.0+)
 * PostgreSQL (v1.1.2+)
 * Flat file (v1.2.alpha3+)
The ''dictionary'' quota backend supports both '''storage''' and '''messages''' quota limits. The current quota is kept in the specified [[Dictionary|dictionary]]. The available dictionaries include:
Line 9: Line 7:
The plugin parameter format is:  * SQL
 * Redis
 * Flat files

See [[Dictionary]] for full description of the available backends.

The quota root format is:
Line 12: Line 16:
# v1.0:
quota = dict:<quota limits> <dictionary URI>
# v1.1+:
quota = dict:<quota root name>:<user name>:<dictionary URI>
quota = dict:<quota root name>:<username>[:<option>[...]]:<dictionary URI>
Line 17: Line 18:
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.
Line 18: Line 20:
If user name is left empty, the logged in username is used (this is probably what you want). The supported options are:
Line 20: Line 22:
== v1.0 & v1.1 ==  * 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.
 * hidden: Hide the quota root from IMAP GETQUOTA* commands.
 * no-unset: When recalculating quota, don't unset the quota first. This is needed if you wish to store the quota usage among other data in the same SQL row - otherwise the entire row could get deleted. Note that the unset is required with PostgreSQL or the merge_quota() trigger doesn't work correctly. (v2.2.20+)
Line 22: Line 28:
Example: 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: By default 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. This can be avoided with the "no-unset" parameter.

== Examples ==

=== Simple per-user flat file ===

This will create one quota-accounting file for each user.

The Dovecot user process (imap, pop3 or lda) needs write access to this file, so %h or mail_location are good candidates to store it.

'''Warning''': if a user has shell or file access to this location, he can mangle his quota file, thus overcoming his quota limit by lying about his used capacity.
Line 25: Line 43:
dict {
  quotadict = mysql:/etc/dovecot-dict-quota.conf
}
Line 30: Line 44:
  # v1.0: 10MB and 1000 messages quota limit
  quota = dict:storage=10240:messages=1000 proxy::quotadict

  # v1.1 + SQL:
  quota = dict:user::proxy::quotadict
  quota = dict:User quota::file:%h/mdbox/dovecot-quota
Line 39: Line 49:
The above example uses dictionary proxy process (see below), because SQL libraries aren't linked to all Dovecot binaries.

Example `dovecot-dict-quota.conf`:
=== Server-based dictionaries ===
Line 44: Line 52:
# v1.0 and v1.1 only - v1.2 has different configuration
connect = host=localhost dbname=mails user=sqluser password=sqlpass
table = quota
select_field = current
where_field = path
username_field = username }}}

Create the table like this:

{{{
create table quota (
  username varchar(255) not null,
  path varchar(100) not null,
  current integer,
  primary key (username, path)
); }}}

== v1.2+ ==

{{{
dict {
  quotadict = mysql:/etc/dovecot-dict-sql.conf
}
Line 69: Line 53:
  # v1.2 + SQL:
  quota = dict:user::proxy::quotadict
  # v1.2 + file:
  quota = dict:user::file:%h/Maildir/dovecot-quota
  # 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/
Line 76: Line 60:
dict {
  sqlquota = mysql:/etc/dovecot/dovecot-dict-sql.conf.ext
}
Line 77: Line 64:
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.
Line 78: Line 66:
The above SQL example uses dictionary proxy process (see below), because SQL libraries aren't linked to all Dovecot binaries. The file example accesses the file directly.

Example {{{dovecot-dict-sql.conf}}}:
Example {{{dovecot-dict-sql.conf.ext}}}:
Line 83: Line 69:
# v1.2+ only:
Line 98: Line 83:
Create the table like this:
Line 99: Line 85:
Create the table like this:
Line 108: Line 93:
MySQL uses the following queries to update the quota. You need suitable privileges.
Line 109: Line 95:
If you're using PostgreSQL, you'll need a trigger (v1.2.beta1+): {{{
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 SQLite, then take a look at the trigger in this post: http://dovecot.org/pipermail/dovecot/2013-July/091421.html

If you're using PostgreSQL, you'll need a trigger:
Line 133: Line 128:
 INSERT INTO quota (bytes, messages, username)
  VALUES (NEW.bytes, NULL, NEW.username);
        INSERT INTO quota (bytes, messages, username)
         VALUES (NEW.bytes, NULL, NEW.username);
Line 136: Line 131:
 INSERT INTO quota (bytes, messages, username)
  VALUES (NEW.bytes, -NEW.messages, NEW.username);
        INSERT INTO quota (bytes, messages, username)
         VALUES (NEW.bytes, -NEW.messages, NEW.username);
Line 150: Line 145:

== v1.0 Inaccuracy problems ==

With Dovecot v1.1+ quota is tracked accurately. With v1.0 you may have a problem:

If two IMAP clients do an expunge at the same time, the quota is reduced twice as much. Maildir++ backend also has the same problem, but it's not that big of a problem with it because it recalculates the quota once in a while anyway. Dict quota is recalculated only if the quota goes below zero (v1.0.rc30+ only).

So either you'll have to trust your users not to abuse this problem, or you could create a nightly cronjob to delete all rows from the SQL quota table to force a daily recalculation. The recalculation will of course slow down the server.
Line 160: Line 146:
Line 167: Line 152:
  quota = mysql:/etc/dovecot-dict-quota.conf
  expire = mysql:/etc/dovecot-dict-expire.conf
  quota = mysql:/etc/dovecot/dovecot-dict-sql.conf.ext
  expire = mysql:/etc/dovecot/dovecot-dict-sql.conf.ext
Line 171: Line 156:
See [[Dict]] for more information, especially about permission issues.

Dictionary quota

NOTE: Using the count backend (possibly together with quota_clone plugin) is now preferred to using this backend. It has less of a chance of quota usage becoming wrong.

The dictionary quota backend supports both storage and messages quota limits. The current quota is kept in the specified dictionary. The available dictionaries include:

  • SQL
  • Redis
  • Flat files

See Dictionary for full description of the available backends.

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.

  • hidden: Hide the quota root from IMAP GETQUOTA* commands.
  • no-unset: When recalculating quota, don't unset the quota first. This is needed if you wish to store the quota usage among other data in the same SQL row - otherwise the entire row could get deleted. Note that the unset is required with PostgreSQL or the merge_quota() trigger doesn't work correctly. (v2.2.20+)

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: By default 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. This can be avoided with the "no-unset" parameter.

Examples

Simple per-user flat file

This will create one quota-accounting file for each user.

The Dovecot user process (imap, pop3 or lda) needs write access to this file, so %h or mail_location are good candidates to store it.

Warning: if a user has shell or file access to this location, he can mangle his quota file, thus overcoming his quota limit by lying about his used capacity.

plugin {
  quota = dict:User quota::file:%h/mdbox/dovecot-quota
  quota_rule = *:storage=10M:messages=1000
}

Server-based dictionaries

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/

  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 SQLite, then take a look at the trigger in this post: http://dovecot.org/pipermail/dovecot/2013-July/091421.html

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 for more information, especially about permission issues.

None: Quota/Dict (last edited 2017-04-12 09:00:54 by TimoSirainen)