|
From: <ju...@kl...> - 2011-01-07 14:38:42
|
On Fri, 07 Jan 2011 14:32:50 +0100, "ju...@kl..." <ju...@kl...> wrote: > On Fri, 7 Jan 2011 14:02:12 +0100, Christian Boltz > <pos...@cb...> wrote: >> Am Friday 07 January 2011 schrieb J4: >>> I had postfixadmin working, and a few days I created an account >>> in it (1234@xxxinfo). The account could login, send email & so on. >>> Today, I added another test account, but the account would not log >>> in. >> >>> I compared the entries for each account in mysql and noticed that >>> these differed: >>> >>> mysql> select * from mailbox; >>> >>> | 12...@xx... | $1111111111111 | S.L TEST | ... >>> | te...@xx... | $11111111111111 | test Test | xxx.info/test/ >>> ... >> >>> mysql> select * from virtual_users; >> >>> | 1 | 1 | xxxx | 12...@xx... | >> >>> mysql> select * from virtual_aliases; >> >>> | 1 | 1 | 12...@xx... | 12...@xx... | >>> | 2 | 1 | 12...@xx... | fre...@kl... | >> >> Seeing your table names, I'd say you must be zed1 from IRC ;-) > > Hi Christian , > Yep. I'm still having mix-up problems. > >> >> It looks like you still suffer from the mixup of >> a) the HOWTO you found somewhere on the web (virtual_* tables) >> b) postfixadmin's tables (mailbox, alias etc. tables) >> >> I'm quite sure that your dovecot still uses the "virtual_users" >> table - >> change it to use the "mailbox" table and it should work. > > Changed the dovecot file dovecot-sql.conf to read: > driver = mysql > connect = host=127.0.0.1 dbname=xxxx user=xxxx password=xxxxx > default_pass_scheme = PLAIN-MD5 > password_query = SELECT email as user, password FROM mailbox WHERE > email='%u'; > ###password_query = SELECT email as user, password FROM virtual_users > WHERE email='%u'; > > > I restarted dovecot, and now no-one could log in, so I deleted all > accounts and domains via postfixadmin, and added one again, but the > IMAP login still failed. Luckily there was a useful error messag:e > Jan 7 14:20:24 logout dovecot: auth-worker(default): > sql(te...@xx...,127.0.0.1): Password query failed: Unknown column > 'email' in 'field list' > > Is the above query contained in the dovecot-sql.conf correct? > > I shall drop the virtual* tables. There is something wrong with the mailbox table. mysql> select email from mailbox; ERROR 1054 (42S22): Unknown column 'email' in 'field list' mysql> describe mailbox; +------------+--------------+------+-----+---------------------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+---------------------+-------+ | username | varchar(255) | NO | PRI | NULL | | | password | varchar(255) | NO | | NULL | | | name | varchar(255) | NO | | NULL | | | maildir | varchar(255) | NO | | NULL | | | quota | bigint(20) | NO | | 0 | | | local_part | varchar(255) | NO | | NULL | | | domain | varchar(255) | NO | MUL | NULL | | | created | datetime | NO | | 0000-00-00 00:00:00 | | | modified | datetime | NO | | 0000-00-00 00:00:00 | | | active | tinyint(1) | NO | | 1 | | +------------+--------------+------+-----+---------------------+-------+ 10 rows in set (0.00 sec) I thought that maybe query should have been: password_query = SELECT username as user, password FROM mailbox WHERE email='%u'; mysql> select username as user,password from mailbox; +------------------------+------------------------------------+ | user | password | +------------------------+------------------------------------+ | te...@xx... | $1$xxxxxxx | +------------------------+------------------------------------+ ...But this did only gave the message: Jan 7 15:36:49 logout dovecot: auth-worker(default): sql(te...@xx...,127.0.0.1): Password query failed: Unknown column 'email' in 'where clause' |
