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.
Virtual (domains | users | aliases) would be administrated thanks to a custom shell script, a CGI script (I've planned to integrate a basic mail administration in our BSD organization website that would be made in Django), ect…
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.
2
3 # $5 and $6 may cause warnings if they are empty...
4 # This is a workaround to prevent these warnings
5
6 P5=$5
7 P6=$6
8
9 if [ -z $P5 ]; then P5=0; fi
10 if [ -z $P6 ]; then P6=0; fi
11
12 # sqlite database
13
14 DATABASE=/usr/local/databases/postfix.db
15
16 # sql queries
17
18 DOMAIN_ADD="INSERT INTO virtual_domains (name) VALUES ('$3');"
19 DOMAIN_DEL="DELETE FROM virtual_domains WHERE name='$3';"
20 DOMAIN_LIST="SELECT * FROM virtual_domains;"
21 DOMAIN_ID="SELECT id FROM virtual_domains WHERE name='$4'"
22 USER_ADD="INSERT INTO virtual_users (domain_id, user, password) VALUES (($DOMAIN_ID), '$3', '`md5 -q -s $P5`');"
23 USER_DEL="DELETE FROM virtual_users WHERE user='$3' AND domain_id=($DOMAIN_ID) AND password='`md5 -q -s $P5`';"
24 USER_LIST="SELECT email FROM view_users;"
25 USER_PASSWD_CHANGE="UPDATE virtual_users SET password='`md5 -q -s $P5`' WHERE user='$3' AND domain_id=($DOMAIN_ID) AND password='`md5 -q -s $P6`';"
26 ALIAS_ADD="INSERT INTO virtual_aliases (domain_id, source, destination) VALUES (($DOMAIN_ID), '$3', '$P5');"
27 ALIAS_DEL="DELETE FROM virtual_aliases WHERE domain_id=($DOMAIN_ID) AND source='$3' AND destination='$5' AND (SELECT password FROM virtual_users WHERE user='$3' AND domain_id=($DOMAIN_ID))='`md5 -q -s $P6`';"
28 ALIAS_LIST="SELECT * FROM view_aliases"
29
30 # handle command line arguments
31
32 case $1 in
33 "domain")
34 case $2 in
35 "add")
36 sqlite3 "$DATABASE" "$DOMAIN_ADD"
37 ;;
38 "del")
39 sqlite3 "$DATABASE" "$DOMAIN_DEL"
40 ;;
41 "list")
42 echo -e "\nList of virtual domains:\n"
43 sqlite3 "$DATABASE" "$DOMAIN_LIST"
44 ;;
45 *)
46 echo 'Bad DOMAIN command ! Commands: add, del, list '
47 ;;
48 esac
49 ;;
50 "user")
51 case $2 in
52 "add")
53 sqlite3 "$DATABASE" "$USER_ADD"
54 ;;
55 "del")
56 sqlite3 "$DATABASE" "$USER_DEL"
57 ;;
58 "passwd-change")
59 sqlite3 "$DATABASE" "$USER_PASSWD_CHANGE"
60 ;;
61 "list")
62 echo -e "\nList of virtual users:\n"
63 sqlite3 "$DATABASE" "$USER_LIST"
64 ;;
65 *)
66 echo 'Bad USER command ! Commands: add, del, passwd-change, list'
67 ;;
68 esac
69 ;;
70 "alias")
71 case $2 in
72 "add")
73 sqlite3 "$DATABASE" "$ALIAS_ADD"
74 ;;
75 "del")
76 sqlite3 "$DATABASE" "$ALIAS_DEL"
77 ;;
78 "list")
79 echo -e "\nList of virtual aliases:\n"
80 sqlite3 "$DATABASE" "$ALIAS_LIST"
81 ;;
82 *)
83 echo 'Bad ALIAS command ! Commands: add, del, list'
84 ;;
85 esac
86 ;;
87 *)
88 echo -e 'Manage mail identities -- script by zecrazytux <zecrazytux@zecrazytux.net>\n\nUSAGE:\n'
89 echo -e '\tmanage-mail-identities domain (add|del|list) DOMAIN'
90 echo -e '\tmanage-mail-identities user (add|del|passwd-change|list) [USER] [DOMAIN] [PASSWORD] [PASSWORD_OLD]'
91 echo -e '\tmanage-mail-identities alias (add|del|list) [ALIAS]'
92 ;;
93 esac