Routing email based on sender address

For some external email addresses, I need my mail server to relay mail through their SMTP host in order to pass DKIM/SPF checks (otherwise my mail server is just forging the from address from the point of view of the destination system). These are the changes needed to make this work in my virtual mail setup.

Database update

Firstly a few new tables are required, one to hold the relay host details (and either username/password or neither if not using authentication) and 3 to link valid sender addresses (users, aliases or external sender addresses) to a relay host:

CREATE TABLE relayhosts(
    id SERIAL PRIMARY KEY,
    host TEXT NOT NULL UNIQUE,
    smtp_username TEXT NULL,
    smtp_password TEXT NULL,
    CONSTRAINT username_and_password CHECK ((smtp_username IS NOT NULL AND smtp_password IS NOT NULL) OR (smtp_username IS NULL AND smtp_password IS NULL))
);
CREATE TABLE virtual_alias_relayhosts(
    virtual_alias_id INTEGER NOT NULL REFERENCES virtual_aliases(id),
    relayhost_id INTEGER NOT NULL REFERENCES relayhosts(id)
);
CREATE TABLE user_relayhosts(
    user_id INTEGER NOT NULL REFERENCES users(id),
    relayhost_id INTEGER NOT NULL REFERENCES relayhosts(id)
);
CREATE TABLE sender_address_relayhosts(
    sender_address_id INTEGER NOT NULL REFERENCES sender_addresses(id),
    relayhost_id INTEGER NOT NULL REFERENCES relayhosts(id)
);

A view to allow postfix to map any of the different kinds of valid sender to a relay host:

CREATE VIEW sender_relayhost_maps AS
    SELECT users.username || '@' || domains.domain AS sender, relayhosts.host AS host
    FROM user_relayhosts
    INNER JOIN relayhosts ON user_relayhosts.relayhost_id = relayhosts.id
    INNER JOIN users ON user_relayhosts.user_id = users.id
    INNER JOIN domains ON users.domain_id = domains.id
    UNION
    SELECT virtual_aliases.alias || '@' || domains.domain AS sender, relayhosts.host AS host
    FROM virtual_alias_relayhosts
    INNER JOIN relayhosts ON virtual_alias_relayhosts.relayhost_id = relayhosts.id
    INNER JOIN virtual_aliases ON virtual_alias_relayhosts.virtual_alias_id = virtual_aliases.id
    INNER JOIN domains ON virtual_aliases.domain_id = domains.id
    UNION
    SELECT sender_addresses.address AS sender, relayhosts.host AS host
    FROM sender_address_relayhosts
    INNER JOIN sender_addresses ON sender_address_relayhosts.sender_address_id = sender_addresses.id
    INNER JOIN relayhosts ON sender_address_relayhosts.relayhost_id = relayhosts.id
;

Grant permissions to the postfix user on the new table/view it needs access to:

GRANT SELECT ON sender_relayhost_maps TO "vmail-reader";
GRANT SELECT ON relayhosts TO "vmail-reader";

Changes to mailadm

My mailadm tool needed updating to allow managing the relay hosts and link user/alias/external addresses to relays. These are simple CRUD operations on the data - the only little bit of “magic” is that it takes any of these types of addresses as an argument and creates the appropriate link by the code testing to see which type the address is (by which table returns a result when querying for the address).

Link to postfix

Finally, the changes needed to allow postfix to do the routing:

# Enable authentication, if needed
smtp_sasl_auth_enable = yes
# Allow plaintext passwords over TLS connections (N.B. this is
# smtp_sasl*_tls*_security_options, not smtp_sasl_security_options which
# defaults to noplaintext,noanonymous (and smtp_sasl_tls_security_options
# defaults to $smtp_sasl_security_options))
smtp_sasl_tls_security_options = noanonymous

sender_dependent_relayhost_maps = proxy:pgsql:/etc/postfix/pgsql-sender-dependent-relayhost-maps.cf
smtp_sasl_password_maps = proxy:pgsql:/etc/postfix/pgsql-smtp-sasl-password-maps.cf

I already had this in my configuration file, to play nicely with GMail per the comment, however you will want TLS if possible for connections over which you are authenticating:

# If remote SMTP servers support TLS, may as well allow it (GMail gives users
# a nice grey padlock if we do this - instead of a red broken one).
smtp_tls_security_level = may

The two database proxy files contain the standard authentication information and the queries for the sender -> proxy map and proxy -> credentials map.

pgsql-sender-dependent-relayhost-maps.cf:

hosts = /var/run/postgresql/
user = vmail-reader
dbname = vmail
query = SELECT host FROM sender_relayhost_maps WHERE sender = '%s'

pgsql-smtp-sasl-password-maps.cf:

hosts = /var/run/postgresql/
user = vmail-reader
dbname = vmail
query = SELECT smtp_username || ':' || smtp_password FROM relayhosts WHERE host = '%s' AND smtp_username IS NOT NULL

Testing

Restart postfix, add some entries to the mapping tables in the database and then try sending some emails as users that both should and should not use the specified relays. Check the mail-server’s logs and received mail headers to see if the mail gets routed out correctly.