Database-layout

Qmail-sql uses at least 1 table: passwd. Other tables are optional: dotqmails and locals. The preferred setup is to declare two database-users: one with full access (not used by qmail-sql) and another one with SELECT-privileges on all tables (used by qmail-sql).

passwd

This table is comparable with /etc/passwd: it contains all the fields from the passwd-file and some additional information. The table is created with:

CREATE TABLE passwd (	login	varchar(64) NOT NULL,
	uid	int NOT NULL,
	gid	int NOT NULL,
	home	varchar(200) NOT NULL,
	virtual_host	varchar(64) NOT NULL,
	password	varchar(13),
	hardquota	int DEFAULT '0',
	startdate	datetime DEFAULT 'NOW()',
	stopdate	datetime DEFAULT NULL,
	enabled	bool DEFAULT '1',
	use_dotqmail	bool DEFAULT '0',
	lastlogin	DATETIME,
	PRIMARY KEY	(login,virtual_host)
	);

Note that you can change the name of the fields as you like.


• The fields login, uid, gid and home don't need further explanation: they have the same meaning as the fields in /etc/passwd.
• password contains the encrypted password. If the passwords are crypted with the conventional crypt() they'll always contain 13 characters. The password-field is not used by qmail-sql but by other daemons like pop and imap. You should check the passwords these daemons expect: if they're happy with crypt()-passwords, you won't need more than 13 characters. If however they want MD5-passwords, you'll need 34 charactes to store the password.
• virtual_host makes it possible to specify different users like info@domain1.be and info@domain2.be. It makes the usage of the file virtualdomains unnecessary. It's also possible to configure qmail-sql with check_host no. In this case, qmail-sql will ignore the host-part of each email-address and search for a db-entry with the same username and virtual_host='' or NULL.
• The fields startdate, stopdate and enabled are easiest to explain with an example. Suppose I want to lookup user test@domain.be. The query would be:
  

  SELECT	login,uid,gid,home,hardquota,use_dotqmail
  FROM	passwd
  WHERE	login='test'
  	AND virtual_host='domain.be'
  	AND enabled='1'
  	AND (startdate IS NULL OR startdate<=NOW())
  	AND (stopdate IS NULL OR stopdate>=NOW())
  	;

  As you notice, startdate should be earlier than the current time OR be NULL. Postgresql allows you to specify 'infinity' as a timestamp, thus making the 'NULL' unnecessary. Unfortunately Mysql doesn't offer such a feature.
• use_dotqmail specifies whether qmail should try to lookup the contents of a dotqmail-file from the database or not. If the value is '0', qmail-sql won't run the second query for the dotqmail, resulting in some speed improvement.
• lastlogin isn't used by qmail-sql but by other daemons like imap and pop to save the latest time a user logged in.
• hardquota is a little misleading. Suppose you have a maildir containing 4.9 MB mail and your quotum is 5 MB. When a new mail arrives, qmail-sql checks your homedir and notices you still have space left. It will accept the mail even if your quotum would be exceeded after delivery of the mail. All other mails will of course be delayed until you've cleaned up your mailbox or exceeded queuelifetime (hardquota refers to the linux kernel-quota consisting of hard quota and soft quota). A value of 0 means that the quota are disabled for this user.




dotqmails

This table can contain the contents of the dotqmail-file of each user. The table is only queried if use_dotqmail was true. If qmail-sql didn't find a dotqmail for the specified user, it will use the dotqmail in the user's homedirectory. [TODO: qmail-sql will always check the dotqmail in the homedirectory but ignore it's value if it also found a dotqmail in the database, which is of course unnecessary ] The table is created with:

CREATE TABLE dotqmails (	login	varchar(64) NOT NULL,
	virtual_host	varchar(64) NOT NULL,
	extension	varchar(64) NOT NULL,
	dotqmail	text NOT NULL,
	PRIMARY KEY	(login,virtual_host,extension),
	FOREIGN KEY	(login,virtual_host)
		REFERENCES passwd (login,virtual_host)
		ON DELETE CASCADE
	);

• login and virtualhost have the same meaning as in the passwd- table.
• dotqmail contains the content of the dotqmail-file. See dot-qmail(5).
• extension perhaps needs some more explanation. On a normal qmail- system, a user (eg. login) can config multiple email-addresses like login-mbox1, login-mbox2 etc. The part of the email-address after the '-' is the so-called extension. Unlike a normal qmail-system, qmail-sql allows only 1 level: you cannot have login-mbox1-mboxa and login-mbox1-mboxb. (if you want such email-boxes, you'll need to define them in the original way: with multiple dotqmail-files) Qmail-sql also supports extension 'default': suppose you defined login-mbox1 and login-default. All mails for login-xxxx will go to login-default (except login-mbox1)
• Mysql doesn't support referential integrity or i don't know how to specify it in SQL: you can enter values in dotqmails, that don't exist in passwd. The 'FOREIGN KEY'-clause seems to be ignored completely, but this is not a problem for qmail-sql. The database-administrator has to do some extra work to check the consistency of his database (if he cares about consistency anyway).




locals

This table contains the hostnames for which you want to use /etc/passwd: all users within this domains have to exist in /etc/passwd, since qmail-sql won't try the database for them. The table is created with:

CREATE TABLE locals (	virtual_host	varchar(64) NOT NULL,
	PRIMARY KEY	(virtual_host)
	);

This table is usually a bad idea: you don't want users like bin, daemon, etc to receive any mail: simply define your /etc/passwd- users also in the database.