path: root/README_FILES/DATABASE_README

PPoossttffiixx LLooookkuupp TTaabbllee OOvveerrvviieeww

-------------------------------------------------------------------------------

OOvveerrvviieeww

This document covers the following topics:

  * The Postfix lookup table model
  * Postfix lists versus tables
  * Preparing Postfix for LDAP or SQL lookups
  * Maintaining Postfix lookup table files
  * Updating Berkeley DB files safely
  * Postfix lookup table types

TThhee PPoossttffiixx llooookkuupp ttaabbllee mmooddeell

Postfix uses lookup tables to store and look up information for access control,
address rewriting and even for content filtering. All Postfix lookup tables are
specified as "type:table", where "type" is one of the database types described
under "Postfix lookup table types" at the end of this document, and where
"table" is the lookup table name. The Postfix documentation uses the terms
"database" and "lookup table" for the same thing.

Examples of lookup tables that appear often in the Postfix documentation:

    /etc/postfix/main.cf:
        alias_maps = hash:/etc/postfix/aliases            (local aliasing)
        header_checks = regexp:/etc/postfix/header_checks (content filtering)
        transport_maps = hash:/etc/postfix/transport      (routing table)
        virtual_alias_maps = hash:/etc/postfix/virtual    (virtual aliasing)

All Postfix lookup tables store information as (key, value) pairs. This
interface may seem simplistic at first, but it turns out to be very powerful.
The (key, value) query interface completely hides the complexities of LDAP or
SQL from Postfix. This is a good example of connecting complex systems with
simple interfaces.

Benefits of the Postfix (key, value) query interface:

  * You can implement Postfix lookup tables first with local Berkeley DB files
    and then switch to LDAP or MySQL without any impact on the Postfix
    configuration itself, as described under "Preparing Postfix for LDAP or SQL
    lookups" below.
  * You can use Berkeley DB files with fixed lookup strings for simple address
    rewriting operations and you can use regular expression tables for the more
    complicated work. In other words, you don't have to put everything into the
    same table.

PPoossttffiixx lliissttss vveerrssuuss ttaabblleess

Most Postfix lookup tables are used to look up information. Examples are
address rewriting (the lookup string is the old address, and the result is the
new address) or access control (the lookup string is the client, sender or
recipient, and the result is an action such as "reject").

With some tables, however, Postfix needs to know only if the lookup key exists.
Any non-empty lookup result value may be used here: the lookup result is not
used. Examples are the local_recipient_maps that determine what local
recipients Postfix accepts in mail from the network, the mydestination
parameter that specifies what domains Postfix delivers locally for, or the
mynetworks parameter that specifies the IP addresses of trusted clients or
client networks. Technically, these are lists, not tables. Despite the
difference, Postfix lists are described here because they use the same
underlying infrastructure as Postfix lookup tables.

PPrreeppaarriinngg PPoossttffiixx ffoorr LLDDAAPP oorr SSQQLL llooookkuuppss

LDAP and SQL are complex systems. Trying to set up both Postfix and LDAP or SQL
at the same time is definitely not a good idea. You can save yourself a lot of
time by implementing Postfix first with local files such as Berkeley DB. Local
files have few surprises, and are easy to debug with the postmap(1) command:

    % ppoossttmmaapp --qq iinnffoo@@eexxaammppllee..ccoomm hhaasshh:://eettcc//ppoossttffiixx//vviirrttuuaall

Once you have local files working properly you can follow the instructions in
ldap_table(5), mysql_table(5), pgsql_table(5) or sqlite_table(5) and replace
local file lookups with LDAP or SQL lookups. When you do this, you should use
the postmap(1) command again, to verify that database lookups still produce the
exact same results as local file lookup:

    % ppoossttmmaapp --qq iinnffoo@@eexxaammppllee..ccoomm llddaapp:://eettcc//ppoossttffiixx//vviirrttuuaall..ccff

Be sure to exercise all the partial address or parent domain queries that are
documented under "table search order" in the relevant manual page: access(5),
canonical(5), virtual(5), transport(5), or under the relevant configuration
parameter: mynetworks, relay_domains, parent_domain_matches_subdomains.

MMaaiinnttaaiinniinngg PPoossttffiixx llooookkuupp ttaabbllee ffiilleess

When you make changes to a database while the mail system is running, it would
be desirable if Postfix avoids reading information while that information is
being changed. It would also be nice if you can change a database without
having to execute "postfix reload", in order to force Postfix to use the new
information. Each time you do "postfix reload" Postfix loses a lot of
performance.

  * If you change a network database such as LDAP, NIS or SQL, there is no need
    to execute "postfix reload". The LDAP, NIS or SQL server takes care of
    read/write access conflicts and gives the new data to Postfix once that
    data is available.

  * If you change a regexp:, pcre:, cidr: or texthash: file then Postfix may
    not pick up the file changes immediately. This is because a Postfix process
    reads the entire file into memory once and never examines the file again.

      o If the file is used by a short-running process such as smtpd(8),
        cleanup(8) or local(8), there is no need to execute "postfix reload"
        after making a change.

      o If the file is being used by a long-running process such as trivial-
        rewrite(8) on a busy server it may be necessary to execute "postfix
        reload".

  * If you change a local file based database such as DBM or Berkeley DB, there
    is no need to execute "postfix reload". Postfix uses file locking to avoid
    read/write access conflicts, and whenever a Postfix daemon process notices
    that a file has changed it will terminate before handling the next client
    request, so that a new process can initialize with the new database.

UUppddaattiinngg BBeerrkkeelleeyy DDBB ffiilleess ssaaffeellyy

Postfix uses file locking to avoid access conflicts while updating Berkeley DB
or other local database files. This used to be safe, but as Berkeley DB has
evolved to use more aggressive caching, file locking may no longer be
sufficient.

Furthermore, file locking would not prevent problems when the update fails
because the disk is full or something else causes a database update to fail. In
particular, commands such as postmap(1) or postalias(1) overwrite existing
files. If the overwrite fails in the middle then you have no usable database,
and Postfix will stop working. This is not an issue with the CDB database type
available with Postfix 2.2 and later: CDB creates a new file, and renames the
file upon successful completion.

With Berkeley DB and other "one file" databases, it is possible to add some
extra robustness by using "mv" to REPLACE an existing database file instead of
overwriting it:

    # ppoossttmmaapp aacccceessss..iinn &&&& mmvv aacccceessss..iinn..ddbb aacccceessss..ddbb

This converts the input file "access.in" into the output file "access.in.db",
and replaces the file "access.db" only when the postmap(1) command was
successful. Of course typing such commands becomes boring quickly, and this is
why people use "make" instead, as shown below. User input is shown in bold
font.

    # ccaatt MMaakkeeffiillee
    all: aliases.db access.db virtual.db ...etcetera...

    # Note 1: commands are specified after a TAB character.
    # Note 2: use postalias(1) for local aliases, postmap(1) for the rest.
    aliases.db: aliases.in
            postalias aliases.in
            mv aliases.in.db aliases.db

    access.db: access.in
            postmap access.in
            mv access.in.db access.db

    virtual.db: virtual.in
            postmap virtual.in
            mv virtual.in.db virtual.db

    ...etcetera...
    # vvii aacccceessss..iinn
    ...editing session not shown...
    # mmaakkee
    postmap access.in
    mv access.in.db access.db
    #

The "make" command updates only the files that have changed. In case of error,
the "make" command will stop and will not invoke the "mv" command, so that
Postfix will keep using the existing database file as if nothing happened.

PPoossttffiixx llooookkuupp ttaabbllee ttyyppeess

To find out what database types your Postfix system supports, use the "ppoossttccoonnff
--mm" command. Here is a list of database types that are often supported:

    bbttrreeee
        A sorted, balanced tree structure. This is available only on systems
        with support for Berkeley DB databases. Database files are created with
        the postmap(1) or postalias(1) command. The lookup table name as used
        in "btree:table" is the database file name without the ".db" suffix.
    ccddbb
        A read-optimized structure with no support for incremental updates.
        Database files are created with the postmap(1) or postalias(1) command.
        The lookup table name as used in "cdb:table" is the database file name
        without the ".cdb" suffix. This feature is available with Postfix 2.2
        and later.
    cciiddrr
        A table that associates values with Classless Inter-Domain Routing
        (CIDR) patterns. The table format is described in cidr_table(5).
    ddbbmm
        An indexed file type based on hashing. This is available only on
        systems with support for DBM databases. Public database files are
        created with the postmap(1) or postalias(1) command, and private
        databases are maintained by Postfix daemons. The lookup table name as
        used in "dbm:table" is the database file name without the ".dir" or
        ".pag" suffix.
    eennvviirroonn
        The UNIX process environment array. The lookup key is the variable
        name. The lookup table name in "environ:table" is ignored.
    ffaaiill
        A table that reliably fails all requests. The lookup table name is used
        for logging only. This table exists to simplify Postfix error tests.
    hhaasshh
        An indexed file type based on hashing. This is available only on
        systems with support for Berkeley DB databases. Public database files
        are created with the postmap(1) or postalias(1) command, and private
        databases are maintained by Postfix daemons. The database name as used
        in "hash:table" is the database file name without the ".db" suffix.
    iinnlliinnee (read-only)
        A non-shared, in-memory lookup table. Example: "inline:{ key=value,
        { key = text with whitespace or comma }}". Key-value pairs are
        separated by whitespace or comma; with a key-value pair inside "{}",
        whitespace is ignored after the opening "{", around the "=" between key
        and value, and before the closing "}". Inline tables eliminate the need
        to create a database file for just a few fixed elements. See also the
        static: map type.
    iinntteerrnnaall
        A non-shared, in-memory hash table. Its contents are lost when a
        process terminates.
    llmmddbb
        OpenLDAP LMDB database. This is available only on systems with support
        for LMDB databases. Public database files are created with the postmap
        (1) or postalias(1) command, and private databases are maintained by
        Postfix daemons. The database name as used in "lmdb:table" is the
        database file name without the ".lmdb" suffix. See lmdb_table(5) for
        details.
    llddaapp (read-only)
        LDAP database client. Configuration details are given in the ldap_table
        (5).
    mmeemmccaacchhee
        Memcache database client. Configuration details are given in
        memcache_table(5).
    mmoonnggooddbb (read-only)
        MongoDB database client. Configuration details are given in
        mongodb_table(5), with examples in MONGODB_README.
    mmyyssqqll (read-only)
        MySQL database client. Configuration details are given in mysql_table
        (5).
    nneettiinnffoo (read-only)
        Netinfo database client.
    nniiss (read-only)
        NIS database client.
    nniisspplluuss (read-only)
        NIS+ database client. Configuration details are given in nisplus_table
        (5).
    ppccrree (read-only)
        A lookup table based on Perl Compatible Regular Expressions. The file
        format is described in pcre_table(5). The lookup table name as used in
        "pcre:table" is the name of the regular expression file.
    ppiippeemmaapp (read-only)
        A pipeline of lookup tables. Example: "pipemap:{type1:name1, ...,
        typen:namen}". Each "pipemap:" query is given to the first table. Each
        lookup result becomes the query for the next table in the pipeline, and
        the last table produces the final result. When any table lookup
        produces no result, the pipeline produces no result. The first and last
        characters of the "pipemap:" table name must be "{" and "}". Within
        these, individual maps are separated with comma or whitespace.
    ppggssqqll (read-only)
        PostgreSQL database client. Configuration details are given in
        pgsql_table(5).
    pprrooxxyy
        Postfix proxymap(8) client for shared access to Postfix databases. The
        lookup table name syntax is "proxy:type:table".
    rraannddmmaapp (read-only)
        An in-memory table that performs random selection. Example: "randmap:
        {result1. ..., resultn}". Each table query returns a random choice from
        the specified results. The first and last characters of the "randmap:
        " table name must be "{" and "}". Within these, individual maps are
        separated with comma or whitespace. To give a specific result more
        weight, specify it multiple times.
    rreeggeexxpp (read-only)
        A lookup table based on regular expressions. The file format is
        described in regexp_table(5). The lookup table name as used in "regexp:
        table" is the name of the regular expression file.
    ssddbbmm
        An indexed file type based on hashing. This is available only on
        systems with support for SDBM databases. Public database files are
        created with the postmap(1) or postalias(1) command, and private
        databases are maintained by Postfix daemons. The lookup table name as
        used in "sdbm:table" is the database file name without the ".dir" or
        ".pag" suffix.
    ssoocckkeettmmaapp (read-only)
        Sendmail-style socketmap client. The name of the table is either iinneett:
        host:port:name for a TCP/IP server, or uunniixx:pathname:name for a UNIX-
        domain server. See socketmap_table(5) for details.
    ssqqlliittee (read-only)
        SQLite database. Configuration details are given in sqlite_table(5).
    ssttaattiicc (read-only)
        A table that always returns its name as the lookup result. For example,
        "static:foobar" always returns the string "foobar" as lookup result.
        Specify "static:{ text with whitespace }" when the result contains
        whitespace; this form ignores whitespace after the opening "{" and
        before the closing "}". See also the inline: map type.
    ttccpp
        TCP/IP client. The protocol is described in tcp_table(5). The lookup
        table name is "tcp:host:port" where "host" specifies a symbolic
        hostname or a numeric IP address, and "port" specifies a symbolic
        service name or a numeric port number.
    tteexxtthhaasshh (read-only)
        A table that produces similar results as hash: files, except that you
        don't have to run the postmap(1) command before you can use the file,
        and that texthash: does not detect changes after the file is read. The
        lookup table name is "texthash:filename", where the file name is taken
        literally; no suffix is appended.
    uunniioonnmmaapp (read-only)
        A table that sends each query to multiple lookup tables and that
        concatenates all found results, separated by comma. The table name
        syntax is the same as for pipemap tables.
    uunniixx (read-only)
        A limited view of the UNIX authentication database. The following
        tables are implemented:
        uunniixx::ppaasssswwdd..bbyynnaammee
            The table is the UNIX password database. The key is a login name.
            The result is a password file entry in passwd(5) format.
        uunniixx::ggrroouupp..bbyynnaammee
            The table is the UNIX group database. The key is a group name. The
            result is a group file entry in group(5) format.

Other lookup table types may be available depending on how Postfix was built.
With some Postfix distributions the list is dynamically extensible as support
for lookup tables is dynamically linked into Postfix.