Duane Hill wrote:
Am 02.06.2011 um 18:40 schrieb Duane Hill:
>> I read wiki a little and it looks i can't define own sql queries, they
>> appear to be hardcoded. Am i wrong? Is it possible to write own queries
>> to get/set quota using such table schema?
No problem.
> I'm using mysql here with everything being served out of a single
> users table. I would think you should be able to do that from a pgsql
> table as well.
[...]
> user_query = \
> SELECT '/var/mail/mailhost/%d/%n' AS home, \
> 'maildir:/var/mail/mailhost/%d/%n' AS mail, \
> 1002 AS uid, 6 AS gid, \
> 'dict:user::proxy::quota' AS quota, \
> CONCAT('*:bytes=',max_bytes,':messages=',max_msgs) AS quota_rule, \
> 'Trash:bytes=+32M' AS quota_rule2, \
> 'Spam:bytes=+32M' AS quota_rule3 \
> FROM users \
> WHERE username = '%u' AND active =
As an example I do it with postgres like this:
I create a view with suitable data from my different tables in postgres:
-----------------------
CREATE VIEW dovecot_user AS
SELECT users.userid, users.domain, users.uid, users.gid,
'*:storage='||ROUND(users.quota/1024) AS quota_rule,
'/home/vhosts/'||users.domain||'/'||users.userid AS home,
domains.mailhost, users.active FROM users, domains
WHERE users.domain = domains.domain;
grant select on dovecot_user to imapd;
-----------------------
And then I just tell dovecot to use it accordingly:
password_query= SELECT password FROM users WHERE userid = '%n' AND domain = '%d'
user_query = SELECT home, uid, gid, quota_rule FROM dovecot_user WHERE userid
= '%n' AND domain = '%d' AND mailhost='blah'
How your table looks in detail is not really important for setting up quotas.
Basically all you need to do is to generate quota_rule fields in your query
and see to it that it delivers suitable data for the user_query.
Duane did this in his example above by using a little more lengthy queries
(BTW: Is there a reason for the redundancy in the password_query?) and I do
this by using a database view and keeping the dovecot user_query itself a
little simpler.
Hope this helps.
Marcus
