dovecot - Aug 2012 - postfix mysql quota map to dovecot dict in mysql

currently dovecot dict is


CREATE TABLE IF NOT EXISTS `quota` (
   `username` varchar(100) COLLATE latin1_general_ci NOT NULL,
   `bytes` bigint(20) NOT NULL DEFAULT '0',
   `messages` int(11) NOT NULL DEFAULT '0',
   PRIMARY KEY (`username`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;


there is not field that says how much quota each user have, so one cant 
make a query on how much its left pr user

how can i resolve this in dovecot dict ?

i like to do an postfix mysql map to temp reject if quota is out of 
limit

-- 
Benny Pedersen

Am 10.08.2012 13:03, schrieb Benny Pedersen:
> 
> currently dovecot dict is
> 
> 
> CREATE TABLE IF NOT EXISTS `quota` (
>   `username` varchar(100) COLLATE latin1_general_ci NOT NULL,
>   `bytes` bigint(20) NOT NULL DEFAULT '0',
>   `messages` int(11) NOT NULL DEFAULT '0',
>   PRIMARY KEY (`username`)
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
> 
> 
> there is not field that says how much quota each user have, so one cant
> make a query on how much its left pr user
> 
> how can i resolve this in dovecot dict ?
> 
> i like to do an postfix mysql map to temp reject if quota is out of limit
> 
you may use ideas
out of postfixadmin mysql layout
-- 
Best Regards
MfG Robert Schetterer

Den 2012-08-10 13:03, Benny Pedersen skrev:

if others need something like this:

CREATE TABLE IF NOT EXISTS `quota` (
   `username` varchar(100) COLLATE latin1_general_ci NOT NULL,
   `bytes` bigint(20) NOT NULL DEFAULT '0',
   `messages` int(11) NOT NULL DEFAULT '0',
   `quotamax` bigint(20) NOT NULL DEFAULT '0',
   PRIMARY KEY (`username`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

# /etc/postfix/dovecot_quota_maps.cf


user = user
password = password
hosts = localhost
dbname = database
query = SELECT CONCAT('DEFER_IF_PERMIT Quotas Excceded') FROM quota 
WHERE username='%s' AND bytes>=quotamax


# /etc/postfix/main.cf
smtpd_recipient_restrictions   check_recipient_access
mysql:/etc/postfix/dovecot_quota_maps.cf


enjoy

add to wiki if one could do this