[Dovecot] Dovecot 2.2.4/sendmail with sql user and aliases
Cedric
cedrict at derytelecom.ca
Tue Jul 23 03:41:30 EEST 2013
Hi,
Ok thank for the interpretation, we used sendmail the last 16 years and
I'm trying to migrate a old linuxconf/imapd setup here.
Le 2013-07-22 18:02, Axel Luttgens a écrit :
> Le 22 juil. 2013 à 19:31, Cedric a écrit :
>
>> Hi,
>>
>> There's the information again :
>>
>> I use sendmail with dovecot-lda delivery :
>>
>> Mvirtual, P=/vdl/libexec/dovecot/dovecot-lda, F=lsDFMShP, S=10, R=20/40,
>> A=/vdl/libexec/dovecot/dovecot-lda -d $u@$h
> Ha... So, there are still people using Sendmail for new installations. ;-)
> Sorry, I don't know why I have thought about postfix.
> My memories about Sendmail are very, very tiny, so I'll assume the above is correct (doesn't seem too audacious, given the lines with "to=test at mail.vdl ... mailer=virtual" written by Sendmail in the log).
>
> Please find hereafter my quick interpretation of what's happening when sending a message to "test at mail.vdl" (note that the same description should apply for a message to "cedrict at mail.vdl" as well).
>
>
>> The current query :
>>
>> user_query = SELECT \
>> s_courriel_utilisateur.utilisateur as user, \
> According to the query output you provide below, you are replacing here "test at mail.vdl" by "cedrict at mail.vdl"
>
>
>> s_courriel_utilisateur.utilisateur as username, \
> and here "test" by "cedrict"
>
>
>> s_courriel_domaine.nom as domain, \
> and here "mail.vdl" by NULL?
> Or something like that.
> Anyway, those substitutions should explain the "username changed test at mail.vdl -> cedrict" and "cedrict -> cedrict at mail.vdl" infos in the log.
> The problem is, the effect of such substitutions isn't really defined in a user_query: at best they don't do anything, at worse they may lead to strange interpretations of %n, %u, %d in doevecot.conf.
>
>
>> 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, \
> Not sure, but I think you are trying to split into three pieces (index, control, inbox) what Dovecot expects to be returned as a single column "mail" (which, when not NULL, overrides mail_location) from a user_query.
>
>
>> "maildir++" as layout, \
> Is this OK in the context of a user_query?
nope it was all in hope to resolve the issue.
>
>> 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 = '%Ld' \
>> and ( s_courriel_utilisateur.utilisateur = '%Ln' or s_courriel_alias.alias = '%Ln' )
>>
>>
>> The query, including the output :
>>
>>
>> [...]
>> *************************** 1. row ***************************
>> user: cedrict at mail.vdl
>> username: cedrict
>> 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
>>
>> The dovecot log , you can see dovecot lda deliver the message to inbox=/var/spool/vmail/mail.vdl/test and not inbox=/var/spool/vmail/mail.vdl/cedrict
>>
>> [root at devshed vdl]# Jul 22 13:29:47 devshed sendmail[32409]: dangerous permissions=40777 on queue directory /var/spool/clientmqueue/
>> [...]
>> Jul 22 13:29:47 devshed dovecot: auth: Debug: master in: USER 1 test at mail.vdl service=lda
>> [...]
>> Jul 22 13:29:47 devshed dovecot: auth-worker(32406): Debug: auth(test at mail.vdl): username changed test at mail.vdl -> cedrict
>> Jul 22 13:29:47 devshed dovecot: auth-worker(32406): Debug: auth(cedrict): username changed cedrict -> cedrict at mail.vdl
>> Jul 22 13:29:47 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 22 13:29:47 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 22 13:29:47 devshed dovecot: lda: Debug: Added userdb setting: plugin/control=/vhome/mail.vdl/home/cedrict/mail/control
>> Jul 22 13:29:47 devshed dovecot: lda: Debug: Added userdb setting: plugin/inbox=/var/spool/vmail/mail.vdl/cedrict
>> Jul 22 13:29:47 devshed dovecot: lda: Debug: Added userdb setting: plugin/index=/vhome/mail.vdl/home/cedrict/mail/index
>> Jul 22 13:29:47 devshed dovecot: lda: Debug: Added userdb setting: plugin/layout=maildir++
> Note here "plugin/control", "plugin/inbox", "plugin/index" and "plugin/layout": considered as unknown settings and placed into plugin sections.
>
>
>> Jul 22 13:29:47 devshed dovecot: lda(test at mail.vdl): Debug: Effective uid=60001, gid=231, home=/vhome/mail.vdl/home/cedrict
>> Jul 22 13:29:47 devshed dovecot: lda(test at mail.vdl): Debug: quota: No quota setting - plugin disabled
>> Jul 22 13:29:47 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=
> The home directory is thus the one provided without ambiguity by the user_query.
> See below for the control and the inbox.
>
>
>> Jul 22 13:29:47 devshed dovecot: lda(test at mail.vdl): Debug: quota: No quota setting - plugin disabled
>> Jul 22 13:29:47 devshed dovecot: lda(test at mail.vdl): Debug: none: root=, index=, indexpvt=, control=, inbox=, alt=
>> Jul 22 13:29:47 devshed dovecot: lda(test at mail.vdl): Debug: Destination address: test at mail.vdl (source: user at hostname)
> Dovecot is now a bit lost and seems to revert to "reasonable" guesses: the domain seems to have been lost (hence the hostname), but the original user ("test") is still at hand.
>
> So, there was still a way to find out something, with the help of that line in dovecot.conf:
>
> mail_location = mbox:%h/mail:LAYOUT=maildir++:INDEX=MEMORY:CONTROL=%h/mail/control:INBOX=/var/spool/vmail/%d/%n
Below i provided two differents sql attempt, your input on how to make
dovecot lda understand it should deliver the mail to the correct file
are welcome
>
>> [...]
>>
>> Thank in advance.
> HTH,
> Axel
There's a new debug output fixing the domain entry
The querry i pasted seem to be wrong for a previous configuration
there's the correct one :
mysql> SELECT
concat(s_courriel_utilisateur.utilisateur,"@",s_courriel_domaine.nom) as
user,s_courriel_utilisateur.utilisateur as
username,s_courriel_domaine.nom as
domain,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' )\G;
*************************** 1. row ***************************
user: cedrict at mail.vdl
username: cedrict
domain: 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 22 20:27:05 devshed sendmail[11778]: r6N0R5bq011778: from=root,
size=36, class=0, nrcpts=1,
msgid=<201307230027.r6N0R5bq011778 at devshed.derytelecom.ca>,
relay=root at localhost
Jul 22 20:27:05 devshed sendmail[11779]: STARTTLS=server,
relay=devshed.derytelecom.ca [127.0.0.1], version=TLSv1/SSLv3,
verify=NO, cipher=DHE-RSA-AES256-SHA, bits=256/256
Jul 22 20:27:05 devshed sendmail[11778]: STARTTLS=client,
relay=[127.0.0.1], version=TLSv1/SSLv3, verify=FAIL,
cipher=DHE-RSA-AES256-SHA, bits=256/256
Jul 22 20:27:05 devshed sendmail[11779]: r6N0R5hL011779:
from=<root at devshed.derytelecom.ca>, size=332, class=0, nrcpts=1,
msgid=<201307230027.r6N0R5bq011778 at devshed.derytelecom.ca>, proto=ESMTP,
daemon=MTA-RX, relay=devshed.derytelecom.ca [127.0.0.1]
Jul 22 20:27:05 devshed sendmail[11778]: r6N0R5bq011778:
to=test at mail.vdl, ctladdr=root (0/0), delay=00:00:00, xdelay=00:00:00,
mailer=relay, pri=30036, relay=[127.0.0.1] [127.0.0.1], dsn=2.0.0,
stat=Sent (r6N0R5hL011779 Message accepted for delivery)
Jul 22 20:27:05 devshed dovecot: lda: Debug: Loading modules from
directory: /vdl/lib/dovecot
Jul 22 20:27:05 devshed dovecot: lda: Debug: Module loaded:
/vdl/lib/dovecot/lib10_quota_plugin.so
Jul 22 20:27:05 devshed dovecot: lda: Debug: Module loaded:
/vdl/lib/dovecot/lib90_sieve_plugin.so
Jul 22 20:27:05 devshed dovecot: auth: Debug: master in: USER 1
test at mail.vdl service=lda
Jul 22 20:27:05 devshed dovecot: auth-worker(11783): Debug: Loading
modules from directory: /vdl/lib/dovecot/auth
Jul 22 20:27:05 devshed dovecot: auth-worker(11783): Debug:
sql(test at mail.vdl): SELECT
concat(s_courriel_utilisateur.utilisateur,'@',s_courriel_domaine.nom) as
user, s_courriel_utilisateur.utilisateur as username,
s_courriel_domaine.nom as domain,
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.dom
Jul 22 20:27:06 devshed dovecot: auth-worker(11783): Debug:
auth(test at mail.vdl): username changed test at mail.vdl -> cedrict at mail.vdl
Jul 22 20:27:06 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 22 20:27:06 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 22 20:27:06 devshed dovecot: lda: Debug: Added userdb setting:
plugin/control=/vhome/mail.vdl/home/cedrict/mail/control
Jul 22 20:27:06 devshed dovecot: lda: Debug: Added userdb setting:
plugin/inbox=/var/spool/vmail/mail.vdl/cedrict
Jul 22 20:27:06 devshed dovecot: lda: Debug: Added userdb setting:
plugin/index=/vhome/mail.vdl/home/cedrict/mail/index
Jul 22 20:27:06 devshed dovecot: lda: Debug: Added userdb setting:
plugin/layout=maildir++
Jul 22 20:27:06 devshed dovecot: lda(test at mail.vdl): Debug: Effective
uid=60001, gid=231, home=/vhome/mail.vdl/home/cedrict
Jul 22 20:27:06 devshed dovecot: lda(test at mail.vdl): Debug: quota: No
quota setting - plugin disabled
Jul 22 20:27:06 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 22 20:27:06 devshed dovecot: lda(test at mail.vdl): Debug: quota: No
quota setting - plugin disabled
Jul 22 20:27:06 devshed dovecot: lda(test at mail.vdl): Debug: none: root=,
index=, indexpvt=, control=, inbox=, alt=
Jul 22 20:27:06 devshed dovecot: lda(test at mail.vdl): Debug: Destination
address: test at mail.vdl (source: user at hostname)
Jul 22 20:27:06 devshed dovecot: lda(test at mail.vdl): Debug: sieve:
Pigeonhole version 0.4.1 initializing
Jul 22 20:27:06 devshed dovecot: lda(test at mail.vdl): Debug: sieve:
script file /vhome/mail.vdl/home/cedrict/mail/sieve/test not found
Jul 22 20:27:06 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 22 20:27:06 devshed dovecot: lda(test at mail.vdl): Debug: sieve: no
default script configured for user
Jul 22 20:27:06 devshed dovecot: lda(test at mail.vdl): Debug: sieve: user
has no valid location for a personal script
Jul 22 20:27:06 devshed dovecot: lda(test at mail.vdl): Debug: sieve: no
scripts to execute: reverting to default delivery.
Jul 22 20:27:06 devshed dovecot: lda(test at mail.vdl):
msgid=<201307230027.r6N0R5bq011778 at devshed.derytelecom.ca>: saved mail
to INBOX
Jul 22 20:27:06 devshed sendmail[11781]: r6N0R5hL011779:
to=<test at mail.vdl>, ctladdr=<root at devshed.derytelecom.ca> (0/0),
delay=00:00:01, xdelay=00:00:01, mailer=virtual, pri=120332,
relay=mail.vdl, dsn=2.0.0, stat=Sent
There's a second config with without domain,maildir,control or user
mysql> SELECT
concat(s_courriel_utilisateur.utilisateur,'@',s_courriel_domaine.nom) as
user,
concat("/var/spool/vmail/",s_courriel_domaine.nom,"/",s_courriel_utilisateur.utilisateur)
as inbox,
concat("/vhome/",s_courriel_domaine.nom,"/home/",s_courriel_utilisateur.utilisateur)
as home, 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' )\G;
*************************** 1. row ***************************
user: cedrict at mail.vdl
inbox: /var/spool/vmail/mail.vdl/cedrict
home: /vhome/mail.vdl/home/cedrict
uid: 60001
gid: 231
1 row in set (0,54 sec)
Jul 22 20:39:22 devshed dovecot: auth: Debug: master in: USER 1
test at mail.vdl service=lda
Jul 22 20:39:22 devshed dovecot: auth-worker(12131): Debug:
sql(test at mail.vdl): SELECT
concat(s_courriel_utilisateur.utilisateur,'@',s_courriel_domaine.nom) as
user,
concat("/var/spool/vmail/",s_courriel_domaine.nom,"/",s_courriel_utilisateur.utilisateur)
as inbox,
concat("/vhome/",s_courriel_domaine.nom,"/home/",s_courriel_utilisateur.utilisateur)
as home, 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 22 20:39:22 devshed dovecot: auth-worker(12131): Debug:
auth(test at mail.vdl): username changed test at mail.vdl -> cedrict at mail.vdl
Jul 22 20:39:22 devshed dovecot: auth: Debug: userdb out: USER 1
test at mail.vdl inbox=/var/spool/vmail/mail.vdl/cedrict
home=/vhome/mail.vdl/home/cedrict uid=60001 gid=231
Jul 22 20:39:22 devshed dovecot: lda: Debug: auth input: test at mail.vdl
inbox=/var/spool/vmail/mail.vdl/cedrict
home=/vhome/mail.vdl/home/cedrict uid=60001 gid=231
Jul 22 20:39:22 devshed dovecot: lda: Debug: Added userdb setting:
plugin/inbox=/var/spool/vmail/mail.vdl/cedrict
Jul 22 20:39:22 devshed dovecot: lda(test at mail.vdl): Debug: Effective
uid=60001, gid=231, home=/vhome/mail.vdl/home/cedrict
Jul 22 20:39:22 devshed dovecot: lda(test at mail.vdl): Debug: quota: No
quota setting - plugin disabled
Jul 22 20:39:22 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 22 20:39:22 devshed dovecot: lda(test at mail.vdl): Debug: quota: No
quota setting - plugin disabled
Jul 22 20:39:22 devshed dovecot: lda(test at mail.vdl): Debug: none: root=,
index=, indexpvt=, control=, inbox=, alt=
Jul 22 20:39:22 devshed dovecot: lda(test at mail.vdl): Debug: Destination
address: test at mail.vdl (source: user at hostname)
Jul 22 20:39:22 devshed dovecot: lda(test at mail.vdl): Debug: sieve:
Pigeonhole version 0.4.1 initializing
Jul 22 20:39:22 devshed dovecot: lda(test at mail.vdl): Debug: sieve:
script file /vhome/mail.vdl/home/cedrict/mail/sieve/test not found
Jul 22 20:39:22 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 22 20:39:22 devshed dovecot: lda(test at mail.vdl): Debug: sieve: no
default script configured for user
Jul 22 20:39:22 devshed dovecot: lda(test at mail.vdl): Debug: sieve: user
has no valid location for a personal script
Jul 22 20:39:22 devshed dovecot: lda(test at mail.vdl): Debug: sieve: no
scripts to execute: reverting to default delivery.
Jul 22 20:39:22 devshed dovecot: lda(test at mail.vdl):
msgid=<201307230039.r6N0dLKl012153 at devshed.derytelecom.ca>: saved mail
to INBOX
Jul 22 20:39:22 devshed sendmail[12156]: r6N0dL0O012154:
to=<test at mail.vdl>, ctladdr=<root at devshed.derytelecom.ca> (0/0),
delay=00:00:00, xdelay=00:00:00, mailer=virtual, pri=120329,
relay=mail.vdl, dsn=2.0.0, stat=Sent
Thank in advance
Cedric
More information about the dovecot
mailing list