Greetings,

I am trying to add the second virtual mailbox domain (transco.org.au) to an existing Postfix/Dovecot/MySQL mail server. Users of the first virtual mailbox domain (transylvania.org.au) have no problems sending and receiving mail.

Users belonging to the second virtual mailbox domain can successfully send mail to outside, however they won't receive the mail, though Postfix delivers the mail to the mail directories.

The configuration:

---

dovecot --version

1.0.2

dovecot -n

1.0.2: /etc/dovecot.conf

base_dir: /var/dovecot/ protocols: imap imaps pop3 pop3s ssl_cert_file: /etc/ssl/dovecotcert.pem disable_plaintext_auth: no login_dir: /var/dovecot/login login_executable(default): /usr/local/libexec/dovecot/imap-login login_executable(imap): /usr/local/libexec/dovecot/imap-login login_executable(pop3): /usr/local/libexec/dovecot/pop3-login login_user: _dovecot first_valid_uid: 5000 last_valid_uid: 5000 mail_location: maildir:/var/spool/vmboxbase/%d/%u mbox_write_locks: fcntl mail_executable(default): /usr/local/libexec/dovecot/imap mail_executable(imap): /usr/local/libexec/dovecot/imap mail_executable(pop3): /usr/local/libexec/dovecot/pop3 mail_plugin_dir(default): /usr/local/lib/dovecot/imap mail_plugin_dir(imap): /usr/local/lib/dovecot/imap mail_plugin_dir(pop3): /usr/local/lib/dovecot/pop3 imap_client_workarounds(default): delay-newmail outlook-idle netscape-eoh tb-extra-mailbox-sep imap_client_workarounds(imap): delay-newmail outlook-idle netscape-eoh tb-extra-mailbox-sep imap_client_workarounds(pop3): outlook-idle pop3_uidl_format(default): pop3_uidl_format(imap): pop3_uidl_format(pop3): %08Xu%08Xv pop3_client_workarounds(default): pop3_client_workarounds(imap): pop3_client_workarounds(pop3): outlook-no-nuls oe-ns-eoh auth default: verbose: yes debug: yes debug_passwords: yes passdb: driver: bsdauth passdb: driver: sql args: /etc/dovecot-mysql.conf userdb: driver: passwd userdb: driver: sql args: /etc/dovecot-mysql.conf

---

password_query = SELECT password FROM users WHERE login = '%u' and active = 'Y'

SELECT password FROM users WHERE login = 'dummy@transco.org.au' and active = 'Y'; mysql> SELECT password FROM users WHERE login = 'dummy@transco.org.au' and active = 'Y'; +---------------+ | password | +---------------+ | Wdrbd5SLusraw | +---------------+ 1 row in set (0.00 sec)

---

user_query = SELECT home, maildir, uid, gid FROM users WHERE login = 'dummy' AND active = 'Y'

mysql> SELECT home, maildir, uid, gid FROM users WHERE login = 'dummy@transco.org.au'; +----------------------+-----------------------+------+------+ | home | maildir | uid | gid | +----------------------+-----------------------+------+------+ | /var/spool/vmboxbase | transco.org.au/dummy/ | 5000 | 5000 | +----------------------+-----------------------+------+------+ 1 row in set (0.00 sec)

more /etc/dovecot-mysql.conf

This file is opened as root, so it should be owned by root and mode 0600.

http://wiki.dovecot.org/AuthDatabase/SQL

For the sql passdb module, you'll need a database with a table that

contains fields for at least the userid and password. If you want to

use the user@domain syntax, you might want to have a separate domain

field as well.

If your users all have the same uig/gid, and have predictable home

directories, you can use the static userdb module to generate the home

dir based on the userid and domain. In this case, you won't need fields

for home, uid, or gid in the database.

If you prefer to use the sql userdb module, you'll want to add fields

for home, uid, and gid. Here is an example table:

CREATE TABLE users (

userid VARCHAR(128) NOT NULL,

password VARCHAR(64) NOT NULL,

home VARCHAR(255) NOT NULL,

uid INTEGER NOT NULL,

gid INTEGER NOT NULL,

active CHAR(1) DEFAULT 'Y' NOT NULL

);

Database driver: mysql, pgsql, sqlite

#driver = driver = mysql

Database connection string. This is driver-specific setting.

pgsql:

For available options, see the PostgreSQL documention for the

PQconnectdb function of libpq.

mysql:

Basic options emulate PostgreSQL option names:

host, port, user, password, dbname

But also adds some new settings:

client_flags - See MySQL manual

ssl_ca, ssl_ca_path - Set either one or both to enable SSL

ssl_cert, ssl_key - For sending client-side certificates to server

ssl_cipher - Set minimum allowed cipher security

(default: HIGH)

You can connect to UNIX sockets by using host: host=/var/run/mysql.sock

Note that currently you can't use spaces in parameters.

sqlite:

The path to the database file.

Examples:

connect = host=192.168.1.1 dbname=users

connect = host=sql.example.com dbname=virtual user=virtual

password=blarg

connect = /etc/dovecot/authdb.sqlite

#connect = dbname=virtual user=virtual connect = host=localhost dbname=mail user=dovecot password=dovecot

Default password scheme.

List of supported schemes is in

http://wiki.dovecot.org/Authentication/PasswordSchemes

#default_pass_scheme = PLAIN-MD5 default_pass_scheme = CRYPT

Query to retrieve the password.

This query must return only one row with "user" and "password" columns.

The query can also return other fields which have a special meaning, see

http://wiki.dovecot.org/PasswordDatabase/ExtraFields

The "user" column is needed to make sure the username gets used with

exactly

the same casing as it's in the database. Note that if you store

username and

domain in separate fields, you most likely want to return a combination of

them as the "user" column, otherwise the domain gets stripped.

Commonly used available substitutions (see

http://wiki.dovecot.org/Variables for full list):

%u = entire userid

%n = user part of user@domain

%d = domain part of user@domain

Note that these can be used only as input to SQL query. If the query

outputs

any of these substitutions, they're not touched. Otherwise it would be

difficult to have eg. usernames containing '%' characters.

Example:

password_query = SELECT concat(userid, '@', domain) AS user,

password FROM users WHERE userid = '%n' AND domain = '% d'

password_query = SELECT pw AS password FROM users WHERE userid =

'%u' AND active = 'Y'

password_query = SELECT password FROM users WHERE login = '%u' and active = 'Y'

Query to retrieve the user information.

The query must return only one row. Commonly returned columns are:

uid - System UID

gid - System GID

home - Home directory

mail - Mail location

Either home or mail is required. uid and gid are required. If more

than one

row is returned or there are missing fields, the login will fail. For

a list

of all fields that can be returned, see

http://wiki.dovecot.org/UserDatabase/ExtraFields

Examples

user_query = SELECT home, uid, gid FROM users WHERE userid = '%n'

AND domain = '%d'

user_query = SELECT dir AS home, user AS uid, group AS gid FROM

users where userid = '%u'

user_query = SELECT home, 501 AS uid, 501 AS gid FROM users WHERE

userid = '%u'

#user_query = SELECT home, uid, gid FROM users WHERE userid = '%u' user_query = SELECT home, maildir, uid, gid FROM users WHERE login = '%u' AND active = 'Y'

If you wish to avoid two SQL lookups (passdb + userdb), you can use

userdb prefetch instead of userdb sql in dovecot.conf. In that case you'll

also have to return userdb fields in password_query prefixed with

"userdb_"

string. For example:

#password_query = SELECT userid as user, password, home as userdb_home, uid as userdb_uid, gid as userdb_gid FROM users WHERE userid = '%u'

ps -aux | grep dovecot

root 13442 0.0 0.2 676 952 ?? Ss 4:27AM 0:00.04 /usr/local/sbin/dovecot root 10354 0.0 0.4 816 1920 ?? S 4:27AM 0:00.03 dovecot-auth root 8330 0.0 0.4 708 2080 ?? S 4:27AM 0:00.03 dovecot-auth -w _dovecot 29456 0.0 0.4 664 1876 ?? S 4:27AM 0:00.04 pop3-login _dovecot 12636 0.0 0.4 716 1868 ?? S 4:27AM 0:00.04 pop3-login _dovecot 26625 0.0 0.4 696 1896 ?? S 4:27AM 0:00.04 imap-login _dovecot 4709 0.0 0.4 556 1892 ?? S 4:27AM 0:00.04 imap-login _dovecot 10294 0.0 0.4 760 1888 ?? S 4:27AM 0:00.04 imap-login _dovecot 13526 0.0 0.4 596 1888 ?? S 4:59AM 0:00.01 pop3-login root 20523 0.0 0.1 472 748 p1 S+ 5:01AM 0:00.00 grep dovecot

ls -ltr /var/spool/vmboxbase/transco.org.au/dummy/new

total 40 -rw------- 1 vmail vmail 462 May 3 11:06 1241312770.V8I6be62M630453.vega.transylvania.org.au -rw------- 1 vmail vmail 708 May 3 11:15 1241313359.V8I6be6bM976701.vega.transylvania.org.au -rw------- 1 vmail vmail 658 May 3 11:21 1241313700.V8I6bd8aM30789.vega.transylvania.org.au -rw------- 1 vmail vmail 672 May 4 02:18 1241367523.V8I6be75M268056.vega.transylvania.org.au -rw------- 1 vmail vmail 706 May 4 02:31 1241368314.V8I6be7cM742941.vega.transylvania.org.au -rw------- 1 vmail vmail 658 May 4 02:54 1241369661.V8I6bd9bM991186.vega.transylvania.org.au -rw------- 1 vmail vmail 690 May 4 03:02 1241370177.V8I6bd98M300668.vega.transylvania.org.au -rw------- 1 vmail vmail 672 May 4 03:54 1241373297.V8I6bda1M149877.vega.transylvania.org.au -rw------- 1 vmail vmail 837 May 4 04:35 1241375739.V8I6bdaeM905070.vega.transylvania.org.au -rw------- 1 vmail vmail 842 May 4 04:36 1241375767.V8I6bdb4M496268.vega.transylvania.org.au

---

When I try to retreive mail for user dummy@tarnsco.org.au, the folovig entry is created in /var/log/maillog, but no mail is retrived. The mail client agent is Thunderbird 2.0.22

May 4 06:04:51 vega dovecot: auth(default): client in: AUTH 1 PLAIN service=POP3 lip=192.168.1.212 rip=192.168.1.101 resp= May 4 06:04:51 vega dovecot: auth(default): client out: CONT 1 May 4 06:04:51 vega dovecot: auth(default): client in: CONT 1 AGR1bW15QHRyYW5zY28ub3JnLmF1AGR1bW15 May 4 06:04:51 vega dovecot: auth(default): bsdauth(dummy@transco.org.au,192.168.1.101): lookup May 4 06:04:51 vega dovecot: auth(default): bsdauth(dummy@transco.org.au,192.168.1.101): unknown user May 4 06:04:51 vega dovecot: auth-worker(default): sql(dummy@transco.org.au,192.168.1.101): query: SELECT password FROM users WHERE login = 'dummy@transco.org.au' and active = 'Y' May 4 06:04:51 vega dovecot: auth(default): client out: OK 1 user=dummy@transco.org.au May 4 06:04:51 vega dovecot: auth(default): master in: REQUEST 31 29200 1 May 4 06:04:51 vega dovecot: auth(default): passwd(dummy@transco.org.au,192.168.1.101): lookup May 4 06:04:51 vega dovecot: auth(default): passwd(dummy@transco.org.au,192.168.1.101): unknown user May 4 06:04:51 vega dovecot: auth-worker(default): sql(dummy@transco.org.au,192.168.1.101): SELECT home, maildir, uid, gid FROM users WHERE login = 'dummy@transco.org.au' AND active = 'Y' May 4 06:04:51 vega dovecot: auth(default): master out: USER 31 dummy@transco.org.au home=/var/spool/vmboxbase maildir=transco.org.au/dummy/ uid=5000 gid=5000 May 4 06:04:51 vega dovecot: pop3-login: Login: user=<dummy@transco.org.au>, method=PLAIN, rip=192.168.1.101, lip=192.168.1.212 May 4 06:04:51 vega dovecot: POP3(dummy@transco.org.au): Disconnected: Logged out top=0/0, retr=0/0, del=0/0, size=0

Any suggestion?

Regards,

Ioan