# Make a copy of this file and edit the copy! # Next upgrade will overwrite the content of this file! # If you want to change the length of the _key, _sender, # _recipient and _address fields, then # # - change the length of all such fields # - update the MAX_ATTR_LEN variable in etc/schema.cf # # In IP address related fields (_ip and _client_address) # 39 chars would be just sufficient, we use 40 instead. # # _passwd field requires: # # - cleartext: as you wish # - encrypt: 13 chars # - md5: 32 chars # - sha[1]: 40 chars # # Postfilter database # DROP DATABASE IF EXISTS postfilter; CREATE DATABASE postfilter CHARACTER SET latin1 COLLATE latin1_bin; USE postfilter; # # Dynamically updated tables # # # Spamtrap table CREATE TABLE spamtrap ( _key CHAR(64) NOT NULL default '', _value CHAR(32) NOT NULL default '', _timestamp TIMESTAMP, UNIQUE INDEX (_key) ) TYPE=MyISAM; # # Greylisting table CREATE TABLE greylist ( _sender CHAR(64) NOT NULL default '', _recipient CHAR(64) NOT NULL default '', _ip char(40) NOT NULL default '', _last_seen TIMESTAMP, _first_seen TIMESTAMP, _count SMALLINT UNSIGNED NOT NULL default 0, UNIQUE KEY _ip (_ip,_sender,_recipient), KEY _last_seen (_last_seen), KEY _first_seen (_first_seen) ) TYPE=MyISAM; # # Whitelist of greylisted clients CREATE TABLE greylist_white ( _key CHAR(64) NOT NULL default '', _value CHAR(32) NOT NULL default 'auto-whitelisted', _timestamp TIMESTAMP, UNIQUE INDEX (_key) ) TYPE=MyISAM; # # Throttle table CREATE TABLE throttle ( _address CHAR(64) NOT NULL default '', _count_max MEDIUMINT UNSIGNED NOT NULL default 0, _count_curr MEDIUMINT UNSIGNED NOT NULL default 1, _rcpt_max MEDIUMINT UNSIGNED NOT NULL default 0, _rcpt_curr MEDIUMINT UNSIGNED NOT NULL default 1, _quota_max INT UNSIGNED NOT NULL default 0, _quota_curr INT UNSIGNED NOT NULL default 0, _disabled INT UNSIGNED NOT NULL default 0, _date INT UNSIGNED NOT NULL default 0, _time_period MEDIUMINT UNSIGNED NOT NULL default 0, _priority TINYINT UNSIGNED NOT NULL default 0, UNIQUE KEY (_address) ) TYPE=MyISAM; # # Captcha table CREATE TABLE captcha ( _sender CHAR(64) NOT NULL DEFAULT '', _recipient CHAR(64) NOT NULL DEFAULT '', _captcha CHAR(32), _timestamp TIMESTAMP, _try TIMESTAMP, _probes TINYINT UNSIGNED DEFAULT 0, _used TINYINT UNSIGNED DEFAULT 0, UNIQUE INDEX (_sender,_recipient) ) TYPE=MyISAM; # # Cookies CREATE TABLE cookie ( _address CHAR(64) NOT NULL DEFAULT '', _cookie CHAR(32), _timestamp TIMESTAMP, UNIQUE INDEX (_address) ) TYPE=MyISAM; # # Tables updated by the administrator or CGI # # # Blacklisted external clients CREATE TABLE banned_client ( _key CHAR(64) NOT NULL default '', _value CHAR(32) NOT NULL default '', _timestamp TIMESTAMP, UNIQUE INDEX (_key) ) TYPE=MyISAM; # # Blacklisted senders CREATE TABLE banned_sender ( _key CHAR(64) NOT NULL default '', _value CHAR(32) NOT NULL default '', _timestamp TIMESTAMP, UNIQUE INDEX (_key) ) TYPE=MyISAM; # # Blacklisted helo names CREATE TABLE banned_helo ( _key CHAR(64) NOT NULL default '', _value CHAR(32) NOT NULL default '', _timestamp TIMESTAMP, UNIQUE INDEX (_key) ) TYPE=MyISAM; # # Global blacklist table CREATE TABLE blacklist ( _key CHAR(64) NOT NULL default '', _value CHAR(32) NOT NULL default '', _timestamp TIMESTAMP, UNIQUE INDEX (_key) ) TYPE=MyISAM; # # Global whitelist table CREATE TABLE whitelist ( _key CHAR(64) NOT NULL default '', _value CHAR(32) NOT NULL default '', _timestamp TIMESTAMP, UNIQUE INDEX (_key) ) TYPE=MyISAM; # # Honeypot addresses CREATE TABLE honeypot ( _key CHAR(64) NOT NULL default '', _value CHAR(32) NOT NULL default '', _timestamp TIMESTAMP, UNIQUE INDEX (_key) ) TYPE=MyISAM; # # Global HELO/EHLO table CREATE TABLE helo ( _key CHAR(64) NOT NULL default '', _value CHAR(32) NOT NULL default '', _timestamp TIMESTAMP, UNIQUE INDEX (_key) ) TYPE=MyISAM; # # User whitelist table CREATE TABLE user_whitelist ( _userid INT UNSIGNED NOT NULL, _address CHAR(64) NOT NULL default '', # type: 0/1, user/captcha _type BOOL DEFAULT 0, _timestamp TIMESTAMP, INDEX (_userid), UNIQUE INDEX (_userid, _address) ) TYPE=MyISAM; # # User table # CREATE TABLE user ( # Unique id _userid INT UNSIGNED NOT NULL AUTO_INCREMENT, # Id of master address, if any _master INT UNSIGNED NOT NULL DEFAULT 0, # The address itself _address CHAR(64) NOT NULL default '', # password, stored in cleartext or encrypted _passwd CHAR(40) DEFAULT '', # address type: 0/1, real/virtual _type BOOL DEFAULT 0, # mode: 0/1, standalone/slave _mode BOOL DEFAULT 0, # externally public or not: 0/1, public/hidden _hidden BOOL DEFAULT 0, # Postfix filtering settings _filter CHAR(255) NOT NULL DEFAULT 'permit', # Preferred language _lang CHAR(2) DEFAULT '', PRIMARY KEY (_userid), INDEX (_master), UNIQUE INDEX (_address) ) TYPE=MyISAM; # id 0 is reserved as master for standalone addresses ALTER TABLE user AUTO_INCREMENT=1; # # Configuration table # CREATE TABLE config ( _version INT UNSIGNED NOT NULL AUTO_INCREMENT, _config MEDIUMTEXT, _timestamp TIMESTAMP, UNIQUE INDEX (_version) ) TYPE=MyISAM; # Do no remove the next line: # Report table # CREATE TABLE report ( # Sender address _sender CHAR(64), # Recipient address _recipient CHAR(64) NOT NULL DEFAULT '', # Client name (see MAX_CLIENT_LEN in etc/schema.cf) _client_name CHAR(128), # Client address _client_address CHAR(40), # (SASL) username, _username CHAR(64) NOT NULL DEFAULT '', # Class name _class CHAR(32), # Policy name _policy CHAR(32), # Report (i.e rejected message or not) _report BOOL DEFAULT 1, # Date, time _timestamp TIMESTAMP, INDEX (_recipient) ) TYPE=MyISAM; # Do no remove the next line: # Report table # # Statistics table # CREATE TABLE statistics ( # Entry data (client_address, etc.) _entry CHAR(64) NOT NULL DEFAULT '', # Entry type _type CHAR(64) NOT NULL DEFAULT '', # Additional info (client_name) _info CHAR(128) NOT NULL DEFAULT '', # Counter _count INT UNSIGNED NOT NULL DEFAULT 0, UNIQUE KEY (_entry, _type) ) TYPE=MyISAM; # Access rights for CGI scripts (captcha/cookie) GRANT DELETE,INSERT,UPDATE,SELECT ON postfilter.* TO 'postfil'@'your.www.server' IDENTIFIED BY 'post filter'; # Access rights for policy daemon from SMTP hosts GRANT DELETE,INSERT,UPDATE,SELECT ON postfilter.* TO 'postfil'@'your.smtp.server' IDENTIFIED BY 'post filter'; # Access rights for reporting and maintenance from admin host GRANT CREATE,DROP,ALTER,SELECT,INSERT,DROP,UPDATE ON postfilter.* TO 'postfil'@'admin.host' IDENTIFIED BY 'post filter';