[Dovecot] sql : uid, gid, home ignored ?

[Jerrale G]

On 1/29/2011 8:24 PM, Nick Edwards wrote:
> On Sat, Jan 29, 2011 at 9:26 PM, Eric Diaz Fernandez<eric.diaz at skynet.be>wrote:
>
>> Hello,
>>
>> I'm trying to setup a dovecot imap server.
>>
>> version is 2.0.9
>> I've setup a mysql database using:
>>
>> user_query = \
>>     SELECT home, uid, gid, home as userdb_home, uid as userdb_uid, gid as
>> userdb_gid, \
>>     FROM user WHERE username = '%n' AND domain = '%d' AND active = 'Y'
>>
>>
> Your SQL statement is incorrect, try:
>
> user_query =  SELECT userdb_home as home, userdb_uid as uid, userdb_gid as
> gid, \
>     FROM user WHERE username = '%n' AND domain = '%d' AND active = 'Y'
>
>
>
> nick
>

For a more extensive mysql support, implementing ALL possible features 
of dovecot & postfix:

connect = host=localhost user=maildb password=XXXXXXXX dbname=mail
driver = mysql
default_pass_scheme = MD5
password_query = SELECT password FROM mailboxes WHERE username = '%n' 
AND domain = '%d' AND active = '1'

user_query = SELECT CONCAT('/home/mail/', mailboxes.domain, '/', 
mailboxes.username, '/') AS home, CONCAT('/home/mail/', 
mailboxes.domain, '/', mailboxes.username,  '/sieve/', 
mailboxes.username, '.sieve') as sieve, CONCAT('/home/mail/',  
mailboxes.domain, '/', mailboxes.username, '/sieve/') as sieve_dir, 
CONCAT('/home/mail/', \ mailboxes.domain, '/', mailboxes.username, 
'/mail/') AS maildir, 600 AS uid, 600AS gid, CONCAT('*:storage=', 
ROUND(mailboxes.quota/1200), 'M') AS quota_rule, \ 'Trash:ignore' AS 
quota_rule2 FROM mail.mailboxes WHERE username = '%n' AND domain = '%d' 
AND active = '1'

iterate_query = SELECT concat(mailboxes.username, '@', mailboxes.domain) 
AS user, CONCAT('/home/mail/', mailboxes.domain, '/', 
mailboxes.username, '/') AS home, \
CONCAT('/home/mail/', mailboxes.domain, '/', mailboxes.username, 
'/mail/') AS maildir FROM mail.mailboxes

ANY VARIABLE settable in dovecot.conf CAN be set in the mysql query! It 
will also override any set in dovecot.conf. home_dir MUST be set 
dynamically for each user IN ORDER to use ANY of the others, such as 
maildir, sieve, sieve_dir, quota_rule, or any other parameter set under 
plugins{ }. In order to set them, just grab it with SELECT 
tablename.tablefield and append AS variable_name.
Jerrale G.
SC Senior Admin