It is possible to request postfix to fetch sasl login information for its relay server from a database. I then have this relay server fetch the very same login information.
How do we format this configuration file to properly fetch?
We know these pieces of information:
- The domain name of the relay.
- The username of the mailbox.
- The domain name on the postfix server (there may be more than one).
Now a plaintext file just containing the passwords would be formatted like this (per line), assuming
mailbox is sending from
domain2 using password
I verified that the plaintext file method works for a specific user and password in the database. Now I want to use a .cf file for connecting to the database.
Given a domains table like this1:
+----+----------+-------------+----------+ | id | username | domain | password | +----+----------+-------------+----------+ | 1 | mailbox | domain2.tld | mailpass | +----+----------+-------------+----------+
In general, such a file would be formatted like so;
user = sqluser password = dbpass hosts = localhost dbname = maildb query = SELECT password FROM accounts WHERE username = '%u' AND domain = '%d'
I'm unsure as to what exactly to put in the 'query' parameter. The official documentation on it is pretty bad: it states you can configure using a sql database, it doesn't specify how at all, no specific examples are given.
Now I tried using the naïve approach; create a query that just returns a value exactly like the line in the plaintext file. I've also tried returning various subsets of the line, without success. Every attempt just generates an "access denied" error.
1: I am aware that storing actual user's passwords in a database in this fashion is a bad idea. The passwords used here are merely for the sake of communication between two specific servers.
The reason for wanting to use a database is a combination of portability and scalability. E.g. iff a domain would want to use multiple mail servers, or move to another email provider. The reason not to just use a single password is to be able to separate concerns (each user has a separate relay password configured which they themselves do not know, but the postfix user on each mail server knows it by looking in a certain database table. It's a mechanism comparable to openstack keystone, though far less complicated).
Doing some more digging: Enabling L4 level logging and looking through the server's connection transcript, it appears to never send an AUTH message (so it's not even figuring out how to 'find' the user). The following messages can be found in the mail log (with domain names, etc. redacted);
postfix/smtp: maps_find: smtp_sasl_password_maps: smtp.relay.tld: not found postfix/smtp: maps_find: smtp_sasl_password_maps: smtp.relay.tld:587: not found postfix/smtp: smtp_sasl_passwd_lookup: no auth info found (email@example.com', host=`smtp.relay.tld')
yet, executing this command:
postmap -q firstname.lastname@example.org mysql:/etc/postfix/mysql-map.cf
In other words, the configuration is working, but the postfix program is doing some weird query in order to fish the password out of the database, and it's nowhere specified what that actually is, nor is it logged.