[Dovecot] domain quota in sql
Hi, I have a problem with setting up quota usage tracking for domains. Problem is, that domain quota is always based on only one user in domain, the last one on whom behalf any operation was performed. To recreate this situation, one has only to perform: doveadm quota recalc -u USER on a user, and the domain quota takes the value of this user quota.
Have anyone a clue what could cause this problem?
Quota tracking is set up both for users and domains. For users quota tracking is working fine, data is saved into dbase without problems. We are using dovecot 2.0.15. Quota enforcing (rules) is NOT a part of the problem.
Important parts of config:
dict { quota = mysql:/etc/dovecot/dovecot-dict-used-quota.conf.ext quota2 = mysql:/etc/dovecot/dovecot-dict-used-quota-domain.conf.ext }
plugin { quota = dict:User quota::proxy::quota quota2 = dict:Domain quota:%d:proxy::quota2 }
/etc/dovecot/dovecot-dict-used-quota.conf.ext map { pattern = priv/quota/storage table = used_quota username_field = username value_field = bytes } map { pattern = priv/quota/messages table = used_quota username_field = username value_field = messages }
/etc/dovecot/dovecot-dict-used-quota-domain.conf.ext map { pattern = priv/quota/storage table = used_quota_domain username_field = domain value_field = bytes } map { pattern = priv/quota/messages table = used_quota_domain username_field = domain value_field = messages }
-- Adam Szpakowski
On 14.2.2012, at 23.29, Adam Szpakowski wrote:
Domain-quota support in Dovecot is only partial. Recalculating domain quota would require more code. Last time when someone asked about this I wasn't even sure how it could be implemented, but nowadays it actually would be possible to do with:
- set domain's quota to 0
- iterate *@domain users via userdb
- for each user get the user's quota and add it to domain quota
On 15.2.2012, at 3.54, Timo Sirainen wrote:
Domain-quota support in Dovecot is only partial. Recalculating domain quota would require more code. Last time when someone asked about this I wasn't even sure how it could be implemented, but nowadays it actually would be possible to do with:
Oh, and you could actually already do this with a doveadm script:
update .. | mysql
doveadm user '*@domain'
doveadm quota get -u $user | some grep+sed magic | mysql
Of course it would be nice if Dovecot internally supported this.
On 15.02.2012 02:55, Timo Sirainen wrote:
Is there a way to add domainname as another collumn in dbase? This will simplify the SQL query and the GROUP BY could be used.
/usr/share/doc/dovecot-core/example-config/conf.d/90-quota.conf:
Multiple quota roots are also possible, for example this gives each user
their own 100MB quota and one shared 1GB quota within the domain:
plugin { #quota = dict:user::proxy::quota #quota2 = dict:domain:%d:proxy::quota_domain }
Also on dovecot2 wiki we can see (http://wiki2.dovecot.org/Quota/Dict):
"If username is left empty, the logged in username is used (this is typically what you want). Another useful username is '%d' for supporting domain-wide quotas."
-- Adam Szpakowski
Am 15.02.2012 08:26, schrieb Adam Szpakowski:
Hi i am not sure what are trying to goal with domain quota, if you setup your accounts i.e with postfixadmin etc then its up to the gui logic , stopping postmasters to setup more accounts ( with quotas ) as you ( the superadmin ) want.
For everything else user quotas usally are enough
thinkable maybe calculation of all users quotas in dict from one domain in a sql postfix query table to stop deliver in more mail, but i dont think this makes real sense
sorry if i missunderstand your question..
Best Regards
MfG Robert Schetterer
Germany/Munich/Bavaria
Am 15.02.2012 09:07, schrieb Adam Szpakowski:
as workaround it should be possible to calculate filllevel by domain with some bash script and cron sending an info mail to you might be not exact, but enough to see whats going on ( depends on your real domain/mailbox setup...maildir etc)
i.e like du -sch schetterer.org/ 1,7G schetterer.org/
or
du -sch schetterer.org/* 24K schetterer.org/....@schetterer.org 431M schetterer.org/...@schetterer.org 1,3G schetterer.org/....@schetterer.org 1,7G sum
perhaps the same works with getting it out from sql
-- Best Regards
MfG Robert Schetterer
Germany/Munich/Bavaria
On 15.02.2012 09:19, Robert Schetterer wrote:
Good enough solution will be to have an additional column in db table with domain for each quota record. This will allow us to do something like this:
SELECT domain, sum(bytes) as sum_bytes, sum(messages) as sum_messages FROM used_quota GROUP BY domain
The missing domain info can be updated regularly via some cron script. This could be a workable solution.
-- Adam Szpakowski
Hi Im not an expert in Dovecot but what prohibits you adding a field to the table Dovecot will not use it thats all Im not sure what version of database You are using but postgres,oracle and never versions of mysql have something called views (could be used to hide the additional column from dovecot if necessary) and as far as i remember triggers that could be launched on insert, update to populate that additional column Other solution (not sure if applicable for Your needs) is to enable file system quota without actually setting up any quotas/group quotas (all users from single domain must belong to same group tho) this allows quick disk usage check without excess disk io
On Wed, 15 Feb 2012 09:07:04 +0100, Adam Szpakowski <as@3a.pl> wrote:
-- Pozdrawiam,
Przemysław Orzechowski
Administrator Sieci/Network Administrator
e: przemek.orzechowski@makolab.net
t: +48 42 683 74 96
MakoLab S.A.
ul. Demokratyczna 46, 93-430 Łódź www.makolab.pl
Spółka zarejestrowana w Krajowym Rejestrze Sądowym przez Sąd Rejonowy dla Łodzi - Śródmieścia w Łodzi XX Wydział Krajowego Rejestru Sądowego pod numerem KRS 0000289179. Wysokość kapitału zakładowego wynosi 707 473 PLN. Kapitał zakładowy został wpłacony w całości. NIP 7250015526, REGON 471343117
Wiadomość ta jest przeznaczona jedynie dla osoby lub podmiotu będącego jej adresatem i może zawierać poufne lub uprzywilejowane informacje. Zakazane jest przeglądanie, przesyłanie, rozpowszechnianie lub inne wykorzystywanie tych informacji, jak również podejmowanie działań na ich podstawie, przez osoby lub podmioty inne niż zamierzony adresat. Jeśli otrzymali Państwo tę wiadomość przez pomyłkę, prosimy o poinformowanie nadawcy i usunięcie jej z komputera.
The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please notify the sender and delete the material from your computer.
On Wed, 2012-02-15 at 08:26 +0100, Adam Szpakowski wrote:
Isn't it basically one table scan? To do UPDATE .. WHERE username = '% @domain'
Is there a way to add domainname as another collumn in dbase? This will simplify the SQL query and the GROUP BY could be used.
Not currently. Perhaps for v2.2 dict-sql redesign.
Well, yeah, it kind of works, but not 100% :) Anyway, why is the rescan a problem anyway? Does your domain quota value keep breaking? In normal operation it should work fine.
participants (4)
-
Adam Szpakowski
-
Przemysław Orzechowski
-
Robert Schetterer
-
Timo Sirainen