[Dovecot] Need a little shadow to MySQL conversion help

[Andreas Ntaflos]

On Tuesday 20 October 2009 17:07:49 Timo Sirainen wrote:
> On Oct 20, 2009, at 9:45 AM, Andreas Ntaflos wrote:
> > I'd say you need a static userdb. Something like this:
>
> A couple of small things:
> > passdb sql {
> > args = /etc/dovecot/dovecot-sql.conf
> > }
> >
> > userdb static {
> > args = uid=vmail gid=vmail home=/var/vmail/%Ld/%Ln
> > allow_all_users=yes }
>
> %Ld/%Ln at this point works for now, but maybe not in future. I'd
> have passdb already change it lowercase. Also allow_all_users=yes
> isn't required (but maybe not harmful) since passdb sql is used.

That's good to know, thanks. And you're right of course, the passdb 
should already return the correct information, Dovecot shouldn't be 
asked to change anything afterwards.

Fortunately I myself don't use MySQL anymore but have migrated 
everything to PostgreSQL. It does a much better job as a DBMS (remote, 
secured access to the DB, fine-grained permissions and rules) and also 
knows about case sensitivity. So foobar at example.com is not the same as 
FooBar at example.com. Which eliminates this problem class altogether.

> > password_query = SELECT password FROM view_users WHERE email='%u';
>
> SELECT password, email AS user FROM ..
>
> changes username to same case as it's in the database (so lowercase
> probably) and there's no need to use %L anymore.

You mean something like 

SELECT password, LOWER(email) as user FROM view_users WHERE email='%u'

should do the trick? I shall test this on my test server, just for fun.

Andreas
-- 
Andreas Ntaflos
Vienna, Austria

GPG Fingerprint: 6234 2E8E 5C81 C6CB E5EC  7E65 397C E2A8 090C A9B4
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 189 bytes
Desc: This is a digitally signed message part.
Url : http://dovecot.org/pipermail/dovecot/attachments/20091020/f11ed025/attachment.bin