E. Postfix configuration 1. main.cf # A little shortcut to our query files query = sqlite:$config_directory/query # we use Mailman here, it maintains its own aliases alias_database=hash:/var/lib/mailman/aliases alias_maps = $query/maps-alias.query, $alias_database append_dot_mydomain = no # This is a Postfix 2.9 feature. This will have no effect in Postfix # 2.8 installs. enable_long_queue_ids = yes # We're doing the same thing with local and virtual delivery, delivering # to $HOME/Mail/ maildir. Dovecot likes consistency. home_mailbox = Mail/ html_directory = /usr/doc/postfix/html inet_protocols = ipv4 # uncomment this if you want to force all addresses into SQLite # local_recipient_maps = $query/maps-local.query mail_owner = postfix mailq_path = /usr/bin/mailq manpage_directory = /usr/man mydestination = $query/dom-local.query ### ### Note: myhostname is of crucial importance here. ### It is presumed that it is defined as a local domain (domClass 1) in ### the database. It also should be the system primary hostname, and the ### value of your reverse DNS PTR record. ### myhostname = myhostname.example.org mydomain = $myhostname # myorigin can be a cause of problems if misunderstood, as well. myorigin = $myhostname mynetworks = 127.0.0.0/8, 172.16.1.0/24, 192.168.0.0/20 newaliases_path = /usr/bin/newaliases # require explicit ".domain.tld" patterns when subdomain matching is # desired parent_domain_matches_subdomains = ### IMPORTANT ### # See the Postscreen README before enabling these options ### # The postscreen access file must exist, even if empty. postscreen_access_list = permit_mynetworks, cidr:/etc/postfix/postscreen_access.cidr postscreen_bare_newline_action = enforce postscreen_bare_newline_enable = yes postscreen_blacklist_action = drop postscreen_dnsbl_action = enforce # Likewise, this file must exist if defined. postscreen_dnsbl_reply_map = pcre:$config_directory/postscreen_dnsbl_reply_map.pcre postscreen_dnsbl_sites = zen.spamhaus.org*3 b.barracudacentral.org*2 bl.spameatingmonkey.net*2 dnsbl.njabl.org*2 dnsbl.ahbl.org*2 bl.spamcop.net dnsbl.sorbs.net spamtrap.trblspam.com swl.spamhaus.org*-4 list.dnswl.org=127.[0..255].[0..255].0*-2 list.dnswl.org=127.[0..255].[0..255].1*-4 list.dnswl.org=127.[0..255].[0..255].[2..255]*-6 postscreen_dnsbl_threshold = 3 postscreen_greet_action = enforce postscreen_non_smtp_command_enable = yes postscreen_pipelining_enable = yes # This is a Postfix 2.9 feature. .211 is my primary MX IP address, .214 # is the secondary. Both are bound on this host. We should never get # mail on the rest of the /29. postscreen_whitelist_interfaces = 192.0.2.211 !192.0.2.208/29 static:all queue_directory = /var/spool/postfix readme_directory = /usr/doc/postfix/README_FILES ### Recipient Delimiter ### # The Sendmail default is "+". The qmail default is "-". In general I # prefer to be positive rather than negative, but I have found many web # forms which stupidly insist that email addresses must not contain a # "+" character. Such is what happens when clueless people who don't # read standards are writing software. Sigh, I can't beat them, so I # joined them. recipient_delimiter = - ### IMPORTANT ### # If you change this, you must also change the maps-valias.query file # which has hardcoded the use of "-". It's also set in Dovecot's file, # dovecot/conf.d/15-lda.conf . # Unset relay_domains ("relay_domains =") and comment # relay_recipient_maps if not using relay domains. Although, if you # simply do not define any "domClass=2" in Domains, it is the same. relay_domains = $query/dom-relay.query relay_recipient_maps = $query/maps-relay.query sample_directory = /etc/postfix sendmail_path = /usr/sbin/sendmail setgid_group = postdrop # Look how simple this is! All the heavy lifting is done in one quick # sqlite query. smtpd_recipient_restrictions = permit_mynetworks, reject_unauth_destination, check_recipient_access $query/access-rcpt.query # check_recipient_access $query/access-rcptDomain.query # the above is needed if using a catchall virtual alias smtpd_reject_footer = See your own postmaster for help, or http://nospam4.nodns4.us/ for more information about the policies of this site. # We only enabled SASL on port 587: MUAs and postscreen do not mix. # This setting is called from master.cf for 587. submission_rcpt_restrictions = permit_sasl_authenticated, permit_mynetworks, reject smtpd_sasl_path = private/auth smtpd_sasl_type = dovecot # Note: TLS configuration is not covered in this document; see here for # details: http://www.postfix.org/TLS_README.html smtpd_tls_CApath = /etc/ssl/certs smtpd_tls_cert_file = /etc/ssl/certs/myhostname.example.org.crt smtpd_tls_key_file = /etc/ssl/private/myhostname.example.org.key smtpd_tls_loglevel = 1 smtpd_tls_received_header = yes smtpd_tls_security_level = may smtpd_tls_session_cache_database = btree:$data_directory/smtpd_tls_session_cache # If you are not going to use this feature, comment it transport_maps = $query/maps-transport.query unknown_address_reject_code = 550 virtual_alias_domains = $query/dom-valias.query virtual_alias_maps = $query/maps-valias.query virtual_gid_maps = $query/maps-vgid.query virtual_mailbox_base = /home virtual_mailbox_domains = $query/dom-vmbox.query virtual_mailbox_maps = $query/maps-vmbox.query virtual_minimum_uid = 800 virtual_uid_maps = $query/maps-vuid.query # restriction classes below # # NAMING SCHEME: # - ^RCdd Composite restriction choices for domain owners where # 'dd' is a numeric ranking, low=less/high=more spam. # - ^z-* Component elements used to make up the RCdd classes # This keeps relevant parts together in the alphabetic-sorted output of # postconf(1), and the component elements collected at the end. # Note: it would have been nice if we could have kept this list and the # class definitions themselves in the SQLite database. I suspect there # are structural reasons why this would not work. I did originally try # keeping the classes and restrictions in SQLite, but since it had to be # dumped to main.cf anyway, it made the data larger and more confusing # for no benefit. # Style note: because I had these in SQLite, I used commas in place of # spaces. In postconf(5) syntax there is no distinction between forms of # whitespace: spaces, tabs, commas, and newline followed by whitespace. smtpd_restriction_classes = z-swl,z-dnswlAll,z-dnswlLo,z-dnswlMed, z-dnswlHi,z-dnswlBLesp,z-zen,z-brbl,z-sem,z-ahbl,z-njabl, z-spamcop,z-sorbs,z-trbl,z-common,z-helo,z-noSender,z-unlist, z-semBscat,z-bscatOrg,z-bscat,z-dblC,z-dblH,z-dblS, z-noPtr,z-noFCrDns,z-unkHelo,z-warnDefer, RC00 RC01 RC10 RC20 RC29 RC30 RC40 RC50 # RC00-reject, RC01-nuts,RC10-aggressive,RC20-moderate, # RC29-whn,RC30-conservative,RC40-weak,RC50-open ### Composite classes # # This is here to make our query easy. We could just return "reject" # in the query, but the tables store an integer for this purpose. RC00 = reject # The next five, in descending order of aggressiveness, are what are # presented to domain owners as their choices (although use of "weak" # and "open" are highly discouraged # # RC01-nuts: don't use this, it is nuts. You will block good mail. RC01 = z-common,z-noFCrDns,z-unkHelo,z-dblC,z-dblH,z-dblS,z-brbl, z-sem,z-ahbl,z-njabl,z-trbl,z-sorbs,z-spamcop,z-bscat, z-dnswlBLesp # RC10-aggressive: when strong anti-spam policies are more important # than occasionally missing a legitimate email RC10 = z-common,z-noFCrDns,warn_if_reject,z-unkHelo, z-dblC,z-dblH,z-dblS,z-swl,z-dnswlHi,z-brbl,z-sem, z-dnswlMed,z-ahbl,z-njabl, z-dnswlAll,z-trbl,z-sorbs,z-spamcop,z-bscat # RC20-moderate RC20 = z-common,z-noPtr,z-dblS,z-dblH,z-dblC, z-swl,z-dnswlLo,z-brbl,z-sem,z-ahbl,z-njabl,z-bscat # RC30-conservative: when more spam is considered better than the # occasional rejection of legitimate email RC30 = z-common,warn_if_reject,z-noPtr, z-dblC,z-dblH,z-dblS,z-swl,z-dnswlAll,z-brbl,z-sem,z-ahbl, z-njabl,warn_if_reject,z-bscat # RC40-weak: does very little against spam RC40 = z-common # uncomment the following to get logs of what could have been # rejected: # warn_if_reject,z-noPtr,warn_if_reject,z-dblC, # warn_if_reject,z-dblH,warn_if_reject,z-dblS, # warn_if_reject,z-brbl,warn_if_reject,z-sem, # warn_if_reject,z-ahbl,warn_if_reject,z-njabl, # warn_if_reject,z-bscat # RC50-open: does nothing against spam. But do note, with postscreen in # use, this host cannot be truly "open". RC50 = permit # RC29-whn: a custom class designed for a particular domain RC29 = z-common,z-noPtr,z-dblS,z-dblH,z-dblC,z-swl,z-dnswlAll, z-brbl,z-sem,z-ahbl,z-njabl,z-bscat ### Component classes ### Most of these will only do one thing, although some will call other ### components. z-ahbl = reject_rbl_client,dnsbl.ahbl.org z-brbl = reject_rbl_client,b.barracudacentral.org z-bscat = check_sender_access,$query/access-bscat.query z-bscatOrg = reject_rbl_client,ips.backscatterers.org z-common = z-helo,z-unlist,z-noSender,z-zen z-dblC = reject_rhsbl_client,dbl.spamhaus.org z-dblH = reject_rhsbl_helo,dbl.spamhaus.org z-dblS = reject_rhsbl_sender,dbl.spamhaus.org z-dnswlAll = permit_dnswl_client,list.dnswl.org ### ! using dnswl's code for ESP as a blacklist ! z-dnswlBLesp = reject_dnsbl_client,list.dnswl.org=127.[0..255].15.0 ### ! Kids, don't try that at home ! z-dnswlHi = permit_dnswl_client,list.dnswl.org=127.[0..255].[0..255].[3..255] z-dnswlLo = permit_dnswl_client,list.dnswl.org=127.[0..255].[0..255].[1..255] z-dnswlMed = permit_dnswl_client,list.dnswl.org=127.[0..255].[0..255].[2..255] z-helo = check_helo_access,pcre:$config_directory/helo_checks, z-njabl = reject_rbl_client,dnsbl.njabl.org z-noFCrDns = reject_unknown_client_hostname z-noPtr = reject_unknown_reverse_client_hostname z-noSender = reject_unknown_sender_domain, z-sem = reject_rbl_client,bl.spameatingmonkey.net z-semBscat = reject_rbl_client,backscatter.spameatingmonkey.net z-sorbs = reject_rbl_client,dnsbl.sorbs.net z-spamcop = reject_rbl_client,bl.spamcop.net z-swl = permit_dnswl_client,swl.spamhaus.org z-trbl = reject_rbl_client,spamtrap.trblspam.com z-unkHelo = reject_unknown_helo_hostname z-unlist = reject_unlisted_recipient z-warnDefer = check_client_access,static:warn,defer_if_reject z-zen = reject_rbl_client,zen.spamhaus.org ### end of main.cf 2. master.cf We're not adding anything here, but if you use any Transport.transport value, it must exist in master.cf. Our sample data has one as "custom" and another as "throttled", so those would have to be added to master.cf. The sample main.cf shows postscreen in use. By default it is not, and it would need to be activated by editing master.cf. This document will not cover that, but rather just refer the reader to the Postfix Postscreen Howto: http://www.postfix.org/POSTSCREEN_README.html The sample main.cf also shows that SASL is not enabled. If you want to use SMTP AUTH, you should enable the submission port in your master.cf. Here is mine: submission inet n - n - - smtpd -o syslog_name=postfix/submission -o smtpd_tls_security_level=encrypt -o smtpd_sasl_auth_enable=yes -o smtpd_recipient_restrictions=$submission_rcpt_restrictions -o milter_macro_daemon_name=ORIGINATING 3. Other files in /etc/postfix The example includes a helo_checks file and two postscreen_* files which you might find useful. Since they are not relevant to the goal of this document, they are not included here. See those files and the comments they contain if interested. 4. Query files (/etc/postfix/query) Naming scheme: the first token is one of "access", "dom", or "maps". This is the type of lookup: smtpd access(5) maps, domain lists, or address maps. Thus all access-*.query files are grouped together in a file listing, as are dom-*.query and maps-*.query files. a. Syntax summary There are three possible arguments within each file, given in standard postconf(5) "key = value" style. This section merely covers what has been used in the example implementation. For complete documentation, refer to SQLITE_README and the sqlite_table(5) manual. (Links are in the Bibliography at the end.) First, "dbpath", the pathname of the sqlite database. The file in this example implementation is /etc/postfix/private/mail.sqlite, where the /etc/postfix/private directory is root:postfix mode 750, to secure the sensitive data therein. Next, "query", which is the sqlite-validated SQL query to return the desired values. Within these queries, we use one or more of these three variables: %s Expands to the entire query string, the lookup key %u In an email address lookup of user@domain, expands to "user", the unqualified localpart, lefthand of the "@" %d In an email address lookup of user@domain, expands to "domain", the righthand side of the "@" If a query contains %u or %d, but the lookup key is NOT of the form, "user@domain", it is suppressed. Finally, "result_format", which has variables much like "query" does. We're only using it in two places, and we only use %s: %s Expands to the entire result string, the lookup value If omitted (as in most of these query files) result_format defaults to "%s". (In other words, the result is presented to Postfix exactly as it came from the query.) b. Access lookups This example only has two access lookups, but others could be added as needed/desired. The recipient access lookup is done for all arriving mail. The backscatter lookup is only invoked for moderate (RC20) and above (which is to say, numerically lower); conservative (RC30) and below do (numerically higher) do not use it. *** query/access-bscat.query : dbpath = /etc/postfix/private/mail.sqlite query = SELECT ALL target FROM BScat WHERE lower(sender) IS '%s' ORDER BY priority # This query is self-contained in the BScat table. If the sender address # matches one of the three suspicious characters, a restriction class is # returned which will cause the client IP address to be looked up # against backscatterer DNSBLs. *** query/access-rcpt.query : dbpath = /etc/postfix/private/mail.sqlite query = SELECT CASE WHEN A1.active!=0 AND A1.rclass IS NOT NULL THEN substr((100 + A1.rclass), 2, 2) ELSE substr((100 + D1.rclass), 2, 2) END FROM "Address" AS A1 JOIN "Domain" AS D1 ON A1.domain=D1.id WHERE A1.localpart IS '%u' AND D1.name IS '%d' # Postfix smtpd(8) access(5) lookups for email addresses # Case 1: smtpd searches for user-exten@domain # Case 2: smtpd searches for user@domain # Case 3: smtpd searches for domain (%u is suppressed) # This query will match without ever going to Case 3. If there is an # extension, it won't match unless user-exten@domain is in the Address # table. (In most cases, it would not be.) # # We take the first non-null value of Address.rclass or Domain.rclass. # It's padded with a leading zero if under 10 (we only have values from # 00-50 as restriction classes RCxx in main.cf.) result_format = RC%s # BUG: This does not work for a catchall address. See # access-rcptDomain.query for the solution, or better yet ... # do not use catchalls! *** query/access-rcptDomain.query : dbpath = /etc/postfix/private/mail.sqlite query = SELECT substr((100 + rclass), 2, 2) FROM Domain WHERE active!=0 AND name IS '%s' result_format = RC%s # BUG: access-rcpt.query does not work for a catchall address. This # solves that, or for a better solution: do not use catchalls! # # This query would need to be enabled at the end of your # smtpd_recipient_restrictions: # # smtpd_recipient_restrictions = permit_mynetworks, # reject_unauth_destination, # check_recipient_access $query/access-rcpt.query, # check_recipient_access $query/access-rcptDomain.query c. Domain lookups Each of these are very similar. They only differ in the Domain.class condition in the "WHERE" clause. All of these lookups are fully contained within the Domain table. They simply look up the name and check the class and active values. *** query/dom-local.query : dbpath = /etc/postfix/private/mail.sqlite query = SELECT Domain.name FROM Domain WHERE Domain.class=1 AND Domain.active!=0 AND Domain.name IS '%s' *** query/dom-relay.query : dbpath = /etc/postfix/private/mail.sqlite query = SELECT Domain.name FROM Domain WHERE Domain.class=2 AND Domain.active!=0 AND Domain.name IS '%s' *** query/dom-valias.query : dbpath = /etc/postfix/private/mail.sqlite query = SELECT Domain.name FROM Domain WHERE Domain.class=3 AND Domain.active!=0 AND Domain.name IS '%s' *** query/dom-vmbox.query : dbpath = /etc/postfix/private/mail.sqlite query = SELECT Domain.name FROM Domain WHERE Domain.class>800 AND Domain.active!=0 AND Domain.name IS '%s' d. Maps (address) lookups These are not so similar as the domain lookups. All of these files have these comments contained. I have arranged them in a logical order of both functionality and complexity. You should understand the earlier queries before trying to understand the later ones! Unfortunately some of the comments will be repetitive, but at least (I believe) everything is covered in detail, as well as having explanations available in each file where it is needed. *** query/maps-local.query : dbpath = /etc/postfix/private/mail.sqlite query = SELECT localpart FROM Address WHERE localpart is '%s' AND domain=0 AND active!=0 # This is an optional query for local_recipient_maps. I chose not to # deploy it for usability reasons, but this file is here as # demonstration that the one SQLite database can indeed manage all # Postfix lookups. # This will work as-is with the sample data if the local_recipient_maps # line in main.cf is uncommented. To continue to use it, ensure that any # new local user is added to the Address table, and closed accounts are # deleted therefrom. # Note that we're using the standard of (-1 * $UID) for the Address.id # for system accounts in the sample data. That is for hoped future # integration with nss-sqlite or pam-sqlite. It's not important at this # point. # Note also the use of the special value of Address.domain=0, as in # alias_maps. Since we do not have to look up a Domain.name for local # recipients, there is no need to JOIN the Domain table. *** query/maps-relay.query : dbpath = /etc/postfix/private/mail.sqlite query = SELECT A1.id FROM Address AS A1 JOIN Domain AS D1 ON A1.domain=D1.id WHERE A1.localpart || '@' || D1.name IS '%s' AND A1.active!=0 # This is the relay_recipient_maps query, and it illustrates the basis # for all the other maps queries. The Address table contains a string # localpart and an integer pointer to the Domain table for each listed # address. In the FROM clause we JOIN the Domain table to get the # Domain.name string. # As in all the others, we test for Address.active to be nonzero, which # is to say the address is enabled for mail receipt. # The sample data does not show it, but it would be possible to # implement a catchall in a relay domain with an Address table entry # with an empty string as localpart. (Don't do it. Friends don't let # friends use catchall addresses!) *** query/maps-vgid.query : dbpath = /etc/postfix/private/mail.sqlite query = SELECT coalesce(VM.gid, 800) FROM VMailbox AS VM JOIN Address AS A1 ON VM.id=A1.id JOIN Domain AS D1 ON A1.domain=D1.id WHERE A1.localpart || '@' || D1.name IS '%s' AND VM.active!=0 # Most howtos for Postfix virtual mailboxes, including the official # VIRTUAL_README document, use static maps for UID and GID: # virtual_uid_maps = static:5000 # virtual_gid_maps = static:5000 # This is easiest for usability, but exposes a potential security # weakness in that an exploit of the imapd or the virtual user/group # could lead to compromise or loss of all mailboxes. # Here for the GID query, we use a default shared GID which owns and has # g+rwx privilege on /home/vmail, under which is where we deliver mail # to %d/%u/Mail/ maildirs. This means that virtual(8) can create new # mailboxes when mail arrives. # This query selects the first non-null value of VMailbox.gid or 800, # which is that default shared GID. As in other maps, Address is joined # to get the localpart, and Domain is joined to get the Domain.name. # VMailbox.active is checked to ensure that the address has not been # disabled. *** query/maps-vuid.query (partly repetitive comments from vgid above): dbpath = /etc/postfix/private/mail.sqlite query = SELECT coalesce(VM.uid, D1.class) FROM VMailbox AS VM JOIN Address AS A1 ON VM.id=A1.id JOIN Domain AS D1 ON A1.domain=D1.id WHERE A1.localpart || '@' || D1.name IS '%s' AND VM.active!=0 # Most howtos for Postfix virtual mailboxes, including the official # VIRTUAL_README document, use static maps for UID and GID: # virtual_uid_maps = static:5000 # virtual_gid_maps = static:5000 # This is easiest for usability, but exposes a potential security # weakness in that an exploit of the imapd or the virtual user/group # could lead to compromise or loss of all mailboxes. # # Here for the UID query, we use a default UID shared by all domains # under control of the same Domain.owner. This UID owns and has u+rwx # privilege on /home/vmail/%d/, under which we deliver mail to %u/Mail/ # maildirs. This means that virtual(8) can create new mailboxes when # mail arrives. # This query selects the first non-null value of VMailbox.gid or # Domain.class which is that default UID. As in other maps, Address is # joined to get the localpart, and Domain is joined to get the # Domain.name. VMailbox.active is checked to ensure that the address has # not been disabled. *** query/maps-vmbox.query : dbpath = /etc/postfix/private/mail.sqlite query = SELECT coalesce(VM.home, 'vmail/%d/%u') FROM VMailbox AS VM JOIN Address AS A1 ON VM.id=A1.id JOIN Domain AS D1 ON A1.domain=D1.id WHERE A1.localpart || '@' || D1.name IS '%s' AND VM.active!=0 result_format = %s/Mail/ # This is for the virtual_mailbox_maps lookup, which returns the path # (relative to virtual_mailbox_base) to a virtual mailbox. It takes # either the VMailbox.home value or a default constructed with "vmail" # as the top directory component followed by the input domain name and # the localpart, each lowercased. # The VMailbox.id refers to an Address record, so we JOIN Address. We # get the localpart string value there, and JOIN Domain to get the # Domain.name string value. # The result is actually the virtual user's $HOME directory, so we # append "/Mail/" to that for the maildir's location. # Note, with virtual_mailbox_base of /home, any values of VMailbox.home # must be under /home, and the virtual_{u,g}id_maps user or group must # have the necessary rwx permission to enter, read & write the maildir. # The appendage of "/Mail/" is an inconvenience when delivering virtual # mail to a local user; it means that the local user must either receive # the mail in his/her regular inbox, or else make a symlink to an IMAP # folder name under ~/Mail/. *** query/maps-alias.query : dbpath = /etc/postfix/private/mail.sqlite # Shorthand in this query: # AA = Alias Address (the address we are looking up) # TA = Target Address # TD = Target Domain query = SELECT ALL CASE WHEN Alias.target=0 THEN Alias.extension ELSE TA.localpart || (CASE WHEN Alias.extension IS NOT NULL THEN '-' || Alias.extension ELSE '' END) || (CASE WHEN TD.id=0 THEN '' ELSE '@' || TD.name END) END FROM "Alias" JOIN "Address" AS TA ON Alias.target=TA.id JOIN "Domain" AS TD ON TA.domain=TD.id JOIN "Address" AS AA ON Alias.address=AA.id WHERE AA.localpart IS '%s' AND AA.domain=0 AND Alias.active!=0; # This is the Postfix alias_maps lookup. The FROM clause is a bit # simpler than in the virtual_alias_maps lookup, because we have no need # to join Domain and look up the alias address' Domain.name (it's NULL). # But we do have to JOIN Address for each of the alias address and the # target address, and for the latter, we do have to JOIN Domain to get # the Domain.name string. # The SELECT clause is SELECT ALL, which in SQLite is the default # behavior, but I used ALL here simply to show that this query is in many # many cases likely to return multiple valid rows. # The outer CASE expression in SELECT is to differentiate the features # specific to aliases(5) as opposed to virtual(5) aliases, and this is # done with the special value of Alias.target=0. In that case we are not # getting an address[-extension][@domain] as the lookup result; we are # getting a "|command" or ":include:" or "/file/to/append", that being # determined by that row's corresponding value of Alias.extension. # The ELSE expression in that outer CASE covers the more typical use of # returning an address as target, and that address is constructed from # the TA.localpart (with -Alias.extension appended if needed) and either # an empty string if TD.id=0 (to return an unqualified localpart) or an # "@" sign plus the TD.name string value. # (In general I recommend against this and prefer to have an explicit # @domain with every address. But sometimes it is useful. Offer void # where taxed or prohibited, or if the reader fails to understand # append_at_myorigin in the postconf(5) manual.) # The WHERE clause is fairly simple. We know for alias_maps that we are # looking at an unqualified localpart of an address, and we have used # Address.domain=0 to designate these in the database. And as with all # maps queries, we test that the Address.active value is nonzero; that # it is not disabled for receipt of mail. # This query has the postconf value of $recipient_delimiter hardcoded: # '-' above. If you change that in dovecot/conf.d/15-lda.conf and # main.cf you must change it here too! # A simple plain-language rundown: # Select: # Alias.extension when Alias.target=0, else # (Target's Address.localpart, # (- and Alias.extension if that exists) # and '@' and Target's Domain.name) *** query/maps-valias.query : dbpath = /etc/postfix/private/mail.sqlite # Shorthand in this query: # AA = Alias Address (the address we are looking up) # AD = Alias Domain (the domain part of that address) # TA = Target Address (the address we are redirecting to) # TD = Target Domain (the domain part of that address) # VA = Virtual Alias query = SELECT TA.localpart || (CASE WHEN VA.extension IS NOT NULL THEN '-' || VA.extension ELSE '' END) || (CASE WHEN TD.id=0 THEN '' ELSE '@' || TD.name END) FROM Alias AS VA JOIN Address AS TA ON (VA.target = TA.id) JOIN Domain AS TD ON (TA.domain = TD.id) JOIN Address AS AA ON (VA.address = AA.id) JOIN Domain AS AD ON (AA.domain = AD.id) WHERE AA.localpart || '@' || AD.name IS '%s' AND VA.active!=0 # This query has the postconf value of $recipient_delimiter hardcoded: # '-' above. If you change that in dovecot/conf.d/15-lda.conf and # main.cf you must change it here too! # The Alias table is simple in appearance, but it defines several # complex relationships. Each of Alias.address and Alias.target is a # pointer to the Address table, where the localpart is stored along with # an integer pointer to the Domain table. So this query joins Address # and Domain twice, once each for Alias.address and Alias.target. The # shorthand summary above is to help understand what this query does. # Here in virtual_alias_maps we do not have the features of invoking a # command, appending to a file, or :include: of a file, so in this case # the Alias.extension column is strictly for what it says it is: the # optional address extension after recipient_delimiter. The SELECT # clause tests for Alias.extension and inserts the delimiter and the # extension after the target address' localpart if necessary. # Note that TD.id=0 in SELECT returns an unqualified localpart. (In # general I recommend against this and prefer to have an explicit # @domain with every address. But sometimes it is useful. Offer void # where taxed or prohibited, or if the reader fails to understand # append_at_myorigin in the postconf(5) manual.) # The WHERE clause assembles the alias address' localpart, an "@" sign, # and the alias address' Domain.name. This enables the use of an empty # string localpart to implement a catchall address for any given domain, # because after the user@domain search, Postfix looks for "@domain" in # virtual_alias_maps. # The WHERE clause also goes on to check the alias address' value of # active, where if zero, the record does not match. # Differentiation from alias_maps here is not necessary, since a NULL # Domain.name will never be matched. Speaking of which, one documented # feature I have NOT implemented here is that of a bare localpart as # lookup key. It could have been done with a different method of # distinction from alias_maps, but I like it this way and do not # consider this feature to be important; alias_maps has us covered for # that functionality if it is needed. # Thanks to Frank Gingras for help with this query. It took my SQL # understanding to a higher level, such that I was later able to # implement the alias_maps and transport_maps queries on my own. :) *** query/maps-transport.query (Aspirin recommended!): dbpath = /etc/postfix/private/mail.sqlite # Shorthand in this query: # LA=lookup address # LD=lookup domain # DT=domain's transport # AT=address's transport # [AD]TN=nexthop domain query = SELECT DISTINCT CASE WHEN AT.id IS NOT NULL THEN coalesce(AT.transport, '') || ':' || (CASE WHEN AT.mx=0 THEN '[' || coalesce(ATN.name, '') || ']' ELSE coalesce(ATN.name, '') END) || (CASE WHEN AT.port IS NOT NULL THEN ':' || AT.port ELSE '' END) ELSE coalesce(DT.transport, '') || ':' || (CASE WHEN DT.mx=0 THEN '[' || coalesce(DTN.name, '') || ']' ELSE coalesce(DTN.name, '') END) || (CASE WHEN DT.port IS NOT NULL THEN ':' || DT.port ELSE '' END) END FROM Domain AS LD LEFT JOIN Address AS LA ON LD.id=LA.domain LEFT JOIN Transport AS DT on LD.transport=DT.id LEFT JOIN Transport AS AT on LA.transport=AT.id LEFT JOIN Domain AS DTN ON DT.nexthop=DTN.id LEFT JOIN Domain AS ATN ON AT.nexthop=ATN.id WHERE (LA.localpart || '@' || LD.name IS '%s' AND CASE WHEN AT.id IS NOT NULL THEN AT.active!=0 AND LA.active!=0 WHEN DT.id IS NOT NULL THEN DT.active!=0 AND LD.active!=0 END) OR (LD.name IS '%s' AND DT.active!=0 AND LD.active!=0) # Because of all the LEFT JOINs, this query returns multiple identical # rows in many cases; thus this SELECT needed the DISTINCT keyword to # limit results. # The SELECT is entirely within one CASE expression. The first part, # "WHEN AT.id IS NOT NULL", triggers only for addresses listed in the # Address table which also have an Address.transport entry. The ELSE # part triggers otherwise, but will only return results when the lookup # domain record has a Domain.transport entry. # Inside each of the CASE conditions, the same sort of formatting for # display is done. In the first part, values are taken from AT and ATN; # DT and DTN in the second. First the [AD]T.transport string is taken, # or if null, an empty string is substituted. A colon is added; this # colon is in fact the only token sure to be returned by this query. # Next, the condition of [AD]T.mx is checked; if zero, the [AD]TN.name # string is enclosed in square brackets "[]". Otherwise the [AD]TN.name # string (or empty string, if that is null) is used. # Finally the state of [AD]T.port is checked. If that is not null, # another colon is added, followed by the port number. Otherwise another # empty string is added. # The FROM table is Domain, because the only input token we can expect # in all cases is a Domain.name string. First, Address is brought in as # a LEFT JOIN ON LD.id=LA.domain. This will be all NULL in case of an # unlisted address or a domain-only lookup. # Next, Transport is LEFT JOINed on each table's transport column. # Either of these can be null. (If both are null, the query returns # nothing.) And then Domain is LEFT JOINed on each of those Transport # table's nexthop column. This too can be null. # The WHERE clause is in two parts to account for user@domain lookups as # well as domain lookups. The left side of the OR matches user@domain # lookups, and repeats the same condition check as in SELECT: "CASE WHEN # AT.id IS NOT NULL", which means that we have a listed Address with an # Address.transport entry. It then checks the Address record and # corresponding Transport record for their active boolean being not # false (not zero.) # The second CASE condition is "WHEN DT.id IS NOT NULL", meaning that # the looked-up Domain has a transport entry. It then checks that Domain # record and corresponding Transport record for their active boolean # being not false (not zero.) # The right side of the OR matches only on a bare domain lookup, and # additionally checks that Domain record and corresponding Transport # record for their active boolean being not false (not zero.) # Whew. # Could this have been simpler? Yes, but only with duplication of data # in the database. A previous version of the Transport table, as well # as all MySQL examples I found, have a simple key/string structure, # storing the entire transport:nexthop:port as a string value. There are # pros and cons to each approach. The biggest "pro" to this one is that # it's in SQLite and not MySQL (meaning by that: the system is stable # and self-contained, not dependent on the external mysqld process. # Great pain comes to you when your transport_maps lookup fails.)