domain quota in sql

classic Classic list List threaded Threaded
11 messages Options
Reply | Threaded
Open this post in threaded view
|

domain quota in sql

Adam Szpakowski
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
Reply | Threaded
Open this post in threaded view
|

Re: domain quota in sql

Timo Sirainen
On 14.2.2012, at 23.29, Adam Szpakowski wrote:

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

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

Reply | Threaded
Open this post in threaded view
|

Re: domain quota in sql

Timo Sirainen
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:

> - set domain's quota to 0

update .. | mysql

> - iterate *@domain users via userdb

doveadm user '*@domain'

> - for each user get the user's quota and add it to domain quota

doveadm quota get -u $user | some grep+sed magic | mysql

Of course it would be nice if Dovecot internally supported this.
Reply | Threaded
Open this post in threaded view
|

Re: domain quota in sql

Adam Szpakowski
On 15.02.2012 02:55, Timo Sirainen wrote:
> 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:
[cut]
I'm using something like this, but directly on the dbase level.
Unfortunately such operation is quite db intensive, so I'm looking for a
simpler solution.

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.

> Of course it would be nice if Dovecot internally supported this.
I was under impression, that dovecot does support this. In examples one
can see:

/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
Reply | Threaded
Open this post in threaded view
|

Re: domain quota in sql

Robert Schetterer
Am 15.02.2012 08:26, schrieb Adam Szpakowski:

> On 15.02.2012 02:55, Timo Sirainen wrote:
>> 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:
> [cut]
> I'm using something like this, but directly on the dbase level.
> Unfortunately such operation is quite db intensive, so I'm looking for a
> simpler solution.
>
> 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.
>
>> Of course it would be nice if Dovecot internally supported this.
> I was under impression, that dovecot does support this. In examples one
> can see:
>
> /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."
>
>

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
Reply | Threaded
Open this post in threaded view
|

Re: domain quota in sql

Adam Szpakowski
On 15.02.2012 08:47, Robert Schetterer wrote:
> 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.
My fault, I should add some "big picture" info.
We are using admin panel based on iRedMail Panel, and want to show quota
usage on the domain level.
Quota enforcing is only on user level and this part works nicely.
What I need is to have in dbase live data which allows me to get quota
usage on the domain level to show it in the admin panel.
The problem is, that this operation must not by db intensive. Getting
entire db and the processing it with some script is fine for daily
reports but not for "live" view.

My example with doveadm for quota recalc was only a way to show how the
problem can be triggered. We are not using doveadm for this purpose.

> 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
In future the quota enforcement on domain level could be a nice add-on,
but for now it is not so important for us.

--
Adam Szpakowski
Reply | Threaded
Open this post in threaded view
|

Re: domain quota in sql

Robert Schetterer
Am 15.02.2012 09:07, schrieb Adam Szpakowski:

> On 15.02.2012 08:47, Robert Schetterer wrote:
>> 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.
> My fault, I should add some "big picture" info.
> We are using admin panel based on iRedMail Panel, and want to show quota
> usage on the domain level.
> Quota enforcing is only on user level and this part works nicely.
> What I need is to have in dbase live data which allows me to get quota
> usage on the domain level to show it in the admin panel.
> The problem is, that this operation must not by db intensive. Getting
> entire db and the processing it with some script is fine for daily
> reports but not for "live" view.

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


>
> My example with doveadm for quota recalc was only a way to show how the
> problem can be triggered. We are not using doveadm for this purpose.
>
>> 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
> In future the quota enforcement on domain level could be a nice add-on,
> but for now it is not so important for us.
>


--
Best Regards

MfG Robert Schetterer

Germany/Munich/Bavaria
Reply | Threaded
Open this post in threaded view
|

Re: domain quota in sql

Adam Szpakowski
On 15.02.2012 09:19, Robert Schetterer wrote:
> 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)
[cut]
I'm affraid that this is not a viable option. It should be done quite
often to achieve "live data" feel and the amount of I/O to do so on
storage/filesystem level is staggering.

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

Reply | Threaded
Open this post in threaded view
|

Re: domain quota in sql

Przemysław Orzechowski
In reply to this post by 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 <[hidden email]> wrote:
> On 15.02.2012 08:47, Robert Schetterer wrote:
>> 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.
> My fault, I should add some "big picture" info.
> We are using admin panel based on iRedMail Panel, and want to show quota

> usage on the domain level.
> Quota enforcing is only on user level and this part works nicely.
> What I need is to have in dbase live data which allows me to get quota
> usage on the domain level to show it in the admin panel.
> The problem is, that this operation must not by db intensive. Getting
> entire db and the processing it with some script is fine for daily
> reports but not for "live" view.
>
> My example with doveadm for quota recalc was only a way to show how the
> problem can be triggered. We are not using doveadm for this purpose.
>
>> 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
> In future the quota enforcement on domain level could be a nice add-on,
> but for now it is not so important for us.

--
Pozdrawiam,

Przemysław Orzechowski

Administrator Sieci/Network Administrator

e: [hidden email]

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.
Reply | Threaded
Open this post in threaded view
|

Re: domain quota in sql

Timo Sirainen
In reply to this post by Adam Szpakowski
On Wed, 2012-02-15 at 08:26 +0100, Adam Szpakowski wrote:
> On 15.02.2012 02:55, Timo Sirainen wrote:
> > 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:
> [cut]
> I'm using something like this, but directly on the dbase level.
> Unfortunately such operation is quite db intensive, so I'm looking for a
> simpler solution.

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.

> > Of course it would be nice if Dovecot internally supported this.
> I was under impression, that dovecot does support this. In examples one
> can see:
>
> /usr/share/doc/dovecot-core/example-config/conf.d/90-quota.conf
..
> Also on dovecot2 wiki we can see (http://wiki2.dovecot.org/Quota/Dict):

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.

Reply | Threaded
Open this post in threaded view
|

Re: domain quota in sql

Adam Szpakowski
On 15.02.2012 14:05, 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.
> Not currently. Perhaps for v2.2 dict-sql redesign.
It will be great feature.
> 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.
Domain quota gathering based on config examples works "funny". Its the
best description I can get (yes, very descriptive ;) ).
There are both entries for users (user@domain) and domains. It is not
making sense for me, so I'm assuming that its the missing percentage of
the working part ;)

Tu sum-up the thread. I went for updating periodically the basic quota
table with missing domain names. Working great.
Thanks all for suggestions.
--
Adam Szpakowski