CVS: tmda/htdocs ChangeLog,1.166,1.167 filter-sources.ht,1.7,1.8 filter-sources.html,1.23,1.24
Timothy Legant <[email protected]>
| Newsgroups | gmane.mail.spam.tmda.cvs |
|---|---|
| Message-ID | <[email protected]> |
Update of /cvsroot/tmda/tmda/htdocs In directory sc8-pr-cvs1:/tmp/cvs-serv30146/htdocs Modified Files: ChangeLog filter-sources.ht filter-sources.html Log Message: Added section on new sources 'from-sql' and 'to-sql'. Index: ChangeLog =================================================================== RCS file: /cvsroot/tmda/tmda/htdocs/ChangeLog,v retrieving revision 1.166 retrieving revision 1.167 diff -u -r1.166 -r1.167 --- ChangeLog 5 Jul 2003 00:37:54 -0000 1.166 +++ ChangeLog 9 Jul 2003 20:21:18 -0000 1.167 @@ -1,3 +1,8 @@ +2003-07-09 Tim Legant <[email protected]> + + * filter-sources.ht: Added section on new sources 'from-sql' and + 'to-sql'. + 2003-07-04 Tim Legant <[email protected]> * tmda-vdomains.ht (Title): Added information on tmda-filter's Index: filter-sources.ht =================================================================== RCS file: /cvsroot/tmda/tmda/htdocs/filter-sources.ht,v retrieving revision 1.7 retrieving revision 1.8 diff -u -r1.7 -r1.8 --- filter-sources.ht 2 Jul 2003 17:13:21 -0000 1.7 +++ filter-sources.ht 9 Jul 2003 20:21:18 -0000 1.8 @@ -145,6 +145,159 @@ (i.e. <code>.+@my\.host\.name</code> would match all users in the <code>my.host.name</code> domain).<br> <br> + <dt> <code> from-sql [ <a href="#domains">-domains</a> ] -wildcards | <-addr_column=<column_name> [ -action_column=<column_name>]> <SQL_query><br> + to-sql [ <a href="#domains">-domains</a> ] -wildcards | <-addr_column=<column_name> [ -action_column=<column_name> ]> <SQL_query> </code> + <dd> The <code>from-sql</code> and <code>to-sql</code> sources match + against addresses stored in a SQL database. The <SQL_query> + is a SQL SELECT statement that should retrieve the appropriate + data. Just what that data needs to be will vary, depending on how + you choose to use the <code>from-sql</code> and + <code>to-sql</code> rules. Remember to enclose the + <SQL_query> in quotes, since it will contain spaces. Double + quotes are recommended, to avoid clashing with the single quotes + used by SQL. <br> + <br> + For a simple SELECT statement, you can put it directly in your + <code>from-sql</code> or <code>to-sql</code> rule. For more + complex statements, you can define a macro in the filter + file or you can define a variable in either /etc/tmdarc or + ~/.tmda/config and use the macro or variable name in the filter + rule. Defining a variable can be more flexible, especially for + multiple users at a single site who need the same SELECT statement + with minor modifications. For example, you could define this + SELECT statement in your /etc/tmdarc: <br> + <pre> + <code>SQL_WHITELIST = """ + SELECT wl.address + FROM whitelist AS wl, users AS u + WHERE u.uid = wl.uid + AND u.address = %(recipient)s + AND %(criteria)s + LIMIT 1"""</code></pre> + This assumes a 'users' table with a unique ID (<code>uid</code>) + and the user's email address (<code>address</code>). It joins the + 'users' and 'whitelist' tables based on the + <code>uid</code>. Details on '%(recipient)s' and '%(criteria)s' + can be found below. <br> + <br> + A rule in your incoming filter using this variable would look like + this: <br> + <pre> + <code>from-sql -addr_column=wl.address "${SQL_WHITELIST}" accept</code></pre> + Note that the variable is quoted because it is a string that + contains spaces. <br> + <br> + <i>Pre-requisite</i>: In either /etc/tmdarc or ~/.tmda/config you + must set the <a + href="config-vars.html#DB_CONNECTION">DB_CONNECTION</a> variable + so that TMDA can talk to the database. How this is done depends on + the database module used. Examples: <br> + <br> + <dl> + <dt>MySQLdb</dt> + <dd><pre><code>DB_CONNECTION = MySQLdb.connect(db='<dbname>', + host='<dbhost>', + user='<username>', + passwd='<password>')</code></pre></dd> + <dt>PyGreSQL</dt> + <dd><pre><code>DB_CONNECTION = pgdb.connect('<dbhost>:<dbname>:<username>:<password>')</code></pre> + </dd> + </dl> + <h4>Wildcard Searches</h4> + The <code>*-sql</code> rules can be used in two scenarios. If the + <code>-wildcards</code> argument is given, the <SQL_query> + is run and the resulting data set is read, in its entirety, from + the database. The first column should be the addresses to match + against. The second column is optional, but if it is present, it + should be the overriding action or NULL. The returned data is + searched in exactly the same way as text files containing + wildcards. See <a href="#email_address">Email Addresses</a> + below. <br> + <br> + Any columns beyond the second will be ignored. This can come in + handy if you need a column in the SELECT list for an ORDER BY + clause. Because the search code stops at the first match, unsorted + data could cause an incorrect match and the overriding action + might not be what you want. If you use wildcards in the address + column and you allow an overriding action, you should sort the + returned values using an ORDER BY clause. + <h4>Exact Match Searches</h4> + If an exact match of the sender or recipient is all you need, + e.g. you don't need wildcards, then you can use the *-sql rules to + have the database perform the search for you, returning only the + rows that exactly matched. You should specify the + <code>-addr_column</code> argument and provide the name of the + column that contains the addresses to search. You do <b>not</b> + need to include this column in the SELECT list. <br> + <br> + If you have an overriding action column, you should give its name + using the <code>-action_column</code> argument. If you use the + <code>-action_column</code> argument, you <b>must</b> include that + column in the SELECT list. <br> + <br> + <i>Caveat</i>: When the exact-match form of the + <code>from-sql</code> rule is used, TMDA can search for more than + one sender at once. If the SELECT statement returns more than one + row, TMDA will use the overriding action from the first row, since + it has no way of knowing which sender (the From:, the Reply-To: or + the envelope sender address) you care most about. Instead of + using overriding actions, consider using separate blacklists and + whitelists. <br> + <br> + Your SELECT statement can be as complex as you care to make it, + including joins, an ORDER BY clause, a LIMIT clause, etc. TMDA + must know where to place the search conditions ("<sender1> = + <addr_column> OR <sender2> = <addr_column>", + etc.). You should include the string "%(criteria)s" in your SELECT + statement at the appropriate location. TMDA will build the list of + conditions based on the addresses to be matched and will replace + "%(criteria)s" with that list. Here's an example to make this + clearer. <br> + <br> + Assume you have the following rule in your incoming filter.<br> + <pre> + <code>from-sql -addr_column=address <SQL_query> ok</code></pre> + An email arrives with a From: header of "[email protected]" and + a Reply-To: header of "[email protected]". TMDA will generate the + following criteria string: <br> + <pre> + <code>(address = '[email protected]' OR address = '[email protected]')</code></pre> + Your SELECT statement (<SQL_query>) might look something like this:<br> + <pre> + <code>SELECT address FROM addr_list WHERE %(criteria)s</code></pre> + The SQL code that TMDA actually sends to the database will look + like this (reformatted for easier readability):<br> + <pre> + <code>SELECT address + FROM addr_list + WHERE (address = '[email protected]' OR + address = '[email protected]')</code></pre> + If you store all of your users' whitelists in a single table (a + good schema design), you will need some way to restrict your + search to a single user's list; the user whose copy of TMDA is + querying the database. In order to facilitate that, the + <code>from-sql</code> and <code>to-sql</code> rules provide three + strings that can be used anywhere in your SELECT statement.<br> + <br> + <ul> + <li>username -- the value of the USERNAME variable from ~/.tmda/config + <li>hostname -- the value of the HOSTNAME variable from ~/.tmda/config + <li>recipient -- username@hostname + </ul> + <br> + You can place these in your SELECT statement by using + "%(username)s", "(%hostname)s" and/or "%(recipient)s", as needed. + TMDA will substitute the appropriate values into the SELECT at the + time of the search. Do not put quotes around the above + variables. The Python DB API takes care of that for you in a + manner appropriate for the database you are using. <br> + <br> + <code>-domains</code> tells the parser that the database column + containing addresses to match might also contain <a + href="#domains">domains</a>. This is the <code>-addr_column</code> + in the case of exact matches or the first column (the name is + irrelevant) in the case of <code>-wildcards</code>. <br> + <br> </dl> The following group of sources may be used only in incoming filter files. <br> <br> Index: filter-sources.html =================================================================== RCS file: /cvsroot/tmda/tmda/htdocs/filter-sources.html,v retrieving revision 1.23 retrieving revision 1.24 diff -u -r1.23 -r1.24 --- filter-sources.html 3 Jul 2003 20:36:40 -0000 1.23 +++ filter-sources.html 9 Jul 2003 20:21:18 -0000 1.24 @@ -1,191 +1,186 @@ -<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> -<html> +<HTML> <!-- THIS PAGE IS AUTOMATICALLY GENERATED. DO NOT EDIT. --> -<!-- Thu Jul 3 14:34:18 2003 --> -<!-- USING HT2HTML 2.0 --> -<!-- SEE http://ht2html.sf.net --> +<!-- Wed Jul 9 15:11:18 2003 --> +<!-- USING HT2HTML 1.2 --> +<!-- SEE http://barry.wooz.org/software/ht2html --> <!-- User-specified headers: Title: TMDA Filter Sources --> -<head> -<title>TMDA Filter Sources</title> -<meta http-equiv="Content-Type" content="text/html; charset=us-ascii"> -<meta name="generator" content="HT2HTML/2.0"> -<style type="text/css"> -body { margin: 0px; } -</style> -</head> -<body bgcolor="#ffffff" text="#000000" - marginwidth="0" marginheight="0" - link="#0000bb" vlink="#551a8b" - alink="#ff0000"> +<HEAD> +<TITLE>TMDA Filter Sources</TITLE> + +</HEAD> +<BODY BGCOLOR="#ffffff" TEXT="#000000" + TOPMARGIN="0" LEFTMARGIN="0" MARGINWIDTH="0" MARGINHEIGHT="0" + LINK="#0000bb" VLINK="#551a8b" + ALINK="#ff0000"> <!-- start of page table --> -<table width="100%" border="0" cellspacing="0" cellpadding="0"> +<TABLE WIDTH="100%" BORDER=0 CELLSPACING=0 CELLPADDING=0> <!-- start of banner row --> -<tr> +<TR> <!-- start of corner cells --> -<td width="150" valign="middle" bgcolor="#afeeee" class="corner"> +<TD WIDTH=150 VALIGN=CENTER BGCOLOR="#afeeee"> <center><font size="+2" - >>>> TMDA </font></center> </td> -<td width="15" bgcolor="#cccccc"> </td><!--spacer--> + >>>> TMDA </font></center> </TD> +<TD WIDTH=15 BGCOLOR="#cccccc"> </TD><!--spacer--> <!-- end of corner cells --> <!-- start of banner --> -<td width="90%" bgcolor="#cccccc" class="banner"> +<TD WIDTH="90%" BGCOLOR="#cccccc"> <!-- start of site links table --> -<table width="100%" border="0" +<TABLE WIDTH="100%" BORDER=0 CELLSPACING=0 CELLPADDING=0 - bgcolor="#ffffff"> -<tr> - <td bgcolor="#cccccc"> + COLS=4 ROWS=1 BGCOLOR="#ffffff"> +<TR> + <TD BGCOLOR="#cccccc"> <a href="./index.html">TMDA Homepage</a><br>[ <a href="http://www.au.tmda.net/">AU</a> | <a href="http://www.us.tmda.net/">US</a> mirror ] - </td> - <td bgcolor="#cccccc"> + </TD> + <TD BGCOLOR="#cccccc"> <a href="./tmda-cgi">tmda-cgi</a> - </td> - <td bgcolor="#cccccc"> + </TD> + <TD BGCOLOR="#cccccc"> <a href="http://sourceforge.net/projects/tmda">TMDA @ SourceForge</a> - </td> - <td bgcolor="#cccccc"> - </td> -</tr> -</table><!-- end of site links table --> - -</td><!-- end of banner --> -</tr><!-- end of banner row --> -<tr><!-- start of sidebar/body row --> + </TD> + <TD BGCOLOR="#cccccc"> + </TD> +</TR> +</TABLE><!-- end of site links table --> + +</TD><!-- end of banner --> +</TR><!-- end of banner row --> +<TR><!-- start of sidebar/body row --> <!-- start of sidebar cells --> -<td width="150" valign="top" bgcolor="#cccccc" class="sidebar"> +<TD WIDTH=150 VALIGN=TOP BGCOLOR="#cccccc"> <!-- start of sidebar table --> -<table width="100%" border="0" cellspacing="0" cellpadding="3" - bgcolor="#ffffff"> -<tr><td bgcolor="#191970"><b><font color="#ffffff"> +<TABLE WIDTH="100%" BORDER=0 CELLSPACING=0 CELLPADDING=3 + BGCOLOR="#ffffff"> +<TR><TD BGCOLOR="#191970"><B><FONT COLOR="#ffffff"> Overview -</font></b></td></tr> -<tr><td bgcolor="#cccccc"> -<a href="index.html">Introduction</a> -</td></tr> -<tr><td bgcolor="#cccccc"> -<a href="history.html">History</a> -</td></tr> -<tr><td bgcolor="#cccccc"> -<a href="features.html">Features</a> -</td></tr> -<tr><td bgcolor="#cccccc"> -<a href="results.html">Results & Testimonials</a> -</td></tr> -<tr><td bgcolor="#cccccc"> -<a href="inuse.html">TMDA In Use</a> -</td></tr> -<tr><td bgcolor="#cccccc"> -<a href="press.html">Press Coverage</a><!-- -*- html -*- --> -</td></tr> -<tr><td bgcolor="#cccccc"> -<tr><td bgcolor="#191970"><b><font color="#ffffff"> +</FONT></B></TD></TR> +<TR><TD BGCOLOR="#cccccc"> +<A HREF="index.html">Introduction</A> +</TD></TR> +<TR><TD BGCOLOR="#cccccc"> +<A HREF="history.html">History</A> +</TD></TR> +<TR><TD BGCOLOR="#cccccc"> +<A HREF="features.html">Features</A> +</TD></TR> +<TR><TD BGCOLOR="#cccccc"> +<A HREF="results.html">Results & Testimonials</A> +</TD></TR> +<TR><TD BGCOLOR="#cccccc"> +<A HREF="inuse.html">TMDA In Use</A> +</TD></TR> +<TR><TD BGCOLOR="#cccccc"> +<A HREF="press.html">Press Coverage</A><!-- -*- html -*- --> +</TD></TR> +<TR><TD BGCOLOR="#cccccc"> +<TR><TD BGCOLOR="#191970"><B><FONT COLOR="#ffffff"> Install -</font></b></td></tr> -<tr><td bgcolor="#cccccc"> -<a href="requirements.html">Requirements</a> -</td></tr> -<tr><td bgcolor="#cccccc"> -<a href="download.html">Download</a> -</td></tr> -<tr><td bgcolor="#cccccc"> -<a href="install.html">Installation</a> -</td></tr> -<tr><td bgcolor="#cccccc"> -<a href="upgrade.html">Upgrading</a> -</td></tr> -<tr><td bgcolor="#cccccc"> -<tr><td bgcolor="#191970"><b><font color="#ffffff"> +</FONT></B></TD></TR> +<TR><TD BGCOLOR="#cccccc"> +<A HREF="requirements.html">Requirements</A> +</TD></TR> +<TR><TD BGCOLOR="#cccccc"> +<A HREF="download.html">Download</A> +</TD></TR> +<TR><TD BGCOLOR="#cccccc"> +<A HREF="install.html">Installation</A> +</TD></TR> +<TR><TD BGCOLOR="#cccccc"> +<A HREF="upgrade.html">Upgrading</A> +</TD></TR> +<TR><TD BGCOLOR="#cccccc"> +<TR><TD BGCOLOR="#191970"><B><FONT COLOR="#ffffff"> Configuration -</font></b></td></tr> -<tr><td bgcolor="#cccccc"> -<a href="config.html">Overview</a> -</td></tr> -<tr><td bgcolor="#cccccc"> -<a href="config-pre.html">Pre-Configuration</a> -</td></tr> -<tr><td bgcolor="#cccccc"> -<a href="config-server.html">Server Configuration</a> -</td></tr> -<tr><td bgcolor="#cccccc"> -<a href="config-client.html">Client Configuration</a> -</td></tr> -<tr><td bgcolor="#cccccc"> -<a href="config-vars.html">Configuration Variables</a> -</td></tr> -<tr><td bgcolor="#cccccc"> -<a href="config-filter.html">Filter Specification</a> -</td></tr> -<tr><td bgcolor="#cccccc"> +</FONT></B></TD></TR> +<TR><TD BGCOLOR="#cccccc"> +<A HREF="config.html">Overview</A> +</TD></TR> +<TR><TD BGCOLOR="#cccccc"> +<A HREF="config-pre.html">Pre-Configuration</A> +</TD></TR> +<TR><TD BGCOLOR="#cccccc"> +<A HREF="config-server.html">Server Configuration</A> +</TD></TR> +<TR><TD BGCOLOR="#cccccc"> +<A HREF="config-client.html">Client Configuration</A> +</TD></TR> +<TR><TD BGCOLOR="#cccccc"> +<A HREF="config-vars.html">Configuration Variables</A> +</TD></TR> +<TR><TD BGCOLOR="#cccccc"> +<A HREF="config-filter.html">Filter Specification</A> +</TD></TR> +<TR><TD BGCOLOR="#cccccc"> <b>Filter Sources</b> -</td></tr> -<tr><td bgcolor="#cccccc"> -<tr><td bgcolor="#191970"><b><font color="#ffffff"> +</TD></TR> +<TR><TD BGCOLOR="#cccccc"> +<TR><TD BGCOLOR="#191970"><B><FONT COLOR="#ffffff"> HOWTOs -</font></b></td></tr> -<tr><td bgcolor="#cccccc"> -<a href="howtos.html">Overview</a> -</td></tr> -<tr><td bgcolor="#cccccc"> -<a href="howto-template.html">Templates</a> -</td></tr> -<tr><td bgcolor="#cccccc"> -<a href="tmda-ofmipd.html">tmda-ofmipd</a> -</td></tr> -<tr><td bgcolor="#cccccc"> -<a href="tmda-vdomains.html">Virtual Domains</a> -</td></tr> -<tr><td bgcolor="#cccccc"> -<tr><td bgcolor="#191970"><b><font color="#ffffff"> +</FONT></B></TD></TR> +<TR><TD BGCOLOR="#cccccc"> +<A HREF="howtos.html">Overview</A> +</TD></TR> +<TR><TD BGCOLOR="#cccccc"> +<A HREF="howto-template.html">Templates</A> +</TD></TR> +<TR><TD BGCOLOR="#cccccc"> +<A HREF="tmda-ofmipd.html">tmda-ofmipd</A> +</TD></TR> +<TR><TD BGCOLOR="#cccccc"> +<A HREF="tmda-vdomains.html">Virtual Domains</A> +</TD></TR> +<TR><TD BGCOLOR="#cccccc"> +<TR><TD BGCOLOR="#191970"><B><FONT COLOR="#ffffff"> Support -</font></b></td></tr> -<tr><td bgcolor="#cccccc"> -<a href="trouble.html">Troubleshooting</a> -</td></tr> -<tr><td bgcolor="#cccccc"> +</FONT></B></TD></TR> +<TR><TD BGCOLOR="#cccccc"> +<A HREF="trouble.html">Troubleshooting</A> +</TD></TR> +<TR><TD BGCOLOR="#cccccc"> <a href="http://tmda.net/faq.cgi" TARGET="Resource Window">FAQ</a> -</td></tr> -<tr><td bgcolor="#cccccc"> -<a href="bugs.html">Bugs & Patches</a> -</td></tr> -<tr><td bgcolor="#cccccc"> +</TD></TR> +<TR><TD BGCOLOR="#cccccc"> +<A HREF="bugs.html">Bugs & Patches</A> +</TD></TR> +<TR><TD BGCOLOR="#cccccc"> <a href="http://tmda.net/lists/listinfo/" TARGET="Resource Window">Mailing Lists</a> -</td></tr> -<tr><td bgcolor="#cccccc"> +</TD></TR> +<TR><TD BGCOLOR="#cccccc"> <a href="http://mla.libertine.org/" TARGET="Resource Window">List Archive</a> -</td></tr> -<tr><td bgcolor="#cccccc"> -<a href="support-commercial.html">Commercial Support</a> -</td></tr> -<tr><td bgcolor="#cccccc"> -<a href="resources.html">External Resources</a> -</td></tr> -<tr><td bgcolor="#cccccc"> -<a href="mirrors.html">Mirrors</a> -</td></tr> -<tr><td bgcolor="#cccccc"> -<tr><td bgcolor="#191970"><b><font color="#ffffff"> +</TD></TR> +<TR><TD BGCOLOR="#cccccc"> +<A HREF="support-commercial.html">Commercial Support</A> +</TD></TR> +<TR><TD BGCOLOR="#cccccc"> +<A HREF="resources.html">External Resources</A> +</TD></TR> +<TR><TD BGCOLOR="#cccccc"> +<A HREF="mirrors.html">Mirrors</A> +</TD></TR> +<TR><TD BGCOLOR="#cccccc"> +<TR><TD BGCOLOR="#191970"><B><FONT COLOR="#ffffff"> Author -</font></b></td></tr> -<tr><td bgcolor="#cccccc"> -<a href="mailto:[email protected]">Jason R. Mastaler</a> -</td></tr> -<tr><td bgcolor="#cccccc"> +</FONT></B></TD></TR> +<TR><TD BGCOLOR="#cccccc"> +<A HREF="mailto:[email protected]">Jason R. Mastaler</A> +</TD></TR> +<TR><TD BGCOLOR="#cccccc"> -</td></tr> -<tr><td bgcolor="#cccccc"> +</TD></TR> +<TR><TD BGCOLOR="#cccccc"> © 2001-2003 -</td></tr> -</table><!-- end of sidebar table --> +</TD></TR> +</TABLE><!-- end of sidebar table --> -</td> -<td width="15"> </td><!--spacer--> +</TD> +<TD WIDTH=15> </TD><!--spacer--> <!-- end of sidebar cell --> <!-- start of body cell --> -<td valign="top" width="90%" class="body"><br> +<TD VALIGN=TOP WIDTH="90%"><BR> <h3>TMDA Filter Sources</h3> In the following list of sources, the expected match field is @@ -331,6 +326,159 @@ (i.e. <code>.+@my\.host\.name</code> would match all users in the <code>my.host.name</code> domain).<br> <br> + <dt> <code> from-sql [ <a href="#domains">-domains</a> ] -wildcards | <-addr_column=<column_name> [ -action_column=<column_name>]> <SQL_query><br> + to-sql [ <a href="#domains">-domains</a> ] -wildcards | <-addr_column=<column_name> [ -action_column=<column_name> ]> <SQL_query> </code> + <dd> The <code>from-sql</code> and <code>to-sql</code> sources match + against addresses stored in a SQL database. The <SQL_query> + is a SQL SELECT statement that should retrieve the appropriate + data. Just what that data needs to be will vary, depending on how + you choose to use the <code>from-sql</code> and + <code>to-sql</code> rules. Remember to enclose the + <SQL_query> in quotes, since it will contain spaces. Double + quotes are recommended, to avoid clashing with the single quotes + used by SQL. <br> + <br> + For a simple SELECT statement, you can put it directly in your + <code>from-sql</code> or <code>to-sql</code> rule. For more + complex statements, you can define a macro in the filter + file or you can define a variable in either /etc/tmdarc or + ~/.tmda/config and use the macro or variable name in the filter + rule. Defining a variable can be more flexible, especially for + multiple users at a single site who need the same SELECT statement + with minor modifications. For example, you could define this + SELECT statement in your /etc/tmdarc: <br> + <pre> + <code>SQL_WHITELIST = """ + SELECT wl.address + FROM whitelist AS wl, users AS u + WHERE u.uid = wl.uid + AND u.address = %(recipient)s + AND %(criteria)s + LIMIT 1"""</code></pre> + This assumes a 'users' table with a unique ID (<code>uid</code>) + and the user's email address (<code>address</code>). It joins the + 'users' and 'whitelist' tables based on the + <code>uid</code>. Details on '%(recipient)s' and '%(criteria)s' + can be found below. <br> + <br> + A rule in your incoming filter using this variable would look like + this: <br> + <pre> + <code>from-sql -addr_column=wl.address "${SQL_WHITELIST}" accept</code></pre> + Note that the variable is quoted because it is a string that + contains spaces. <br> + <br> + <i>Pre-requisite</i>: In either /etc/tmdarc or ~/.tmda/config you + must set the <a + href="config-vars.html#DB_CONNECTION">DB_CONNECTION</a> variable + so that TMDA can talk to the database. How this is done depends on + the database module used. Examples: <br> + <br> + <dl> + <dt>MySQLdb</dt> + <dd><pre><code>DB_CONNECTION = MySQLdb.connect(db='<dbname>', + host='<dbhost>', + user='<username>', + passwd='<password>')</code></pre></dd> + <dt>PyGreSQL</dt> + <dd><pre><code>DB_CONNECTION = pgdb.connect('<dbhost>:<dbname>:<username>:<password>')</code></pre> + </dd> + </dl> + <h4>Wildcard Searches</h4> + The <code>*-sql</code> rules can be used in two scenarios. If the + <code>-wildcards</code> argument is given, the <SQL_query> + is run and the resulting data set is read, in its entirety, from + the database. The first column should be the addresses to match + against. The second column is optional, but if it is present, it + should be the overriding action or NULL. The returned data is + searched in exactly the same way as text files containing + wildcards. See <a href="#email_address">Email Addresses</a> + below. <br> + <br> + Any columns beyond the second will be ignored. This can come in + handy if you need a column in the SELECT list for an ORDER BY + clause. Because the search code stops at the first match, unsorted + data could cause an incorrect match and the overriding action + might not be what you want. If you use wildcards in the address + column and you allow an overriding action, you should sort the + returned values using an ORDER BY clause. + <h4>Exact Match Searches</h4> + If an exact match of the sender or recipient is all you need, + e.g. you don't need wildcards, then you can use the *-sql rules to + have the database perform the search for you, returning only the + rows that exactly matched. You should specify the + <code>-addr_column</code> argument and provide the name of the + column that contains the addresses to search. You do <b>not</b> + need to include this column in the SELECT list. <br> + <br> + If you have an overriding action column, you should give its name + using the <code>-action_column</code> argument. If you use the + <code>-action_column</code> argument, you <b>must</b> include that + column in the SELECT list. <br> + <br> + <i>Caveat</i>: When the exact-match form of the + <code>from-sql</code> rule is used, TMDA can search for more than + one sender at once. If the SELECT statement returns more than one + row, TMDA will use the overriding action from the first row, since + it has no way of knowing which sender (the From:, the Reply-To: or + the envelope sender address) you care most about. Instead of + using overriding actions, consider using separate blacklists and + whitelists. <br> + <br> + Your SELECT statement can be as complex as you care to make it, + including joins, an ORDER BY clause, a LIMIT clause, etc. TMDA + must know where to place the search conditions ("<sender1> = + <addr_column> OR <sender2> = <addr_column>", + etc.). You should include the string "%(criteria)s" in your SELECT + statement at the appropriate location. TMDA will build the list of + conditions based on the addresses to be matched and will replace + "%(criteria)s" with that list. Here's an example to make this + clearer. <br> + <br> + Assume you have the following rule in your incoming filter.<br> + <pre> + <code>from-sql -addr_column=address <SQL_query> ok</code></pre> + An email arrives with a From: header of "[email protected]" and + a Reply-To: header of "[email protected]". TMDA will generate the + following criteria string: <br> + <pre> + <code>(address = '[email protected]' OR address = '[email protected]')</code></pre> + Your SELECT statement (<SQL_query>) might look something like this:<br> + <pre> + <code>SELECT address FROM addr_list WHERE %(criteria)s</code></pre> + The SQL code that TMDA actually sends to the database will look + like this (reformatted for easier readability):<br> + <pre> + <code>SELECT address + FROM addr_list + WHERE (address = '[email protected]' OR + address = '[email protected]')</code></pre> + If you store all of your users' whitelists in a single table (a + good schema design), you will need some way to restrict your + search to a single user's list; the user whose copy of TMDA is + querying the database. In order to facilitate that, the + <code>from-sql</code> and <code>to-sql</code> rules provide three + strings that can be used anywhere in your SELECT statement.<br> + <br> + <ul> + <li>username -- the value of the USERNAME variable from ~/.tmda/config + <li>hostname -- the value of the HOSTNAME variable from ~/.tmda/config + <li>recipient -- username@hostname + </ul> + <br> + You can place these in your SELECT statement by using + "%(username)s", "(%hostname)s" and/or "%(recipient)s", as needed. + TMDA will substitute the appropriate values into the SELECT at the + time of the search. Do not put quotes around the above + variables. The Python DB API takes care of that for you in a + manner appropriate for the database you are using. <br> + <br> + <code>-domains</code> tells the parser that the database column + containing addresses to match might also contain <a + href="#domains">domains</a>. This is the <code>-addr_column</code> + in the case of exact matches or the first column (the name is + irrelevant) in the case of <code>-wildcards</code>. <br> + <br> </dl> The following group of sources may be used only in incoming filter files. <br> <br> @@ -600,7 +748,7 @@ </dl> -</td><!-- end of body cell --> -</tr><!-- end of sidebar/body row --> -</table><!-- end of page table --> -</body></html> +</TD><!-- end of body cell --> +</TR><!-- end of sidebar/body row --> +</TABLE><!-- end of page table --> +</BODY></HTML> _______________________________________ tmda-cvs mailing list http://tmda.net/lists/listinfo/tmda-cvs