[Dovecot] 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
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.
On Tuesday 20 October 2009 17:07:49 Timo Sirainen wrote:
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.
That's good to know, thanks. And you're right of course, the passdb should already return the correct information, Dovecot shouldn't be asked to change anything afterwards.
Fortunately I myself don't use MySQL anymore but have migrated everything to PostgreSQL. It does a much better job as a DBMS (remote, secured access to the DB, fine-grained permissions and rules) and also knows about case sensitivity. So foobar@example.com is not the same as FooBar@example.com. Which eliminates this problem class altogether.
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.
You mean something like
SELECT password, LOWER(email) as user FROM view_users WHERE email='%u'
should do the trick? I shall test this on my test server, just for fun.
Andreas
Andreas Ntaflos Vienna, Austria
GPG Fingerprint: 6234 2E8E 5C81 C6CB E5EC 7E65 397C E2A8 090C A9B4
On Tue, 2009-10-20 at 17:57 +0200, Andreas Ntaflos wrote:
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.
You mean something like
SELECT password, LOWER(email) as user FROM view_users WHERE email='%u'
should do the trick? I shall test this on my test server, just for fun.
Well, yes, but "email" field would most likely already be lowercased in the database, so the LOWER() wouldn't be all that useful.
On Tuesday 20 October 2009 17:57:35 Andreas Ntaflos wrote:
On Tuesday 20 October 2009 17:07:49 Timo Sirainen wrote:
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.
That's good to know, thanks. And you're right of course, the passdb should already return the correct information, Dovecot shouldn't be asked to change anything afterwards.
Fortunately I myself don't use MySQL anymore but have migrated everything to PostgreSQL. It does a much better job as a DBMS (remote, secured access to the DB, fine-grained permissions and rules) and also knows about case sensitivity. So foobar@example.com is not the same as FooBar@example.com. Which eliminates this problem class altogether.
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.
You mean something like
SELECT password, LOWER(email) as user FROM view_users WHERE email='%u'
I probably misunderstood that.
SELECT password, email AS user FROM ...
is indeed enough to return the correct case, no need to use the LOWER function.
This whole case sensitivity thing is only a problem if the passdb query returns just the password and no username. Returning the username as well is the way to go.
Andreas
Andreas Ntaflos Vienna, Austria
GPG Fingerprint: 6234 2E8E 5C81 C6CB E5EC 7E65 397C E2A8 090C A9B4
On Tue, 20 Oct 2009 17:57:35 +0200 Andreas Ntaflos daff@dword.org wrote:
On Tuesday 20 October 2009 17:07:49 Timo Sirainen wrote:
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.
That's good to know, thanks. And you're right of course, the passdb should already return the correct information, Dovecot shouldn't be asked to change anything afterwards.
Fortunately I myself don't use MySQL anymore but have migrated everything to PostgreSQL. It does a much better job as a DBMS (remote, secured access to the DB, fine-grained permissions and rules) and also knows about case sensitivity. So foobar@example.com is not the same as FooBar@example.com. Which eliminates this problem class altogether.
MySQL, since at least 5.x knows about case. You can Google for the information if you want.
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.
You mean something like
SELECT password, LOWER(email) as user FROM view_users WHERE email='%u'
should do the trick? I shall test this on my test server, just for fun.
Andreas
Jerry |::::======= gesbbb@yahoo.com |::::======= |=========== |=========== |
Woolsey-Swanson Rule:
People would rather live with a problem they cannot solve rather than accept a solution they cannot understand.
Thank you all for your help on this. I'll probably try it tonight when most people will be off line and hopefully it won't be down long. Everything already is lower case so that shouldn't be an issue.
On Tuesday 20 October 2009 18:57:48 Marc Perkel wrote:
Thank you all for your help on this. I'll probably try it tonight when most people will be off line and hopefully it won't be down long. Everything already is lower case so that shouldn't be an issue.
Another suggestion, a little off-topic and probably not quite original: you should really try to use a test server for experimenting, at least if you do any non-trivial hosting or can tell your users to go to hell when they complain :) Nothing makes your life more miserable than a screwed up mail system you cannot easily recover.
The test server can even be a small virtual machine as long as it sports the same software infrastructure (Dovecot, Postfix, MySQL, Apache and whatever else) as the production system. Using a test system goes a long way in ensuring that you don't run into nasty surprises and only changes you tested and of which you know the consequences are applied to the production instance.
Andreas
Andreas Ntaflos Vienna, Austria
GPG Fingerprint: 6234 2E8E 5C81 C6CB E5EC 7E65 397C E2A8 090C A9B4
On Tuesday 20 October 2009 18:49:16 Jerry wrote:
On Tue, 20 Oct 2009 17:57:35 +0200
Andreas Ntaflos daff@dword.org wrote:
On Tuesday 20 October 2009 17:07:49 Timo Sirainen wrote:
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.
That's good to know, thanks. And you're right of course, the passdb should already return the correct information, Dovecot shouldn't be asked to change anything afterwards.
Fortunately I myself don't use MySQL anymore but have migrated everything to PostgreSQL. It does a much better job as a DBMS (remote, secured access to the DB, fine-grained permissions and rules) and also knows about case sensitivity. So foobar@example.com is not the same as FooBar@example.com. Which eliminates this problem class altogether.
MySQL, since at least 5.x knows about case. You can Google for the information if you want.
Good to know, thanks. Creating tables with a *_cs collation seems to be the trick.
Andreas
Andreas Ntaflos Vienna, Austria
GPG Fingerprint: 6234 2E8E 5C81 C6CB E5EC 7E65 397C E2A8 090C A9B4
This seems to work fine for me. Thanks to everyone for your help.
default_pass_scheme = CRYPT
password_query =
SELECT user_name, domain_name, password
FROM users WHERE user_name = '%n' AND domain_name = '%d'
But - slightly off topic. Suppose I wanted to add some kind of date/time field to MySQL so that I can records the date and time of the last login. Is there an easy way to do that?
On Thu, 2009-10-22 at 11:31 -0700, Marc Perkel wrote:
But - slightly off topic. Suppose I wanted to add some kind of date/time field to MySQL so that I can records the date and time of the last login. Is there an easy way to do that?
http://wiki.dovecot.org/PostLoginScripting#Last-login_tracking is the way to do it currently.
participants (4)
-
Andreas Ntaflos
-
Jerry
-
Marc Perkel
-
Timo Sirainen