File: spec_6.html

package info (click to toggle)
exim-html 3.20-1
links: PTS
area: main
in suites: etch, etch-m68k, sarge, woody
size: 2,868 kB
ctags: 4,188
sloc: makefile: 40; sh: 19
file content (942 lines) | stat: -rw-r--r-- 29,329 bytes
<HTML>
<HEAD>
<!-- This HTML file has been created by texi2html 1.52
     from spec on 25 November 2000 -->

<TITLE>Exim Specification - 6. File and database lookups</TITLE>
</HEAD>
<body bgcolor="#FFFFFF" text="#00005A" link="#FF6600" alink="#FF9933" vlink="#990000">
Go to the <A HREF="spec_1.html">first</A>, <A HREF="spec_5.html">previous</A>, <A HREF="spec_7.html">next</A>, <A HREF="spec_59.html">last</A> section, <A HREF="spec_toc.html">table of contents</A>.
<P><HR><P>


<H1><A NAME="SEC142" HREF="spec_toc.html#TOC142">6. File and database lookups</A></H1>
<P>
<A NAME="IDX402"></A>
<A NAME="IDX403"></A>
<A NAME="IDX404"></A>
Exim can be configured to look up data in files or databases in a number of
different circumstances (see 6.4 below). Two different styles of
data lookup are implemented:

</P>

<UL>

<LI>

The <EM>single-key</EM> style requires the specification of a file in which to look,
and a single key to search for. The lookup type determines how the file is
searched.

<LI>

The <EM>query</EM> style accepts a generalized database query, which may contain one
or more keys.
</UL>

<P>
The code for each lookup type is in a separate source file which is compiled
and included in the binary of Exim only if the corresponding compile-time
option is set. The default settings in <TT>`src/EDITME'</TT> are:

<PRE>
LOOKUP_DBM=yes
LOOKUP_LSEARCH=yes
</PRE>

<P>
which means that only linear searching and DBM lookups are included by default.

</P>

<P>



<H2><A NAME="SEC143" HREF="spec_toc.html#TOC143">6.1 Single-key lookup types</A></H2>

<P>
<A NAME="IDX405"></A>
The following single-key lookup types are implemented:

</P>

<UL>

<LI>

<A NAME="IDX406"></A>
<A NAME="IDX407"></A>
<EM>lsearch</EM>: The given file is a text file which is searched linearly for a line
beginning with the single key, terminated by a colon or white space or the end
of the line. White space between the key and the colon is permitted. The
remainder of the line, with leading and trailing white space removed, is the
data. This can be continued onto subsequent lines by starting them with any
amount of white space, but only a single space character is included in the
data at such a junction. If the data begins with a colon, the key must be
terminated by a colon, for example:

<PRE>
baduser:  :fail:
</PRE>

Empty lines and lines beginning with # are ignored, even if they occur in the
middle of an item. This is the traditional textual format of alias files.

<LI>

<A NAME="IDX408"></A>
<A NAME="IDX409"></A>
<EM>dbm</EM>: Calls to DBM library functions are used to extract data from the given
DBM file by looking up the record with the given key. The terminating binary
zero is included in the key that is passed to the DBM library.
There is a variant called <EM>dbmnz</EM> which does not include the terminating binary
zero in the key.

<LI>

<A NAME="IDX410"></A>
<A NAME="IDX411"></A>
<EM>nis</EM>: The given file is the name of a NIS map, and a NIS lookup is done with
the given key, excluding the terminating binary zero. There is a variant called
<EM>nis0</EM> which does include the terminating binary zero in the key. This is
reportedly needed for Sun-style alias files. Exim does not recognize NIS
aliases; the full map names must be used.

<LI>

<A NAME="IDX412"></A>
<A NAME="IDX413"></A>
<EM>cdb</EM>: The given file is searched as a Constant DataBase file, using the key
string without the terminating binary zero. The cdb format is designed for
indexed files that are read frequently and never updated, except by total
re-creation. As such, it is particulary suitable for large files containing
aliases or other indexed data referenced by an MTA. Information about cdb can
be found at

<PRE>
<A HREF="http://www.pobox.com/~djb/cdb.html">http://www.pobox.com/~djb/cdb.html</A>
</PRE>

The cdb distribution is not needed in order to build Exim with cdb support, as
the code for reading cdb files is included directly in Exim itself. However, no
means of building or testing cdb files is provided with Exim because these are
available within the cdb distribution.
</UL>



<H2><A NAME="SEC144" HREF="spec_toc.html#TOC144">6.2 An lsearch file is not an item list</A></H2>

<P>
There has been some confusion about the way <EM>lsearch</EM> lookups work, in
particular in domain and host lists. An item in one of these lists may be a
plain file name, or a file name preceded by a search type, and these behave
differently. For a plain file name, for example

<PRE>
local_domains = /etc/local-mail-domains
</PRE>

<P>
each line of the file is treated as if it appeared as an item in the list, and
negated items, wild cards, and regular expressions may be present. However, if
an item is specified as an <EM>lsearch</EM> lookup, for example

<PRE>
local_domains = lsearch;/etc/local-mail-domains
</PRE>

<P>
then negated items, wild cards, and regular expressions may not be used,
because <EM>lsearch</EM> is an indexed lookup method which, when given a key (the
domain in the above example), yields a data value that corresponds to that key.
The fact that the file is searched linearly does not make this kind of search
any different from the other single-key lookup types, and an <EM>lsearch</EM> file can
always be directly converted into one of the other types without change of
function. Thus, the keys in <EM>lsearch</EM>ed files are literal strings and are not
interpreted in any way.

</P>


<H2><A NAME="SEC145" HREF="spec_toc.html#TOC145">6.3 Query-style lookup types</A></H2>

<P>
The following query-style lookup types are implemented:

</P>

<UL>

<LI>

<A NAME="IDX414"></A>
<A NAME="IDX415"></A>
<EM>nisplus</EM>: This does a NIS+ lookup using a query that may contain any number of
keys, and which can specify the name of the field to be returned. See section
6.10 below.

<LI>

<A NAME="IDX416"></A>
<A NAME="IDX417"></A>
<font color=green>
<EM>ldap</EM>: This does an LDAP lookup using a query in the form of a URL, and
returns attributes from a single entry. There is a variant called <EM>ldapm</EM> which
permits values from multiple entries to be returned. A third variant called
<EM>ldapdn</EM> returns the Distinguished Name of a single entry instead of any
attribute values.
</font>
See section 6.11 below.

<LI>

<A NAME="IDX418"></A>
<A NAME="IDX419"></A>
<EM>mysql</EM>: The format of the query is an SQL statement that is passed to a MySQL
database. See section 6.12 below.

<LI>

<A NAME="IDX420"></A>
<A NAME="IDX421"></A>
<font color=green>
<EM>pgsql</EM>: The format of the query is an SQL statement that is passed to a
PostgreSQL database. See section 6.12 below.
</font>

<LI>

<A NAME="IDX422"></A>
<A NAME="IDX423"></A>
<font color=green>
<EM>dnsdb</EM>: This does a DNS search for a record whose domain name is the supplied
query. The resulting data is the contents of the record. See section
6.13 below.
</font>

<LI>

<EM>testdb</EM>: This is a lookup type which is for use in debugging Exim. It is
not likely to be useful in normal operation.
</UL>



<H2><A NAME="SEC146" HREF="spec_toc.html#TOC146">6.4 Use of data lookups</A></H2>

<P>
There are three different types of configuration item in which data lookups can
be specified:

</P>

<OL>

<LI>

Any string that is to be expanded may contain explicit lookup requests. String
expansions are described in chapter 9.

<LI>

Some drivers can be configured directly to look up data in files.

<LI>

Lists of domains and other items can contain lookup requests as a way of
avoiding excessively long linear lists.
<font color=green>
In this case, any data that is returned by the lookup is normally discarded;
whether the lookup succeeds or fails is all that counts. However, in the case
of the <EM>domains</EM> and <EM>local_parts</EM> options for directors and routers, the data
is preserved in variables for later use. See sections 7.12,
7.13, and 7.16 for descriptions of the different list
types.
</font>
</OL>

<P>
In a string expansion, all the parameters of the lookup are specified
explicitly, while for the other types there is always one implicit key
involved. For example, the <EM>local_domains</EM> option contains a list of local
domains; when it is being searched there is some domain name that is an
implicit key.

</P>
<P>
This is not a problem for single-key lookups; the relevant file name is
specified, and the key is implicit. For example, the list of local domains
could be given as

<PRE>
local_domains = dbm;/local/domain/list
</PRE>

<P>
However, for query-style lookups the entire query has to be specified, and to
do this, some means of including the implicit key is required.
<A NAME="IDX424"></A>
The special expansion variable $<EM>key</EM> is provided for this purpose. NIS+ could
be used to look up local domains by a setting such as

<PRE>
local_domains = nisplus;[domain=$key],domains.org_dir
</PRE>

<P>
In cases where drivers can be configured to do lookups, there are always three
alternative configuration options: <EM>file</EM> is used for single-key lookups,
using an implicit key, and <EM>query</EM> or <EM>queries</EM> is specified for query-style
lookups. In these cases the query is an expanded string, and the implicit
key that would be used for <EM>file</EM> is always available as one of the normal
expansion variables. The difference between <EM>query</EM> and <EM>queries</EM> is that in
the latter case the string is treated as a colon-separated list of queries
that are tried in order until one succeeds.

</P>



<H2><A NAME="SEC147" HREF="spec_toc.html#TOC147">6.5 Temporary errors in lookups</A></H2>

<P>
<A NAME="IDX425"></A>
<font color=green>
Lookup functions can return temporary error codes if the lookup cannot be
completed. (For example, a NIS or LDAP database might be unavailable.) For this
reason, it is not advisable to use a lookup that might do this for critical
options such as (to give an extreme example) <EM>local_domains</EM>.

</P>
<P>
When a lookup cannot be completed in a transport, director, or router, delivery
of the message is deferred, as for any other temporary error. In other
circumstances Exim may assume the lookup has failed, or may give up altogether.
These are some specific cases:

</P>

<UL>

<LI>

<EM>local_domains</EM>, <EM>hold_domains</EM>, or <EM>queue_remote_domains</EM> during delivery:
the address it is checking is deferred; other addresses may succeed if they
match something earlier in the list.

<LI>

<EM>domains</EM>, <EM>local_parts</EM>, <EM>senders</EM>, or <EM>condition</EM> on a router or director:
delivery is deferred.

<LI>

<EM>local_domains</EM>, <EM>percent_hack_domains</EM>, or <EM>relay_domains</EM> while receiving
SMTP: a 451 temporary error is given to the RCPT command.

<LI>

<EM>local_domains</EM> during verification: a temporary error given.

<LI>

<EM>mx_domains</EM> during lookuphost: delivery is deferred.

<LI>

<EM>mx_domains</EM> in the smtp transport (for hosts specified on the transport):
treat as not matching.

<LI>

<EM>queue_smtp_domains</EM> in the smtp transport: treat as not matching --
otherwise all SMTP deliveries would be held up.
</UL>

<P>
</font>

</P>


<H2><A NAME="SEC148" HREF="spec_toc.html#TOC148">6.6 Default values in single-key lookups</A></H2>

<P>
<A NAME="IDX426"></A>
<A NAME="IDX427"></A>
<A NAME="IDX428"></A>
<A NAME="IDX429"></A>
<A NAME="IDX430"></A>
In this context, a `default value' is a value specified by the administrator
that is to be used if a lookup fails.

</P>
<P>
If `*' is added to a single-key lookup type (for example, <EM>lsearch*</EM>) and
the initial lookup fails, the key `*' is looked up in the file to provide
a default value. See also the section on partial matching below.

</P>
<P>
<A NAME="IDX431"></A>
<A NAME="IDX432"></A>
<A NAME="IDX433"></A>
Alternatively, if `*@' is added to a single-key lookup type (for example
<EM>dbm*@</EM>) then, if the initial lookup fails and the key contains an @
character, a second lookup is done with everything before the last @ replaced
by *. This makes it possible to provide per-domain defaults in alias files
that include the domains in the keys. If the second lookup fails (or doesn't
take place because there is no @ in the key), `*' is looked up.

</P>



<H2><A NAME="SEC149" HREF="spec_toc.html#TOC149">6.7 Partial matching in single-key lookups</A></H2>

<P>
<A NAME="IDX434"></A>
<A NAME="IDX435"></A>
<A NAME="IDX436"></A>
The normal operation of a single-key lookup is to search the file for an exact
match with the given key. However, in a number of situations where domains are
being looked up, it is useful to be able to do partial matching. In this case,
information in the file that has a key starting with `*.' is matched by any
domain that ends with the components that follow the full stop. For example, if
a key in a DBM file is

<PRE>
*.dates.fict.book
</PRE>

<P>
then when partial matching is enabled this is matched by (amongst others)
<EM>2001.dates.fict.book</EM> and <EM>1984.dates.fict.book</EM>. It is also matched by
<EM>dates.fict.book</EM>, if that does not appear as a separate key in the file.

</P>
<P>
Partial matching is implemented by doing a series of separate lookups using
keys constructed by modifying the original subject key. This means that it can
be used with any of the single-key lookup types, provided that the special
partial-matching keys beginning with `*.' are included in the data file. Keys
in the file that do not begin with `*.' are matched only by unmodified
subject keys when partial matching is in use.

</P>

<P>
Partial matching is requested by adding the string `partial-' to the front of
the name of a single-key lookup type, for example, <EM>partial-dbm</EM>. When this is
done, the subject key is first looked up unmodified; if that fails, `*.'
is added at the start of the subject key, and it is looked up again. If that
fails, further lookups are tried with dot-separated components removed
from the start of the subject key, one-by-one, and `*.' added on the front of
what remains.

</P>
<P>
A minimum number of two non-* components are required. This can be adjusted
by including a number before the hyphen in the search type. For example,
<EM>partial3-lsearch</EM> specifies a minimum of three non-* components in the
modified keys. Omitting the number is equivalent to `partial2-'. If the subject
key is <EM>2250.dates.fict.book</EM> then the following keys are looked up when the
minimum number of non-* components is two:

<PRE>
2250.dates.fict.book
*.2250.dates.fict.book
*.dates.fict.book
*.fict.book
</PRE>

<P>
As soon as one key in the sequence is successfully looked up, the lookup
finishes. If `partial0-' is used, the original key gets shortened right down to
the null string, and the final lookup is for `*' on its own.

</P>
<P>
If the search type ends in `*' or `*@' (see section
6.6 above), the search for an ultimate default
that this implies happens after all partial lookups have failed. If `partial0-'
is specified, adding `*' to the search type has no effect, because the `*'
key is already included in the sequence of partial lookups.

</P>
<P>
The use of `*' in lookup partial matching differs from its use as a wildcard
in domain lists and the like. Partial matching works only in terms of
dot-separated components; a key such as <TT>`*fict.book'</TT>
in a database file is useless, because the asterisk in a partial matching
subject key is always followed by a dot.

</P>



<H2><A NAME="SEC150" HREF="spec_toc.html#TOC150">6.8 Lookup caching</A></H2>

<P>
<A NAME="IDX437"></A>
<A NAME="IDX438"></A>
Exim caches the most recent lookup result on a per-file basis for single-key
lookup types, and keeps the relevant files open. In some types of configuration
this can lead to many files being kept open for messages with many recipients.
To avoid hitting the operating system limit on the number of simultaneously
open files, Exim closes the least recently used file when it needs to open more
files than its own internal limit, which can be changed via the
<EM>lookup_open_max</EM> option. For query-style lookups, a single data cache per
lookup type is kept. The files are closed and the caches flushed at strategic
points during delivery -- for example, after all directing and routing is
complete.

</P>



<H2><A NAME="SEC151" HREF="spec_toc.html#TOC151">6.9 Quoting lookup data</A></H2>

<P>
<A NAME="IDX439"></A>
<A NAME="IDX440"></A>
When data from an incoming message is included in a query-style lookup, there
is the possibility of special characters in the data messing up the syntax of
the query. For example, a NIS+ query that contains

<PRE>
[name=$local_part]
</PRE>

<P>
will be broken if the local part happens to contain a closing square bracket.
For NIS+, data can be enclosed in double quotes like this:

<PRE>
[name="$local_part"]
</PRE>

<P>
but this still leaves the problem of a double quote in the data. The rule for
NIS+ is that double quotes must be doubled. Other lookup types have different
rules, and to cope with the differing requirements, an expansion operator
of the following form is provided:

<PRE>
${quote_&#60;<EM>lookup-type</EM>&#62;:&#60;<EM>string</EM>&#62;}
</PRE>

<P>
For example, the safest way to write the NIS+ query is

<PRE>
[name="${quote_nisplus:$local_part}"]
</PRE>

<P>
See chapter 9 for full coverage of string expansions. The quote
operator can be used for all lookup types, but has no effect for single-key
lookups, since no quoting is ever needed in their key strings.

</P>



<H2><A NAME="SEC152" HREF="spec_toc.html#TOC152">6.10 More about NIS+</A></H2>

<P>
<A NAME="IDX441"></A>
<A NAME="IDX442"></A>
NIS+ queries consist of a NIS+ <EM>indexed name</EM> followed by an optional colon
and field name. If this is given, the result of a successful query is the
contents of the named field; otherwise the result consists of a concatenation
of <EM>field-name=field-value</EM> pairs, separated by spaces. Empty values and
values containing spaces are quoted. For example, the query

<PRE>
[name=mg1456],passwd.org_dir
</PRE>

<P>
might return the string

<PRE>
name=mg1456 passwd="" uid=999 gid=999 gcos="Martin Guerre"
home=/home/mg1456 shell=/bin/bash shadow=""
</PRE>

<P>
(split over two lines here to fit on the page), whereas

<PRE>
[name=mg1456],passwd.org_dir:gcos
</PRE>

<P>
would just return

<PRE>
Martin Guerre
</PRE>

<P>
with no quotes. A NIS+ lookup fails if NIS+ returns more than one table entry
for the given indexed key.
The effect of the <EM>quote_nisplus</EM> expansion operator is to double any quote
characters within the text.

</P>



<H2><A NAME="SEC153" HREF="spec_toc.html#TOC153">6.11 More about LDAP</A></H2>

<P>
<A NAME="IDX443"></A>
<A NAME="IDX444"></A>
<font color=green>
The original LDAP implementation came from the University of Michigan; this has
become `Open LDAP', and there are now two different releases. Another
implementation comes from Netscape, and Solaris 7 and subsequent releases
contain inbuilt LDAP support. Unfortunately, though these are all compatible at
the lookup function level, their error handling is different. For this reason
it is necessary to set a compile-time variable when building Exim with LDAP, to
indicate which LDAP library is in use. One of the following should appear in
your <TT>`Local/Makefile'</TT>:

<PRE>
LDAP_LIB_TYPE=UMICHIGAN
LDAP_LIB_TYPE=OPENLDAP1
LDAP_LIB_TYPE=OPENLDAP2
LDAP_LIB_TYPE=NETSCAPE
LDAP_LIB_TYPE=SOLARIS
</PRE>

<P>
If LDAP_LIB_TYPE is not set, Exim assumes OpenLDAP 1, which has the same
interface as the University of Michigan version.

</P>
<P>
There are three LDAP lookup types, which behave slightly differently in the way
they handle the results of a query.

</P>

<UL>

<LI>

<EM>ldap</EM> requires the result to contain just one entry; if there are more, it
gives an error.

<LI>

<EM>ldapdn</EM> also requires the result to contain just one entry, but it is the
Distinguished Name that is returned rather than any attribute values.

<LI>

<EM>ldapm</EM> permits the result to contain more than one entry; the attributes from
all of them are returned.
</UL>

<P>
</font>

</P>
<P>
An LDAP query takes the form of a URL as defined in RFC 2255. For example, in
the configuration of an <EM>aliasfile</EM> director one might have these settings:

<PRE>
search_type = ldap
query = ldap:///cn=$local_part,o=University%20of%20Cambridge,\
        c=UK?mailbox?base?
</PRE>

<P>
Two levels of quoting are required in LDAP queries, the first for LDAP and the
second because the LDAP query is represented as a URL. The <EM>quote_ldap</EM>
expansion operator implements the following rules:

</P>

<UL>

<LI>

For LDAP quoting, the characters #,+"\&#60;&#62;;*() have to be preceded by a
backslash. (In fact, only some of these need to be quoted in Distinguished
Names, and others in LDAP filters, but it does no harm to have a single quoting
rule for all of them.)

<LI>

For URL quoting, all characters except alphanumerics and !$'()*+-._ are
replaced by %<EM>xx</EM> where <EM>xx</EM> is the hexadecimal character code. Note that
backslash has to be quoted in a URL, so characters that are escaped for LDAP
end up preceded by %5C in the final encoding.
</UL>

<P>
The example above does not specify an LDAP server. A server can
be specified in a query by starting it with

<PRE>
ldap://&#60;<EM>hostname</EM>&#62;:&#60;<EM>port</EM>&#62;/...
</PRE>

<P>
If the port (and preceding colon) are omitted, the standard LDAP port (389) is
used. When, however, no server is specified in a query, a list of default
servers is taken from the <EM>ldap_default_servers</EM> configuration option. This
supplies a colon-separated list of servers which are tried in turn until one
successfully handles a query, or there is a serious error. Successful handling
either returns the requested data, or indicates that it does not exist. Serious
errors are syntactical, or multiple values when only a single value is
expected. Errors which cause the next server to be tried are connection
failures, bind failures, and timeouts.

</P>
<P>
For each server name in the list, a port number can be given. The standard way
of specifing a host and port is to use a colon separator (RFC 1738). Because
<EM>ldap_default_servers</EM> is a colon-separated list, such colons have to be
doubled. For example

<PRE>
ldap_default_servers = ldap1.example.com::145:ldap2.example.com
</PRE>

<P>
If <EM>ldap_default_servers</EM> is unset, a URL with no server name is passed
to the LDAP library with no server name, and the library's default (normally
the local host) is used.

</P>
<P>
The LDP URL syntax provides no way of passing authentication and other control
information to the server. To make this possible, the URL in an LDAP query may
be preceded by any number of `&#60;<EM>name</EM>&#62;=&#60;<EM>value</EM>&#62;' settings, separated by
spaces. If a value contains spaces it must be enclosed in double quotes, and
when double quotes are used, backslash is interpreted in the usual way inside
them. The following names are recognized:

<PRE>
USER     set the DN, for authenticating the LDAP bind
PASS     set the password, likewise
SIZE     set the limit for the number of entries returned
TIME     set the maximum waiting time for a query
</PRE>

<P>
The values may be given in any order.
<font color=green>
The default is no time limit, and no limit on the number of entries returned.
</font>
Here is an example of an LDAP query in an Exim lookup which uses some of these
values. This is a single line, folded for ease of reading:

<PRE>
${lookup ldap
  {user="cn=manager,o=University of Cambridge,c=UK" pass=secret
  ldap:///o=University%20of%20Cambridge,c=UK?sn?sub?(cn=foo)}
  {$value}fail}
</PRE>

<P>
The encoding of spaces as %20 is a URL thing which should not be done for any
of the auxiliary data.
<font color=green>
Exim configuration settings that include lookups which contain password
information should be preceded by `hide' to prevent non-admin users from using
the -<EM>bP</EM> option to see their values.

</P>
<P>
<font color=green>
The <EM>ldapdn</EM> lookup type returns the Distinguished Name from a single entry as
a sequence of values, for example

<PRE>
cn=manager, o=University of Cambridge, c=UK
</PRE>

<P>
For <EM>ldap</EM> and <EM>ldapm</EM>, if a query finds only entries with no attributes, Exim
behaves as if the entry did not exist, and the lookup fails.
</font>

</P>
<P>
The <EM>ldap</EM> lookup type generates an error if more than one entry matches the
search filter, whereas <EM>ldapm</EM> permits this case, and inserts a newline in the
result between the data from different entries. It is possible for multiple
values to be returned for both <EM>ldap</EM> and <EM>ldapm</EM>, but in the former case you
know that whatever values are returned all came from a single entry in the
directory.

</P>
<P>
In the common case where you specify a single attribute in your LDAP query, the
result is not quoted, and if there are multiple values, they are separated by
commas. If you specify multiple attributes, they are returned as
space-separated strings, quoted if necessary, preceded by the attribute name.
For example,

<PRE>
ldap:///o=base?attr1,attr2?sub?(uid=fred)
</PRE>

<P>
might yield

<PRE>
attr1="value one" attr2=value2
</PRE>

<P>
If you do not specify any attributes in the search, the same format is used for
all attributes in the entry. For example,

<PRE>
ldap:///o=base??sub?(uid=fred)
</PRE>

<P>
might yield

<PRE>
objectClass=top attr1="value one" attr2=value2
</PRE>

<P>
The <EM>extract</EM> operator in string expansions can be used to pick out individual
fields from such data.
</font>

</P>



<H2><A NAME="SEC154" HREF="spec_toc.html#TOC154">6.12 More about MySQL and PostgreSQL</A></H2>

<P>
<A NAME="IDX445"></A>
<A NAME="IDX446"></A>
<A NAME="IDX447"></A>
<A NAME="IDX448"></A>
<font color=green>
If any MySQL or PostgreSQL lookups are used, the <EM>mysql_servers</EM>
or <EM>pgsql_servers</EM> option (as appropriate) must be set to a colon-separated
list of slash-separated host, database, user, password, tuples. Because
password data is sensitive, you should precede the setting with `hide', to
prevent non-admin users from obtaining the setting via the -<EM>bP</EM> option. For
example:

<PRE>
hide mysql_servers = localhost/users/root/secret:\
                     otherhost/users/root/othersecret
</PRE>

<P>
For each query, these parameter groups are tried in order until a connection
and a query succeeds. For MySQL, no database need be supplied -- if it is
absent, it must be given in the queries. A host may be specified as
&#60;<EM>name</EM>&#62;:&#60;<EM>port</EM>&#62; but because this is a colon-separated list, the colon has to
be doubled. Queries are SQL statements, so an example might be

<PRE>
${lookup mysql{select mailbox from users where id='ph10'}{$value}fail}
</PRE>

<P>
If the result of the query contains more than one field, the data for
each field in the row is returned, preceded by its name, so the result
of

<PRE>
${lookup pgsql{select home,name from users where id='ph10'}{$value}}
</PRE>

<P>
might be

<PRE>
home=/home/ph10 name="Philip Hazel"
</PRE>

<P>
Values containing spaces and empty values are double quoted, with embedded
quotes escaped by backslash.

</P>
<P>
If the result of the query contains just one field, the value is passed back
verbatim, without a field name, for example:

<PRE>
Philip Hazel
</PRE>

<P>
If the result of the query yields more than one row, it is all concatenated,
with a newline between the data for each row.

</P>
<P>
The <EM>quote_mysql</EM> and <EM>quote_pgsql</EM> expansion operators convert newline, tab,
carriage return, and backspace to \n, \t, \r, and \b respectively, and the
characters single-quote, double-quote, and backslash are escaped with
backslashes. The <EM>quote_pgsql</EM> expansion operator, in addition, escapes the
percent and underscore characters. This cannot be done for MySQL because these
escapes are not recognized in contexts where these characters are not special.
</font>

</P>

<P>
<font color=green>


<H2><A NAME="SEC155" HREF="spec_toc.html#TOC155">6.13 More about dnsdb</A></H2>

<P>
<A NAME="IDX449"></A>
<A NAME="IDX450"></A>
The <EM>dnsdb</EM> lookup type uses the DNS as its database. A query consists of a
record type and a domain name, separated by an equals sign. For example, an
expansion string could contain:

<PRE>
${lookup dnsdb{mx=a.b.example}{$value}fail}
</PRE>

<P>
The supported record types are A, CNAME, MX, NS, PTR, and TXT, and, when Exim
is compiled with IPv6 support, AAAA and A6. If no type is given, TXT is
assumed. When the type is PTR, the address should be given as normal; it gets
converted to the necessary magic internally. For example:

<PRE>
${lookup dnsdb{ptr=192.168.4.5}{$value}fail}
</PRE>

<P>
For MX records, both the preference value and the host name are returned,
separated by a space. If multiple records are found (or, for A6 lookups, if a
single record leads to multiple addresses), the data is returned as a
concatenation, separated by newlines. The order, of course, depends on the DNS
resolver.
</font>

</P>

<P><HR><P>
Go to the <A HREF="spec_1.html">first</A>, <A HREF="spec_5.html">previous</A>, <A HREF="spec_7.html">next</A>, <A HREF="spec_59.html">last</A> section, <A HREF="spec_toc.html">table of contents</A>.
</BODY>
</HTML>