Users of a RPM-based system might be interested in the
supplied SRPM. This SRPM contains additional patches
such as the TLS and SMTP-AUTH patch and is rebuilt with
rpmbuild --rebuild --with pgsql qmail-sql.src.rpm or
rpmbuild --rebuild --with mysql qmail-sql.src.rpm
In the next section, the installation is described of a new qmail-sql system. Users of the old qmail+pgsql-patches will notice that i made some minor changes to the databasedesign. They do not need to alter their database: i've made a few fields NOT NULL and changed the default stopdate from 'infinity' to NULL, since mysql lacks such a nice feature and i wanted to use the same design for both databases. (qmail+pgsql has always used queries like .. WHERE (startdate >= NOW() OR startdate IS NULL) .. so there's no need to change anything).
Everything starts with downloading the source of qmail 1.03 and the qmail-sql patch. I'll suppose you downloaded everything to the directory where you want to build it.
Unpack the sources and apply the patch:
# tar xzf qmail-1.03.tar.gz
# cd qmail-1.03
# patch -p1 < ../qmail-sql-latest.patch
Modify the source to match with the desired database
Follow the qmail installationprocedure as described in INSTALL. Testing won't work until qmail-sql is configured. The installation-procedure will create a file locals in /var/qmail/control. This file contains all hostnames which will be locally processed. Since these domains are in the database, qmail-sql doesn't need this file. If qmail-sql doesn't find such a file at startup, it will select all the local hostnames from the database. If however the file exists, it will use the contents of the file.
Qmail-sql can automatically create new homedirs for your database-users.
This feature isn't turned on by default. If you want automatic creation of
homedirs, you should read the source of sqlcreatehomedir, and then decide if
you can trust this program and your database-administrators !
Sqlreatehomedir can only create homedirs in already-existing directories. It
always creates a Maildir. If you're using mailboxes, you'll have to change
the source (simply comment all lines concerning Maildirs). To install
# cp sqlcreatehomedir /usr/local/sbin (make sure /usr/local/sbin is accessible for group nofiles)
# chown root:nofiles /usr/local/sbin/sqlcreatehomedir
# chmod 4750 /usr/local/sbin/sqlcreatehomedir
Create the database. To make things somewhat easier, a little script
(initdb) is supplied that can create the database for pgsql as well as for
mysql. You'll need to specify your databasetype by editing the script.
The script will create two database-users: one with write-access
(usermgr) and another which can only read the tables
(userconsult). Qmail-sql needs only read-access, but other daemons
might need the write-access to update the lastlogin-timestamp. Your
database-administrators probably also need the write-access. You can change
the name of the fields as you like, but make sure that the name of the
column containing login and virtual_host is the same for both the passwd-
table as the dotqmails-table. The script creates a reasonable default
database and was tested with recent version of mysql and postgresql. It
will need some small modifications to work with postgresql 6.5. You're
strongly encouraged to read the explanation about the
databasedesign to make sure the the database-design reflects your
wishes. Adding extra colums is no problem at all.
Note about postgres: make sure your database is accessible from your mailserver by editing pg_hba.conf (in your postgresql-directory).
Configure qmail-sql by editing /var/qmail/control/sqlserver. A sample sqlserver is provided with the source (sqlserver.sample) and matches with the database created with initdb. The configurationfile is independent of your choosen database. A list of all options is available here. Note that the file also contains options used by other daemons: they were put in the same file, since these daemons also need the databasedesign.
Change the permissions of the sqlserver-file to read-permission for the
group nofiles and user qmail-send:
# chown qmails.nofiles sqlserver
# chmod 440 sqlserver
Please check the startup-order at boot time: your databaseserver should be started _before_ qmail. Shutdown-order is less important: qmail will defer all mails if your database-server isn't responding. The only real problem occurs when qmail tries to initialize his locals, and can't connect to a databaserver. If you're using a locals-file, you won't have any trouble, but you'll have some more administrative work. Note that it might by usefull to modify your qmail-startupscript to delay startup a few seconds: the initscript of postgresql exits as soon as the server started, but the server isn't immediately in production state: if qmail-sql tries to connect too soon, connection will be refused.
The installation should be checked by running some small test. This test will try to lookup a user and print its information. I'll assume you didn't yet define any users.
Qmail-sql always needs an alias-user. It will fail horribly if
you don't specify this user:
sql> INSERT INTO passwd (login,uid,gid,home,virtual_host) VALUES ('alias',1000,1000,'/home/virtual.org','virtual.org');
Create the homedir of this alias-user:
# mkdir /home/virtual.org
# chown 1000.1000 /home/virtual.org
Let qmail-getpw search for this user:
# ./qmail-getpw alias virtual.org
It should print: alias10001000/home/virtual.org0
If it doesn't you probably have an error in your sqlserver-file or your database is unreachable. Try
# ./qmail-getpw alias virtual.org ; echo $?
If it prints 111, there was a database-problem. Turns debugging on on your databaseserver and start to monitor the queries. Try qmail-getpw again. If you don't see a query, you'll have to check your connectionstring and the access-privileges of your database. If you see a query, you can execute the query in your databaseclient (eg psql,mysql) and check the output.
Now let's create another user and try qmail-getpw on this one. This
shouldn't give any error:
sql> INSERT INTO passwd (login,uid,gid,home,virtual_host) VALUES ('test',1000,1000,'/home/virtual.org/test','virtual.org');
(skip step 1 and 2 if you turned sqlcreatehomedir on)
# mkdir /home/virtual.org/test
# chown 1000.1000 /home/virtual.org/test
# ./qmail-getpw test virtual.org
Try a non-existant user like foo. You should see:
A non-existant user in a non-existant domain: you didn't even define
./qmail-getpw foo foo; echo $?
It should print 116 (no alias).
Suppose you want the dotqmail-feature (skip this step if you don't want this feature). We'll configure the database for the following setup:
|all other addresses||./catchall|
Update the database
psql> UPDATE passwd SET use_dotqmail='1' WHERE login='test' AND virtual_host='virtual.org';
psql> UPDATE passwd SET use_dotqmail='1' WHERE login='alias' AND virtual_host='virtual.org';
psql> INSERT INTO dotqmails (login,virtual_host,extension,dotqmail) VALUES ('test','virtual.org','123','./mailbox123');
psql> INSERT INTO dotqmails (login,virtual_host,extension,dotqmail) VALUES ('alias','virtual.org','info','./mailboxinfo');
psql> INSERT INTO dotqmails (login,virtual_host,extension,dotqmail) VALUES ('alias','virtual.org','sales','./mailboxsales');
psql> INSERT INTO dotqmails (login,virtual_host,extension,dotqmail) VALUES ('alias','virtual.org','default','./catchall');
Run the tests and check their output
# ./qmail-getpw test-123 virtual.org
# ./qmail-getpw test virtual.org
# ./qmail-getpw info virtual.org
# ./qmail-getpw sales virtual.org
# ./qmail-getpw foo virtual.org
Everything worked ? Great ! Now you can run some additional test by sending emails to these users since there's now 1 complication left: you've run all tests as root, and the qmail-system doesn't run as root. If qmail-sql fails, you should check the permissions of the sqlserver-file: this file must be readable for user qmails and group nofiles. Nobody else needs access to that file !
That's it. Have fun !