Exim4, dovecot with sqlite authentication

As I am moving away from courier to dovecot I also wanted to move away from the old courierauth DB and use something newer and slicker: sqlite3. Having all my SMTP and IMAP users in sqlite3 is nice, since you don't need them to have a actual system user.

Creating the sqlite3 db is easy, just "sqlite3 /etc/dovecot/authdb.sqlite". I used the SQL query from the dovecot page:

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
);

The next step was to edit the /etc/dovecot/dovecot.conf and /etc/dovecot/dovecot-sql.conf. I just created the entries in the sqlite db manually (my courierdb is small)

insert into users values ('jolt', 'mekk.com','oldcryptedpw', '/home/courier/jolt','104','105');

where all of the values are directly from the /etc/courier/userdb.

Now the fun part: get exim4 to play well with sqlite. ( I leave out the Exim dovecot config, since I used the exact same one from courier (i.e. it's the same path. Read the Dovecot-courier migration document for config details).

In exim I commented out my existing login: and plain: sections and replaced it with this:

plain:
driver = plaintext
public_name = PLAIN
server_prompts = :

server_condition = "${if and { \
{!eq{$2}{}} \
{!eq{$3}{}} \
{crypteq{$3}{${lookup sqlite{/etc/dovecot/authdb.sqlite SELECT password FROM users WHERE ( domain = \
'${domain:$2}' \
AND userid = '${local_part:$2}') OR userid='$2' }{$value}fail}} }} {yes}{no}}"
server_set_id = $2

login:
driver = plaintext
public_name = LOGIN
server_prompts = "Username:: : Password::"
server_condition = "${if and { \
{!eq{$1}{}} \
{!eq{$2}{}} \
{crypteq{$3}{${lookup sqlite{/etc/dovecot/authdb.sqlite SELECT password FROM users WHERE ( domain = \
'${domain:$2}' \
AND userid = '${local_part:$2}') OR userid='$2' }{$value}fail}} }} {yes}{no}}"
server_set_id = $1

The above lines are just a modified version of the MySQL authentication example at the Exim wiki.

Now I tried exim4 and and after changing the select clause a bit it actually worked!

Now back to dovecot. Dovecot needs to be configured (in /etc/dovecot/dovecot.conf) to use both passdb sql and fetch userdb info at the same time (enable userdb prefetch). I missed that myself, of course, so be warned:

# SQL database 
passdb sql {
  # Path for SQL configuration file
  args = /etc/dovecot/dovecot-sql.conf
}

userdb prefetch {
}

And dont forget to disable PAM (just comment it out, don't forget the }).


Then I needed to change the default crypt method to CRYPT (thats what my courierdb used, remember?). Here is the /etc/dovecot/dovecot-sql.conf config file for your reading pleasure:

# Database driver: mysql, pgsql, sqlite
driver = sqlite
connect = /etc/dovecot/authdb.sqlite


# Default password scheme.
#
# List of supported schemes is in
# http://wiki.dovecot.org/Authentication/PasswordSchemes
#
#default_pass_scheme = PLAIN-MD5
default_pass_scheme = CRYPT

# and enable the last line for user and pw prefetch:
password_query = SELECT userid as user, password, home as userdb_home, uid as userdb_uid, gid as userdb_gid FROM users WHERE userid = '%u'

I think that's pretty much it, so good luck!