This documentation is for Dovecot v2.x, see wiki1 for v1.x documentation.
Differences between revisions 7 and 49 (spanning 42 versions)
Revision 7 as of 2007-04-03 04:53:40
Size: 2604
Editor: TimoSirainen
Comment:
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. Currently the only supported dictionary backend is MySQL. '''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:
The plugin parameter format is `dict:<quota limits> <dictionary URI>`. For example: 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:

 * 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.
Line 9: Line 44:
  # 10MB and 1000 messages quota limit
  quota = dict:storage=10240:messages=1000 mysql:/etc/dovecot-dict-quota.conf
} }}}
  quota = dict:User quota::file:%h/mdbox/dovecot-quota
  quota_rule = *:storage=10M:messages=1000
}
}}}
Line 13: Line 49:
However, '''the above example won't really work'''. This is because it would require linking all the binaries with MySQL library, which I didn't really want to do. Currently you'll have to do this via the dictionary proxy (see below). Actually the performance is better that way anyway, and I don't really see a reason not to use it.

Example `dovecot-dict-quota.conf`:
=== Server-based dictionaries ===
Line 18: Line 52:
connect = host=localhost dbname=mails user=mailuser
table = quota
select_field = current
where_field = path
username_field = username }}}
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/
Line 24: Line 58:
  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
}
}}}
Line 27: Line 86:
create table quota (
  username varchar(255) not null,
  path varchar(255) not null,
  current integer,
  primary key (username, path)
); }}}
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.
Line 34: Line 95:
== Inaccuracy problems == {{{
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
Line 36: Line 103:
Quota plugin doesn't currently track expunges entirely correctly. If two IMAP clients do an expunge at the same time, the quota is reduced twice as much. The other quota backends have the same problem, but it's not that big of a problem with them because they recalculate the quota once in a while anyway. Dict quota is recalculated only if the quota goes below zero. If you're using PostgreSQL, you'll need a trigger:
Line 38: Line 105:
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. {{{
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;
Line 40: Line 118:
This problem will be fixed completely in later Dovecot versions.   LOOP
    UPDATE quota SET bytes = bytes + NEW.bytes,
      messages = messages + NEW.messages
      WHERE username = NEW.username;
    IF found THEN
      RETURN NULL;
    END IF;
Line 42: Line 126:
    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();
}}}
Line 43: Line 146:
Line 46: Line 148:
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: 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:
Line 50: Line 152:
  quotadict = mysql:/etc/dovecot-dict-quota.conf
} }}}

Example quota plugin configuration for this:

{{{
plugin {
  quota = dict:storage=10240:messages=1000 proxy::quotadict
} }}}
  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.

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)