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).

  1. passwd
  2. 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.



  3. dotqmails
  4. 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
    );



  5. locals
  6. 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.