Postfix SQL map contract, version 1

The examples in examples/postfix/ define the supported MySQL/MariaDB lookup queries for the module's 1.x entity schema. They are configuration examples, not automatically deployed configuration. Drupal owns and writes the tables; Postfix reads them. This is not the schema of the standalone PostfixAdmin product.

Tables and row selection

Entity	Base table	Routing table	Routing columns
Domain	domain	domain_field_data	domain, active
Mailbox	mailbox	mailbox_field_data	username, maildir, quota, active
Alias	alias	alias_field_data	address, goto, active
Alias domain	alias_domain	alias_domain_field_data	alias_domain, target_domain, active

Drupal generates these tables. The Drupal entity type IDs are namespaced as postfix_admin_domain, postfix_admin_mailbox, postfix_admin_alias, and postfix_admin_alias_domain; the physical table names above do not change during the ID migration. The base tables hold entity identity; routing queries must read the field-data tables, including both sides of alias-domain joins. Do not replace Drupal tables with hand-written schema or import standalone PostfixAdmin tables.

Each routing table also contains id, langcode, default_langcode and status. Use default_langcode = 1 on every joined table. An entity can have multiple language rows, and its default language need not be English. Omitting this filter can duplicate results or multiply rows in a join. Routing fields are currently non-translatable, but are stored alongside the translatable fields.

For compatibility with existing routing, active = 1 controls delivery. Drupal publication status, confirmation activated, tokens, ownership, and Drupal access permissions are not SQL lookup filters. Alias-domain maps require both the mapping and its target alias/mailbox to be active. Domain activation is checked by the domain map, not implicitly by every recipient map; the Postfix recipient/domain configuration must apply the intended domain policy.

The examples assume no Drupal table prefix. If the site uses a prefix, replace every table name with the corresponding physical table name, including both tables in joins. Preserve the site's configured database character set/collation and address normalization; case sensitivity follows that collation. Do not introduce a database collation change as part of deploying these maps.

Lookup results

Files below use the mysql_virtual_ prefix and _maps.cf suffix.

Map	Input	Result
domains	Domain name	Active domain name
mailbox	Full mailbox address	Stored maildir
mailbox_limit	Full mailbox address	Stored quota, unchanged
alias	Full alias address	Stored goto destination list
alias	@domain	That domain's catch-all goto
alias_domain	local@alias-domain	Target domain's exact alias goto
alias_domain_mailbox	local@alias-domain	Target domain's mailbox maildir
alias_domain_catchall	local@alias-domain	Target domain's @domain alias goto

Absent or inactive records return no result. goto is a comma-separated list; these queries do not split or rewrite destinations. Postfix expands %s to the whole input, %u to the local part and %d to the domain, with SQL quoting. The alias-domain queries using %u do not run for an empty local part. %d requires an address containing a domain. The catch-all query intentionally does not require %u and can also resolve an @alias-domain fallback key.

Order virtual_alias_maps as exact alias, exact alias-domain, then alias-domain catch-all. Postfix performs its own fallback to @domain; postmap -q performs only the single lookup requested. Test direct catch-alls with @domain and alias-domain catch-alls with an address at the alias domain. A direct unknown address must not match the exact alias map. A catch-all accepts otherwise unknown recipients, so enable it only where that is intended.

The mailbox quota map returns the stored value without conversion. Its meaning and enforcement belong to the configured delivery agent; this module does not implement a quota service. Do not activate mailbox delivery or change transports merely to install these examples on an alias-only site.

Database access and deployment

Copy the applicable examples to a protected Postfix configuration directory and add hosts, dbname, user, and password using that site's connection details. Keep credentials outside Git and readable only by the administrator and the Postfix lookup process (for example, owner root, group postfix, mode 0640). Use Postfix's mysql:/proxy:mysql: lookup type, not a generated hash database.

Use a dedicated account with SELECT only on the four routing tables, never the Drupal writer account. Where supported, restrict the grants to the columns in the table above plus default_langcode. For example, adapting the database and account names after creating a dedicated account:

GRANT SELECT (domain, active, default_langcode)
  ON mail_site.domain_field_data TO 'postfix_reader'@'localhost';
GRANT SELECT (username, maildir, quota, active, default_langcode)
  ON mail_site.mailbox_field_data TO 'postfix_reader'@'localhost';
GRANT SELECT (address, goto, active, default_langcode)
  ON mail_site.alias_field_data TO 'postfix_reader'@'localhost';
GRANT SELECT (alias_domain, target_domain, active, default_langcode)
  ON mail_site.alias_domain_field_data TO 'postfix_reader'@'localhost';

Do not grant access to mailbox passwords, confirmation tokens, or unrelated Drupal data. Restrict network access and use authenticated TLS when the database is remote. Connection security is site configuration, not embedded in examples.

Upgrade and uninstall

Back up the database and active Postfix configuration before changing either. Keep the existing schema/table names and update Drupal through its normal update process. The legacy token invalidation update clears only token fields; the regression test verifies routing results before and after that update.

Compare each deployed query with the examples, account for any table prefix, and run postmap -q for known, unknown, inactive, alias-domain and catch-all recipients before switching map paths. Then validate the Postfix configuration and reload it through the site's deployment process. Updating the module alone does not update externally managed SQL maps.

Drupal's standard content uninstall validator blocks uninstall while any of the four routing entity types contains records, including inactive records. The regression test checks this protection for each entity type. Do not force uninstall or delete entities merely to bypass that guard: uninstall after data removal drops the module's tables. First migrate live routing elsewhere, switch Postfix maps, verify delivery, and retain a restorable backup. Drupal cannot discover external Postfix consumers or prevent direct database deletion.

Any future breaking table, column, filtering or lookup change requires a new contract version and an explicit data/map migration plan. Namespacing Drupal entity IDs does not rename or replace the Postfix routing tables.

Automated verification

SqlMapContractTest creates real entities through Drupal, with French default rows and English translations, and executes the shipped queries. It checks all seven maps, direct/alias-domain catch-alls, multiple destinations, missing and inactive records, quoted input, updates, deletion, and uninstall protection.

Ordinary kernel runs can use SQLite. CI additionally installs postfix-mysql and sets POSTFIX_ADMIN_POSTMAP=/usr/sbin/postmap. The same assertions then run the real postmap -q against Drupal's isolated, prefixed MySQL test tables. Missing Postfix support or lookup errors fail CI. Temporary map credentials are restricted to mode 0600 and removed after each lookup. The tests neither send mail nor read the production mail database.

Postfix references: MySQL tables, virtual alias processing.