[Dovecot] Need a little shadow to MySQL conversion help
Andreas Ntaflos
daff at dword.org
Tue Oct 20 16:45:39 EEST 2009
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
More information about the dovecot
mailing list