dovecot-sql-example.conf revision c87d1e148ae76cf20f3adc7fc84fd54219dc62d5
5f5870385cff47efd2f58e7892f251cf13761528Timo Sirainen# This file is opened as root, so it should be owned by root and mode 0600.
fc71e94957d0c2959a609450a2f303640d681858Sascha Wilde# For the sql passdb module, you'll need a database with a table that
fc71e94957d0c2959a609450a2f303640d681858Sascha Wilde# contains fields for at least the userid and password. If you want to
9bb91f1dbf7cf8cfbd2df7784101df98d59fb46dTimo Sirainen# use the user@domain syntax, you might want to have a separate domain
fc71e94957d0c2959a609450a2f303640d681858Sascha Wilde# field as well.
fc71e94957d0c2959a609450a2f303640d681858Sascha Wilde# If your users all have the same uig/gid, and have predictable home
fc464e5b2b2ab4d415a5d5b90ce4475d34620a75Timo Sirainen# directories, you can use the static userdb module to generate the home
d99107ddf4d9bccb710994482daf65276a9d6321Timo Sirainen# dir based on the userid and domain. In this case, you won't need fields
fc71e94957d0c2959a609450a2f303640d681858Sascha Wilde# for home, uid, or gid in the database.
fc71e94957d0c2959a609450a2f303640d681858Sascha Wilde# If you prefer to use the sql userdb module, you'll want to add fields
fc71e94957d0c2959a609450a2f303640d681858Sascha Wilde# for home, uid, and gid. Here is an example table:
aba994a4e79a020b4748e0ceffc194e5a18e1d1aTimo Sirainen# CREATE TABLE users (
aba994a4e79a020b4748e0ceffc194e5a18e1d1aTimo Sirainen# userid VARCHAR(128) NOT NULL,
aba994a4e79a020b4748e0ceffc194e5a18e1d1aTimo Sirainen# password VARCHAR(64) NOT NULL,
aba994a4e79a020b4748e0ceffc194e5a18e1d1aTimo Sirainen# home VARCHAR(255) NOT NULL,
aba994a4e79a020b4748e0ceffc194e5a18e1d1aTimo Sirainen# uid INTEGER NOT NULL,
fc71e94957d0c2959a609450a2f303640d681858Sascha Wilde# gid INTEGER NOT NULL,
aba994a4e79a020b4748e0ceffc194e5a18e1d1aTimo Sirainen# active CHAR(1) DEFAULT 'Y' NOT NULL
4b1359bde7d32667197548652a4b4f540062e2acTimo Sirainen# Database driver: mysql, pgsql, sqlite
c5a6a6565be93224fc26522eda855b0990f256e8Timo Sirainen# Database connection string. This is driver-specific setting.
aba994a4e79a020b4748e0ceffc194e5a18e1d1aTimo Sirainen# For available options, see the PostgreSQL documention for the
fc71e94957d0c2959a609450a2f303640d681858Sascha Wilde# PQconnectdb function of libpq.
aba994a4e79a020b4748e0ceffc194e5a18e1d1aTimo Sirainen# Basic options emulate PostgreSQL option names:
4b1359bde7d32667197548652a4b4f540062e2acTimo Sirainen# host, port, user, password, dbname
aba994a4e79a020b4748e0ceffc194e5a18e1d1aTimo Sirainen# But also adds some new settings:
4b1359bde7d32667197548652a4b4f540062e2acTimo Sirainen# client_flags - See MySQL manual
4b1359bde7d32667197548652a4b4f540062e2acTimo Sirainen# ssl_ca, ssl_ca_path - Set either one or both to enable SSL
4b1359bde7d32667197548652a4b4f540062e2acTimo Sirainen# ssl_cert, ssl_key - For sending client-side certificates to server
4b1359bde7d32667197548652a4b4f540062e2acTimo Sirainen# ssl_cipher - Set minimum allowed cipher security (default: HIGH)
fc71e94957d0c2959a609450a2f303640d681858Sascha Wilde# default_file - Read options from the given file instead of
aba994a4e79a020b4748e0ceffc194e5a18e1d1aTimo Sirainen# the default my.cnf location
fc71e94957d0c2959a609450a2f303640d681858Sascha Wilde# default_group - Read options from the given group (default: client)
fc71e94957d0c2959a609450a2f303640d681858Sascha Wilde# You can connect to UNIX sockets by using host: host=/var/run/mysql.sock
fc71e94957d0c2959a609450a2f303640d681858Sascha Wilde# Note that currently you can't use spaces in parameters.
3b22894b8805b186c73d8b754001e8d7e944be85Timo Sirainen# The path to the database file.
fc464e5b2b2ab4d415a5d5b90ce4475d34620a75Timo Sirainen# connect = host=192.168.1.1 dbname=users
fc464e5b2b2ab4d415a5d5b90ce4475d34620a75Timo Sirainen# connect = host=sql.example.com dbname=virtual user=virtual password=blarg
fc464e5b2b2ab4d415a5d5b90ce4475d34620a75Timo Sirainen#connect = dbname=virtual user=virtual
4b1359bde7d32667197548652a4b4f540062e2acTimo Sirainen# Default password scheme.
4b1359bde7d32667197548652a4b4f540062e2acTimo Sirainen# List of supported schemes is in
4b1359bde7d32667197548652a4b4f540062e2acTimo Sirainen# http://wiki.dovecot.org/Authentication/PasswordSchemes
4b1359bde7d32667197548652a4b4f540062e2acTimo Sirainen#default_pass_scheme = PLAIN-MD5
4b1359bde7d32667197548652a4b4f540062e2acTimo Sirainen# Query to retrieve the password.
fc71e94957d0c2959a609450a2f303640d681858Sascha Wilde# This query must return only one row with "user" and "password" columns.
64bfe7b4a42512971db154937905dfa2bdb9cf2cTimo Sirainen# The query can also return other fields which have a special meaning, see
c5a6a6565be93224fc26522eda855b0990f256e8Timo Sirainen# http://wiki.dovecot.org/PasswordDatabase/ExtraFields
64bfe7b4a42512971db154937905dfa2bdb9cf2cTimo Sirainen# The "user" column is needed to make sure the username gets used with exactly
fc71e94957d0c2959a609450a2f303640d681858Sascha Wilde# the same casing as it's in the database. Note that if you store username and
64bfe7b4a42512971db154937905dfa2bdb9cf2cTimo Sirainen# domain in separate fields, you most likely want to return a combination of
aba994a4e79a020b4748e0ceffc194e5a18e1d1aTimo Sirainen# them as the "user" column, otherwise the domain gets stripped.
c5a6a6565be93224fc26522eda855b0990f256e8Timo Sirainen# Commonly used available substitutions (see
4b1359bde7d32667197548652a4b4f540062e2acTimo Sirainen# http://wiki.dovecot.org/Variables for full list):
fc71e94957d0c2959a609450a2f303640d681858Sascha Wilde# %u = entire userid
fc71e94957d0c2959a609450a2f303640d681858Sascha Wilde# %n = user part of user@domain
fc71e94957d0c2959a609450a2f303640d681858Sascha Wilde# %d = domain part of user@domain
fc71e94957d0c2959a609450a2f303640d681858Sascha Wilde# Note that these can be used only as input to SQL query. If the query outputs
fc71e94957d0c2959a609450a2f303640d681858Sascha Wilde# any of these substitutions, they're not touched. Otherwise it would be
fc71e94957d0c2959a609450a2f303640d681858Sascha Wilde# difficult to have eg. usernames containing '%' characters.
aba994a4e79a020b4748e0ceffc194e5a18e1d1aTimo Sirainen# password_query = SELECT concat(userid, '@', domain) AS user, password FROM users WHERE userid = '%n' AND domain = '%d'
aba994a4e79a020b4748e0ceffc194e5a18e1d1aTimo Sirainen# password_query = SELECT pw AS password FROM users WHERE userid = '%u' AND active = 'Y'
fc71e94957d0c2959a609450a2f303640d681858Sascha Wilde#password_query = \
aba994a4e79a020b4748e0ceffc194e5a18e1d1aTimo Sirainen# SELECT userid as user, password \
aba994a4e79a020b4748e0ceffc194e5a18e1d1aTimo Sirainen# FROM users WHERE userid = '%u'
fc71e94957d0c2959a609450a2f303640d681858Sascha Wilde# Query to retrieve the user information.
fc71e94957d0c2959a609450a2f303640d681858Sascha Wilde# The query must return only one row. Commonly returned columns are:
64bfe7b4a42512971db154937905dfa2bdb9cf2cTimo Sirainen# uid - System UID
1c6dd898551d7d4d61970b24a8372438f6b72f97Timo Sirainen# gid - System GID
1c6dd898551d7d4d61970b24a8372438f6b72f97Timo Sirainen# home - Home directory
fc71e94957d0c2959a609450a2f303640d681858Sascha Wilde# mail - Mail location
fc71e94957d0c2959a609450a2f303640d681858Sascha Wilde# Either home or mail is required. uid and gid are required. If more than one
fc71e94957d0c2959a609450a2f303640d681858Sascha Wilde# row is returned or there are missing fields, the login will fail. For a list
fc71e94957d0c2959a609450a2f303640d681858Sascha Wilde# of all fields that can be returned, see
64bfe7b4a42512971db154937905dfa2bdb9cf2cTimo Sirainen# http://wiki.dovecot.org/UserDatabase/ExtraFields
aba994a4e79a020b4748e0ceffc194e5a18e1d1aTimo Sirainen# user_query = SELECT home, uid, gid FROM users WHERE userid = '%n' AND domain = '%d'
aba994a4e79a020b4748e0ceffc194e5a18e1d1aTimo Sirainen# user_query = SELECT dir AS home, user AS uid, group AS gid FROM users where userid = '%u'
aba994a4e79a020b4748e0ceffc194e5a18e1d1aTimo Sirainen# user_query = SELECT home, 501 AS uid, 501 AS gid FROM users WHERE userid = '%u'
aba994a4e79a020b4748e0ceffc194e5a18e1d1aTimo Sirainen#user_query = SELECT home, uid, gid FROM users WHERE userid = '%u'
aba994a4e79a020b4748e0ceffc194e5a18e1d1aTimo Sirainen# If you wish to avoid two SQL lookups (passdb + userdb), you can use
aba994a4e79a020b4748e0ceffc194e5a18e1d1aTimo Sirainen# userdb prefetch instead of userdb sql in dovecot.conf. In that case you'll
3c296d819c54e21ce05c3d2eeeedc79be42ac593Timo Sirainen# also have to return userdb fields in password_query prefixed with "userdb_"
aba994a4e79a020b4748e0ceffc194e5a18e1d1aTimo Sirainen# string. For example:
aba994a4e79a020b4748e0ceffc194e5a18e1d1aTimo Sirainen#password_query = \
aba994a4e79a020b4748e0ceffc194e5a18e1d1aTimo Sirainen# SELECT userid as user, password, \
4b1359bde7d32667197548652a4b4f540062e2acTimo Sirainen# home as userdb_home, uid as userdb_uid, gid as userdb_gid \
aba994a4e79a020b4748e0ceffc194e5a18e1d1aTimo Sirainen# FROM users WHERE userid = '%u'