Re: MySQL issue

2017-05-29 Thread Luigi Rosa 

If you put the database name in the connect string you can omit it in the query

Append at the end of the connect string "dbname=mailserver" and remove 
"mailserver." from the table name


Of course the user you are using must have at least SELECT access to db 
mailserver



Amaechi Janneh wrote on 29/05/2017 22:30:

Yes it is.

On Mon, May 29, 2017 at 12:28 AM, Luigi Rosa > wrote:


Amaechi Janneh wrote on 29/05/2017 02:47:

May 28 20:20:32 AmaechiJ dovecot[10516]: auth-worker(10892): Error: sql(
a...@example.com ,): User query failed: Table
'mailserver.users' doesn't exist


Start MySQL command line interface with the same username/password you use
in Dovecot and do the same query Dovecot does.

"mailserver" is the database name?




--


Ciao,
luigi

/
+--[Luigi Rosa]--
\

If you rely only on your eyes, your other senses weaken.
--Frank Herbert, "Dune"

Re: MySQL issue

2017-05-29 Thread Odhiambo Washington 
On 29 May 2017 at 03:47, Amaechi Janneh  wrote:

> Hello.
>
> It appears that I have an issue with my dovecot setup. When trying to
> authenticate, i get this error:
>
> May 28 20:18:26 AmaechiJ dovecot[10516]: imap-login: Login: user=<
> a...@example.com>, method=PLAIN, rip=, lip=, mpid=10879, TLS,
> ses...QOQBg9rlR>
> May 28 20:18:42 AmaechiJ dovecot[10516]: imap(a...@example.com): Connection
> closed in=116 out=1311
> May 28 20:20:32 AmaechiJ dovecot[10516]: auth-worker(10892): Warning:
> mysql: Query failed, retrying: Table 'mailserver.users' doesn't exist
> May 28 20:20:32 AmaechiJ dovecot[10516]: auth-worker(10892): Error: sql(
> a...@example.com,): User query failed: Table 'mailserver.users' doesn't
> exist
> May 28 20:20:32 AmaechiJ dovecot[10516]: imap-login: Login: user=<
> a...@example.com>, method=PLAIN, rip=, lip=, mpid=10894, TLS,
> ses...QZwBg9rlR>
>
> But I already have the users data under virtual_users. So what should I do?
>
> Regards,
>
> Amaechi Janneh
>
> I am running dovecot 2.2.13 Debian 8
>

What does virtual_users have to do with a table named 'users' in the
database named  'mailserver'?

I fear the Debian split configs, but maybe you should check your
configuration in etc/dovecot/dovecot-sql.conf.ext or something close? Make
sure the params there match what you want in terms of MySQL db and tables.



-- 
Best regards,
Odhiambo WASHINGTON,
Nairobi,KE
+254 7 3200 0004/+254 7 2274 3223
"Oh, the cruft."

Re: MySQL issue

2017-05-29 Thread Amaechi Janneh 
Yes it is.

On Mon, May 29, 2017 at 12:28 AM, Luigi Rosa  wrote:

> Amaechi Janneh wrote on 29/05/2017 02:47:
>
> May 28 20:20:32 AmaechiJ dovecot[10516]: auth-worker(10892): Error: sql(
>> a...@example.com,): User query failed: Table 'mailserver.users' doesn't
>> exist
>>
>
> Start MySQL command line interface with the same username/password you use
> in Dovecot and do the same query Dovecot does.
>
> "mailserver" is the database name?
>
>
>
> --
>
>
> Ciao,
> luigi
>
> /
> +--[Luigi Rosa]--
> \
>
> Never ascribe to malice that which is caused by greed and ignorance.
> --Cal Keegan
>

Re: MySQL issue

2017-05-28 Thread Luigi Rosa 

Amaechi Janneh wrote on 29/05/2017 02:47:


May 28 20:20:32 AmaechiJ dovecot[10516]: auth-worker(10892): Error: sql(
a...@example.com,): User query failed: Table 'mailserver.users' doesn't exist


Start MySQL command line interface with the same username/password you use in 
Dovecot and do the same query Dovecot does.


"mailserver" is the database name?



--


Ciao,
luigi

/
+--[Luigi Rosa]--
\

Never ascribe to malice that which is caused by greed and ignorance.
--Cal Keegan

MySQL issue

2017-05-28 Thread Amaechi Janneh 
Hello.

It appears that I have an issue with my dovecot setup. When trying to
authenticate, i get this error:

May 28 20:18:26 AmaechiJ dovecot[10516]: imap-login: Login: user=<
a...@example.com>, method=PLAIN, rip=, lip=, mpid=10879, TLS, ses...QOQBg9rlR>
May 28 20:18:42 AmaechiJ dovecot[10516]: imap(a...@example.com): Connection
closed in=116 out=1311
May 28 20:20:32 AmaechiJ dovecot[10516]: auth-worker(10892): Warning:
mysql: Query failed, retrying: Table 'mailserver.users' doesn't exist
May 28 20:20:32 AmaechiJ dovecot[10516]: auth-worker(10892): Error: sql(
a...@example.com,): User query failed: Table 'mailserver.users' doesn't exist
May 28 20:20:32 AmaechiJ dovecot[10516]: imap-login: Login: user=<
a...@example.com>, method=PLAIN, rip=, lip=, mpid=10894, TLS, ses...QZwBg9rlR>

But I already have the users data under virtual_users. So what should I do?

Regards,

Amaechi Janneh

I am running dovecot 2.2.13 Debian 8

Re: [Dovecot] current quota in mysql issue

2008-03-16 Thread stefan 
ok, i will answer my question by myself:

from the dovecot wiki:
You can configure quota globally by placing the settings in plugin
section in dovecot.conf and you can give per-user limits by having your
userdb return the quota setting as an extra field. The userdb quota
setting always overrides the global plugin setting.

stefan


 hi all,

 i have a problem with storing the current quota in mysql. the
 configuration of the dictionary quota mostly looks like the example from
 the wiki.
 the dirsize quota limit is read correctly from the user_query, but nothing
 stored with quotadict in the quota table. i wonder that there is nothing
 like a dict in the logfile. did i configured anything wrong?

 regars
 stefan


 logfile from a imap login:
 Mar 15 14:12:18 mx01 dovecot: IMAP([EMAIL PROTECTED]): Effective uid=150,
 gid=8
 Mar 15 14:12:18 mx01 dovecot: IMAP([EMAIL PROTECTED]): Loading modules from
 directory: /usr/lib/dovecot/modules/imap
 Mar 15 14:12:18 mx01 dovecot: IMAP([EMAIL PROTECTED]): Module loaded:
 /usr/lib/dovecot/modules/imap/lib01_quota_plugin.so
 Mar 15 14:12:18 mx01 dovecot: IMAP([EMAIL PROTECTED]): Module loaded:
 /usr/lib/dovecot/modules/imap/lib02_imap_quota_plugin.so
 Mar 15 14:12:18 mx01 dovecot: IMAP([EMAIL PROTECTED]): Namespace:
 type=private, prefix=, sep=/, inbox=yes, hidden=no, subscriptions=no
 Mar 15 14:12:18 mx01 dovecot: IMAP([EMAIL PROTECTED]): maildir:
 data=/var/vmail/mydomain.de/me/Maildir
 Mar 15 14:12:18 mx01 dovecot: IMAP([EMAIL PROTECTED]): maildir:
 root=/var/vmail/mydomain.de/me/Maildir,
 index=/var/vmail/mydomain.de/me/Maildir, control=, inbox=
 Mar 15 14:12:18 mx01 dovecot: IMAP([EMAIL PROTECTED]): dirsize quota limit =
 2000kB
 Mar 15 14:12:18 mx01 dovecot: imap-login: Login: user=[EMAIL PROTECTED],
 method=PLAIN, rip=10.1.1.147, lip=10.1.1.146


 here is my current config:

 package from debian etch (1.0.rc15)

 ###
 # file dovecot.conf
 disable_plaintext_auth = no
 base_dir = /var/run/dovecot/
 protocols = imap imaps pop3 pop3s
 protocol imap {
   listen = mx01.int.example.net:143
   ssl_listen = mx01.int.example.net:993
 }
 protocol pop3 {
   listen = mx01.int.example.net:110
   ssl_listen = mx01.int.example.net:995
 }
 log_timestamp = .%Y-%m-%d %H:%M:%S .
 syslog_facility = mail
 ssl_cert_file = /etc/ssl/example.net/mailserver/mail-cert.pem
 ssl_key_file = /etc/ssl/example.net/mailserver/mail-key.pem
 ssl_ca_file = /etc/ssl/example.net/mailserver/ca.pem
 ssl_parameters_regenerate = 168
 verbose_ssl = no
 mail_extra_groups = mail
 mail_debug = yes
 auth_verbose = no
 auth_debug = no
 auth_debug_passwords = no
 first_valid_uid = 150
 last_valid_uid = 150
 first_valid_gid = 8
 last_valid_gid = 8
 maildir_copy_with_hardlinks = yes
 protocol imap {
   login_executable = /usr/lib/dovecot/imap-login
   mail_executable = /usr/lib/dovecot/imap
   imap_client_workarounds = outlook-idle
   imap_max_line_length = 65536
   mail_plugins = quota imap_quota
 }
 protocol pop3 {
   login_executable = /usr/lib/dovecot/pop3-login
   mail_executable = /usr/lib/dovecot/pop3
   mail_plugins = quota
 }
 protocol lda {
   postmaster_address = [EMAIL PROTECTED]
   sendmail_path = /usr/lib/sendmail
   auth_socket_path = /var/run/dovecot/auth-master
   mail_plugins = cmusieve quota
 }
 auth_username_translation = %Lu
 auth default {
   mechanisms = plain
 passdb sql {
   args = /etc/dovecot/dovecot-sql.conf
 }
 userdb sql {
   args = /etc/dovecot/dovecot-sql.conf
 }
 user = nobody
 socket listen {
 master {
   path = /var/run/dovecot/auth-master
   mode = 0660
   user = vmail
   group = mail
 }
 client {
   path = /var/spool/postfix/private/auth
   mode = 0660
   user = postfix
   group = postfix
 }
   }
 }
 namespace private {
   separator = /
   prefix =
   inbox = yes
  }
 plugin {
   quota = dict::: proxy::quotadict
 }
 dict {
   quotadict = mysql:/etc/dovecot/dovecot-sql-quota-dict.conf
 }

 ###
 # file dovecot-sql.conf
 driver = mysql
 connect = host=mx01.int.example.net dbname=db_mail user=dovecot
 password=
 default_pass_scheme = PLAIN
 user_query = select concat('/var/vmail/%d/%n/Maildir') as home,
 concat('maildir:/var/vmail/%d/%n/Maildir') as mail, 150 as uid, 8 as gid,
 quota from qry_dovecot where user = '%u'
 password_query = select user, password, concat('/var/vmail/%d/%n') as
 userdb_home, concat('maildir:/var/vmail/%d/%n') as userdb_mail, 150 as
 userdb_uid, 8 as userdb_gid from qry_dovecot where user = '%u'

 ###
 # file dovecot-sql-quota-dict.conf
 connect = host=db01.int.example.net dbname=db_mail user=quota
 password==
 table = quota
 select_field = current
 where_field = path
 username_field = username

 (don't wonder about the different database hosts. mx01 is a readonly slave
 from db01)

[Dovecot] current quota in mysql issue

2008-03-15 Thread stefan 
hi all,

i have a problem with storing the current quota in mysql. the
configuration of the dictionary quota mostly looks like the example from
the wiki.
the dirsize quota limit is read correctly from the user_query, but nothing
stored with quotadict in the quota table. i wonder that there is nothing
like a dict in the logfile. did i configured anything wrong?

regars
stefan


logfile from a imap login:
Mar 15 14:12:18 mx01 dovecot: IMAP([EMAIL PROTECTED]): Effective uid=150, gid=8
Mar 15 14:12:18 mx01 dovecot: IMAP([EMAIL PROTECTED]): Loading modules from
directory: /usr/lib/dovecot/modules/imap
Mar 15 14:12:18 mx01 dovecot: IMAP([EMAIL PROTECTED]): Module loaded:
/usr/lib/dovecot/modules/imap/lib01_quota_plugin.so
Mar 15 14:12:18 mx01 dovecot: IMAP([EMAIL PROTECTED]): Module loaded:
/usr/lib/dovecot/modules/imap/lib02_imap_quota_plugin.so
Mar 15 14:12:18 mx01 dovecot: IMAP([EMAIL PROTECTED]): Namespace:
type=private, prefix=, sep=/, inbox=yes, hidden=no, subscriptions=no
Mar 15 14:12:18 mx01 dovecot: IMAP([EMAIL PROTECTED]): maildir:
data=/var/vmail/mydomain.de/me/Maildir
Mar 15 14:12:18 mx01 dovecot: IMAP([EMAIL PROTECTED]): maildir:
root=/var/vmail/mydomain.de/me/Maildir,
index=/var/vmail/mydomain.de/me/Maildir, control=, inbox=
Mar 15 14:12:18 mx01 dovecot: IMAP([EMAIL PROTECTED]): dirsize quota limit =
2000kB
Mar 15 14:12:18 mx01 dovecot: imap-login: Login: user=[EMAIL PROTECTED],
method=PLAIN, rip=10.1.1.147, lip=10.1.1.146


here is my current config:

package from debian etch (1.0.rc15)

###
# file dovecot.conf
disable_plaintext_auth = no
base_dir = /var/run/dovecot/
protocols = imap imaps pop3 pop3s
protocol imap {
  listen = mx01.int.example.net:143
  ssl_listen = mx01.int.example.net:993
}
protocol pop3 {
  listen = mx01.int.example.net:110
  ssl_listen = mx01.int.example.net:995
}
log_timestamp = .%Y-%m-%d %H:%M:%S .
syslog_facility = mail
ssl_cert_file = /etc/ssl/example.net/mailserver/mail-cert.pem
ssl_key_file = /etc/ssl/example.net/mailserver/mail-key.pem
ssl_ca_file = /etc/ssl/example.net/mailserver/ca.pem
ssl_parameters_regenerate = 168
verbose_ssl = no
mail_extra_groups = mail
mail_debug = yes
auth_verbose = no
auth_debug = no
auth_debug_passwords = no
first_valid_uid = 150
last_valid_uid = 150
first_valid_gid = 8
last_valid_gid = 8
maildir_copy_with_hardlinks = yes
protocol imap {
  login_executable = /usr/lib/dovecot/imap-login
  mail_executable = /usr/lib/dovecot/imap
  imap_client_workarounds = outlook-idle
  imap_max_line_length = 65536
  mail_plugins = quota imap_quota
}
protocol pop3 {
  login_executable = /usr/lib/dovecot/pop3-login
  mail_executable = /usr/lib/dovecot/pop3
  mail_plugins = quota
}
protocol lda {
  postmaster_address = [EMAIL PROTECTED]
  sendmail_path = /usr/lib/sendmail
  auth_socket_path = /var/run/dovecot/auth-master
  mail_plugins = cmusieve quota
}
auth_username_translation = %Lu
auth default {
  mechanisms = plain
passdb sql {
  args = /etc/dovecot/dovecot-sql.conf
}
userdb sql {
  args = /etc/dovecot/dovecot-sql.conf
}
user = nobody
socket listen {
master {
  path = /var/run/dovecot/auth-master
  mode = 0660
  user = vmail
  group = mail
}
client {
  path = /var/spool/postfix/private/auth
  mode = 0660
  user = postfix
  group = postfix
}
  }
}
namespace private {
  separator = /
  prefix =
  inbox = yes
 }
plugin {
  quota = dict::: proxy::quotadict
}
dict {
  quotadict = mysql:/etc/dovecot/dovecot-sql-quota-dict.conf
}

###
# file dovecot-sql.conf
driver = mysql
connect = host=mx01.int.example.net dbname=db_mail user=dovecot
password=
default_pass_scheme = PLAIN
user_query = select concat('/var/vmail/%d/%n/Maildir') as home,
concat('maildir:/var/vmail/%d/%n/Maildir') as mail, 150 as uid, 8 as gid,
quota from qry_dovecot where user = '%u'
password_query = select user, password, concat('/var/vmail/%d/%n') as
userdb_home, concat('maildir:/var/vmail/%d/%n') as userdb_mail, 150 as
userdb_uid, 8 as userdb_gid from qry_dovecot where user = '%u'

###
# file dovecot-sql-quota-dict.conf
connect = host=db01.int.example.net dbname=db_mail user=quota
password==
table = quota
select_field = current
where_field = path
username_field = username

(don't wonder about the different database hosts. mx01 is a readonly slave
from db01)