"SfR Fresh" - the SfR Freeware/Shareware Archive

Member "dovecot-1.0.15/doc/wiki/AuthDatabase.SQL.txt" of archive dovecot-1.0.15.tar.gz:

    1 
    2 
    3 SQL
    4 ===
    5 
    6 
    7 SQL can be used for both passdb and userdb lookups. If the args parameter in passdb sql and userdb sql contain the exact same filename, only one SQL connection is used for both passdb and userdb lookups. 
    8 
    9 
   10 Password database lookups
   11 =========================
   12 
   13 
   14 'password_query' setting contains the SQL query to look up the password. It must return a field named "password". If you have it by any other name in the database, you can use the SQL's "AS" keyword ('SELECT pw AS password ..'). You can use all the normal >>variables<< such as '%u' in the SQL query. 
   15 If all the passwords are in same format, you can use 'default_pass_scheme' to specify it. Otherwise each password needs to be prefixed with "{password-scheme}", for example "{plain}plaintext-password". See >>Authentication/PasswordSchemes<< for a list of supported password schemes. 
   16 By default MySQL does case-insensitive string comparisons, so you may have a problem if your users are logging with different as "user", "User" and "uSer". To fix this, you can make the SQL database return a "user" field, which makes Dovecot modify the username to the returned value. 
   17 Note that if you're using separate user and domain fields, a common problem is that you're returning only the "user" field from the database. *This drops out the domain from the username*. So make sure you're returning a concatenated user@domain string. See the examples below. 
   18 The query can also return other >>extra fields<< which have special meaning. 
   19 
   20 
   21 Password verification by SQL server
   22 ===================================
   23 
   24 
   25 If the passwords are in some special format in the SQL server that Dovecot doesn't recognize, it's still possible to use them. Change the SQL query to return NULL as the password and return the row only if the password matches. The password is in '%w' variable. For example: 
   26 
   27 ---%<-------------------------------------------------------------------------
   28 # NOTE: '\' line splitting is used only for readability, currently Dovecot doesn't support it
   29 password_query = SELECT NULL AS password, userid AS user \
   30   FROM users WHERE userid = '%u' AND mysql_pass = password('%w')
   31 ---%<-------------------------------------------------------------------------
   32 
   33 This of course makes the verbose logging a bit wrong, since password mismatches are also logged as "unknown user". 
   34 
   35 
   36 User database lookups
   37 =====================
   38 
   39 
   40 Usually your SQL database contains also the userdb information. This means user's UID, GID and home directory. If you're using only static UID and GID, and your home directory can be specified with a template, you could use >>static userdb<< instead. It is also a bit faster since it avoids doing the userdb SQL query. 
   41 'user_query' setting contains the SQL query to look up the userdb information. The commonly returned userdb fields are uid, gid, home and mail. See >>UserDatabase/ExtraFields<< for more information about these and other fields that can be returned. 
   42 If you're using a single UID and GID for all users, you can use a SQL query such as: 
   43 
   44 ---%<-------------------------------------------------------------------------
   45 user_query = SELECT 500 AS uid, 500 AS gid, home FROM users WHERE userid = '%n'
   46 ---%<-------------------------------------------------------------------------
   47 
   48 
   49 
   50 Prefetching
   51 ===========
   52 
   53 
   54 If you want to avoid doing two SQL queries when logging in with IMAP/POP3, you can make the 'password_query' return all the necessary userdb fields and use prefetch userdb to use those fields. If you're using Dovecot's deliver you'll still need to have the 'user_query' working. 
   55 See >>UserDatabase/Prefetch<< for example configuration 
   56 
   57 
   58 Example
   59 =======
   60 
   61 
   62 Note that "user" can have a special meaning in some SQL databases, so we're using "userid" instead. 
   63 SQL table creation command: 
   64 ---%<-------------------------------------------------------------------------
   65 CREATE TABLE users (
   66     userid VARCHAR(128) NOT NULL,
   67     domain VARCHAR(128) NOT NULL,
   68     password VARCHAR(64) NOT NULL,
   69     home VARCHAR(255) NOT NULL,
   70     uid INTEGER NOT NULL,
   71     gid INTEGER NOT NULL
   72 );
   73 ---%<-------------------------------------------------------------------------
   74 
   75 
   76 
   77 MySQL
   78 =====
   79 
   80 
   81 dovecot-sql.conf: 
   82 ---%<-------------------------------------------------------------------------
   83 # NOTE: '\' line splitting is used only for readability, currently Dovecot doesn't support it
   84 
   85 # The mysqld.sock socket may be in different locations in different systems
   86 driver = mysql
   87 connect = host=/var/run/mysqld/mysqld.sock dbname=mails user=admin password=pass
   88 # Alternatively you can connect to localhost as well:
   89 #connect = host=localhost dbname=mails user=admin password=pass
   90 
   91 password_query = SELECT concat(userid, '@', domain) AS user, password \
   92   FROM users WHERE userid = '%n' AND domain = '%d'
   93 
   94 user_query = SELECT home, uid, gid FROM users WHERE userid = '%u'
   95 ---%<-------------------------------------------------------------------------
   96 
   97 
   98 
   99 PostgreSQL
  100 ==========
  101 
  102 
  103 dovecot-sql.conf: 
  104 ---%<-------------------------------------------------------------------------
  105 # NOTE: '\' line splitting is used only for readability, currently Dovecot doesn't support it
  106 
  107 # You can also set up non-password authentication by modifying PostgreSQL's pg_hba.conf
  108 driver = pgsql
  109 connect = host=localhost dbname=mails user=admin password=pass
  110 
  111 # PostgreSQL:
  112 password_query = SELECT userid || '@' || domain AS user, password \
  113   FROM users WHERE userid = '%n' AND domain = '%d'
  114 
  115 user_query = SELECT home, uid, gid FROM users WHERE userid = '%u'
  116 ---%<-------------------------------------------------------------------------
  117 
  118 (This file was created from the wiki on 2007-12-11 04:42)