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

[Duane Hill]

Hello Marcus,

Thursday, June 2, 2011, 1:56:58 PM, you wrote:


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

Isn't the redundancy suppose to be if you are using prefetch? If not,
correct me.

> Hope this helps.

> Marcus



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