SQL Database (sql)

SQL can be used for both passdb and userdb lookups.

If all the SQL settings for the passdb and userdb are equal, only one SQL connection is used for both passdb and userdb lookups.

Dovecot Configuration

# sql driver-specific settings

passdb sql {
  sql_driver = # ...
  query = # ...
}

passdb

passdb_sql_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 Settings variables such as %{user} in the SQL query.

If all the passwords are in same format, you can use passdb_default_password_scheme to specify it. Otherwise each password needs to be prefixed with {password-scheme}, for example {plain}cleartext-password. See password schemes for a list of supported password schemes.

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 userdb: Extra Fields which makes Dovecot modify the username to the returned value.

INFO

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 or username/domain fields separately. See the examples below.

The query can also return other passdb: Extra Fields which have special meaning.

You can't use multiple statements in one query, but you could use a stored procedure. If you want something like a last login update, use post-login scripting instead.

Password Verification by SQL Server

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. You'll also need to return a non-NULL nopassword field. The password is in %{password} variable. For example:

passdb sql {
  query = SELECT NULL AS password, 'Y' as nopassword, userid AS user \
    FROM users \
    WHERE userid = '%{user}' AND mysql_pass = password('%{password}')
}

This of course makes the verbose logging a bit wrong, since password mismatches are also logged as unknown user.

userdb

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 authentication database instead. It is also a bit faster since it avoids doing the userdb SQL query.

userdb_sql_query setting contains the SQL query to look up the userdb information. The commonly returned userdb fields are uid, gid, home, and mail. See userdb: Extra Fields for more information about these and other fields that can be returned.

If you're using a single UID and GID for all users, you can set them in dovecot.conf with:

mail_uid = vmail
mail_gid = vmail

User Iteration

Some commands, such as doveadm -A need to get a list of users. With SQL userdb this is done with the userdb_sql_iterate_query setting.

You can either return:

• user field containing either user or user@domain style usernames, or
• username and domain fields

Any other fields are ignored.

Prefetching

If you want to avoid doing two SQL queries when logging in with IMAP/POP3, you can make the passdb_sql_query return all the necessary userdb fields and use prefetch authentication database to use those fields.

If you're using Dovecot's deliver you'll still need to have the userdb_sql_query working.

Worker processes

MySQL and SQLite lookups are always done using auth-worker processes.

Changed: 3.1.0 PostgreSQL and Cassandra lookups are done by default in the main auth process. They can be made to use auth-worker processes by setting passdb_use_worker = yes and userdb_use_worker = yes.

High Availability

You can add multiple MySQL Configuration or PostgreSQL Configuration settings to specify multiple hosts for MySQL and PostgreSQL. Dovecot will do round robin load balancing between them. If one of them goes down, the others will handle the traffic.

Examples

INFO

user can have a special meaning in some SQL databases, so we're using userid instead.

SQL table creation command:

CREATE TABLE users (
    userid VARCHAR(128) NOT NULL,
    domain VARCHAR(128) NOT NULL,
    password VARCHAR(64) NOT NULL,
    home VARCHAR(255) NOT NULL,
    uid INTEGER NOT NULL,
    gid INTEGER NOT NULL
);

MySQL

Add to your dovecot.conf file:

sql_driver = mysql

# The mysqld.sock socket may be in different locations in different systems.
mysql /var/run/mysqld/mysqld.sock {
  user = admin
  password = pass
  dbname = mails

  #ssl = yes
  #ssl_client_ca_dir = /etc/ssl/certs
}
# Alternatively you can connect to localhost as well:
#mysql localhost {
#}

passdb sql {
  query = SELECT userid AS username, domain, password \
    FROM users \
    WHERE userid = '%{user | username}' AND domain = '%{user | domain}'
}
userdb sql {
  query = SELECT home, uid, gid \
    FROM users \
    WHERE userid = '%{user | username}' AND domain = '%{user | domain}'
  # For using doveadm -A:
  iterate_query = SELECT userid AS username, domain FROM users
}

PostgreSQL

Add to your dovecot.conf file:

sql_driver = pgsql

pgsql localhost {
  parameters {
    user = admin
    # You can also set up non-password authentication by modifying PostgreSQL's
    # pg_hba.conf
    password = pass
    dbname = mails
  }
}

passdb sql {
  query = SELECT userid AS username, domain, password \
    FROM users \
    WHERE userid = '%{user | username}' AND domain = '%{user | domain}'
}
userdb sql {
  query = SELECT home, uid, gid \
    FROM users \
    WHERE userid = '%{user | username}' AND domain = '%{user | domain}'
  # For using doveadm -A:
  iterate_query = SELECT userid AS username, domain FROM users
}

SQLite

Add to your dovecot.conf file:

sql_driver = sqlite
sqlite_path = /path/to/sqlite.db

passdb sql {
  query = SELECT userid AS username, domain, password \
    FROM users \
    WHERE userid = '%{user | username}' AND domain = '%{user | domain}'
}
userdb sql {
  query = SELECT home, uid, gid \
    FROM users \
    WHERE userid = '%{user | username}' AND domain = '%{user | domain}'
  # For using doveadm -A:
  iterate_query = SELECT userid AS username, domain FROM users
}