Skip site navigation (1)Skip section navigation (2)

FreeBSD Manual Pages

  
 
  

home | help
SQLITE_TABLE(5)		      File Formats Manual	       SQLITE_TABLE(5)

NAME
       sqlite_table - Postfix SQLite configuration

SYNOPSIS
       postmap -q "string" sqlite:$config_directory/filename

       postmap -q - sqlite:$config_directory/filename <inputfile

DESCRIPTION
       The  Postfix  mail system uses optional tables for address rewriting or
       mail routing. These tables are usually in dbm or	db format.

       Alternatively, lookup tables can	be specified as	SQLite databases.   In
       order  to use SQLite lookups, define an SQLite source as	a lookup table
       in main.cf, for example:
	   alias_maps =	sqlite:/etc/sqlite-aliases.cf

       The file	/usr/local/etc/postfix/sqlite-aliases.cf has the  same	format
       as  the	Postfix	main.cf	file, and can specify the parameters described
       below.

BACKWARDS COMPATIBILITY
       For compatibility with other Postfix lookup tables,  SQLite  parameters
       can also	be defined in main.cf.	In order to do that, specify as	SQLite
       source a	name that doesn't begin	with a slash or	a dot.	The SQLite pa-
       rameters	will then be accessible	as the name you've given the source in
       its definition, an underscore, and the name of the parameter.  For  ex-
       ample,  if  the	map is specified as "sqlite:sqlitename", the parameter
       "query" below would be defined in main.cf as "sqlitename_query".

       Normally, the SQL query is specified via	a single query parameter  (de-
       scribed in more detail below).  When this parameter is not specified in
       the map definition, Postfix reverts to an older interface, with the SQL
       query  constructed  from	the select_field, table, where_field and addi-
       tional_conditions parameters.  The  old	interface  will	 be  gradually
       phased out. To migrate to the new interface set:

	   query = SELECT [select_field]
	       FROM [table]
	       WHERE [where_field] = '%s'
		   [additional_conditions]

       Insert the value, not the name, of each legacy parameter. Note that the
       additional_conditions parameter is optional and if not empty, will  al-
       ways start with AND.

LIST MEMBERSHIP
       When using SQL to store lists such as $mynetworks, $mydestination, $re-
       lay_domains, $local_recipient_maps, etc., it is important to understand
       that the	table must store each list member as a separate	key. The table
       lookup verifies the *existence* of the key. See "Postfix	 lists	versus
       tables" in the DATABASE_README document for a discussion.

       Do  NOT create tables that return the full list of domains in $mydesti-
       nation or $relay_domains	etc., or IP addresses in $mynetworks.

       DO create tables	with each matching item	as a key and with an arbitrary
       value.  With  SQL databases it is not uncommon to return	the key	itself
       or a constant value.

SQLITE PARAMETERS
       dbpath The SQLite database file location. Example:
		  dbpath = customer_database

       query  The SQL query template used to search the	database, where	%s  is
	      a	substitute for the address Postfix is trying to	resolve, e.g.
		  query	= SELECT replacement FROM aliases WHERE	mailbox	= '%s'

	      This parameter supports the following '%'	expansions:

	      %%     This is replaced by a literal '%' character.

	      %s     This  is  replaced	by the input key.  SQL quoting is used
		     to	make sure that the input key does not  add  unexpected
		     metacharacters.

	      %u     When the input key	is an address of the form user@domain,
		     %u	is replaced by the SQL quoted local part  of  the  ad-
		     dress.   Otherwise,  %u  is replaced by the entire	search
		     string.  If the localpart is empty,  the  query  is  sup-
		     pressed and returns no results.

	      %d     When the input key	is an address of the form user@domain,
		     %d	is replaced by the SQL quoted domain part of  the  ad-
		     dress.  Otherwise,	the query is suppressed	and returns no
		     results.

	      %[SUD] The upper-case equivalents	of the above expansions	behave
		     in	 the  query  parameter identically to their lower-case
		     counter-parts.  With the result_format parameter (see be-
		     low),  they  expand  the input key	rather than the	result
		     value.

	      %[1-9] The patterns %1, %2, ... %9 are replaced  by  the	corre-
		     sponding  most  significant  component of the input key's
		     domain. If	the input key is  user@mail.example.com,  then
		     %1	is com,	%2 is example and %3 is	mail. If the input key
		     is	unqualified or does not	have enough domain  components
		     to	 satisfy all the specified patterns, the query is sup-
		     pressed and returns no results.

	      The domain parameter described below limits the  input  keys  to
	      addresses	in matching domains. When the domain parameter is non-
	      empty, SQL queries for unqualified  addresses  or	 addresses  in
	      non-matching domains are suppressed and return no	results.

	      This  parameter is available with	Postfix	2.2. In	prior releases
	      the SQL query  was  built	 from  the  separate  parameters:  se-
	      lect_field,  table,  where_field	and additional_conditions. The
	      mapping from the old parameters to the equivalent	query is:

		  SELECT [select_field]
		  FROM [table]
		  WHERE	[where_field] =	'%s'
			[additional_conditions]

	      The '%s' in the WHERE  clause  expands  to  the  escaped	search
	      string.	With  Postfix  2.2 these legacy	parameters are used if
	      the query	parameter is not specified.

	      NOTE: DO NOT put quotes around the query parameter.

       result_format (default: %s)
	      Format template applied to result	attributes. Most commonly used
	      to  append  (or prepend) text to the result. This	parameter sup-
	      ports the	following '%' expansions:

	      %%     This is replaced by a literal '%' character.

	      %s     This is replaced by the value of  the  result  attribute.
		     When result is empty it is	skipped.

	      %u     When the result attribute value is	an address of the form
		     user@domain, %u is	replaced by the	local part of the  ad-
		     dress.  When  the	result	has  an	 empty localpart it is
		     skipped.

	      %d     When a result attribute value is an address of  the  form
		     user@domain, %d is	replaced by the	domain part of the at-
		     tribute value. When  the  result  is  unqualified	it  is
		     skipped.

	      %[SUD1-9]
		     The  upper-case  and decimal digit	expansions interpolate
		     the parts of the input key	rather than the	result.	 Their
		     behavior  is  identical to	that described with query, and
		     in	fact because  the  input  key  is  known  in  advance,
		     queries  whose  key  does not contain all the information
		     specified in the result template are suppressed  and  re-
		     turn no results.

	      For example, using "result_format	= smtp:[%s]" allows one	to use
	      a	mailHost attribute as the basis	of a transport(5) table. After
	      applying	the result format, multiple values are concatenated as
	      comma separated strings. The expansion_limit and	parameter  ex-
	      plained below allows one to restrict the number of values	in the
	      result, which is especially useful for maps that must return  at
	      most one value.

	      The  default value %s specifies that each	result value should be
	      used as is.

	      This parameter is	available with Postfix 2.2 and later.

	      NOTE: DO NOT put quotes around the result	format!

       domain (default:	no domain list)
	      This is a	list of	domain names, paths to files, or dictionaries.
	      When  specified,	only  fully qualified search keys with a *non-
	      empty* localpart and a matching domain are eligible for  lookup:
	      'user'  lookups,	bare  domain lookups and "@domain" lookups are
	      not performed. This can significantly reduce the query  load  on
	      the SQLite server.
		  domain = postfix.org,	hash:$config_directory/searchdomains

	      It  is best not to use SQL to store the domains eligible for SQL
	      lookups.

	      This parameter is	available with Postfix 2.2 and later.

	      NOTE: DO NOT define this parameter for local(8) aliases, because
	      the input	keys are always	unqualified.

       expansion_limit (default: 0)
	      A	 limit	on  the	total number of	result elements	returned (as a
	      comma separated list) by a lookup	against	the map.  A setting of
	      zero  disables the limit.	Lookups	fail with a temporary error if
	      the limit	is exceeded.  Setting the  limit  to  1	 ensures  that
	      lookups do not return multiple values.

OBSOLETE QUERY INTERFACE
       This  section  describes	 an interface that is deprecated as of Postfix
       2.2. It is replaced by  the  more  general  query  interface  described
       above.	If  the	 query parameter is defined, the legacy	parameters de-
       scribed here ignored.  Please migrate  to  the  new  interface  as  the
       legacy interface	may be removed in a future release.

       The  following  parameters  can	be  used  to fill in a SELECT template
       statement of the	form:

	   SELECT [select_field]
	   FROM	[table]
	   WHERE [where_field] = '%s'
		 [additional_conditions]

       The specifier %s	is replaced by the search string, and is escaped so if
       it  contains single quotes or other odd characters, it will not cause a
       parse error, or worse, a	security problem.

       select_field
	      The SQL "select" parameter. Example:
		  select_field = forw_addr

       table  The SQL "select .. from" table name. Example:
		  table	= mxaliases

       where_field
	      The SQL "select .. where"	parameter. Example:
		  where_field =	alias

       additional_conditions
	      Additional conditions to the SQL query. Example:
		  additional_conditions	= AND status = 'paid'

SEE ALSO
       postmap(1), Postfix lookup table	maintenance
       postconf(5), configuration parameters
       ldap_table(5), LDAP lookup tables
       mysql_table(5), MySQL lookup tables
       pgsql_table(5), PostgreSQL lookup tables

README FILES
       Use "postconf readme_directory" or "postconf html_directory" to	locate
       this information.
       DATABASE_README,	Postfix	lookup table overview
       SQLITE_README, Postfix SQLITE howto

LICENSE
       The Secure Mailer license must be distributed with this software.

HISTORY
       SQLite support was introduced with Postfix version 2.8.

AUTHOR(S)
       Original	implementation by:
       Axel Steiner

							       SQLITE_TABLE(5)

NAME | SYNOPSIS | DESCRIPTION | BACKWARDS COMPATIBILITY | LIST MEMBERSHIP | SQLITE PARAMETERS | OBSOLETE QUERY INTERFACE | SEE ALSO | README FILES | LICENSE | HISTORY | AUTHOR(S)

Want to link to this manual page? Use this URL:
<https://www.freebsd.org/cgi/man.cgi?query=sqlite_table&sektion=5&manpath=FreeBSD+12.0-RELEASE+and+Ports>

home | help