Installing Postfix on Ubuntu using SQLite

This Page was written with Ubuntu 24.04 LTS in mind, and may not work correctly with other versions or distributions.

Note: This is NOT a full How-To. Please see Installing Postfix on Fedora using MySQL for more information on setting up Postfix as a Internet facing SMTP server.

This guide will walk you threw installing Postfix 3.10.1 with SQLite support on a Ubuntu 16.04 LTS server.

Installing Postfix

Installing the dependencies

apt install -y gcc make m4 libdb-dev libssl-dev sqlite3 libsqlite3-dev libsasl2-dev libicu-dev libnsl-dev

Add Needed Users and Directories

adduser --system --group --no-create-home postdrop
adduser --system --group --no-create-home postfix

Downloading Postfix

mkdir -p /var/src/ && cd /var/src
wget http://ftp.porcupine.org/mirrors/postfix-release/official/postfix-3.10.1.tar.gz
tar -xzf postfix-3.10.1.tar.gz
cd postfix-3.10.1/

Building Postfix

Configure Postfix for SQLite database

make -f Makefile.init makefiles \
    'CCARGS=-DHAS_SQLITE -I/usr/local/include' \
    'AUXLIBS_SQLITE=-L/usr/local/lib -lsqlite3 -lpthread' && \
make && make install

Configure Postfix for mySQL & SQLite databases

make makefiles 'CCARGS=-DHAS_SQLITE -I/usr/include/mysql -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',
  local_part varchar(255) NOT NULL,
  active tinyint(1) NOT NULL default '1');

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'

Installing Dovecot

apt install build-essential autoconf automake libtool pkg-config gettext bison flex lua5.4 liblua5.4-dev libunwind-dev libldap-dev libsodium-dev zlib1g-dev

mkdir -p /var/src
cd /var/src/
wget https://dovecot.org/releases/2.4/dovecot-2.4.0.tar.gz
tar -xzf dovecot-2.4.0.tar.gz
cd dovecot-2.4.0.tar.gz
./configure --with-sqlite && make && install

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 ( '[email protected]', 'password', 'Matt', 'laptop.mattrude.com/[email protected]/', 'laptop.mattrude.com', 'matt' );" |sqlite3 /etc/postfix/postfix.sqlite

Last we need to add an the mailboxes alias

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