[Dovecot] Custom sql query for keeping quota with dict-sql

[Duane Hill]

Thursday, June 2, 2011, 9:48:11 AM, Marcin wrote:

> Hello!
> I'd like to setup quota for for virtualusers, i'm using both maildir++
> and mdbox as storage, and i'm using postgresql to keep users.
> I'm keeping all users data in one table:
> [...]
> login text NOT NULL,
> domain text,
> password text,
> quota_mb integer NOT NULL DEFAULT 0
> [... other columns ...]

> i'd like to keep present quota in such two columns:
> quota_dict_bytes integer NOT NULL DEFAULT 0,
> quota_dict_messages integer NOT NULL DEFAULT 0

> I read wiki a little and it looks i can't define own sql queries, they
> appear to be hardcoded. Am i wrong? Is it possible to write own queries
> to get/set quota using such table schema? Or maybe there is diffrent way
> to track quota for diffrent storages?

> Regards!
> Marcin

I'm using mysql here with everything being served out of a single
users table. I would think you should be able to do that from a pgsql
table as well.

Here's my password_query and user_query:

password_query = \
  SELECT username AS user, password, \
         '/var/mail/mailhost/%d/%n' AS userdb_home, \
         'maildir:/var/mail/mailhost/%d/%n' AS userdb_mail, \
         1002 AS userdb_uid, 6 AS userdb_gid, \
         'dict:user::proxy::quota' AS userdb_quota, \
         CONCAT('*:bytes=',max_bytes,':messages=',max_msgs) AS userdb_quota_rule, \
         'Trash:bytes=+32M' AS userdb_quota_rule2, \
         'Spam:bytes=+32M' AS userdb_quota_rule3 \
  FROM users \
  WHERE username = '%u' AND active = 1

user_query = \
  SELECT '/var/mail/mailhost/%d/%n' AS home, \
         'maildir:/var/mail/mailhost/%d/%n' AS mail, \
         1002 AS uid, 6 AS gid, \
         'dict:user::proxy::quota' AS quota, \
         CONCAT('*:bytes=',max_bytes,':messages=',max_msgs) AS quota_rule, \
         'Trash:bytes=+32M' AS quota_rule2, \
         'Spam:bytes=+32M' AS quota_rule3 \
  FROM users \
  WHERE username = '%u' AND active = 1

-- 
Best regards,
 Duane                            mailto:duane at duanemail.org