Hello Marcus,

Thursday, June 2, 2011, 1:56:58 PM, you wrote:

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.

Isn't the redundancy suppose to be if you are using prefetch? If not, correct me.

Hope this helps.

Marcus

-- Best regards, Duane mailto:duane@duanemail.org

Hello Marcin,

Friday, June 3, 2011, 4:05:21 AM, you wrote:

Marcus, Duane, thanks for replies! I'll answer in one mail. In your config (Duane) quota is defined as 'dict:user::proxy::quota' it needs dictionary to be configured ( http://wiki2.dovecot.org/Quota/Dict ) if i understood correctly. In config snip from Marcus i can't find definition of "quota", what kind of backend are you using? maildir? Regards!

Sorry. I'm using maildir. And I do have dict set up:

... dict { quota = mysql:/usr/local/etc/dovecot/dovecot-dict-sql.conf.ext } ... service dict { unix_listener dict { group = mail mode = 0600 user = vmail } } ...

-- Best regards, Duane mailto:duane@duanemail.org

On Fri, 2011-06-03 at 15:17 +0200, Marcin Mirosław wrote:

W dniu 03.06.2011 13:51, Timo Sirainen pisze: Helo Timo!

...

You can't define your own SQL queries for dict quota. Besides being able to do such query, there's the problem that when recalculating quota it's done by deleting a row and inserting it back. You wouldn't want to do that for your users table. Yes...:) I don't know internals in dovecot, mayby it will be nonsensical what i'll say. Could it be better to set NULL instead delete of row?

The problem is that updates don't work then:

mails=> insert into foo values (null); mails=> update foo set bar=bar+1; UPDATE 1 mails=> select * from foo; bar

(1 row)

The whole problem is how to atomically increase/decrease a value in SQL, when the row may or may not exist.

Of course it could be assumed that the row always exists and this would become a lot simpler. Hmm. That would basically require a whole new dict backend that used different SQL queries.

Or let user can define own queries e.g.: DICT_QUOTA_DEL= ... DICT_QUOTA_INSERT= DICT_QUOTA_OTHER_ACTIONS=

It's not that simple.