Setting up an SQLite database

This page documents how to set up an SQLite database for use with the qmail rules interface.

Installing SQLite

SQLite is usually included in the operating system's repositories. If this is the case, you should install it from there, so that the server can be upgraded easily when security issues are found.

For example, under the RedHat-flavoured distributions (i.e. RedHat Enterprise, Fedora, CentOS, etc.) you can run a command like this to install the server:

# yum install sqlite

The specific package name may vary, but the procedure should be very similar.

If your system doesn't have a package repository from which to install the software, you can always download the source code and follow the directions to build it on your system.

Decide where to put the database file

SQLite stores all of the data for a "database" within a single file. The permissions and ownership on this file are important. Both Apache and qmail-smtpd need the following permissions:

• Both "read" and "write" permission to the database file itself.
• All of "read", "write", and "execute" permission to the directory which contains the database file.
• At least "execute" permission on every higher-level directory, all the way back to "/".

On my test system, Apache runs CGI scripts as the "apache" user, and qmail-smtpd runs as the "qmaild" user. In order to set up the permissions, I created a group called "rulesdb" and added these two userids to it.

# groupadd rulesdb
# usermod -a -G rulesdb apache
# usermod -a -G rulesdb qmaild

After doing this, I restarted the processes which run as these userids, so that they would gain their new group memberships (and therefore be able to access the file.)

# svc -t /service/qmail-smtp* # /etc/init.d/httpd restart Stopping httpd: [ OK ] Starting httpd: [ OK ]

Because the system is related to qmail, I think it makes sense to store the file under the /var/qmail directory somewhere. On my test system, I created a /var/qmail/rules directory, containing a file called rules.sqlite. You can obviously do whatever you like on your own system, however this is the directory and filename I'll be using on this web page.

While testing this setup, I discovered that, even though the qmail-smtpd process runs as the qmaild user, whcih is a member of the rulesdb group, the process doesn't inherit the supplementary group IDs of the userid as which it runs. This is because when tcpserver "becomes" the requested uid and gid after bind()-ing to the listening socket, it does not also pick up the supplementary gid's of the uid.

Because Apache DOES pick up the supplementary gid's of it's anonymous user, we can make the files accessible to both Apache and qmail-smtpd by making them owned by the qmaild user and the rulesdb group, and making them both owner- and group-writable.

The actual commands I ended up using are shown below.

Creating the database file

You should already have downloaded the files and run the ./fix-permissions.sh script.

To set up the SQLite database file, do the following:

• Create the database file and the tables, and insert the phases. Unlike most other database engines, there is no server process to configure, and no userids to set up. The entire database is contained within a single file.

  You can physically put the database file wherever you like, and you can give it any filename you like. The only requirements are that the file, and the directory in which it is contained, must be writable to the userid(s) which will be accessing it. As noted above, on my test system I stored the file as /var/qmail/rules/rules.sqlite.

  The download comes with a file called "lib/create-tables.sqlite", which contains the SQLite commands needed to create or re-create the tables within the database file. It also has a file called "lib/insert-standard-phases.sql", which has the generic SQL commands needed (i.e. INSERT queries) to populate the phases table with the standard phase names and security levels that I use on my own server.

  You can create the database file, create the tables, and insert the phases with this command:

  # cd /www/secure.domain.xyz/rules/lib (your web site's DocumentRoot)
  # mkdir /var/qmail/rules
  # sqlite3 /var/qmail/control/rules.sqlite (The full path to the database file)
  SQLite version 3.3.6
  Enter ".help" for instructions
  sqlite> .read create-tables.sqlite
  sqlite> .read insert-standard-phases.sql
  sqlite> .q

  The individual commands should not produce any output. If you see any error messages, figure out what the problem is and try again.

• Set the security on the database file.

  These commands will set the permissions on the file and the directories in which it lives:

  # chown -R qmaild:rulesdb /var/qmail/rules
  # chmod -R ug+rw,o-rwx /var/qmail/rules
  # chmod a+x /var/qmail /var / (all parent directories going back to "/")

Once this is done, the database will be ready for the web interface to be used to start creating rules.

When configuring the web interface and the mail script, you will need to set the value of the G4DBCONN environment variable. For an SQLite database, the value needs to look like this:

SetEnv G4DBCONN "dbi:SQLite:dbname=/full/path/to/database.file"

Copyright 1995-2024 John M. Simpson KG4ZOW (email) (PGP key)
Last updated 2012-07-24 07:17:12 +0000