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

[Marcus Jodorf]

Duane Hill wrote:

Am 02.06.2011 um 18:40 schrieb Duane Hill:

>> 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?

No problem.

> 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.
[...]

> 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 = 


As an example I do it with postgres like this:


I create a view with suitable data from my different tables in postgres:

-----------------------

CREATE VIEW dovecot_user  AS

   SELECT users.userid, users.domain, users.uid, users.gid,
   '*:storage='||ROUND(users.quota/1024) AS quota_rule,
   '/home/vhosts/'||users.domain||'/'||users.userid  AS home,
   domains.mailhost, users.active FROM users, domains
   WHERE users.domain = domains.domain;

grant select on dovecot_user to imapd;

-----------------------

And then I just  tell dovecot to use it accordingly:

password_query= SELECT password FROM users WHERE userid = '%n' AND domain = '%d'
user_query = SELECT home, uid, gid, quota_rule FROM dovecot_user  WHERE userid = '%n' AND domain = '%d' AND mailhost='blah'


How your table looks in detail is not really important for setting up quotas.
Basically all you need to do is to generate  quota_rule fields in your query and see to it that it delivers suitable data for the user_query.

Duane did this in his example above by using a little more lengthy queries (BTW: Is there a reason for the redundancy in the password_query?) and I do this by using a database view and keeping the dovecot user_query itself a little simpler.



Hope this helps.

Marcus