[Dovecot] dovecot lda and quota plugin with mysql

[J4]

On 01/17/2011 11:53 AM, J4 wrote:
> On 01/16/2011 08:42 PM, Timo Sirainen wrote:
>> On Thu, 2011-01-13 at 17:22 +0100, J4 wrote:
>>
>>>     I have postfix delivering to dovecot lda (via spamassinassin).  I
>>> have configured the quota plugin, but it does not seem to respect the
>>> details in the quota table in mysql.  The query I have used looks
>>> correct to me, and the user can connect to the database.
>> ..
>>> # Relevant portions of dovecot -n:
>> This output doesn't show a quota_rule. If you don't have any, everyone
>> has unlimited quota.
>>
>>> mysql> select * from quota;
>>> +-----------------------------+-----------+----------+
>>> | username                    | bytes     | messages |
>>> +-----------------------------+-----------+----------+
>>> | root at test.info       |     19524 |       18 |
>>> | bob at test.info     |      9420 |        6 |
>>> | test at klunky.co.uk          |      4662 |        4 |
>>> | bob1 at test.info    |     19284 |       13 |
>>> | bob2 at test.info      | 173906172 |     5702 |
>>> | postmaster at test.info |     10587 |        9 |
>>> | bob3 at test.info  |      3695 |        2 |
>>> +-----------------------------+-----------+----------+
>>> 7 rows in set (0.00 sec)
>> Looks like it's counting the quota, so I guess you're just missing the
>> limits.
> Hi Timo,
> 	Many thanks for getting back to me. This has led me to look more into
> the mailbox and quota tables.
>
> I think the limits are inserted into the mailbox table,
> mysql> select * from mailbox;
> +----------------------------+------------------------------------+----------------------------+-----------------------------+----------+------------+------------------+---------------------+---------------------+--------+
> | username                   | password                           | name
>                       | maildir                     | quota    |
> local_part | domain           | created             | modified
>   | active |
> +----------------------------+------------------------------------+----------------------------+-----------------------------+----------+------------+------------------+---------------------+---------------------+--------+
> | test at test.info    | $1$1xxxxxxxxx | Fred TEST
>               | test.info/test/    | 10240000 | test     | test.info |
> 2011-01-07 17:47:25 | 2011-01-07 17:47:25 |      1 |
>
> +----------------------------+------------------------------------+----------------------------+-----------------------------+----------+------------+------------------+---------------------+---------------------+--------+
>
> ... while the actual usage is inserted into the quota table (as shown in
> the original Email). I think that Dovecot should read the limits from
> mailbox.quota and retrieve the usage from the quota.bytes and
> quota.messages.
>
> Somehow, I have to tell Dovecot to use these values in mailbox.quota.  I
> think that I have to add a reference to the file in the dovecot.conf,
> and add an entry into the appropriate sql.conf file.
>
> Regards, J.
>
I updated the /etc/dovecot/dovecot-sql.conf file to include this modifed
query, that I hoped would check the quota, but this failed.  I think
dovecot simply ignored this.  I wonder if the alias quota_rule makes any
sence to Dovecot as I only see : quota = dict:user::proxy::quotadict 
defined in the dovecot.conf and not quota_rule.


# cat /etc/dovecot/dovecot-sql.conf
password_query = SELECT username as user, password FROM mailbox WHERE
email='%u';
user_query = SELECT 5000 AS uid, 5000 AS gid,'/home/vmail/%d/%n' as
home, concat('*:storage= ', quota) AS quota_rule FROM mailbox WHERE user
= '%n';