Postfix: header/body_checks using MySQL (zt)

On 20 July 2000, Stephen Bolinger <pflist@pigglet.com> wrote:
> >> Is it possible to have all of my header/body checks in a mysql
> >> table? If it is, how is it called?
> >>
> >> header_checks = mysql:/etc/postfix/header_checks.mysql ???
> >>
> >> If I specify it like this, will it still interpret regular
> >> expression returned from the lookup? Or will it only match
> >> absolutely?
>
> >In order to subject header/body lines to regular expressions, specify
> >a map type that supports regular expressions.
> >
> > Wietse
>
> Does anyone know if mysql supports regular expressions? I have the
> line below working in an absolute matching fashion, but not as regexp:
> header_checks = mysql:/etc/postfix/header_checks.mysql
>
> This checks the database and searches the header for an exact literal
> match, so if I have "/^Subject: foo/" in the database it only matches
> if it finds "/^Subject: foo/" (rather than matching on "Subject:
> foo"). Anyone know how I can get the regular expression strings in
> the database to be interpreted as regexp?

    Write your SQL query so that it does a regexp match. Logically,
a map in Postfix is a sort of recipe that translates a given input
to a well-defined (that is, consistently reproducible) output. For
$header_checks, the input is a header line, and the output is one of
"OK", "REJECT", or "don't know". The translation process depends on
the _type_ of the map, but not on _where_ the result is used. A map of
type "hash" will lookup the input as a key in a hashed database, and
return the corresponding value, or "don't know" if not found. A map of
type "regexp" or "pcre" will match the input against each regexps in a
given file, and return the corresponding rhs if a match is found, or a
"don't know" otherwise. A "mysql" map builds a query from the SELECT
you supply and the input, and returns whatever the database returns in
response to the query. It doesn't matter whether the map is used in a
$header_checks, in a $canonical_maps, in a transport table, or somewhere
else; the result of the lookup only depends on the type of the map. The
magic of doing regexp matches if $header_checks is of type "regexp" or
"pcre" is contained in the type of the map; if you change the type,
you lose the magic. Now, in the case of "mysql" maps you can still
get it back because MySQL can do regexp matches in queries: just write
your SELECT with a regexp match clause. Postfix won't do it for you
automatically.

    That said, I'm not sure using a "mysql" map for $header_checks
is a good idea: a query will be issued for each header line, and the
resulting performance would be less than stellar. Why on Earth do you
want to do that anyway?

    Regards,

    Liviu Daia