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