The Database Schema

This page documents the structure of the database used by the Qmail Rules interface.

I went through several ideas for how to store the rules in a database, and finally settled on the schema (the set of database tables) described below. After I got it working on my own server (using PostgreSQL), I ended up having to change a few field types in order to make the schema work across all three of the database engines I'm testing with (i.e. PostgreSQL, MySQL, and SQLite.)

phases

The phases table contains information defining the "phases", which are essentially groups of rules which are processed together, and which are administered by the same type of user (i.e. mailbox owner, domain administrator, or system administrator.)

Within each phase, the rules have specific sequence numbers (as described in the "rules" table, below.) When mail arrives, the processing script will evaluate each rule in order by phase and sequence. The first rule which matches the message will be followed, and the message will be accepted or rejected without processing any of the rules which follow it.

The phases table's structure looks like this:

The default phases, the ones I use on my own systems, are contained in the lib/insert-standard-phases.sql file. They are:

This set of phases allows a machine owner to set up system-wide rules which either CAN or CANNOT be overridden, by putting the rules into phase 1 (i.e. before anybody else's rules execute) or phase 5 (i.e. after everybody else's rules have executed, if none of those domains' or users' rules matched the message.) They also allow a domain owner to set up domain-wide rules which eithe CAN or CANNOT be overridden by their users, by putting the rules into phase 2 (before the individual mailbox owners' rules execute) or phase 4 (after the mailbox owners' rules execute, if none of their rules matched the message.)

I have found that these phases provide enough flexibility for my needs and the needs of my clients and users. However, if your needs are different, you can change the list of phases. The web interface does not offer any way to edit the phases, however you can make changes by either editing the insert-standard-phases.sql file before using it, or by runninging UPDATE, INSERT, and DELETE queries against the phases table in your database.

If you do edit the list of phases, you should make sure that the phase numbers start with 1, and that they are sequential (i.e. 1, 2, 3, etc.) without skipping any numbers. Changing the phase names after you have started using the system won't cause any problems, however changing the phase numbers or the access levels might cause confusion with any rules which already exist in the database. For this reason, I recommend that if you do plan to change the phase numbers or access levels, you should do it when you first install the system, before any rules are created... or you should be prepared to manually adjust the "phase" values for any rules which may already exist in the database.

rules

The rules table contains the actual mail processing rules. Each rule contains information about whose email is affected by the rule, the order in which the rule should be processed, what kind of test is being done by the rule, and whether to accept or reject the message if the test matches the message.

The rules table's structure looks like this:

This table's primary index is a combination of the phase, recipient, and seq fields.

The recipient value in each rule is an expression which can be matched using the SQL "LIKE" operator. The values look like this:

The type values and the extra data they require are:

greylist

The "greylist" table contains data used to implement greylisting. This data consists of the sender, recipient, and sending IP address, as well as timestamps showing the first and last times the entry was used, and counts of how many times messages were attempted before and after the greylisting period exipred.

The rcptcheck.pl processing script uses and updates this table as needed for greylisting. The web interface doesn't use it, other than allowing System Administrators to clean out greylist records which haven't been used in a while.

The greylist table's structure looks like this:

This table's primary key is a combination of the sender, recipient, and ip fields.

Web users with System Administrator access can use the cleanup.cgi script (they will see a link at the bottom of the list, below the focus form/list) to clean up any greylist records which haven't been used in a certain number of days. (This function keys off of the last field.)

log

The "log" table contains a log of every message which was processed by the rcptcheck.pl script, which rule (if any) ended up matching the message, and the disposition (i.e. whether the script told qmail-smtpd to accept, reject, or delay the message.)

The rcptcheck.pl processing script adds records to this table. The web interface reads from it, to count how many times each rule has been matched by incoming messages.

The log table's structure looks like this:

sessions

The "sessions" table contains a list of the web sessions. Each session is identified by a cookie (a collection of random-looking characters - I'm actually using the MD5 hash of a collection of data which should be unique to each request), and contains the browser's IP, the userid and access level, the first time the session was accessed, when the session expires, a count of how many web requests happened in the session, and a flag disabling the session (which is set if the user manually logs out.) The web interface uses this table, the mail processing script does not have any connection to it.

If the browser doesn't access the system for a certain length of time (30 minutes by default), the session will "time out" and the user will be forced to re-enter their userid and password. The time limit can be changed by the machine owner (by changing the SESSION_TIMEOUT environment variable in the .htaccess file.)

The sessions table's structure looks like this:

The web interface adds a record to this table whenever a user logs in, and updates the session's record for every request which arrives within that session. Users with System administrator access can use the cleanup.cgi script (a link will be at the very bottom of the list, below the focus form/list) to remove any old sessions from the database.

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