[Dovecot] Dovecot 2.2.4/sendmail with sql user and aliases
Cedric
cedrict at derytelecom.ca
Wed Jul 17 23:43:05 EEST 2013
Hi,
We are currently moving from linuxconf/dovecot to a dovecot setup with
sql support, the problem we currently facing is having a message sent to
a aliase delivered to the user email inbox with dovecot-lda, i have
created cedrict at mail.vdl and a alias test at mail.vdl, when i send to
cedrict at mail.vdl the message is included in the user inbox file, but
when i send to test at mail.vdl it should do the same and not create a new
created file named test, thank in advance for your help.
# dovecot --version
2.2.4
dovecot.conf
auth_debug = yes
auth_debug_passwords = yes
auth_verbose = yes
default_internal_user = root
default_login_user = mail
disable_plaintext_auth = no
first_valid_uid = 100
mail_access_groups = mail users sysadmin popusers
mail_location =
mbox:%h/mail:LAYOUT=maildir++:INDEX=MEMORY:CONTROL=%h/mail/control:INBOX=/var/spool/vmail/%d/%n
mail_max_userip_connections = 40
mail_plugins = " quota sieve"
managesieve_notify_capability = mailto
managesieve_sieve_capability = fileinto reject envelope
encoded-character vacation subaddress comparator-i;ascii-numeric
relational regex imap4flags copy include variables body enotify
environment mailbox date ihave
mbox_lazy_writes = no
passdb {
args = /vdl/etc/dovecot/dovecot-sql.conf
driver = sql
}
plugin {
sieve = %h/mail/sieve/%n
sieve_global_dir = /vdl/etc/dovecot/
sieve_vacation_default_period = 1d
sieve_vacation_max_period = 7d
}
postmaster_address = x at x.x
protocols = imap pop3
service auth {
unix_listener auth-userdb {
mode = 0600
user = popusers
}
}
service imap-login {
inet_listener imap {
port = 143
}
process_min_avail = 4
vsz_limit = 256 M
}
service pop3-login {
inet_listener pop3 {
port = 110
}
process_min_avail = 16
}
ssl = no
submission_host = localhost:25
userdb {
args = /vdl/etc/dovecot/dovecot-sql.conf
driver = sql
}
protocol imap {
mail_plugins = quota imap_quota mail_log notify
}
protocol pop3 {
mail_plugins = quota
}
protocol lda {
mail_plugins = quota sieve
}
dovecot-sql.conf
password_query = SELECT \
concat(s_courriel_utilisateur.utilisateur, '@', s_courriel_domaine.nom)
AS user,s_courriel_utilisateur.password as password \
FROM s_courriel_utilisateur left join s_courriel_alias on
s_courriel_utilisateur.id=s_courriel_alias.utilisateur and
s_courriel_alias.archive !=1 ,s_courriel_domaine \
WHERE s_courriel_utilisateur.archive !=1 \
and s_courriel_domaine.archive !=1 \
and
s_courriel_utilisateur.domaine=s_courriel_domaine.id \
and s_courriel_domaine.nom = '%d' \
and ( s_courriel_utilisateur.utilisateur = '%n'
or s_courriel_alias.alias = '%n' )
user_query = SELECT \
concat("/vhome/",s_courriel_domaine.nom,"/home/",s_courriel_utilisateur.utilisateur)
as home, \
concat("/vhome/",s_courriel_domaine.nom,"/home/",s_courriel_utilisateur.utilisateur,"/mail/index")
as "index", \
concat("/vhome/",s_courriel_domaine.nom,"/home/",s_courriel_utilisateur.utilisateur,"/mail/control")
as control, \
concat("/var/spool/vmail/",s_courriel_domaine.nom,"/",s_courriel_utilisateur.utilisateur)
as inbox, \
"maildir++" as layout, \
s_courriel_utilisateur.uid as
uid,s_courriel_domaine.gid as gid \
FROM s_courriel_utilisateur left join s_courriel_alias on
s_courriel_utilisateur.id=s_courriel_alias.utilisateur and
s_courriel_alias.archive !=1 ,s_courriel_domaine \
WHERE s_courriel_utilisateur.archive !=1 \
and s_courriel_domaine.archive !=1 \
and
s_courriel_utilisateur.domaine=s_courriel_domaine.id \
and s_courriel_domaine.nom = '%d' \
and ( s_courriel_utilisateur.utilisateur = '%n'
or s_courriel_alias.alias = '%n' )
Transaction debug for cedrict at mail.vdl with message added to file
/var/spool/vmail/mail.vdl/cedrict
SELECT
concat("/vhome/",s_courriel_domaine.nom,"/home/",s_courriel_utilisateur.utilisateur)
as home,
concat("/vhome/",s_courriel_domaine.nom,"/home/",s_courriel_utilisateur.utilisateur,"/mail/index")
as "index",
concat("/vhome/",s_courriel_domaine.nom,"/home/",s_courriel_utilisateur.utilisateur,"/mail/control")
as control,
concat("/var/spool/vmail/",s_courriel_domaine.nom,"/",s_courriel_utilisateur.utilisateur)
as inbox, "maildir++" as layout, s_courriel_utilisateur.uid as
uid,s_courriel_domaine.gid as gid FROM s_courriel_utilisateur left join
s_courriel_alias on
s_courriel_utilisateur.id=s_courriel_alias.utilisateur and
s_courriel_alias.archive !=1 ,s_courriel_domaine WHERE
s_courriel_utilisateur.archive !=1 and s_courriel_domaine.archive !=1
and s_courriel_utilisateur.domaine=s_courriel_domaine.id and
s_courriel_domaine.nom = 'mail.vdl' and (
s_courriel_utilisateur.utilisateur = 'cedrict' or
s_courriel_alias.alias = 'cedrict');
+------------------------------+-----------------------------------------+-------------------------------------------+-----------------------------------+-----------+-------+------+
| home | index
| control |
inbox | layout | uid | gid |
+------------------------------+-----------------------------------------+-------------------------------------------+-----------------------------------+-----------+-------+------+
| /vhome/mail.vdl/home/cedrict | /vhome/mail.vdl/home/cedrict/mail/index
| /vhome/mail.vdl/home/cedrict/mail/control |
/var/spool/vmail/mail.vdl/cedrict | maildir++ | 60001 | 231 |
+------------------------------+-----------------------------------------+-------------------------------------------+-----------------------------------+-----------+-------+------+
Jul 17 14:00:56 devshed dovecot: auth: Debug: master in: USER 1
cedrict at mail.vdl service=lda
Jul 17 14:00:56 devshed dovecot: auth-worker(7009): Debug: Loading
modules from directory: /vdl/lib/dovecot/auth
Jul 17 14:00:56 devshed dovecot: auth-worker(7009): Debug:
sql(cedrict at mail.vdl): SELECT
concat("/vhome/",s_courriel_domaine.nom,"/home/",s_courriel_utilisateur.utilisateur)
as home,
concat("/vhome/",s_courriel_domaine.nom,"/home/",s_courriel_utilisateur.utilisateur,"/mail/index")
as "index",
concat("/vhome/",s_courriel_domaine.nom,"/home/",s_courriel_utilisateur.utilisateur,"/mail/control")
as control,
concat("/var/spool/vmail/",s_courriel_domaine.nom,"/",s_courriel_utilisateur.utilisateur)
as inbox, "maildir++" as layout, s_courriel_utilisateur.uid as
uid,s_courriel_domaine.gid as gid FROM s_courriel_utilisateur left join
s_courriel_alias on
s_courriel_utilisateur.id=s_courriel_alias.utilisateur and
s_courriel_alias.archive !=1 ,s_courriel_domaine WHERE
s_courriel_utilisateur.archive !=1 and s_courriel_domaine.archive !=1
and s_courriel_utilisateur.domaine=s_courriel_domaine.id and
s_courriel_domaine.nom = 'mail.vdl' and (
s_courriel_utilisateur.utilisateur = 'cedrict' or
s_courriel_alias.alias = 'cedrict'
Jul 17 14:00:56 devshed dovecot: auth: Debug: userdb out: USER 1
cedrict at mail.vdl home=/vhome/mail.vdl/home/cedrict
index=/vhome/mail.vdl/home/cedrict/mail/index
control=/vhome/mail.vdl/home/cedrict/mail/control
inbox=/var/spool/vmail/mail.vdl/cedrict layout=maildir++ uid=60001
gid=231
Jul 17 14:00:56 devshed dovecot: lda: Debug: auth input:
cedrict at mail.vdl home=/vhome/mail.vdl/home/cedrict
index=/vhome/mail.vdl/home/cedrict/mail/index
control=/vhome/mail.vdl/home/cedrict/mail/control
inbox=/var/spool/vmail/mail.vdl/cedrict layout=maildir++ uid=60001 gid=231
Jul 17 14:00:56 devshed dovecot: lda: Debug: Added userdb setting:
plugin/control=/vhome/mail.vdl/home/cedrict/mail/control
Jul 17 14:00:56 devshed dovecot: lda: Debug: Added userdb setting:
plugin/inbox=/var/spool/vmail/mail.vdl/cedrict
Jul 17 14:00:56 devshed dovecot: lda: Debug: Added userdb setting:
plugin/index=/vhome/mail.vdl/home/cedrict/mail/index
Jul 17 14:00:56 devshed dovecot: lda: Debug: Added userdb setting:
plugin/layout=maildir++
Jul 17 14:00:56 devshed dovecot: lda(cedrict at mail.vdl): Debug: Effective
uid=60001, gid=231, home=/vhome/mail.vdl/home/cedrict
Jul 17 14:00:56 devshed dovecot: lda(cedrict at mail.vdl): Debug: quota: No
quota setting - plugin disabled
Jul 17 14:00:56 devshed dovecot: lda(cedrict at mail.vdl): Debug:
maildir++: root=/vhome/mail.vdl/home/cedrict/mail, index=, indexpvt=,
control=/vhome/mail.vdl/home/cedrict/mail/control,
inbox=/var/spool/vmail/mail.vdl/cedrict, alt=
Jul 17 14:00:56 devshed dovecot: lda(cedrict at mail.vdl): Debug: quota: No
quota setting - plugin disabled
Jul 17 14:00:56 devshed dovecot: lda(cedrict at mail.vdl): Debug: none:
root=, index=, indexpvt=, control=, inbox=, alt=
Jul 17 14:00:56 devshed dovecot: lda(cedrict at mail.vdl): Debug:
Destination address: cedrict at mail.vdl (source: user at hostname)
Jul 17 14:00:56 devshed dovecot: lda(cedrict at mail.vdl): Debug: sieve:
Pigeonhole version 0.4.1 initializing
Jul 17 14:00:56 devshed dovecot: lda(cedrict at mail.vdl): Debug: sieve:
script file /vhome/mail.vdl/home/cedrict/mail/sieve/cedrict not found
Jul 17 14:00:56 devshed dovecot: lda(cedrict at mail.vdl): Debug: sieve:
user's script /vhome/mail.vdl/home/cedrict/mail/sieve/cedrict doesn't
exist (trying default script location instead)
Jul 17 14:00:56 devshed dovecot: lda(cedrict at mail.vdl): Debug: sieve: no
default script configured for user
Jul 17 14:00:56 devshed dovecot: lda(cedrict at mail.vdl): Debug: sieve:
user has no valid location for a personal script
Jul 17 14:00:56 devshed dovecot: lda(cedrict at mail.vdl): Debug: sieve: no
scripts to execute: reverting to default delivery.
Jul 17 14:00:56 devshed dovecot: lda(cedrict at mail.vdl):
msgid=<201307171800.r6HI0unE007004 at devshed.derytelecom.ca>: saved mail
to INBOX
Transaction debug for alias test at mail.vdl with message added to file
/var/spool/vmail/mail.vdl/test
mysql> SELECT
concat("/vhome/",s_courriel_domaine.nom,"/home/",s_courriel_utilisateur.utilisateur)
as home,
concat("/vhome/",s_courriel_domaine.nom,"/home/",s_courriel_utilisateur.utilisateur,"/mail/index")
as "index",
concat("/vhome/",s_courriel_domaine.nom,"/home/",s_courriel_utilisateur.utilisateur,"/mail/control")
as control,
concat("/var/spool/vmail/",s_courriel_domaine.nom,"/",s_courriel_utilisateur.utilisateur)
as inbox, "maildir++" as layout, s_courriel_utilisateur.uid as
uid,s_courriel_domaine.gid as gid FROM s_courriel_utilisateur left
join s_courriel_alias on
s_courriel_utilisateur.id=s_courriel_alias.utilisateur and
s_courriel_alias.archive !=1 ,s_courriel_domaine WHERE
s_courriel_utilisateur.archive !=1 and s_courriel_domaine.archive !=1
and s_courriel_utilisateur.domaine=s_courriel_domaine.id and
s_courriel_domaine.nom = 'mail.vdl' and (
s_courriel_utilisateur.utilisateur = 'test' or s_courriel_alias.alias =
'test' );
+------------------------------+-----------------------------------------+-------------------------------------------+-----------------------------------+-----------+-------+------+
| home | index
| control |
inbox | layout | uid | gid |
+------------------------------+-----------------------------------------+-------------------------------------------+-----------------------------------+-----------+-------+------+
| /vhome/mail.vdl/home/cedrict | /vhome/mail.vdl/home/cedrict/mail/index
| /vhome/mail.vdl/home/cedrict/mail/control |
/var/spool/vmail/mail.vdl/cedrict | maildir++ | 60001 | 231 |
+------------------------------+-----------------------------------------+-------------------------------------------+-----------------------------------+-----------+-------+------+
1 row in set (0,00 sec)
Jul 17 14:03:10 devshed dovecot: auth-worker(7135): Debug:
sql(test at mail.vdl): SELECT
concat("/vhome/",s_courriel_domaine.nom,"/home/",s_courriel_utilisateur.utilisateur)
as home,
concat("/vhome/",s_courriel_domaine.nom,"/home/",s_courriel_utilisateur.utilisateur,"/mail/index")
as "index",
concat("/vhome/",s_courriel_domaine.nom,"/home/",s_courriel_utilisateur.utilisateur,"/mail/control")
as control,
concat("/var/spool/vmail/",s_courriel_domaine.nom,"/",s_courriel_utilisateur.utilisateur)
as inbox, "maildir++" as layout, s_courriel_utilisateur.uid as
uid,s_courriel_domaine.gid as gid FROM s_courriel_utilisateur left
join s_courriel_alias on
s_courriel_utilisateur.id=s_courriel_alias.utilisateur and
s_courriel_alias.archive !=1 ,s_courriel_domaine WHERE
s_courriel_utilisateur.archive !=1 and s_courriel_domaine.archive !=1
and s_courriel_utilisateur.domaine=s_courriel_domaine.id and
s_courriel_domaine.nom = 'mail.vdl' and (
s_courriel_utilisateur.utilisateur = 'test' or s_courriel_alias.alias =
'test' )
Jul 17 14:03:10 devshed dovecot: auth: Debug: userdb out: USER 1
test at mail.vdl home=/vhome/mail.vdl/home/cedrict
index=/vhome/mail.vdl/home/cedrict/mail/index
control=/vhome/mail.vdl/home/cedrict/mail/control
inbox=/var/spool/vmail/mail.vdl/cedrict layout=maildir++ uid=60001
gid=231
Jul 17 14:03:10 devshed dovecot: lda: Debug: auth input: test at mail.vdl
home=/vhome/mail.vdl/home/cedrict
index=/vhome/mail.vdl/home/cedrict/mail/index
control=/vhome/mail.vdl/home/cedrict/mail/control
inbox=/var/spool/vmail/mail.vdl/cedrict layout=maildir++ uid=60001 gid=231
Jul 17 14:03:10 devshed dovecot: lda: Debug: Added userdb setting:
plugin/control=/vhome/mail.vdl/home/cedrict/mail/control
Jul 17 14:03:10 devshed dovecot: lda: Debug: Added userdb setting:
plugin/inbox=/var/spool/vmail/mail.vdl/cedrict
Jul 17 14:03:10 devshed dovecot: lda: Debug: Added userdb setting:
plugin/index=/vhome/mail.vdl/home/cedrict/mail/index
Jul 17 14:03:10 devshed dovecot: lda: Debug: Added userdb setting:
plugin/layout=maildir++
Jul 17 14:03:10 devshed dovecot: lda(test at mail.vdl): Debug: Effective
uid=60001, gid=231, home=/vhome/mail.vdl/home/cedrict
Jul 17 14:03:10 devshed dovecot: lda(test at mail.vdl): Debug: quota: No
quota setting - plugin disabled
Jul 17 14:03:10 devshed dovecot: lda(test at mail.vdl): Debug: maildir++:
root=/vhome/mail.vdl/home/cedrict/mail, index=, indexpvt=,
control=/vhome/mail.vdl/home/cedrict/mail/control,
inbox=/var/spool/vmail/mail.vdl/test, alt=
Jul 17 14:03:10 devshed dovecot: lda(test at mail.vdl): Debug: quota: No
quota setting - plugin disabled
Jul 17 14:03:10 devshed dovecot: lda(test at mail.vdl): Debug: none: root=,
index=, indexpvt=, control=, inbox=, alt=
Jul 17 14:03:10 devshed dovecot: lda(test at mail.vdl): Debug: Destination
address: test at mail.vdl (source: user at hostname)
Jul 17 14:03:10 devshed dovecot: lda(test at mail.vdl): Debug: sieve:
Pigeonhole version 0.4.1 initializing
Jul 17 14:03:10 devshed dovecot: lda(test at mail.vdl): Debug: sieve:
script file /vhome/mail.vdl/home/cedrict/mail/sieve/test not found
Jul 17 14:03:10 devshed dovecot: lda(test at mail.vdl): Debug: sieve:
user's script /vhome/mail.vdl/home/cedrict/mail/sieve/test doesn't exist
(trying default script location instead)
Jul 17 14:03:10 devshed dovecot: lda(test at mail.vdl): Debug: sieve: no
default script configured for user
Jul 17 14:03:10 devshed dovecot: lda(test at mail.vdl): Debug: sieve: user
has no valid location for a personal script
Jul 17 14:03:10 devshed dovecot: lda(test at mail.vdl): Debug: sieve: no
scripts to execute: reverting to default delivery.
I also tried adding the username in the sql query, same problem, there's
the debug :
[root at devshed ~]# Jul 17 14:11:59 devshed dovecot: lda: Debug: Loading
modules from directory: /vdl/lib/dovecot
Jul 17 14:11:59 devshed dovecot: lda: Debug: Module loaded:
/vdl/lib/dovecot/lib10_quota_plugin.so
Jul 17 14:11:59 devshed dovecot: lda: Debug: Module loaded:
/vdl/lib/dovecot/lib90_sieve_plugin.so
Jul 17 14:11:59 devshed dovecot: auth: Debug: master in: USER 1
test at mail.vdl service=lda
Jul 17 14:11:59 devshed dovecot: auth-worker(7385): Debug: Loading
modules from directory: /vdl/lib/dovecot/auth
Jul 17 14:11:59 devshed dovecot: auth-worker(7385): Debug:
sql(test at mail.vdl): SELECT s_courriel_utilisateur.utilisateur as
username,
concat("/vhome/",s_courriel_domaine.nom,"/home/",s_courriel_utilisateur.utilisateur)
as home,
concat("/vhome/",s_courriel_domaine.nom,"/home/",s_courriel_utilisateur.utilisateur,"/mail/index")
as "index",
concat("/vhome/",s_courriel_domaine.nom,"/home/",s_courriel_utilisateur.utilisateur,"/mail/control")
as control,
concat("/var/spool/vmail/",s_courriel_domaine.nom,"/",s_courriel_utilisateur.utilisateur)
as inbox, "maildir++" as layout, s_courriel_utilisateur.uid as
uid,s_courriel_domaine.gid as gid FROM s_courriel_utilisateur left
join s_courriel_alias on
s_courriel_utilisateur.id=s_courriel_alias.utilisateur and
s_courriel_alias.archive !=1 ,s_courriel_domaine WHERE
s_courriel_utilisateur.archive !=1 and s_courriel_domaine.archive !=1
and s_courriel_utilisateur.domaine=s_courriel_domaine.id and
s_courriel_domaine.nom = 'mail.vdl' and (
s_courriel_utilisateur.utilisateur = 'tes
Jul 17 14:11:59 devshed dovecot: auth-worker(7385): Debug:
auth(test at mail.vdl): username changed test at mail.vdl -> cedrict at mail.vdl
Jul 17 14:11:59 devshed dovecot: auth: Debug: userdb out: USER 1
test at mail.vdl home=/vhome/mail.vdl/home/cedrict
index=/vhome/mail.vdl/home/cedrict/mail/index
control=/vhome/mail.vdl/home/cedrict/mail/control
inbox=/var/spool/vmail/mail.vdl/cedrict layout=maildir++ uid=60001
gid=231
Jul 17 14:11:59 devshed dovecot: lda: Debug: auth input: test at mail.vdl
home=/vhome/mail.vdl/home/cedrict
index=/vhome/mail.vdl/home/cedrict/mail/index
control=/vhome/mail.vdl/home/cedrict/mail/control
inbox=/var/spool/vmail/mail.vdl/cedrict layout=maildir++ uid=60001 gid=231
Jul 17 14:11:59 devshed dovecot: lda: Debug: Added userdb setting:
plugin/control=/vhome/mail.vdl/home/cedrict/mail/control
Jul 17 14:11:59 devshed dovecot: lda: Debug: Added userdb setting:
plugin/inbox=/var/spool/vmail/mail.vdl/cedrict
Jul 17 14:11:59 devshed dovecot: lda: Debug: Added userdb setting:
plugin/index=/vhome/mail.vdl/home/cedrict/mail/index
Jul 17 14:11:59 devshed dovecot: lda: Debug: Added userdb setting:
plugin/layout=maildir++
Jul 17 14:11:59 devshed dovecot: lda(test at mail.vdl): Debug: Effective
uid=60001, gid=231, home=/vhome/mail.vdl/home/cedrict
Jul 17 14:11:59 devshed dovecot: lda(test at mail.vdl): Debug: quota: No
quota setting - plugin disabled
Jul 17 14:11:59 devshed dovecot: lda(test at mail.vdl): Debug: maildir++:
root=/vhome/mail.vdl/home/cedrict/mail, index=, indexpvt=,
control=/vhome/mail.vdl/home/cedrict/mail/control,
inbox=/var/spool/vmail/mail.vdl/test, alt=
Jul 17 14:11:59 devshed dovecot: lda(test at mail.vdl): Debug: quota: No
quota setting - plugin disabled
Jul 17 14:11:59 devshed dovecot: lda(test at mail.vdl): Debug: none: root=,
index=, indexpvt=, control=, inbox=, alt=
Jul 17 14:11:59 devshed dovecot: lda(test at mail.vdl): Debug: Destination
address: test at mail.vdl (source: user at hostname)
Jul 17 14:11:59 devshed dovecot: lda(test at mail.vdl): Debug: sieve:
Pigeonhole version 0.4.1 initializing
Jul 17 14:11:59 devshed dovecot: lda(test at mail.vdl): Debug: sieve:
script file /vhome/mail.vdl/home/cedrict/mail/sieve/test not found
Jul 17 14:11:59 devshed dovecot: lda(test at mail.vdl): Debug: sieve:
user's script /vhome/mail.vdl/home/cedrict/mail/sieve/test doesn't exist
(trying default script location instead)
Jul 17 14:11:59 devshed dovecot: lda(test at mail.vdl): Debug: sieve: no
default script configured for user
Jul 17 14:11:59 devshed dovecot: lda(test at mail.vdl): Debug: sieve: user
has no valid location for a personal script
Jul 17 14:11:59 devshed dovecot: lda(test at mail.vdl): Debug: sieve: no
scripts to execute: reverting to default delivery.
Jul 17 14:11:59 devshed dovecot: lda(test at mail.vdl):
msgid=<201307171811.r6HIBxWe007380 at devshed.derytelecom.ca>: saved mail
to INBOX
Cedric
More information about the dovecot
mailing list