Postfix + MariaDB + Mailman 3 Core
== ENVIRONMENT == OS: Debian 12 Mailman Core: 3.11.2 Install Mode: venv
== QUESTION == I'd like to do a more advanced setup with regards to my MTA setup.
*** I have mastered the art of setting up Mailman 3 Core to use MariaDB vs PostgreSQL ***
What I'm after is the ability to make use of MariaDB with:
transport_maps local_recipient_maps relay_domains
In particular, with regards to Mailman 3 Core generated content. So instead of referencing a postmap generated file which MM3 presently generates, but have MM3 add and/or remove those records to/from a MariaDB table.
Appreciate any insights on this task.
At least as a temporary workaround, I wrote a script which periodically syncs the "postfix_domains" and "postfix_lmtp" entries with my database. Essentially I let MM3 create the files, but have Postfix setup to use my database instead.
Actually, thinking about it a bit further, I am changing my logic. Instead of running a cron job for this, I'm just going to implement it into my frontend code.
So, when I "create" a domain, I'll have it create a record in the database equiv to what "postfix_domains" would have in it... In the same way, when I create a "list" I'll have it create the records in the database equiv to what "postfix_lmtp" would have in it...
With this adjusted logic, when I "delete a list" or "delete a domain" I can delete the appropriate database record as well... Ofcourse making sure to add some fail safe features into the code and/or database itself to prevent removing something that is still in use.
*** generally speaking removing all "transport_map" ("postfix_lmtp) entries for a list after deletion should be pretty safe, but there'll need to be a constraint and logic for ensuring the "relay_domains" ("postfix_domains") entry is not removed if it's being used by another list. ***
Ha, it was great chatting with myself on this... Got me thinking... Lol
Peter Knowles via Mailman-users writes:
In particular, with regards to Mailman 3 Core generated content. So instead of referencing a postmap generated file which MM3 presently generates, but have MM3 add and/or remove those records to/from a MariaDB table.
All that is already in the 'mailman' database. I don't do MySQL, but there must be a cli where you can examine the tables etc. There are about 50 tables altogether in a fully populated mailman database IIRC.
Information about lists from which you can construct posting addresses and list-id is in the 'list_name' and 'mail_host' columns of the 'mailinglist' table, and the domains themselves have a separate table 'domain'.
Steve
Steve,
I'm referring to Postfix specific records, not Mailman records which are not the topic of conversation here.
Presently, Mailman creates two files under "/opt/mailman/mm/var/data"...
"postfix_lmtp" which is a "transport_maps" table "postfix_domains" which is a "relay_domains" table
The instructions in the docs says to set these up as "hash" table entries in Postfix so that when they're updated by MM Postfix references get updated accordingly.
What I'm doing is, converting the "hash tables" to MariaDB tables stored in a Postfix specific database.
Two primary objectives are intended with this updated architecture design...
allow management of Postfix tables through my web UI
Offer future scalability whereby the database for MM and Postfix amongst other things may get placed on a separate node, and/or setup in a Master-Master mirror cluster.
*** On a segway, but related to the overall goal... I'm adding OpenDKIM management into my admin UI, which also is tied to a MariaDB database for key signing tasks and management. For the same reasons above, this is why I'm moving from files to databases. ***
Peter Knowles via Mailman-users writes:
I'm referring to Postfix specific records, not Mailman records which are not the topic of conversation here.
I'm referring to a setup that works in practice on two sites (so far) managing about 25,000 lists. No hash:postfix_* database files, no records in the SQL database other than Mailman's own, no need to synchronize Postfix's databases from Mailman's.
You're going to need to write new code to create those Postfix- specific records and to deal with synchronization of the Postfix database to Mailman's. You'll have to maintain it, I'm pretty sure we would not accept that patch in the main Mailman distribution.[1]
For the setup I'm talking about, eventually I'll figure out the best way to configure a generic mom'n'pop host (currently these are hosts dedicated to Mailman, so I use the virtual mailbox approach but that can cause trouble if there are other mailflows to the Mailman domain(s)). Then I'll submit a MR and it will be (Abhilash willing) a standard part of a near-future release. What's not to like?
Footnotes: [1] I am not authoritative on that, I just don't think my colleagues will prefer it to the approach of accessing the 'mailman' database.
On 28.02.25 07:21, Peter Knowles via Mailman-users wrote:
I'm confused...
We are using postgresql as mailman database.
$ sudo -u postgres psql mailman -c 'SELECT DISTINCT mail_host from mailinglist;'
would list me all used domains which will go into postfix_domains.
$ sudo -u postgres psql mailman -c 'SELECT list_name, mail_host from mailinglist;'
lists all mailing lists with domains. It would require additional rows for all the special purpose addresses like list-bounces@ etc.
You can link postfix directly to postgresql using a pgsql_table.
Then postfix reads the latest in information from the mailman database.
-Gerald
Oh, in that case. I've got things covered by my custom solution I'm working on already.
The original request was to have instead of "postfix_domains" and "postfix_lmtp" files be created as static files, that the entries could be added to a database backend (MariaDB, PostgreSQL, etc) automatically and as an option.
Basically, since we can switch between DB backends for Mailman Core, why not add the option to switch backends for Postfix and other SMTP software (who support this) as an option.
Just a thought, but as mentioned if not possible or desired, I have now found a way to accomplish this within my own code base during "creation", "modification", and "deletion" as needed.
participants (3)
-
Gerald Vogt -
Peter Knowles -
Stephen J. Turnbull