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
signature.asc
Description: This is a digitally signed message part.
