Tables used for SQL support - ezmlm-idx FAQ

5.3 Tables used for SQL support

The basic philosophy is that the database can be on any host (if you use SENDER restrictions, connectivity to the main host is more important than to the sublists), and you choose the database and ‘table root’ names. The default database is ‘ezmlm’ and the default table root is ‘list’. Each list has a separate table root. Any number of lists can share a database.

The main list address table is named with the table root only, others have that name with various suffixes. In the following ‘list’ is used as the table root.

5.3.1 Address tables

‘list’

List subscriber addresses.

‘list_digest’

Digest list subscriber addresses.

‘list_allow’

List subscriber alias addresses. Used only if SENDER restrictions are used for the list. This is configured in the default SQL list setup, but a local (ezmlm-style non-SQL) database could also be used.

‘list_deny’

List deny addresses. This table is created, but the default configuration, if it uses the ‘deny’ addresses at all, will do so with a local database.

‘list_mod’

Moderator addresses. Created for completeness, but not used in the default configuration. If moderators are used, the addresses are stored in a local database.

5.3.2 Subscriber log tables

For each of the above tables, there is a ‘*_slog’ table that contains one row per transaction against the corresponding address table. The entries contain a time stamp, the subscription address; a direction indicator (- for removals, ‘+’ for additions); a type indicator (blank for ezmlm-manage, ‘m’ for “manual”, ‘p’ for “probe”, i.e. bounce handling; and the subscriber ‘From:’ line contents (only additions and only when made by ezmlm-manage or by ezmlm-sub(1) -n).

5.3.3 Message logging tables

For both the list and the digest list, there are a pair of tables that log messages:

‘list_cookie’

The main list stores the message number and a pseudo-random cookie in this table when it processes the message. The cookie is derived from the secret DIR/key, the message sender and the message number. Thus, it is non-repeating and virtually impossible to guess beforehand. Sublists will check that the cookie sent with the message is the same as the one received with the message.

The digest list is created similarly, except that it is ezmlm-get(1) that originates the message and creates the cookie. This is done in ‘list_digest_cookie’.

‘list_mlog’

Both the main list and the sublists make entries in this table. Each entry consists of a time stamp, a message number, a list number, and a code. The code is 0 for “message arrival”, 1 for “finished processing”, 2 for “receipt received” and -1 for “bounce”. The lists will refuse to process messages that do not have the correct cookie, or if the message already has an entry with a code of greater than 0. To inject a message at the sublist, an attacker would have to inject a message with the correct code before the list has processed the “real” message, or subvert the SQL server. In practice, this is very hard to do, unless the attacker has broken security at the database server or a sublist. This authentication mechanism is intended to make it safe to sublist moderated lists. It also blocks any message duplication between main list and sublist from being propagated to the subscribers.

The codes 2 for “receipt received” and -1 for “bounce” are entered by ezmlm-receipt(1) at the main list. This program is configured instead of ezmlm-return(1) if the main list was set up with ezmlm-make -w6. ezmlm-receipt(1) checks the cookie of messages addresses to ‘mainlocal-return-receipt@mainhost’ and if correct enters the ‘receipt received’ code. This address is normally in the subscriber database with a hash of 98, so that each list sends a message to the address after all subscriber addresses.

Bounces of sublist messages should not lead to removal of the sublist from the database. ezmlm-receipt(1) will instead log the bounce to the ‘list_mlog’ table. It will also store up to 50 bounces in the bounce directory. This helps error detection and diagnosis. After the first 50 bounces, no more bounces are stored, until you manually remove the old ones. This is to prevent filling up your hard disk in case a configuration error causes a deluge of bounces.

The digest list is treated in the same manner. Here, the tables is ‘list_digest_mlog’ and the feedback address is ‘mainlocal-digest-return-receipt@mainhost’.