dovecot - Oct 2009 - Need a little shadow to MySQL conversion help

Excuse me for being stupid and confused.

I'm trying to convert my dovecot install from passwd-file to mysql. I 
have a single table called "users" that has 3 fields, user_name, 
domain_name and password. The data came from passwd/shadow files so it 
uses the same password encryption as the shadow files do. The directory 
where the email is stored is calculated elsewhere and not stored in the 
database. The UID and GID are both mail for all virtual users. The user 
logs in passing the full email address and the password.

So - what would be the config for using MySQL? Do I even need a userdb? 
If so - what would it be?

Thanks in advance.

On Monday 19 October 2009 19:13:36 Marc Perkel wrote:
> Excuse me for being stupid and confused.
>
> I'm trying to convert my dovecot install from passwd-file to mysql. I
> have a single table called "users" that has 3 fields, user_name,
> domain_name and password. The data came from passwd/shadow files so
> it uses the same password encryption as the shadow files do. The
> directory where the email is stored is calculated elsewhere and not
> stored in the database. The UID and GID are both mail for all virtual
> users. The user logs in passing the full email address and the
> password.
>
> So - what would be the config for using MySQL? Do I even need a
> userdb? If so - what would it be?
I'd say you need a static userdb. Something like this:

passdb sql {
 args = /etc/dovecot/dovecot-sql.conf
}

userdb static {
 args = uid=vmail gid=vmail home=/var/vmail/%Ld/%Ln allow_all_users=yes
}

Since you have a "users" table that does not contain the full e-mail 
address of a given user you should create a view that does so that the 
query in dovecot-sql.conf remains simple. You could also simplify the 
users table to contain the full email address. There are ways aplenty. 

mysql>
CREATE VIEW view_users AS
SELECT CONCAT(users.user_name, '@', users.domain_name) AS email,
    users.password
FROM users

Then dovecot-sql.conf could look like this:

driver = mysql
connect = host=127.0.0.1 dbname=mailserver user=mailuser password=foo
default_pass_scheme = CRYPT
password_query = SELECT password FROM view_users WHERE email='%u';

This database setup, however, is not really normalised so if that is a 
concern (it probably will be when you start hosting more than one 
virtual domain) you should look to refine the it.

I can very much recommend the ISPmail tutorials of Christoph Haas, found 
at <http://workaround.org/ispmail>. It explains very well how to do 
virtual mail hosting with a MySQL database backend and other fun stuff. 
Much of what I have written in this message you can find there.

HTH Andreas
-- 
Andreas Ntaflos

GPG Fingerprint: 6234 2E8E 5C81 C6CB E5EC  7E65 397C E2A8 090C A9B4
-- 
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: signature.asc
Type: application/pgp-signature
Size: 189 bytes
Desc: This is a digitally signed message part.
URL:
<http://dovecot.org/pipermail/dovecot/attachments/20091020/c07b2f72/attachment-0002.bin>

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.
> 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.