[Dovecot] dovecot v-2.0, postfix+mysql configuration issue.
Greetings Gentlemen, Im in the attempt of configuring a postfix mail server on RHEL6.2 with the said components in the subject. I followed a couple of guides and I beleive that Im almost there, but I get the following error when I try to send a mail to a user who is in the mysql database as follows, Dovecot version that is being used is 2.0.X.
echo test | mail vimuth@mydomain.rock
[root@box1 ~]# tail -f /var/log/maillog Mar 26 07:00:44 box1 dovecot: auth: Error: sql(vimuth@mydomain.rock): User query failed: Table 'mailserver.users' doesn't exist (using built-in default user_query: SELECT home, uid, gid FROM users WHERE username = '%n' AND domain = '%d') Mar 26 07:00:44 box1 dovecot: lda: Error: user vimuth@mydomain.rock: Auth USER lookup failed Mar 26 07:00:44 box1 dovecot: lda: Fatal: Internal error occurred. Refer to server log for more information. Mar 26 07:00:44 box1 postfix/pipe[6291]: A9E0E23B96: to=vimuth@mydomain.rock, relay=dovecot, delay=14255, delays=14255/0.02/0/0.12, dsn=4.3.0, status=deferred (temporary failure) Mar 26 07:05:44 box1 postfix/qmgr[5675]: 2E5AD23B66: from=root@box1.mydomain.rock, size=421, nrcpt=1 (queue active) Mar 26 07:05:44 box1 dovecot: auth: Error: mysql: Query failed, retrying: Table 'mailserver.users' doesn't exist Mar 26 07:05:44 box1 dovecot: auth: Error: sql(vimuth@mydomain.rock): User query failed: Table 'mailserver.users' doesn't exist (using built-in default user_query: SELECT home, uid, gid FROM users WHERE username = '%n' AND domain = '%d') Mar 26 07:05:44 box1 dovecot: lda: Error: user vimuth@mydomain.rock: Auth USER lookup failed Mar 26 07:05:44 box1 dovecot: lda: Fatal: Internal error occurred. Refer to server log for more information. Mar 26 07:05:44 box1 postfix/pipe[6312]: 2E5AD23B66: to=vimuth@mydomain.rock, relay=dovecot, delay=16007, delays=16007/0.03/0/0.08, dsn=4.3.0, status=deferred (temporary failure)
As I understand, this has something to do with the way I have setup my database. Im gonna post what are in my most important configuration files and also of course in the database.
here's *"/etc/dovecot/dovecot-sql.conf.ext"*
driver = mysql connect = host=127.0.0.1 dbname=mailserver user=mailuser password=redhat default_pass_scheme = MD5 password_query = SELECT email as user, password FROM virtual_users WHERE email='%u';
here's */etc/postfix/main.cf*
# TLS parameters smtpd_use_tls=yes smtpd_tls_session_cache_database = btree:${data_directory}/smtpd_scache smtp_tls_session_cache_database = btree:${data_directory}/smtp_scache smtpd_tls_key_file = /etc/pki/tls/private/smtpd.key smtpd_tls_cert_file = /etc/pki/tls/smtpd.crt smtpd_tls_CAfile = /etc/pki/tls/certs/cacert.pem smtpd_tls_loglevel = 1 smtpd_tls_received_header = yes smtpd_tls_session_cache_timeout = 3600s tls_random_source = dev:/dev/urandom
# set smtpd restrictions smtpd_recipient_restrictions = permit_sasl_authenticated, permit_mynetworks, reject_unauth_destination</pre> virtual_mailbox_domains = mysql:/etc/postfix/ mysql-virtual-mailbox-domains.cf virtual_mailbox_maps = mysql:/etc/postfix/mysql-virtual-mailbox-maps.cf virtual_alias_maps = mysql:/etc/postfix/mysql-virtual-alias-maps.cf
mailbox_command = /usr/libexec/dovecot/deliver mailbox_transport = dovecot virtual_transport = dovecot dovecot_destination_recipient_limit = 1
Now to the *database * mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mail | | mailserver | | mysql | +--------------------+[
mysql> use mailserver; Database changed mysql> select * from mailserver; mysql> show tables; +----------------------+ | Tables_in_mailserver | +----------------------+ | virtual_aliases | | virtual_domains | | virtual_users | +----------------------+
mysql> select * from virtual_users; +----+-----------+----------------------------------+----------------------+ | id | domain_id | password | email | +----+-----------+----------------------------------+----------------------+ | 1 | 1 | e2798af12a7a0f4f70b4d69efbc25f4d | vimuth@mydomain.rock | +----+-----------+----------------------------------+----------------------+ 1 row in set (0.00 sec)
Please can someone help. If you need more information please let me know. I'm ready to tear down the entire implementation and start from the scratch.
Many thanks in advance. /Vimuth
On Thu, 28 Mar 2013 11:48:42 +0530 Vimuth vimuthd@gmail.com wrote:
Mar 26 07:00:44 box1 dovecot: auth: Error: sql(vimuth@mydomain.rock): User query failed: Table 'mailserver.users' doesn't exist (using built-in default user_query: SELECT home, uid, gid FROM users WHERE username = '%n' AND domain = '%d')
here's *"/etc/dovecot/dovecot-sql.conf.ext"*
driver = mysql connect = host=127.0.0.1 dbname=mailserver user=mailuser password=redhat default_pass_scheme = MD5 password_query = SELECT email as user, password FROM virtual_users WHERE email='%u';
looks like the "user_query" is missing in this file and therefore dovecot uses a builtin query which uses a different table name
Thank you very much for replying Mr Oli. Ummm Im confused. Could you please kindly mention as to how I should mention the user query? If I may speak though following are the mysql statements I used to create the tables n etc..
GRANT SELECT ON mailserver.* TO 'mailuser'@'127.0.0.1' IDENTIFIED BY 'mailuser2011';
CREATE TABLE virtual_domains (
id int(11) NOT NULL auto_increment,
name varchar(50) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE virtual_users (
id int(11) NOT NULL auto_increment,
domain_id int(11) NOT NULL,
password varchar(32) NOT NULL,
email varchar(100) NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY email (email),
FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE virtual_aliases (
id int(11) NOT NULL auto_increment,
domain_id int(11) NOT NULL,
source varchar(100) NOT NULL,
destination varchar(100) NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
But in that dovecot link you've sent me mention of a home that my db doesnt know about.
SELECT home, uid, gid FROM users WHERE userid = '%n' AND domain = '%d'
Please help sir.
This is just a general question. if at all possible could you please point me to a good working guide related to this whole attempt. Thats is "postfix+dovecot-2.0+mysql+TLS" ? I'm yet to find a good source in order to get there.Problem is whatever you find is postfix with dovecot1.X implementation.
thanks and Regards
On Thu, Mar 28, 2013 at 12:55 PM, Oli Schacher dovecot@lists.wgwh.chwrote:
On Thu, 28 Mar 2013 11:48:42 +0530 Vimuth vimuthd@gmail.com wrote:
Mar 26 07:00:44 box1 dovecot: auth: Error: sql(vimuth@mydomain.rock): User query failed: Table 'mailserver.users' doesn't exist (using built-in default user_query: SELECT home, uid, gid FROM users WHERE username = '%n' AND domain = '%d')
here's *"/etc/dovecot/dovecot-sql.conf.ext"*
driver = mysql connect = host=127.0.0.1 dbname=mailserver user=mailuser password=redhat default_pass_scheme = MD5 password_query = SELECT email as user, password FROM virtual_users WHERE email='%u';
looks like the "user_query" is missing in this file and therefore dovecot uses a builtin query which uses a different table name
On 2013-03-28, Vimuth wrote:
echo test | mail vimuth@mydomain.rock
[root@box1 ~]# tail -f /var/log/maillog Mar 26 07:00:44 box1 dovecot: auth: Error: sql(vimuth@mydomain.rock): User query failed: Table 'mailserver.users' doesn't exist (using built-in default user_query: SELECT home, uid, gid FROM users WHERE username = '%n' AND domain = '%d') Mar 26 07:00:44 box1 dovecot: lda: Error: user vimuth@mydomain.rock: Auth USER lookup failed Mar 26 07:00:44 box1 dovecot: lda: Fatal: Internal error occurred. Refer to server log for more information.
Dovecot needs to know if the user exists and where the mails should be saved. You can use a dedicated user lookup query like
SELECT home, uid, gid FROM users WHERE userid = '%u'
or you can use a static userdb. See: http://wiki2.dovecot.org/UserDatabase/Static
But it's also possible to combine passdb and userdb into a single lookup. This is explained here: http://wiki2.dovecot.org/UserDatabase/Prefetch
Because you're using virtual users you'll find other useful informations here: http://wiki2.dovecot.org/VirtualUsers
I guess you could start with a static userdb like
userdb { driver = static args = uid=vmail gid=vmail home=/var/mail/%d/%n }
In this example, mails are saved in /var/mail/domain/username.
-- Daniel
Hey, i have the same problem.
My Dovecot server runs quite good i can do all the things i would like to do. but i have this in my log when i connect to the server.
Sep 20 00:29:57 lx.x.x.x dovecot: auth-worker(9971): Warning: mysql: Query failed, retrying: Table 'mailserver.users' doesn't exist Sep 20 00:29:57 lx.x.x.x dovecot: auth-worker(9971): Error: sql(postmaster@lx.x.x.x.dedicated.hosteurope.de,x.x.x.x): User query failed: Table 'mailserver.users' doesn't exist (using built-in default user_query: SELECT home, uid, gid FROM users WHERE username = '%n' AND domain = '%d') Sep 20 00:29:57 lx.x.x.x dovecot: imap-login: Login: user=postmaster@lx.x.x.x.dedicated.hosteurope.de, method=PLAIN, rip=x.x.x.x, lip=x.x.x.x, mpid=9975, TLS, session=<IWQBGMTmSwDZU2Fd>
But the mail system works…
Now when i create the table users from the example in the config file an it is been empty no errors are in the login and the mail system works also..
Maybe this can be an bug? I have read all your examples and tips
-- View this message in context: http://dovecot.2317879.n4.nabble.com/dovecot-v-2-0-postfix-mysql-configurati... Sent from the Dovecot mailing list archive at Nabble.com.
Sep 20 00:29:57 lx.x.x.x dovecot: auth-worker(9971): Error: sql(postmaster@lx.x.x.x.dedicated.hosteurope.de,x.x.x.x): User query failed: Table 'mailserver.users' doesn't exist (using built-in default user_query: SELECT home, uid, gid FROM users WHERE username = '%n' AND domain = '%d')
If you are using SQL as Auth backend echeck if you have enabled 'iterate_query' in your configuration
Check the http://wiki2.dovecot.org/AuthDatabase/SQL page, specifically the section about 'User Iteration'
-Thanks Vijay
On Fri, Sep 20, 2013 at 4:17 AM, malocatze malocatze@trash-mail.com wrote:
Hey, i have the same problem.
My Dovecot server runs quite good i can do all the things i would like to do. but i have this in my log when i connect to the server.
Sep 20 00:29:57 lx.x.x.x dovecot: auth-worker(9971): Warning: mysql: Query failed, retrying: Table 'mailserver.users' doesn't exist Sep 20 00:29:57 lx.x.x.x dovecot: auth-worker(9971): Error: sql(postmaster@lx.x.x.x.dedicated.hosteurope.de,x.x.x.x): User query failed: Table 'mailserver.users' doesn't exist (using built-in default user_query: SELECT home, uid, gid FROM users WHERE username = '%n' AND domain = '%d') Sep 20 00:29:57 lx.x.x.x dovecot: imap-login: Login: user=postmaster@lx.x.x.x.dedicated.hosteurope.de, method=PLAIN, rip=x.x.x.x, lip=x.x.x.x, mpid=9975, TLS, session=<IWQBGMTmSwDZU2Fd>
But the mail system works…
Now when i create the table users from the example in the config file an it is been empty no errors are in the login and the mail system works also..
Maybe this can be an bug? I have read all your examples and tips
-- View this message in context: http://dovecot.2317879.n4.nabble.com/dovecot-v-2-0-postfix-mysql-configurati... Sent from the Dovecot mailing list archive at Nabble.com.
participants (5)
-
Daniel Luttermann
-
malocatze
-
Oli Schacher
-
Vijay Rajah
-
Vimuth