USING SQL FOR LOOKUPS, LOG/REPORTING AND QUARANTINE =================================================== This text contains a general SQL-related documentation. For aspects specific to using SQL database for lookups, please see README.lookups . For general aspects of lookups, please see README.lookups. For MySQL-specific notes and schema please see README.sql-mysql. For PostgreSQL-specific notes and schema please see README.sql-pg (which in most respects applies also to a SQLite database). Since version of amavisd-new-20020630 a SQL is supported for lookups. Since amavisd-new-2.3.0, SQL is also supported for storing information about processed mail (logging/reporting) and optionally for quarantining to a SQL database. The amavisd.conf variables @storage_sql_dsn and @lookup_sql_dsn control access to a SQL server and specify a database (dsn = data source name). The @lookup_sql_dsn enables and specifies a database for lookups, the @storage_sql_dsn enables and specifies a database for reporting and quarantining. Both settings are independent. Interpretation of @lookup_sql_dsn and @storage_sql_dsn lists is as follows: - empty list disables the function and is a default; - if both lists are empty no SQL support code will be compiled-in, reducing the amount of virtual memory needed for each child process; - a list can contain one or more triples: [dsn,user,passw]; more than one triple may be specified to specify multiple (backup) SQL servers - the first that responds will be used as long as it works, then search is retried; - if both lists contain refs to the _same_ triples (not just equal triples), only one connection to a SQL server will be used; otherwise two independent connections to databases will be used, possibly to different SQL servers, which may even be of different type (e.g. SQLlite for lookups (read-only), and PostgreSQL or MySQL for transactional reporting, offering fine lock granularity). Example setting: @lookup_sql_dsn = ( ['DBI:mysql:database=mail;host=127.0.0.1;port=3306', 'user1', 'passwd1'], ['DBI:mysql:database=mail;host=host2', 'username2', 'password2'], ['DBI:Pg:database=mail;host=host1', 'amavis', ''] ["DBI:SQLite:dbname=$MYHOME/sql/mail_prefs.sqlite", '', ''] ); @storage_sql_dsn = @lookup_sql_dsn; # none, same, or separate database See man page for the Perl module DBI, and corresponding DBD modules man pages (DBD::mysql, DBD::Pg, DBD::SQLite, ...) for syntax of the first argument. Since version 2.3.0 amavisd-new also offers quarantining to a SQL database, along with a mechanism to release quarantined messages (either from SQL or from normal files, possibly gzipped). To enable quarantining to SQL, the @storage_sql_dsn must be enabled (facilitating quarantine management), and some or all variables $virus_quarantine_method, $spam_quarantine_method, $banned_files_quarantine_method and $bad_header_quarantine_method should specify the value 'sql:'. Specifying 'sql:' as a quarantine method without also specifying a database in @storage_sql_dsn is an error. When setting up access controls to a database, keep in mind that amavisd-new only needs read-only access to the database used for lookups, the permission to do a SELECT suffices. For security reasons it is undesirable to permit other operations such as INSERT, DELETE or UPDATE to a dataset used for lookups. For managing the lookups database one should preferably use a different username with more privileges. The database specified in @storage_sql_dsn needs to provide read/write access (SELECT, INSERT, UPDATE), and a database server offering transactions must be used. Database schemas are available in README.sql-mysql (for MySQL) and in README.sql-pg (for PostgreSQL and SQLite). There are two parts of a schema, an read-only part used for lookups, and a R/W part used for logging and quarantining. They are completely independent and may reside on different SQL servers (even on different types of SQL server), but may also coexist in a single database if desired. Note that some databases are very well suited for lookups, but less so for highly concurent transactional use in logging/quarantining. Some experience: - SQLite works nicely for lookups, avoiding a need for a separate server process, but its coarse locking granularity makes its unquitable for logging and quarantining; - MySQL and PostgreSQL are both fine for lookups; - PostgreSQL is better suited for SQL logging/quarantining because maintenance operations (cleaning of old records) are much faster than with MySQL; Note that SQL logging is needed for amavisd-new pen-pals feature to work; - if using MySQL for logging/quarantining, a sufficiently recent version must be used, as support for transactions is required for the R/W access; - if using SpamAssassin with its Bayes (and AWL) database on SQL, bayes plugin works faster with MySQL than with PostgreSQL; note that SA databases are independent from amavisd-new databases and may reside on a separate SQL server, possibly of a different type. See SpamAssassin documentation that comes with its distribution, files sql/README* . ===================== Example data follows: ===================== INSERT INTO users VALUES ( 1, 9, 5, 'u1+foo@y.example.com','Name1 Surname1'); INSERT INTO users VALUES ( 2, 8, 5, 'u1@y.example.com', 'Name1 Surname1'); INSERT INTO users VALUES ( 3, 8, 2, 'u2@y.example.com', 'Name2 Surname2'); INSERT INTO users VALUES ( 4, 8, 7, 'u3@z.example.com', 'Name3 Surname3'); INSERT INTO users VALUES ( 5, 8, 7, 'u4@example.com', 'Name4 Surname4'); INSERT INTO users VALUES ( 6, 8, 1, 'u5@example.com', 'Name5 Surname5'); INSERT INTO users VALUES ( 7, 7, 9, 'userB+bar', 'NameB SurnameB'); INSERT INTO users VALUES ( 8, 6, 10, 'userC', 'NameC SurnameC'); INSERT INTO users VALUES ( 9, 6, 11, 'userD', 'NameD SurnameD'); INSERT INTO users VALUES (10, 5, 61, '@.a.b.example.com', NULL); INSERT INTO users VALUES (11, 4, 62, '@.sub2.example.com', NULL); INSERT INTO users VALUES (12, 3, 70, '@example.com', NULL); INSERT INTO users VALUES (12, 2, 70, '@.example.com', NULL); INSERT INTO users VALUES (13, 1, 80, '@.com', NULL); INSERT INTO users VALUES (13, 1, 80, '@.edu', NULL); INSERT INTO users VALUES (14, 0, 99, '@.', NULL); -- catchall INSERT INTO users VALUES (15, 5, 0, '@sub1.example.net', NULL); INSERT INTO users VALUES (16, 5, 7, '@sub2.example.net', NULL); INSERT INTO users VALUES (17, 3, 5, '@example.net', NULL); INSERT INTO users VALUES (18, 8, 5, 'u1@example.org', 'u1'); INSERT INTO users VALUES (19, 8, 6, 'u2@example.org', 'u2'); INSERT INTO users VALUES (20, 8, 3, 'u3@example.org', 'u3'); INSERT INTO policy (id, policy_name, virus_lover, spam_lover, bypass_virus_checks, bypass_spam_checks, spam_tag2_level, spam_kill_level) VALUES (0, 'none', NULL,NULL, NULL,NULL, NULL, NULL), (1, 'Non-paying', 'N','N', 'Y','Y', NULL, NULL), (2, 'Uncensored', 'Y','Y', 'N','N', NULL, NULL), (3, 'Wants all spam','N','Y', 'N','N', NULL, NULL), (4, 'Wants viruses', 'Y','N', 'N','N', NULL, NULL), (5, 'Normal', 'N','N', 'N','N', NULL, NULL), (6, 'Trigger happy', NULL,NULL, NULL,NULL, 4.9, 4.9), (7, 'Permissive', NULL,NULL, NULL,NULL, 9, 20), (8, '6.5/7.8', NULL,NULL, NULL,NULL, 6.5, 7.8), (9, 'userB', NULL,NULL, NULL,NULL, 6.3, 6.3), (10,'userC', NULL,NULL, NULL,NULL, 6.0, 6.0), (11,'userD', NULL,NULL, NULL,NULL, 7, 7), (61,'our-sub-a', NULL,'Y', NULL,NULL, NULL, NULL), (62,'our-sub-2', NULL,'Y', NULL,NULL, NULL, NULL), (70,'our domain', NULL,NULL, NULL,NULL, NULL, NULL), (80,'our com & edu', NULL,NULL, NULL,NULL, NULL, 6.6), (99,'catchall', NULL,NULL, NULL,NULL, 5.6, 6.7); -- sender envelope addresses needed for white/blacklisting INSERT INTO mailaddr VALUES (1, 5, '@example.com'); INSERT INTO mailaddr VALUES (2, 9, 'owner-postfix-users@postfix.org'); INSERT INTO mailaddr VALUES (3, 9, 'amavis-user-admin@lists.sourceforge.net'); INSERT INTO mailaddr VALUES (4, 9, 'makemoney@example.com'); INSERT INTO mailaddr VALUES (5, 5, '@example.net'); INSERT INTO mailaddr VALUES (6, 9, 'spamassassin-talk-admin@lists.sourceforge.net'); INSERT INTO mailaddr VALUES (7, 9, 'spambayes-bounces@python.org'); -- whitelist for user 14, i.e. default for recipients in domain sub1.example.net INSERT INTO wblist VALUES (14, 1, 'W'); INSERT INTO wblist VALUES (14, 3, 'W'); -- whitelist and blacklist for user 17, i.e. u1@example.org INSERT INTO wblist VALUES (17, 2, 'W'); INSERT INTO wblist VALUES (17, 3, 'W'); INSERT INTO wblist VALUES (17, 6, 'W'); INSERT INTO wblist VALUES (17, 7, 'W'); INSERT INTO wblist VALUES (17, 5, 'B'); INSERT INTO wblist VALUES (17, 4, 'B'); -- $sql_select_policy setting in amavisd.conf tells amavisd -- how to fetch per-recipient policy settings. -- See comments there. Example: -- -- SELECT *,users.id FROM users,policy -- WHERE (users.policy_id=policy.id) AND (users.email IN (%k)) -- ORDER BY users.priority DESC; -- -- $sql_select_white_black_list in amavisd.conf tells amavisd -- how to check sender in per-recipient whitelist/blacklist. -- See comments there. Example: -- -- SELECT wb FROM wblist,mailaddr -- WHERE (wblist.rid=?) AND (wblist.sid=mailaddr.id) AND (mailaddr.email IN (%k)) -- ORDER BY mailaddr.priority DESC; NOTE: the SELECT, INSERT and UPDATE clauses as used by the amavisd-new program are configurable through %sql_clause; see amavisd.conf-default Upgrading from pre 2.4.0 amavisd-new SQL schema to the 2.4.0 schema requires adding column 'quar_loc' to table msgs, and creating FOREIGN KEY constraint to facilitate deletion of expired records. The following clauses should be executed for upgrading pre-2.4.0 amavisd-new SQL schema to the 2.4.0 schema: -- mandatory change: ALTER TABLE msgs ADD quar_loc varchar(255) DEFAULT ''; -- optional but highly recommended: ALTER TABLE quarantine ADD FOREIGN KEY (mail_id) REFERENCES msgs(mail_id) ON DELETE CASCADE; ALTER TABLE msgrcpt ADD FOREIGN KEY (mail_id) REFERENCES msgs(mail_id) ON DELETE CASCADE; -- the following two ALTERs are not essential; if data type of maddr.id is -- incompatible with msgs.sid and msgs.rid (e.g. BIGINT vs. INT) and MySQL -- complains, don't bother to apply the constraint: ALTER TABLE msgs ADD FOREIGN KEY (sid) REFERENCES maddr(id) ON DELETE RESTRICT; ALTER TABLE msgrcpt ADD FOREIGN KEY (rid) REFERENCES maddr(id) ON DELETE RESTRICT;