How to Integrate Transport Settings (Mail Domain) to MySQL

[javier.wong]

At the Postfix level I know you can integrate the mail domains to a MySQL Database.

I tried it via Webmin, and whilst the mail domains seem to be fine at that level, EFA doesnt seem to accept those domains. Furthermore, at the console level under Transport Settings it is not the same as in the Webmin Postfix.

Is there a way to get this working?

[javier.wong]

Does anyone know how this can be done? I really need a way to push the domains to a mysql table without having to manually key them in via the console. Thanks.

[shawniverson]

It can be done, of course, but it will probably require some coding to make this possible. The EFA-Configure only understands the flat file, not mysql.

[pdwalker]

Javier,

Given that EFA doesn't support his directly, you could easily have a script that would take the EFA domain list and convert it into a series of SQL insert/update statements.

Can you tell me the database scheme used (show create table XXX) for your mysql table? It shouldn't take more than a few minutes to script up.

[pdwalker]

better yet, tell me what instructions you used for integrating postfix and mysql and I'll figure it out for you.

[javier.wong]

'CREATE TABLE `transport` (
`name` varchar(255) NOT NULL,
`maps_to` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8'

@pdwalker This is what i used which seems to be readable at the postfix level (not other parts of EFA).

But honestly it doesnt make much sense to convert the flat file into SQL insert / update statements - it should be rather that EFA is able to read the mysql DB for transport settings. Actually the reason why I want to accomplish this is so that the postfix transport settings can be centralized to be used by a cluster of EFA servers.

@shawniverson, would this be in the roadmap? I think there is certainly demand for such functionality

[javier.wong]

Right now, I have to manually update all my clustered EFA servers individually whenever there is a new mail domain.

Do any of you have experience in pushing these domain updates to multiple servers?

[pdwalker]

'But honestly it doesnt make much sense to convert the flat file into SQL insert / update statements - it should be rather that EFA is able to read the mysql DB for transport settings. Actually the reason why I want to accomplish this is so that the postfix transport settings can be centralized to be used by a cluster of EFA servers.

Yes, but until that feature is enabled, you'll have to manage it by hand on your own servers.

To do that, it's easiest to run them as a series of sql insert/update commands which is easily accomplished by the following:

Code: Select all

sed -r \
-e "/^#/d" \
-e "s/[ ]+$//g" \
-e "/^$/d" \
-e "s/[ ]+/',  '/g" \
-e "s/^/insert into transport \(name, maps_to\) values \('/g" \
-e "s/$/'\) on duplicate key update maps_to = values\(maps_to\);/g" < /etc/postfix/transport > /tmp/update.sql

the sed substitutions are as follows:

• remove comment lines
• remove trailing spaces
• remove blank lines
• collapse multiple spaces into the middle of the sql command
• prefix of the sql command
• end of the sql command

The order of the substitutions is important

The commands will update the maps_to if the record already exists, but only if you add a primary key to your transport table

Code: Select all

alter table transport add primary key (name);

Then you can update your table

Code: Select all

mysql -uroot -p<YOURPW> <YOURDB> < /tmp/update.sql

.
This will not delete records. If you want to delete records, then you can delete it manually, or start your update with a "delete * from transport" to make sure your deleted records are deleted.

The above has not been extensively tested. It seems to work on my system, but it might cause your system to halt and catch fire. You should test it before actually trying it in production yourself.

[thelight]

I connected my postfix to my SQL database, but how does it read the maps_to as destination mailserver?
My test setup is allowing my testdomain and testmailbox as defined in my SQL DB, but it doesnt forward the mail to the destination server declared in the maps_to column in SQL.

My postfix verifies the SQL DB what domains and mailboxes are allowed (and allows my testmail via telnet).
I followed these instructions to connect postfix to SQL:
https://workaround.org/ispmail/wheezy/c ... e-database

Hoping to find some answers.
Thanks in advance!