Postfix with a SQLite backend

Sysadmin Mail Postfix SQLite

A small mail server for a handful of people does not need a database server. A single SQLite file can hold domains, mailboxes and aliases, and the same file can serve both the mail transfer agent and the delivery agent.

Building Postfix with SQLite support

Postfix does not look up mail accounts in SQLite out of the box on every platform, so it is built with an extra option enabled. On a source based system the option goes into the package configuration before building, and the resulting binary can then read maps from the database file. Once built this way, virtual domains, users and aliases can all live in SQLite.

Tables to start from

Three tables and two views cover the common case. The tables hold domains, users and aliases; the views join a name to its domain so the mail agent can query a single address.

sqlite3 /path/to/mail.db

CREATE TABLE virtual_domains (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name VARCHAR(50) NOT NULL
);

CREATE TABLE virtual_users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  domain_id INTEGER NOT NULL,
  user VARCHAR(40) NOT NULL,
  password VARCHAR(64) NOT NULL,
  UNIQUE (domain_id, user)
);

CREATE TABLE virtual_aliases (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  domain_id INTEGER NOT NULL,
  source VARCHAR(40) NOT NULL,
  destination VARCHAR(80) NOT NULL
);

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;

CREATE VIEW view_aliases AS
  SELECT virtual_aliases.source || '@' || virtual_domains.name AS email,
         virtual_aliases.destination AS destination
  FROM virtual_aliases
  LEFT JOIN virtual_domains ON virtual_aliases.domain_id = virtual_domains.id;

Design the tables to fit the job. A column that marks an account as active, or one that records that a bill has been paid, is often worth the small extra effort.

Postfix lookups

Point the relevant maps at the database and give each map a query. One map answers whether a mailbox exists, one answers whether a domain is local, and one resolves an alias to its destination.

virtual_mailbox_maps    = sqlite:/etc/postfix/sqlite-mailboxes.cf
virtual_mailbox_domains = sqlite:/etc/postfix/sqlite-domains.cf
virtual_alias_maps      = sqlite:/etc/postfix/sqlite-aliases.cf

# sqlite-mailboxes.cf
dbpath = /path/to/mail.db
query  = SELECT 1 FROM view_users WHERE email = '%s'

Dovecot on the same file

The delivery agent can read the very same database for authentication, which keeps one source of truth.

# dovecot sql config
driver   = sqlite
connect  = /path/to/mail.db
password_query = SELECT email AS user, password FROM view_users WHERE email = '%u';

One caution

Keep a copy of the database file and its permissions under control. A mail store that lives in one file is wonderfully simple, right up to the moment the file is lost.

See also the mail and database references on the resources page.