dovecot - Sep 2006 - [SOLVED] quota with: postfixadmin, postfix, mysql & dovecot

Hi,

I'm quite new to dovecot but already quite satisfied. Your documentation
in the wiki is great but on quota + mysql it is not very clear. Because
of the previously mentioned I have got the following note to make which
you will hopefully use to adjust your wiki documentation on
per-user-quota + mysql. I've searched your mail archive as good as
possible and found no messages on this. If already messages have been
exactly on this topic I want to apologize for sending this one.

The notes I want to make is about this page: http://wiki.dovecot.org/Quota

I'm using postfix + postfixadmin & mysql and dovecot  (previously I used
courier-imap instead of dovecot).

The problems I ran in to was with setting up the per-user quota, as the
explanation at the wiki is quite vague.

For instance, the dict plugin is used to keep the quota counters in the
mysql backend but only one single line shows how to set-up a per user quota.

Setting up a per user quota where the max-quota is stored in mysql
should be made making use of this setting:

=========user_query = SELECT home, uid, gid, 'dirsize:storage=' ||
quota_bytes AS
quota FROM users WHERE userid = '%u'
=========

nowhere is mentioned that this should also! be set:

==========protocol imap {
mail_plugins = quota imap_quota
}
protocol pop3 {
mail_plugins = quota
}
==========

the second point I want to make is that the mysqlquery is incorrect as
|| does not do the job of joining the two strings. Secondly is dovecot
making use of kilobytes instead of bytes like postfix (and postfixadmin)
does.

For this I have found the following solution:

=========
user_query = SELECT maildir, 125 AS uid, 125 AS gid,\
 CONCAT('dirsize:storage=', ROUND( mailbox.quota / 1024 ) ) AS quota \
 FROM mailbox WHERE username = '%u' AND active = '1'

=========
Here I select the quota field from the mailbox table. This is then
divided by 1024 (1Kbyte) and ROUNDED off in order to get rid of the .00
that results from this division.


Greetz,

Peter Veerman
pveerman NO-SPAM at NO-SPAM hackwerk DOT net

Peter Veerman wrote:

> The notes I want to make is about this page: http:// 
> wiki.dovecot.org/Quota
[...]
> nowhere is mentioned that this should also! be set:
>
> ==========> protocol imap {
> mail_plugins = quota imap_quota
> }
> protocol pop3 {
> mail_plugins = quota
> }
> ==========
To quote the second paragraph of http://wiki.dovecot.org/Quota:

--->8---

The quota backends are implemented as plugins for Dovecot. There is  
also a plugin named "quota" which does the actual work. For reporting
quota information via IMAP, there is the "imap_quota" plugin. Usually
you'd enable these by adding them to the mail_plugins settings in  
config file:

protocol imap {
   mail_plugins = quota imap_quota
}
protocol pop3 {
   mail_plugins = quota
}
# In case you're using Dovecot-LDA:
protocol lda {
   mail_plugins = quota
}

--->8---

> the second point I want to make is that the mysqlquery is incorrect as
> || does not do the job of joining the two strings.
The page solely states this as "an example using a SQL-based user  
database". No word about mysql.

You just ran into the problem that mysql isn't following the  
standards concerning the ||-operator (as it is by the way documented  
in the mysql manuals).
But maybe there should simply be a small note that this doesn't work  
with mysql because it is broken in this part and that one should use  
concat instead.
> Secondly is dovecot
> making use of kilobytes instead of bytes like postfix (and  
> postfixadmin)
> does.
Which is also documented on the wiki page. But I agree - this  
behaviour is in contrast to about all other programs and IMHO this is  
very annoying.

> user_query = SELECT maildir, 125 AS uid, 125 AS gid,\
>  CONCAT('dirsize:storage=', ROUND( mailbox.quota / 1024 ) ) AS
quota \
>  FROM mailbox WHERE username = '%u' AND active = '1'
I solved this in a similar way using views with postgresql.

But the real problem IMHO is not bytes vs. kilobytes (solution is  
very simple as you showed above) but dovecot treating '0' values in  
the database literally. All the other programs take '0' as unlimited  
quota and therefore it's very easy to experience an unpleasant  
surprise with dovecot-lda.

If you run postfixadmin as mentioned above you will probably  
completely lock out all your users with unlimited quota. I could be  
wrong here but IIRC postfixadmin uses '0' for unlimited/unset quota,  
too.


Marcus