Re: [Dovecot] Unknown column username in where clause

2010-07-20 Thread Daniel Luttermann 
Timo Sirainen wrote on 20.07.2010:

 On 19.7.2010, at 22.59, Daniel Luttermann wrote:

 The queries uses only userid instead of username so I think if one
 sql field were missing it should report that userid could not be
 found because the query is FROM users WHERE userid = '%u'.
 
 I've not select username in any sql select so I wonder where does
 this name come from.

 There's actually a default query.. Hmm. Maybe it should be made
 empty, since probably no one wants to use it.

hmm, I've nothing read about that - but it's OK because the examples
in the wiki creates tables with the field username instead of
userid which I've used so the default query should then match...

 password_query = SELECT userid AS user, password, \
  home AS userdb_home, uid AS userdb_uid, gid AS userdb_gid, \
  concat('*:bytes=', quota_bytes) as userdb_quota_rule \
  FROM users WHERE userid = '%u'

 You must also have a user_query for LDA/LMTP.

OK, it seems to be working if I add this in dovecot-sql.conf.ext too:

  user_query = SELECT home, uid, gid, \
concat('*:bytes=', quota_bytes) as quota_rule \
FROM users WHERE userid = '%u'

--
Daniel

Re: [Dovecot] Unknown column username in where clause

2010-07-19 Thread Anton Dollmaier 

Hi,



I dont' know where the field username should come from because I
don't use this field (or query).


perhabs this is the problem? ;-)



The relevant files are:


Output of dovecot -n would be better, together with the additional files.



dovecot-sql.conf.ext


password_query = SELECT userid AS user, password, \
   home AS userdb_home, uid AS userdb_uid, gid AS userdb_gid, \
   concat('*:bytes=', quota_bytes) as userdb_quota_rule \
   FROM users WHERE userid = '%u'



I think you're missing a user_query.



best regards,

Anton

Re: [Dovecot] Unknown column username in where clause

2010-07-19 Thread Daniel Luttermann 
Anton Dollmaier wrote on 19.07.2010:

 Hi,

 I dont' know where the field username should come from because I
 don't use this field (or query).

 perhabs this is the problem?

The queries uses only userid instead of username so I think if one
sql field were missing it should report that userid could not be
found because the query is FROM users WHERE userid = '%u'.

I've not select username in any sql select so I wonder where does
this name come from.

 Output of dovecot -n would be better, together with the additional files.

dovecot -n
==

# 2.0.rc2 (7dd7adba1c9e): /etc/dovecot/dovecot.conf
# OS: Linux 2.6.33.2 x86_64 Debian 5.0.5 
auth_username_format = %Lu
dict {
  quota = mysql:/etc/dovecot/dovecot-dict-sql.conf.ext
}
hostname = tux.linuxmail.at
listen = 89.238.81.84
mail_location = mdbox:~/mdbox
mail_plugins = acl quota
mail_privileged_group = vmail
managesieve_notify_capability = mailto
managesieve_sieve_capability = comparator-i;octet comparator-i;ascii-casemap 
fileinto reject envelope encoded-character vacation subaddress 
comparator-i;ascii-numeric relational regex imap4flags copy include variables 
body enotify environment mailbox date spamtest spamtestplus virustest
namespace {
  hidden = no
  inbox = yes
  location = 
  prefix = 
  separator = /
  subscriptions = yes
  type = private
}
passdb {
  args = /etc/dovecot/dovecot-sql.conf.ext
  driver = sql
}
plugin {
  acl = vfile:/etc/dovecot/global-acls/%d:cache_secs=300
  quota = dict:user::proxy::quota
  quota_rule = *:storage=0
  sieve = ~/.dovecot.sieve
  sieve_dir = ~/sieve
}
postmaster_address = m...@dlutt.de
protocols = imap lmtp sieve
service auth {
  unix_listener /var/spool/postfix/private/auth {
group = postfix
mode = 0666
user = postfix
  }
  user = $default_internal_user
}
service dict {
  unix_listener dict {
group = vmail
mode = 0660
user = vmail
  }
}
service imap-login {
  inet_listener imap {
port = 143
  }
}
service lmtp {
  unix_listener /var/spool/postfix/private/dovecot-lmtp {
group = postfix
mode = 0660
user = postfix
  }
}
service managesieve-login {
  inet_listener sieve {
address = 127.0.0.1
port = 4190
  }
}
ssl_cert = /etc/ssl/certs/dovecot.pem
ssl_key = /etc/ssl/private/dovecot.key
userdb {
  driver = prefetch
}
userdb {
  args = /etc/dovecot/dovecot-sql.conf.ext
  driver = sql
}
protocol lmtp {
  mail_plugins = $mail_plugins sieve
}
protocol imap {
  mail_plugins = $mail_plugins imap_acl imap_quota
}


Additional files


/etc/dovecot/dovecot-dict-sql.conf.ext

connect = host=/var/run/mysqld/mysqld.sock dbname=mail user=user password=pwd
map {
  pattern = priv/quota/storage
  table = quota
  username_field = userid
  value_field = bytes
}
map {
  pattern = priv/quota/messages
  table = quota
  username_field = userid
  value_field = messages
}

/etc/dovecot/dovecot-sql.conf.ext

driver = mysql
connect = host=/var/run/mysqld/mysqld.sock dbname=mail user=user password=pwd
default_pass_scheme = PLAIN
password_query = SELECT userid AS user, password, \
  home AS userdb_home, uid AS userdb_uid, gid AS userdb_gid, \
  concat('*:bytes=', quota_bytes) as userdb_quota_rule \
  FROM users WHERE userid = '%u'


--
Daniel

Re: [Dovecot] Unknown column username in where clause

2010-07-19 Thread Timo Sirainen 
On 19.7.2010, at 22.59, Daniel Luttermann wrote:

 The queries uses only userid instead of username so I think if one
 sql field were missing it should report that userid could not be
 found because the query is FROM users WHERE userid = '%u'.
 
 I've not select username in any sql select so I wonder where does
 this name come from.

There's actually a default query.. Hmm. Maybe it should be made empty, since 
probably no one wants to use it.

 password_query = SELECT userid AS user, password, \
  home AS userdb_home, uid AS userdb_uid, gid AS userdb_gid, \
  concat('*:bytes=', quota_bytes) as userdb_quota_rule \
  FROM users WHERE userid = '%u'

You must also have a user_query for LDA/LMTP.