[Dovecot] Quota plugin and SQL
I have read through http://wiki.dovecot.org/Quota/Dict, which suggests a database schema for using quota. For 1.2, this includes a username, bytes and messages.
Is there any way to modify the queries used to split out username into localpart and domain? This would enable me to put the current quota information in my main mailbox table and display it to users in my admin interface.
Or possibly it should be in a separate table for a reason, and I should be look there for the values?
Thanks,
David
On Mar 6, 2010, at 2:12 PM, David wrote:
I have read through http://wiki.dovecot.org/Quota/Dict, which
suggests a database schema for using quota. For 1.2, this includes
a username, bytes and messages.Is there any way to modify the queries used to split out username
into localpart and domain? This would enable me to put the current
quota information in my main mailbox table and display it to users
in my admin interface.Or possibly it should be in a separate table for a reason, and I
should be look there for the values?
Know nothing about the quota plugin as I work in media and don't use
quotes but why not just concat the localpart and domain into an
additional column or query against a view?
// Brad
On 3/6/2010 5:12 PM, David wrote:
I have read through http://wiki.dovecot.org/Quota/Dict, which suggests a database schema for using quota. For 1.2, this includes a username, bytes and messages.
Is there any way to modify the queries used to split out username into localpart and domain? This would enable me to put the current quota information in my main mailbox table and display it to users in my admin interface.
Or possibly it should be in a separate table for a reason, and I should be look there for the values?
Thanks,
David
http://wiki.dovecot.org/Variables
Excerpt:
%u user full username (e.g. user@domain) %n username user part in user@domain, same as %u if there's no domain %d domain domain part in user@domain, empty if user there's no domain
Leeman
On 07/03/2010 03:17, Leeman Strout wrote:
On 3/6/2010 5:12 PM, David wrote:
I have read through http://wiki.dovecot.org/Quota/Dict, which suggests a database schema for using quota. For 1.2, this includes a username, bytes and messages.
Is there any way to modify the queries used to split out username into localpart and domain? This would enable me to put the current quota information in my main mailbox table and display it to users in my admin interface.
Or possibly it should be in a separate table for a reason, and I should be look there for the values?
Thanks, I know about those variables as I use them in the query that selects my authentication details.
However, I cannot see a way to change the actual query that is used by the quota plugin. If the quota plugin uses 1 row per user (as it seems to) then I'd like to integrate it with my authentication table.
Therefore: can I change the SQL queries that the quota plugin uses?
David
On Sat, 2010-03-06 at 22:12 +0000, David wrote:
I have read through http://wiki.dovecot.org/Quota/Dict, which suggests a database schema for using quota. For 1.2, this includes a username, bytes and messages.
Is there any way to modify the queries used to split out username into localpart and domain? This would enable me to put the current quota information in my main mailbox table and display it to users in my admin interface.
Not currently. If you're using PostgreSQL, you could create a view and update-rule though. I don't know if that's possible with MySQL.
On 07/03/2010 09:58, Timo Sirainen wrote:
On Sat, 2010-03-06 at 22:12 +0000, David wrote:
I have read through http://wiki.dovecot.org/Quota/Dict, which suggests a database schema for using quota. For 1.2, this includes a username, bytes and messages.
Is there any way to modify the queries used to split out username into localpart and domain? This would enable me to put the current quota information in my main mailbox table and display it to users in my admin interface.
Not currently. If you're using PostgreSQL, you could create a view and update-rule though. I don't know if that's possible with MySQL.
Thanks Timo. Is that something which could change in a future version?
David
On 03/07/2010 11:02 AM David wrote:
On 07/03/2010 09:58, Timo Sirainen wrote:
On Sat, 2010-03-06 at 22:12 +0000, David wrote: Not currently. If you're using PostgreSQL, you could create a view and update-rule though. I don't know if that's possible with MySQL.
Thanks Timo. Is that something which could change in a future version?
A modification in the quota code would be great. I'm using the UID for my users - not their email addresses, which is stored nowhere only in the quota table.
Mh, don't know how hard it would be modify the code that way, that it would accept a dict configuration like:
map {
pattern = priv/quota/storage
table = quota
username_field = uid
username_var = %{uid}
value_field = bytes
}Regards, Pascal
The trapper recommends today: cafefeed.1006602@localdomain.org
On 03/07/2010 01:53 PM Pascal Volk wrote:
map { pattern = priv/quota/storage table = quota username_field = uid username_var = %{uid} value_field = bytes }
BTW: This also would make it possible to have 'domain quota' (only), when the 'username_var' is set to %L{domain}. That was asked in #dovecot some times.
Regards, Pascal
The trapper recommends today: cafefeed.1006614@localdomain.org
On Sun, 2010-03-07 at 14:49 +0100, Pascal Volk wrote:
On 03/07/2010 01:53 PM Pascal Volk wrote:
map { pattern = priv/quota/storage table = quota username_field = uid username_var = %{uid} value_field = bytes }
BTW: This also would make it possible to have 'domain quota' (only), when the 'username_var' is set to %L{domain}. That was asked in #dovecot some times.
It's already possible to do domain quota. And actually that's how it's also possible to do %{uid} type of quota:
quota = dict:user:%{uid}:proxy::quota
Em 06/03/2010 19:12, David escreveu:
Is there any way to modify the queries used to split out username into localpart and domain? This would enable me to put the current quota information in my main mailbox table and display it to users in my admin interface.
i'm successfully using a PHP script for displaying a graph with current quota information in my admin interface. Is this what you're looking for ? Quota usage is taken directly from IMAP server, through IMAP commands.
http://img707.imageshack.us/img707/9551/quota.jpg
i'm actually using a script based on the one found here:http://lists.horde.org/archives/imp/Week-of-Mon-20040816/038902.html
the original script grabs the current usage from database, and i dont have the information there. So i changed it to use imap_open and grab quota directly from IMAP server. Something like:
Changes would be something like:
function draw_bar($mailbox, $width, $height) { global $imaphost, $usuario, $senha; $mbox = @imap_open($imaphost, $usuario, $senha, OP_HALFOPEN); $q = @imap_get_quotaroot($mbox, 'INBOX'); imap_close($mbox); if ($q) { $taken = $q['usage']; $total = $q['limit']; quota_bar($taken, $total, $width, $height); } }
that's pretty straightforward when your passwords are stored in clear-text. In my cases password is SSHA256-hashed before storing, so i had to configure a masteruse on dovecot to acchieve that.
http://wiki.dovecot.org/Authentication/MasterUsers
so i have something:$user = $realuser . "*mymasteruser@local" $password = "mypasteruserpassword"
--
Atenciosamente / Sincerily,
Leonardo Rodrigues
Solutti Tecnologia
http://www.solutti.com.br
Minha armadilha de SPAM, NÃO mandem email
gertrudes@solutti.com.br
My SPAMTRAP, do not email it
participants (6)
-
Bradley Giesbrecht
-
David
-
Leeman Strout
-
Leonardo Rodrigues
-
Pascal Volk
-
Timo Sirainen