[Dovecot] [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
On Sep 30, 2006, at 7:47pm, Marcus Jodorf wrote:
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.
Although I agree i've seen many systems use zero for "unlimited"
wouldn't it be a bad assumption on dovecot's part? I would assume
there be a boolean that defines if the user has a quota.
For awhile I had a database where zero did mean 'unset' but that in
turn didn't mean 'unlimited' but rather was an indication that that
user should follow the domain default quota. I made a rather nice
query that evaluated and selected the greater of the domain or user
quota. But that's neither here nor there.
I would think a quota returned to dovecot as a zero should really
mean the account can't hold mail. If you want unlimited don't return
a quota value at all, which would cause dovecot to fall back on it's
default (which is easily defined as no quota at all).
FYI: as far as the bytes and kilobytes issue is concerned, my
database has an integer for megabytes :P I had to multiply instead of
divide.
.tim
On Sep 30, 2006, at 7:47pm, Marcus Jodorf wrote:
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.
Although I agree i've seen many systems use zero for "unlimited"
wouldn't it be a bad assumption on dovecot's part? I would assume
there be a boolean that defines if the user has a quota.
For awhile I had a database where zero did mean 'unset' but that in
turn didn't mean 'unlimited' but rather was an indication that that
user should follow the domain default quota. I made a rather nice
query that evaluated and selected the greater of the domain or user
quota. But that's neither here nor there.
I would think a quota returned to dovecot as a zero should really
mean the account can't hold mail. If you want unlimited don't return
a quota value at all, which would cause dovecot to fall back on it's
default (which is easily defined as no quota at all).
FYI: as far as the bytes and kilobytes issue is concerned, my
database has an integer for megabytes :P I had to multiply instead of
divide.
.tim
Marcus Jodorf wrote:
CONCAT('dirsize:storage=', ROUND( mailbox.quota / 1024 ) ) AS quota
FROM mailbox WHERE username = '%u' AND active = '1' user_query = SELECT maildir, 125 AS uid, 125 AS gid,\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.
Luckily i don't have any users with unlimited quota so i did not ran in to this problem but, is there any solution to solve this in the (My)SQL query?
Peter
Peter Veerman wrote:
Marcus Jodorf wrote:
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.
Luckily i don't have any users with unlimited quota so i did not ran in to this problem but, is there any solution to solve this in the (My)SQL query?
Peter
You could use an IF statement in your SQL query to return NULL if = 0, like:
IF(mailbox.quota = 0, NULL, ROUND( mailbox.quota / 1024 ) )) AS quota
Peter Fern wrote:
Luckily i don't have any users with unlimited quota so i did not ran in to this problem but, is there any solution to solve this in the (My)SQL query? You could use an IF statement in your SQL query to return NULL if = 0, like:
IF(mailbox.quota = 0, NULL, ROUND( mailbox.quota / 1024 ) )) AS quota Okay Thanks Peter!
In the meantime I found out that the quota with dirsize and the above solution to have a per user quota is causing extraordinary performance problems. I know I know :) it is mentioned in the Wiki that it's a little bit slower and probably the configuration of my server won't help too:
Sun Enterprice 250 UltraSparc-II Processor (400.00 MHz CPU) 512 MB RAM (4 disk) software raid5 for data storage
But hey, Courier-IMAP here outperforms Dovecot and that's not what I was looking for ;-)
So i'm looking for the fastest alternative to dirsize that still allows me to have my per-user quota. I think that the quota dict is what i'm looking for. The problem is that i am not sure if i can use it without making use of the dovecot LDA. As I am not using the dovecot LDA nor planning to use it as i'm happy with my current LDA (postfix's).
I really am sorry for making this question as i know it might sound quite dumb for all the experienced Dovecot users but all I found the functioning of dict is this:
---->8---- The /dictionary/ quota backend supports both *storage* and *messages* quota limits. The current quota is kept in a dictionary. Currently (as of 1.0.beta9 and later), the only supported dictionary backend is MySQL. ---->8---- Source: http://wiki.dovecot.org/Quota/Dict
Peter
On Oct 1, 2006, at 7:17am, Peter Veerman wrote:
So i'm looking for the fastest alternative to dirsize that still
allows me to have my per-user quota. I think that the quota dict is what i'm looking for. The problem is that i am not sure if i can use it without making use of the dovecot LDA. As I am not using the dovecot LDA nor planning to use it as i'm happy with my current LDA (postfix's).
To tell you the truth Peter, I too was confused by the wiki
descriptions on how to set up quota -- and perhaps i don't have it
set up correctly as a result. However, this is what i have configured
and it appears to be working:
in dovecot.conf I set a default quota amount of only 10 MB (mostly to
make it obvious if i've screwed up) and i used the maildir method for
this since it seemed recommended method of tracking quota usage:
plugin { quota = maildir:storage=10240 }
and in my dovecot-sql.conf file, i return the quota from the user
database which appears to successfully override the quota value from
the standard conf file:
user_query = SELECT pop AS home, CONCAT('maildir:', smtp) AS mail,
uid, gid, CONCAT('maildir:storage=', quota * 1024) AS quota FROM
users WHERE username = '%u'
the import part of this obviuosly being: CONCAT('maildir:storage=', quota * 1024) AS quota
As for all those wiki statements about using dict as a backend that
supports mysql -- I believe (and i'm guessing here) that that is
reference to if you want to use mysql to be the storage method for
keeping track of your current quota usage. For example, the maildir++
backend allows the imap server to continually calculate message sizes
and numbers while storing them in a flat human-readable text file in
each imap mailbox. If you wanted to use a dict+mysql solution, the
server would simply retrieve those quota usage numbers from the
database instead.
I think the thing that would really clarify the wiki for me would be
if we had two defined terms. "Quota Limit" as being the generally
static value for a domain or user that sets the maximum data size or
message count. "Quota Status" would be the values associated with
your users current usage. In the end, you need to decide where the
Quota Limit will be stored and retrieved, but also need to define how
the Quota Status will be calculated/retrieved, and stored (if at all).
I'm sure there are others on the list that understand the quota
backends better than I... but this is what i've gleaned so far.
.tim
participants (6)
-
Marcus Jodorf
-
Peter Fern
-
Peter Veerman
-
Timothy Benjamin Martin
-
Timothy Martin
-
Timothy Martin