Jim wrote: > Heute (17.12.2005/05:03 Uhr) schrieb Gary V ([EMAIL PROTECTED]),
>>> 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. > Great tutorial. :) And very simply explained. Thank you, Gary Thanks Jim. If the example data is studied in the README it becomes apparent what form the data needs to be in. I should also mention once you have your data entered, and need to change something, of course you should begin studying SQL, but to cheat: UPDATE users SET policy_id=6 WHERE email='[EMAIL PROTECTED]'; Here we change the policy for [EMAIL PROTECTED] In addition to studying: http://www.ijs.si/software/amavisd/README.lookups.txt this link may be useful: http://marc.theaimsgroup.com/?l=amavis-user&m=113320135827485&w=2 I have not started to play with the wblist, but along with README.sql I think these links could help with understanding wblist. http://www.ijs.si/software/amavisd/amavisd-new-docs.html#wblist http://marc.theaimsgroup.com/?l=amavis-user&m=113173915721641&w=2 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 AMaViS-user@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/amavis-user AMaViS-FAQ:http://www.amavis.org/amavis-faq.php3 AMaViS-HowTos:http://www.amavis.org/howto/
