> Is there a tutorial for someone like me to start using mysql w/Amavis > and SA?
This should help you with SpamAssassin: http://www200.pair.com/mecham/spam/debian-spamassassin-sql.html and I would read the README.sql for amavisd-new: http://www.ijs.si/software/amavisd/README.sql.txt To get you started, I copied the database scheme from the README for the users, mailaddr, wblist and policy tables to a website that you can wget. On my system I add a user 'amavis' that can only read data, and a user 'amavisu' that can maintain the data, you should change the 'passwd': cd /usr/local/src wget http://www200.pair.com/mecham/spam/basic_users_v1.sql mysql -u root -p CREATE DATABASE amavisd; USE amavisd; SOURCE basic_users_v1.sql; GRANT SELECT ON amavisd.* TO [EMAIL PROTECTED] IDENTIFIED BY 'passwd'; GRANT CREATE, DROP, ALTER, SELECT, INSERT, UPDATE, DELETE ON amavisd.* TO [EMAIL PROTECTED] IDENTIFIED BY 'passwd'; FLUSH PRIVILEGES; use amavisd; show tables; describe policy; In amavisd.conf, I added: @lookup_sql_dsn = ( ['DBI:mysql:amavisd:localhost', 'amavis', 'passwd'] ); Also take a look at: http://infocenter.guardiandigital.com/archive/amavis/2004/Dec/0316.html I also started to make some personal notes that I can share (I don't really know if they are accurate however): ############################################################### The purpose of the policy_id field will be apparent when we add some data to the policy table, but what is the 'priority' field used for? If you were to read README.lookups, you would see that recipient lookups in hash tables are performed starting with the most specific data (the full email address) and end with the most general data (a catchall). Here is the example of the order of lookups performed when using a hash table: [EMAIL PROTECTED] [EMAIL PROTECTED] user+foo@ user@ sub.example.com .sub.example.com .example.com .com . For SQL, the structure of what is looked up is a little different, but we still want the lookups performed in the same 'specific' to 'general' order, for example: [EMAIL PROTECTED] [EMAIL PROTECTED] user+foo user @example.com @.example.com @.com @. The key is that the SQL SELECT statement used in the program uses the priority field to create this order (in descending order): >From the amavisd source code: # The SQL select clause to fetch per-recipient policy settings # The %k will be replaced by a comma-separated list of query addresses # (e.g. full address, domain only, catchall). Use ORDER if there # is a chance that multiple records will match - the first match wins # If field names are not unique (e.g. 'id'), the later field overwrites the # earlier in a hash returned by lookup, which is why we use '*,users.id'. $sql_select_policy = 'SELECT *,users.id FROM users LEFT JOIN policy ON users.policy_id=policy.id'. ' WHERE users.email IN (%k) ORDER BY users.priority DESC'; So, given a recipient address of '[EMAIL PROTECTED]', the SQL SELECT statement that determines the policy for a given recipient would end up looking like this: SELECT *,users.id FROM users LEFT JOIN policy ON users.policy_id=policy.id WHERE users.email IN ("[EMAIL PROTECTED]", "[EMAIL PROTECTED]", "user4+spam", "user4", "@example.com", "@.example.com", "@.com", "@.") ORDER BY users.priority DESC; ################################################################### This is just a start (but a usable one) for amavisd-new tables. Gary V ------------------------------------------------------- This SF.net email is sponsored by: Splunk Inc. Do you grep through log files for problems? Stop! Download the new AJAX search engine that makes searching your log files as easy as surfing the web. DOWNLOAD SPLUNK! http://ads.osdn.com/?ad_id=7637&alloc_id=16865&op=click _______________________________________________ AMaViS-user mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/amavis-user AMaViS-FAQ:http://www.amavis.org/amavis-faq.php3 AMaViS-HowTos:http://www.amavis.org/howto/
