@ 21 Nov 09

Postfix patched to support SQLite backend on (Free)BSD

As you may know, the boulz.org group (Doxin, Kimelto, Monsieurp, and myself) is playing around with a cheap dedicated server (kimsufi) running FreeBSD (the power to serve, and the only BSD flavour supported by OVH :/) (If you're using NetBSD, continue to read :p)

A few domains are hosted. I've set up a mail service on it. But I didn’t want to manage a whole SQL server as Postgres or MySQL.
Keep It Simple (and Secure, but that’s not the matter for this kind of decision), I decided to use a SQLite backend.

Postfix & SQLite

A patch exists for Postfix and Dovecot supports it natively.

The FreeBSD port of postfix does'nt include this patch (emind me to send a PR to ask for this feature to be part of the Port !).
The postfix pkgsrc port now include that patch (I sent a patch that was quickly accepted). Add this to your /etc/mk.conf before building postfix:

     PKG_OPTIONS.postfix+=sqlite

On FreeBSD:

The Makefile have to be modified:

Add these five lines to /usr/ports/mail/postfix/Makefile :

 PATCH_SITES=            http://www.treibsand.com/postfix-sqlite/
 PATCHFILES=             postfix-2.5.0_sqlite.patch
 PATCH_DIST_STRIP=       -p1
 POSTFIX_CCARGS+=        -DHAS_SQLITE
 POSTFIX_AUXLIBS+=       -lsqlite3

Then, install the port, as usual:

 # make install clean

Postfix can now handle a SQLite database as backend to store virtual domains, users and aliases !

Prerequisites or “going further”

For great explanations about postfix, dovecot and sqlite, I advise you to read that great howto.

This page focuses on SQLite and doesn’t explain anything.

Creation of the database and SQL tables

You can design your own sql tables to feet your need.

For example, you may want to add an activated parameter to easily control if the user is actived/desactivated. Or has_payed to check that the user has paid his due.

This is a simple configuration that only defines domains, users and aliases and doesn’t add extra parameters:

 # sqlite3 /path/to/sqlite/database  
 sqlite> CREATE TABLE `virtual_domains` (id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(50) NOT NULL);    
 sqlite> CREATE TABLE `virtual_users` (id INTEGER PRIMARY KEY AUTOINCREMENT,   
 domain_id INT(11) NOT NULL, user VARCHAR(40) NOT NULL, password VARCHAR(32) NOT NULL,   
 CONSTRAINT UNIQUE_EMAIL UNIQUE (domain_id,user),  
 CONSTRAINT UNIQUE_EMAIL UNIQUE (domain_id,user),      
 FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE  
 );   
 sqlite> CREATE TABLE `virtual_aliases` (   
 id INTEGER PRIMARY KEY AUTOINCREMENT,  
 domain_id INT(11) NOT NULL,   
 source VARCHAR(40) NOT NULL,   
 destination VARCHAR(80) NOT NULL,   
 FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE   
 );  
 sqlite> CREATE VIEW view_aliases AS   
 SELECT (virtual_aliases.source || '@' || virtual_domains.name) AS email, destination   
 FROM virtual_aliases   
 LEFT JOIN virtual_domains ON virtual_aliases.domain_id=virtual_domains.id;  
 sqlite> CREATE VIEW view_users AS  
 SELECT (virtual_users.user || '@' || virtual_domains.name) AS email, virtual_users.password AS password    
 FROM virtual_users    
 LEFT JOIN virtual_domains ON virtual_users.domain_id=virtual_domains.id;  
 sqlite>.quit

It creates the three tables needed to manage virtual domains (virtual_domains), virtual users and virtual aliases.
It also creates two views that are used to get easy read acces to virtual users (view_users) and to virtual aliases (view_aliases)

Postfix configuration

  • main.cf (main postfix config file)

    virtual_mailbox_maps = sqlite:/usr/local/etc/postfix/sqlite-mailbox.cf
    virtual_mailbox_domains = sqlite:/usr/local/etc/postfix/sqlite-domains.cf
    virtual_alias_maps = sqlite:/usr/local/etc/postfix/sqlite-alias.cf

  • sqlite-mailbox.cf (information about how to get virtual users identities through a SQLite backend)

    dbpath = /usr/local/databases/postfix.db
    dbname = /usr/local/databases/postfix.db
    query = SELECT 1 FROM view_users WHERE email=‘%s'

  • sqlite-domains.cf (information about how to get virtual domains identities through a SQLite backend)

    dbpath = /usr/local/databases/postfix.db
    query = SELECT 1 FROM virtual_domains WHERE name=‘%s'

  • sqlite-aliases.cf (information about how to get virtual aliases identities through a SQLite backend)

    dbpath = /usr/local/databases/postfix.db
    query = SELECT destination FROM view_aliases WHERE email=‘%s'

Dovecot configuration

  • dovecot-sql.conf

    driver = sqlite
    connect = /usr/local/databases/postfix.db
    password_query = SELECT email as user, password FROM view_users WHERE email=‘%u’;
    default_pass_scheme = PLAIN-MD5

Note that I store a MD5 hash of the password in the SQLite database.

My virtual (domains | users | aliases) administration script

Here is a basic shell script that can be easier than typing SQL statements by hand :p althought it ain’t userfriendly neither.