MySQL for Postfix and Amavisd-New

# mysql -uroot
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD ('choose_a_root_password');

mysql> create database vmail;
Query OK, 1 row affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON `vmail`.* TO 'vmail_u'@'localhost' IDENTIFIED BY 'choose_a_password';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON `vmail`.* TO 'vmail_u'@'127.0.0.1' IDENTIFIED BY 'choose_a_password';
Query OK, 0 rows affected (0.00 sec)

mysql> use vmail;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Database changed

-- Table structure
 
CREATE TABLE IF_NOT EXISTS `postfix_aliases` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `domain_id` int(11) NOT NULL,
  `address` varchar(255) NOT NULL,
  `goto` varchar(255) NOT NULL,
  `created` datetime NOT NULL,
  `modified` timestamp NOT NULL default '0000-00-00 00:00:00' on update CURRENT_TIMESTAMP,
  `active` enum('0','1') NOT NULL default '1',
  PRIMARY_KEY  (`id`),
  KEY `address` (`address`),
  KEY `domain_id` (`domain_id`),
  KEY `active` (`active`)
) DEFAULT CHARSET=utf8 COMMENT='Virtual Aliases';
 
 
CREATE TABLE IF_NOT EXISTS `postfix_domains` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NULL DEFAULT NULL COMMENT 'CMS UID',
  `domain` varchar(255) NOT NULL,
  `description` varchar(255) NULL,
  `aliases` int(10) NOT NULL default '0',
  `mailboxes` int(10) NOT NULL default '0',
  `maxquota` bigint(20) NOT NULL default '0',
  `quota` bigint(20) NOT NULL default '0',
  `transport` enum('virtual','relay') NOT NULL default 'virtual',
  `goto` varchar(255) NULL,
  `backupmx` tinyint(1) NOT NULL default '0',
  `created` datetime NOT NULL,
  `modified` timestamp NOT NULL default '0000-00-00 00:00:00' on update CURRENT_TIMESTAMP,
  `active` enum('0','1') NOT NULL default '1',
  PRIMARY_KEY  (`id`),
  KEY `domain` (`domain`),
  KEY `active` (`active`)
) DEFAULT CHARSET=utf8 COMMENT='Virtual Domains';
 
 
CREATE TABLE IF_NOT EXISTS `postfix_mailboxes` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `domain_id` int(11) NOT NULL,
  `email` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  `username` varchar(255) NOT NULL,
  `domain` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `maildir` varchar(255) NOT NULL,
  `quota` bigint(20) NOT NULL default '0',
  `created` datetime NOT NULL,
  `modified` timestamp NOT NULL default '0000-00-00 00:00:00' on update CURRENT_TIMESTAMP,
  `active` enum('0','1') NOT NULL default '1',
  `vacation_active` enum('0','1') NOT NULL default '0',
  `vacation_subject` varchar(255) default NULL,
  `vacation_body` text,
  PRIMARY_KEY  (`id`),
  KEY `email` (`email`),
  KEY `username` (`username`),
  KEY `active` (`active`)
) DEFAULT CHARSET=utf8 COMMENT='Virtual Mailboxes';

-- Sample data
 
INSERT INTO `vmail`.`postfix_domains` (
`id` ,
`user_id` ,
`domain` ,
`description` ,
`aliases` ,
`mailboxes` ,
`maxquota` ,
`quota` ,
`transport` ,
`goto` ,
`backupmx` ,
`created` ,
`modified` ,
`active`
)
VALUES (
  NULL , NULL , 'example.net', NULL , '0', '0', '0', '0', 'virtual', NULL ,
'0', NOW( ) , NOW( ) , '1'
), (
  NULL , NULL , 'example.com', 'relayed example domain', '0', '0', '0', '0',
'relay', 'smtp:[mail.example.com]', '0', NOW( ) , NOW( ) , '1'
);
 
 
INSERT INTO `vmail`.`postfix_mailboxes` (
`id` ,
`domain_id` ,
`email` ,
`password` ,
`username` ,
`domain` ,
`name` ,
`maildir` ,
`quota` ,
`created` ,
`modified` ,
`active` ,
`vacation_active` ,
`vacation_subject` ,
`vacation_body`
)
VALUES (
  NULL , '1', 'user@example.net', MD5( 'password' ) , 'user', 'example.net',
'user', 'example.net/user/', '0', NOW( ) , NOW( ) , '1', '0', NULL , NULL
);
 
 
INSERT INTO `vmail`.`postfix_aliases` (
`id` ,
`domain_id` ,
`address` ,
`goto` ,
`created` ,
`modified` ,
`active`
)
VALUES (
  NULL , '1', 'user@example.net', 'user@example.net', NOW( ) , NOW( ) , '1'
);

-- local users
CREATE TABLE users (
  id         int unsigned NOT NULL AUTO_INCREMENT PRIMARY_KEY,  -- unique id
  priority   integer      NOT NULL DEFAULT '7',  -- sort field, 0 is low prior.
  policy_id  integer unsigned NOT NULL DEFAULT '1',  -- JOINs with policy.id
  email      varbinary(255) NOT NULL UNIQUE,
  fullname   varchar(255) DEFAULT NULL,    -- not used by amavisd-new
  local      char(1)      -- Y/N  (optional field, see note further down)
);
 
 
-- any e-mail address (non- rfc2822-quoted), external or local,
-- used as senders in wblist
CREATE TABLE mailaddr (
  id         int unsigned NOT NULL AUTO_INCREMENT PRIMARY_KEY,
  priority   integer      NOT NULL DEFAULT '7',  -- 0 is low priority
  email      varbinary(255) NOT NULL UNIQUE
);
 
 
-- per-recipient whitelist and/or blacklist,
-- puts sender and recipient in relation wb  (white or blacklisted sender)
CREATE TABLE wblist (
  rid        integer unsigned NOT NULL,  -- recipient: users.id
  sid        integer unsigned NOT NULL,  -- sender: mailaddr.id
  wb         varchar(10)  NOT NULL,  -- W or Y / B or N / space=neutral / score
  PRIMARY_KEY (rid,sid)
);
 
 
CREATE TABLE policy (
  id  int unsigned NOT NULL AUTO_INCREMENT PRIMARY_KEY,
                                    -- 'id' this is the _only_ required field
  policy_name      varchar(32),     -- not used by amavisd-new, a comment
 
  virus_lover          char(1) default NULL,     -- Y/N
  spam_lover           char(1) default NULL,     -- Y/N
  banned_files_lover   char(1) default NULL,     -- Y/N
  bad_header_lover     char(1) default NULL,     -- Y/N
 
  bypass_virus_checks  char(1) default NULL,     -- Y/N
  bypass_spam_checks   char(1) default NULL,     -- Y/N
  bypass_banned_checks char(1) default NULL,     -- Y/N
  bypass_header_checks char(1) default NULL,     -- Y/N
 
  spam_modifies_subj   char(1) default NULL,     -- Y/N
 
  virus_quarantine_to      varchar(64) default NULL,
  spam_quarantine_to       varchar(64) default NULL,
  banned_quarantine_to     varchar(64) default NULL,
  bad_header_quarantine_to varchar(64) default NULL,
  clean_quarantine_to      varchar(64) default NULL,
  other_quarantine_to      varchar(64) default NULL,
 
  spam_tag_level  float default NULL, -- higher score inserts spam info headers
  spam_tag2_level float default NULL, -- inserts 'declared spam' header fields
  spam_kill_level float default NULL, -- higher score triggers evasive actions
                                      -- e.g. reject/drop, quarantine, ...
                                     -- (subject to final_spam_destiny setting)
  spam_dsn_cutoff_level        float default NULL,
  spam_quarantine_cutoff_level float default NULL,
 
  addr_extension_virus      varchar(64) default NULL,
  addr_extension_spam       varchar(64) default NULL,
  addr_extension_banned     varchar(64) default NULL,
  addr_extension_bad_header varchar(64) default NULL,
 
  warnvirusrecip      char(1)     default NULL, -- Y/N
  warnbannedrecip     char(1)     default NULL, -- Y/N
  warnbadhrecip       char(1)     default NULL, -- Y/N
  newvirus_admin      varchar(64) default NULL,
  virus_admin         varchar(64) default NULL,
  banned_admin        varchar(64) default NULL,
  bad_header_admin    varchar(64) default NULL,
  spam_admin          varchar(64) default NULL,
  spam_subject_tag    varchar(64) default NULL,
  spam_subject_tag2   varchar(64) default NULL,
  message_size_limit  integer     default NULL, -- max size in bytes, 0 disable
  banned_rulenames    varchar(64) default NULL  -- comma-separated list of ...
        -- names mapped through %banned_rules to actual banned_filename tables
);

-- provide unique id for each e-mail address, avoids storing copies
CREATE TABLE maddr (
  partition_tag integer      DEFAULT 0, -- see $partition_tag
  id         bigint unsigned NOT NULL AUTO_INCREMENT PRIMARY_KEY,
  email      varbinary(255)  NOT NULL,  -- full mail address
  domain     varchar(255)    NOT NULL,  -- only domain part of the email
address
                                        -- with subdomain fields in reverse
  CONSTRAINT part_email UNIQUE (partition_tag,email)
) ENGINE=InnoDB;
 
-- information pertaining to each processed message as a whole;
-- NOTE: records with NULL msgs.content should be ignored by utilities,
--   as such records correspond to messages just being processes, or were lost
-- NOTE: instead of a character field time_iso, one might prefer:
--   time_iso TIMESTAMP NOT NULL DEFAULT 0,
--   but the following MUST then be set in amavisd.conf: $timestamp_fmt_mysql=1
CREATE TABLE msgs (
  partition_tag integer    DEFAULT 0,   -- see $partition_tag
  mail_id    varbinary(12) NOT NULL,    -- long-term unique mail id
  secret_id  varbinary(12) DEFAULT '',  -- authorizes release of mail_id
  am_id      varchar(20)   NOT NULL,    -- id used in the log
  time_num   integer unsigned NOT NULL, -- rx_time: seconds since Unix epoch
  time_iso   TIMESTAMP NOT NULL DEFAULT 0,
  sid        bigint unsigned NOT NULL,  -- sender: maddr.id
  policy     varchar(255)  DEFAULT '',  -- policy bank path (like macro %p)
  client_addr varchar(255) DEFAULT '',  -- SMTP client IP address (IPv4 or v6)
  size       integer unsigned NOT NULL, -- message size in bytes
  content    char(1),                   -- content type: V/B/U/S/Y/M/H/O/T/C
    -- virus/banned/unchecked/spam(kill)/spammy(tag2)/
    -- /bad-mime/bad-header/oversized/mta-err/clean
    -- is NULL on partially processed mail
    -- (prior to 2.7.0 the CC_SPAMMY was logged as 's', now 'Y' is used;
    -- to avoid a need for case-insenstivity in queries)
  quar_type  char(1),                   -- quarantined as: ' '/F/Z/B/Q/M/L
                                        --  none/file/zipfile/bsmtp/sql/
                                        --  /mailbox(smtp)/mailbox(lmtp)
  quar_loc   varbinary(255) DEFAULT '', -- quarantine location (e.g. file)
  dsn_sent   char(1),                   -- was DSN sent? Y/N/q (q=quenched)
  spam_level float,                     -- SA spam level (no boosts)
  message_id varchar(255)  DEFAULT '',  -- mail Message-ID header field
  from_addr  varchar(255)  DEFAULT '',  -- mail From header field,    UTF8
  subject    varchar(255)  DEFAULT '',  -- mail Subject header field, UTF8
  host       varchar(255)  NOT NULL,    -- hostname where amavisd is running
  PRIMARY_KEY (partition_tag,mail_id),
  FOREIGN_KEY (sid) REFERENCES maddr(id) ON DELETE RESTRICT
) ENGINE=InnoDB;
CREATE INDEX msgs_idx_sid      ON msgs (sid);
CREATE INDEX msgs_idx_mess_id  ON msgs (message_id); -- useful with pen pals
CREATE INDEX msgs_idx_time_num ON msgs (time_num);
-- alternatively when purging based on time_iso (instead of msgs_idx_time_num):
-- CREATE INDEX msgs_idx_time_iso ON msgs (time_iso);
 
-- per-recipient information related to each processed message;
-- NOTE: records in msgrcpt without corresponding msgs.mail_id record are
--  orphaned and should be ignored and eventually deleted by external utilities
CREATE TABLE msgrcpt (
  partition_tag integer    DEFAULT 0,    -- see $partition_tag
  mail_id    varbinary(12) NOT NULL,     -- (must allow duplicates)
  rseqnum    integer       DEFAULT 0,    -- recipient count within one message
  rid        bigint unsigned NOT NULL,   -- recipient: maddr.id (dupl. allowed)
  content    char(1),                    -- content type: V/B/U/S/Y/M/H/O/T/C
  ds         char(1)       NOT NULL,     -- delivery status: P/R/B/D/T
                                         -- pass/reject/bounce/discard/tempfail
  rs         char(1)       NOT NULL,     -- release status: initialized to ' '
  bl         char(1)       DEFAULT ' ',  -- sender blacklisted by this recip
  wl         char(1)       DEFAULT ' ',  -- sender whitelisted by this recip
  bspam_level float,                     -- spam level + per-recip boost
  smtp_resp  varchar(255)  DEFAULT '',   -- SMTP response given to MTA
  PRIMARY_KEY (partition_tag,mail_id,rseqnum),
  FOREIGN_KEY (rid)     REFERENCES maddr(id)     ON DELETE RESTRICT
--  FOREIGN KEY (mail_id) REFERENCES msgs(mail_id) ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE INDEX msgrcpt_idx_mail_id  ON msgrcpt (mail_id);
CREATE INDEX msgrcpt_idx_rid      ON msgrcpt (rid);
 
-- mail quarantine in SQL, enabled by $*_quarantine_method='sql:'
-- NOTE: records in quarantine without corresponding msgs.mail_id record are
--  orphaned and should be ignored and eventually deleted by external utilities
CREATE TABLE quarantine (
  partition_tag integer    DEFAULT 0,    -- see $partition_tag
  mail_id    varbinary(12) NOT NULL,     -- long-term unique mail id
  chunk_ind  integer unsigned NOT NULL,  -- chunk number, starting with 1
  mail_text  blob          NOT NULL,     -- store mail as chunks of octets
  PRIMARY_KEY (partition_tag,mail_id,chunk_ind)
-- FOREIGN KEY (mail_id) REFERENCES msgs(mail_id) ON DELETE CASCADE
) ENGINE=InnoDB;
 
-- field msgrcpt.rs is primarily intended for use by quarantine management
-- software; the value assigned by amavisd is a space;
-- a short _preliminary_ list of possible values:
--   'V' => viewed (marked as read)
--   'R' => released (delivered) to this recipient
--   'p' => pending (a status given to messages when the admin received the
--                   request but not yet released; targeted to banned parts)
--   'D' => marked for deletion; a cleanup script may delete it

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.02 sec)