[Dovecot] Need a little shadow to MySQL conversion help

[Andreas Ntaflos]

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: 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/c07b2f72/attachment-0001.bin