Installing Postfix with SQLite Support

This How-to will show you how to set up Postfix with SQLite support on a Fedora 11 system.  This will require us to compile Postfix from scratch.

Installing the dependences

yum -y install sqlite sqlite-devel gcc make patch db4-devel cyrus-sasl-devel
echo "postfix:x:89:89::/var/spool/postfix:/sbin/nologin" >> /etc/passwd
echo "postdrop:x:90:90::/var/spool/postfix:/sbin/nologin" >> /etc/passwd
echo "postfix:x:89:" >> /etc/group
echo "postdrop:x:90:" >> /etc/group
ln -s /usr/lib/sasl2/ /usr/local/lib/sasl2

Download and Patch Postfix

Download and unpack the current Postfix Version.

wget http://postfix.energybeam.com/source/official/postfix-2.6.5.tar.gz
tar -xzf postfix-2.6.5.tar.gz
cd postfix-2.6.5

Download and Patch Postfix with the SQLite Postfix patch

wget http://www.treibsand.com/postfix-sqlite/postfix-2.6-20080216_sqlite.patch
patch -ul -d . -p1 < postfix-2.6-20080216_sqlite.patch
echo $?

Building Postfix

To Build with SQLite Support

make -f Makefile.init makefiles 'CCARGS=-DHAS_SQLITE -I/usr/local/include' \
'AUXLIBS=-L/usr/local/lib -lsqlite3'
echo $?

Or to Build with SQLite & TLS Support
Requires:

yum -y install openssl-devel
make -f Makefile.init makefiles 'CCARGS=-DHAS_SQLITE -I/usr/local/include -DUSE_TLS' \
'AUXLIBS=-L/usr/local/lib -lsqlite3 -lz -lm -lssl -lcrypto'
echo $?

To Build with SQLite, Dovecot, & TLS Support

yum -y install openssl-devel dovecot-devel
make makefiles 'CCARGS=-DHAS_SQLITE -I/usr/include/sasl/ -DUSE_SASL_AUTH -DUSE_CYRUS_SASL  -DDEF_SERVER_SASL_TYPE=\"dovecot\" -DUSE_TLS' -I/usr/local/include \
   'AUXLIBS=-L/usr/local/lib -lsqlite3 -lz -lm -lssl -lcrypto -lsasl2'
echo $?

Compiling Postfix

Now Compile Postfix

make
echo $?

And Install it

make install

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.

• postfix.sqlite

Once you have your database file place it in /etc/postfix/ as something like /etc/postfix/postfix.sqlite then run

chown root:root /etc/postfix/postfix.sqlite
chmod 600 /etc/postfix/postfix.sqlite

Configuring Postfix

Now add the maps to you config.

/etc/postfix/main.cf

relay_domains = sqlite:/etc/postfix/sqlite_relay_domains_maps.cf
relay_recipient_maps = sqlite:/etc/postfix/sqlite_relay_recipient_maps.cf
virtual_alias_maps = sqlite:/etc/postfix/sqlite_virtual_alias_maps.cf
virtual_mailbox_domains = sqlite:/etc/postfix/sqlite_virtual_domains_maps.cf
virtual_mailbox_maps = sqlite:/etc/postfix/sqlite_virtual_mailbox_maps.cf

virtual_mailbox_base = /var/spool/virtualmailboxes
virtual_minimum_uid= 1000
virtual_uid_maps = static:1000
virtual_gid_maps = static:1000

sqlite_relay_domains_maps.cf

dbpath = /etc/postfix/postfix.sqlite
query = SELECT domain FROM domain WHERE domain='%s' AND backupmx = '1' AND active = '1'

sqlite_relay_recipient_maps.cf

dbpath = /etc/postfix/postfix.sqlite
query = SELECT goto FROM alias WHERE address='%s' AND active = 1

sqlite_virtual_alias_maps.cf

dbpath = /etc/postfix/postfix.sqlite
query = SELECT goto FROM alias WHERE address='%s' AND active = '1'

sqlite_virtual_domains_maps.cf

dbpath = /etc/postfix/postfix.sqlite
query = SELECT domain FROM domain WHERE domain='%s' AND backupmx = '0' AND active = '1'

sqlite_virtual_mailbox_maps.cf

dbpath = /etc/postfix/postfix.sqlite
query = SELECT maildir FROM mailbox WHERE username='%s' AND active = '1'

Setting up a fresh install of Postfix

mkdir /var/spool/virtualmailboxes/
echo "virtualmail:x:1000:1000::/var/spool/virtualmailboxes:/sbin/nologin" >> /etc/passwd
echo "virtualmail:x:1000:" >> /etc/group
chmod 700 /var/spool/virtualmailboxes/
chown -R virtualmail:virtualmail /var/spool/virtualmailboxes/
rm -f /usr/lib/sendmail
ln -s /usr/sbin/sendmail /usr/lib/sendmail

Adding SQLite entry’s

First add a Domain

echo "INSERT INTO domain ( domain, description, transport )
VALUES ( 'laptop.mattrude.com', 'laptops domain', 'virtual' );" |sqlite3 /etc/postfix/postfix.sqlite

Then add a user

echo "INSERT INTO mailbox ( username, password, name, maildir, domain, local_part )
VALUES ( 'matt@laptop.mattrude.com', 'password', 'Matt', 'laptop.mattrude.com/matt@laptop.mattrude.com/', 'laptop.mattrude.com', 'matt' );" |sqlite3 /etc/postfix/postfix.sqlite

Last we need to add the mailboxes alias

echo "INSERT INTO alias ( address, goto, domain )
VALUES ( 'matt@laptop.mattrude.com', 'matt@laptop.mattrude.com', 'laptop.mattrude.com' );" |sqlite3 /etc/postfix/postfix.sqlite

Next, contue on to my Dovecot SQLite how-to to finish your email server.