dovecot: Updated comments and defaults to SQL queries. SQL is ty...

[dovecot at dovecot.org]

details:   http://hg.dovecot.org/dovecot/rev/c3365e886746
changeset: 7110:c3365e886746
user:      Timo Sirainen <tss at iki.fi>
date:      Sat Jan 05 02:29:02 2008 +0200
description:
Updated comments and defaults to SQL queries. SQL is typically used with
separate user and domain fields, so use that kind of default settings.

diffstat:

2 files changed, 38 insertions(+), 32 deletions(-)
doc/dovecot-sql-example.conf |   64 ++++++++++++++++++++++--------------------
src/auth/db-sql.c            |    6 +--

diffs (143 lines):

diff -r e6823d781317 -r c3365e886746 doc/dovecot-sql-example.conf
--- a/doc/dovecot-sql-example.conf	Sat Jan 05 01:28:46 2008 +0200
+++ b/doc/dovecot-sql-example.conf	Sat Jan 05 02:29:02 2008 +0200
@@ -3,20 +3,21 @@
 # http://wiki.dovecot.org/AuthDatabase/SQL
 #
 # For the sql passdb module, you'll need a database with a table that
-# contains fields for at least the userid and password. If you want to
+# contains fields for at least the username and password. If you want to
 # use the user at domain syntax, you might want to have a separate domain
 # field as well.
 #
 # If your users all have the same uig/gid, and have predictable home
 # directories, you can use the static userdb module to generate the home
-# dir based on the userid and domain. In this case, you won't need fields
+# dir based on the username and domain. In this case, you won't need fields
 # for home, uid, or gid in the database.
 #
 # If you prefer to use the sql userdb module, you'll want to add fields
 # for home, uid, and gid. Here is an example table:
 #
 # CREATE TABLE users (
-#     userid VARCHAR(128) NOT NULL,
+#     username VARCHAR(128) NOT NULL,
+#     domain VARCHAR(128) NOT NULL,
 #     password VARCHAR(64) NOT NULL,
 #     home VARCHAR(255) NOT NULL,
 #     uid INTEGER NOT NULL,
@@ -57,7 +58,7 @@
 #   connect = host=sql.example.com dbname=virtual user=virtual password=blarg
 #   connect = /etc/dovecot/authdb.sqlite
 #
-#connect = dbname=virtual user=virtual
+#connect =
 
 # Default password scheme.
 #
@@ -66,20 +67,23 @@
 #
 #default_pass_scheme = PLAIN-MD5
 
-# Query to retrieve the password.
+# Query to retrieve the password. It can return fields:
 #
-# This query must return only one row with "user" and "password" columns.
+#   password - The user's password. This field must be returned.
+#   user - user at domain from the database. Needed with case-insensitive lookups.
+#   username and domain - An alternative way to represent the "user" field.
+#
+# The "user" field is often necessary with case-insensitive lookups to avoid
+# e.g. "name" and "nAme" logins creating two different mail directories. If
+# your user and domain names are in separate fields, you can return "username"
+# and "domain" fields instead of "user".
+#
 # The query can also return other fields which have a special meaning, see
 # http://wiki.dovecot.org/PasswordDatabase/ExtraFields
 #
-# The "user" column is needed to make sure the username gets used with exactly
-# the same casing as it's in the database. Note that if you store username and
-# domain in separate fields, you most likely want to return a combination of
-# them as the "user" column, otherwise the domain gets stripped.
-#
-# Commonly used available substitutions (see
-# http://wiki.dovecot.org/Variables for full list):
-#   %u = entire userid
+# Commonly used available substitutions (see http://wiki.dovecot.org/Variables
+# for full list):
+#   %u = entire user at domain
 #   %n = user part of user at domain
 #   %d = domain part of user at domain
 # 
@@ -88,38 +92,40 @@
 # difficult to have eg. usernames containing '%' characters.
 #
 # Example:
-#   password_query = SELECT concat(userid, '@', domain) AS user, password FROM users WHERE userid = '%n' AND domain = '%d'
-#   password_query = SELECT pw AS password FROM users WHERE userid = '%u' AND active = 'Y'
+#   password_query = SELECT userid AS user, pw AS password \
+#     FROM users WHERE userid = '%u' AND active = 'Y'
 #
 #password_query = \
-#  SELECT userid as user, password \
-#  FROM users WHERE userid = '%u'
+#  SELECT username, domain, password \
+#  FROM users WHERE username = '%n' AND domain = '%d'
 
 # Query to retrieve the user information.
 #
 # The query must return only one row. Commonly returned columns are:
-#   uid - System UID
-#   gid - System GID
+#   uid - System UID (overrides mail_uid setting)
+#   gid - System GID (overrides mail_gid setting)
 #   home - Home directory
-#   mail - Mail location
+#   mail - Mail location (overrides mail_location setting)
 #
-# Either home or mail is required. uid and gid are required. If more than one
-# row is returned or there are missing fields, the login will fail. For a list
-# of all fields that can be returned, see 
+# None of these are strictly required. If you use a single UID and GID, and
+# home or mail directory fits to a template string, you could use userdb static
+# instead. For a list of all fields that can be returned, see
 # http://wiki.dovecot.org/UserDatabase/ExtraFields
 #
-# Examples
-#   user_query = SELECT home, uid, gid FROM users WHERE userid = '%n' AND domain = '%d'
+# Examples:
+#   user_query = SELECT home, uid, gid FROM users WHERE userid = '%u'
 #   user_query = SELECT dir AS home, user AS uid, group AS gid FROM users where userid = '%u'
 #   user_query = SELECT home, 501 AS uid, 501 AS gid FROM users WHERE userid = '%u'
 #
-#user_query = SELECT home, uid, gid FROM users WHERE userid = '%u'
+#user_query = \
+#  SELECT home, uid, gid \
+#  FROM users WHERE username = '%n' AND domain = '%d'
 
 # If you wish to avoid two SQL lookups (passdb + userdb), you can use
 # userdb prefetch instead of userdb sql in dovecot.conf. In that case you'll
 # also have to return userdb fields in password_query prefixed with "userdb_"
 # string. For example:
 #password_query = \
-#  SELECT userid as user, password, \
-#  home as userdb_home, uid as userdb_uid, gid as userdb_gid \
+#  SELECT userid AS user, password, \
+#    home AS userdb_home, uid AS userdb_uid, gid AS userdb_gid \
 #  FROM users WHERE userid = '%u'
diff -r e6823d781317 -r c3365e886746 src/auth/db-sql.c
--- a/src/auth/db-sql.c	Sat Jan 05 01:28:46 2008 +0200
+++ b/src/auth/db-sql.c	Sat Jan 05 02:29:02 2008 +0200
@@ -29,9 +29,9 @@ struct sql_settings default_sql_settings
 struct sql_settings default_sql_settings = {
 	MEMBER(driver) NULL,
 	MEMBER(connect) NULL,
-	MEMBER(password_query) "SELECT password FROM users WHERE userid = '%u'",
-	MEMBER(user_query) "SELECT home, uid, gid FROM users WHERE userid = '%u'",
-	MEMBER(update_query) "UPDATE users SET password = '%w' WHERE userid = '%u'",
+	MEMBER(password_query) "SELECT username, domain, password FROM users WHERE username = '%n' AND domain = '%d'",
+	MEMBER(user_query) "SELECT home, uid, gid FROM users WHERE username = '%n' AND domain = '%d'",
+	MEMBER(update_query) "UPDATE users SET password = '%w' WHERE username = '%n' AND domain = '%d'",
 	MEMBER(default_pass_scheme) "PLAIN-MD5"
 };