Installing Dovecot with SQLite Support

Following in line with my previous post on Installing Postfix with SQL Support. This post will describe installing Dovecot from source with full SQLite support.

Installing from Source

First start out by downloading the lastest version from Dovecot’s website (the current version as of the writing of the how-to is 1.2.8).

yum -y install sqlite sqlite-devel gcc make patch db4-devel cyrus-sasl-devel

Next download and untar the source code.

wget http://dovecot.org/releases/1.2/dovecot-1.2.8.tar.gz
tar -xzf dovecot-1.2.8.tar.gz
cd dovecot-1.2.8/

Next, you will need to configure the code before compiling.

./configure --with-sqlite
echo $?

Assuming the configure command finishes with out error (the last line should be a “0”). Compile and install Dovecot.

make && make install

Configuring Dovecot for SQLite

First we need to create or modify the dovecot config file for SQLite access.  If you are currenly using MySQL with Dovecot, switching to SQLite is pretty easy and strate forward.  Or you may just use the below dovecot config file.

### Dovecot configuration file ###
### /etc/dovecot.conf ###
protocols = pop3 imap
login_user = postfix
auth_cache_size = 128
auth_cache_ttl = 600
mail_debug = yes

mail_location = maildir:%h/

protocol imap {
 listen = *:143
}

protocol lda {
  postmaster_address = postmaster@mattrude.com
  hostname = odin.mattrude.com
  mail_plugin_dir = /usr/local/lib/dovecot/lda
  auth_socket_path = /var/run/dovecot/auth-master
}

auth default {
  mechanisms = plain login
  userdb sql {
    args = /etc/dovecot-sqlite.conf
  }
  passdb sql {
    args = /etc/dovecot-sqlite.conf
  }
  socket listen {
    master {
      path = /var/run/dovecot/auth-master
      user = virtualmail
      group = virtualmail
    }
    client {
      path = /var/spool/postfix/private/auth
      mode = 0660
      user = postfix
      group = postfix
    }
  }
}

After you have created the main Dovecot config file, you will need to add the SQLite config file (below).

### /etc/dovecot-sqlite.conf ###
driver = sqlite
connect = /etc/postfix/postfix.sqlite
password_query = SELECT password, username AS user
  FROM mailbox WHERE username = '%u' AND domain = '%d'
user_query = SELECT maildir, 1000 AS uid, 1000 AS gid FROM mailbox WHERE
  username = '%u' AND domain = '%d' AND active = '1'

After the config files have been created, we need to create the database file, here is where you will need SQLite installed on the system.

Building the SQLite Database

In order to use the SQLite function, you need a SQLite database. First using SQLite3 run

sqlite3 /etc/postfix/postfix.sqlite

To create the database, then you can copy and past the following scheme into the new database.

CREATE TABLE alias (
  address varchar(255) NOT NULL,
  goto text NOT NULL,
  domain varchar(255) NOT NULL,
  created datetime NOT NULL default '0000-00-00 00:00:00',
  modified datetime NOT NULL default '0000-00-00 00:00:00',
  active tinyint(1) NOT NULL default '1');

CREATE TABLE domain (
  domain varchar(255) NOT NULL,
  description varchar(255) NOT NULL,
  aliases int(10) NOT NULL default '0',
  mailboxes int(10) NOT NULL default '0',
  maxquota bigint(20) NOT NULL default '0',
  quota bigint(20) NOT NULL default '0',
  transport varchar(255) NOT NULL,
  backupmx tinyint(1) NOT NULL default '0',
  created datetime NOT NULL default '0000-00-00 00:00:00',
  modified datetime NOT NULL default '0000-00-00 00:00:00',
  active tinyint(1) NOT NULL default '1' );

CREATE TABLE mailbox (
  username varchar(255) NOT NULL,
  password varchar(255) NOT NULL,
  name varchar(255) NOT NULL,
  maildir varchar(255) NOT NULL,
  quota bigint(20) NOT NULL default '0',
  domain varchar(255) NOT NULL,
  created datetime NOT NULL default '0000-00-00 00:00:00',
  modified datetime NOT NULL default '0000-00-00 00:00:00',
  active tinyint(1) NOT NULL default '1',
  local_part varchar(255) NOT NULL );

Then close the database

.quit

Or you may download mine from below and use the same scheme work.

mkdir /var/run/dovecot

Dovecot INIT file

#!/bin/bash
#
#	/etc/rc.d/init.d/dovecot
#
# Starts the dovecot daemon
#
# chkconfig: - 65 35
# description: Dovecot Imap Server
# processname: dovecot
# Source function library.
. /etc/init.d/functions

test -x /usr/local/sbin/dovecot || exit 0

RETVAL=0
prog="Dovecot Imap"

start() {
       echo -n $"Starting $prog: "
	daemon /usr/local/sbin/dovecot
	RETVAL=$?
	[ $RETVAL -eq 0 ] && touch /var/lock/subsys/dovecot
	echo
}

stop() {
	echo -n $"Stopping $prog: "
	killproc /usr/local/sbin/dovecot
	RETVAL=$?
	[ $RETVAL -eq 0 ] && rm -f /var/lock/subsys/dovecot
	echo
}

case "$1" in
  start)
	start
	;;
  stop)
	stop
	;;
  reload|restart)
	stop
	start
	RETVAL=$?
	;;
  condrestart)
	if [ -f /var/lock/subsys/dovecot ]; then
	    stop
	    start
	fi
	;;
  status)
	status /usr/local/sbin/dovecot
	RETVAL=$?
	;;
  *)
	echo $"Usage: $0 {condrestart|start|stop|restart|reload|status}"
	exit 1
esacexit $RETVAL
Advertisements

3 thoughts on “Installing Dovecot with SQLite Support

  1. Pingback: Installing Postfix with SQLite Support – Matt Rude

  2. A few issues with your instructions:
    You concatenated user_query and password_query together
    a trailing comma after “AS gid” makes the query invalid
    on both queries, either use %u to query the user and change userid for username, or use “local_part = ‘%n’ AND” or else the query will fail silently. Either way, userid is a non-existant field.

    These out of the way, thank you! Excellent to get my setup working.

    • Thanks for the input!
      I believe I have corrected all of the mistakes you found in my how-to. Thanks for your help. If you find any other problems, please let me know.

      -Matt

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s