2

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:

  1. The domain name of the relay.
  2. The username of the mailbox.
  3. 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 mailpass: 

smtp.relay.tld mailbox@domain2.tld:mailpass

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[6494]: maps_find: smtp_sasl_password_maps: smtp.relay.tld: not  found
postfix/smtp[6494]: maps_find: smtp_sasl_password_maps: smtp.relay.tld:587:  not found
postfix/smtp[6494]: smtp_sasl_passwd_lookup: no auth info found 
(sender=`user@domain.tld', host=`smtp.relay.tld')

yet, executing this command: 

postmap -q user@domain.tld mysql:/etc/postfix/mysql-map.cf

produces 

smtp.relay.tld user@domain.tld:mailpass

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.

aphid
  • 281

2 Answers2

3

Two things are necessary:

First, the option smtp_sender_dependent_authentication = yes must be enabled in the postfix main configuration file (usual location is /etc/postfix/main.cf). Without this option, the server will only search based on the domain name of the relay host, which would be the same for every user.

Second, the correct query should look like this: 

query = SELECT CONCAT(username, "@", domain, ":", password) FROM accounts \
        WHERE username = '%u' AND domain = '%d'

Note: config files don't support multi-line in this way; so remove the \ and newline

aphid
  • 281
1

Just making a wild guess here (I haven't mixed Postfix & SQL), but here's what I would start with:

  1. The "local part" of email address does not automatically become the "username" for relay authentication. So even though in your database they're both identical, Postfix cannot make that assumption automatically, so the username field has to be returned anyway.

  2. All Postfix tables perform lookups in the same way: one input results in one output, and the Postfix core doesn't actually care about whether the specific table backend supports multiple columns or not. I think it expects a single string and parses it the same way in all cases.

According to point #2, if the plaintext table stores mailbox@domain2.tld:mailpass, then the SQL query should probably also return a single column formatted in the same way. So I would try this:

query = SELECT CONCAT(username, ":", password) FROM accounts WHERE username = '%u' AND domain = '%d'

But if that's not correct, then two separate columns should work:

query = SELECT username, password FROM accounts WHERE username = '%u' AND domain = '%d'
grawity
  • 501,077