sql query for sympa integration in postfix

[jblanel]

Hello, I find it much more convenient to query the sympa db from my postfix instance (with a dedicated sql readonly user) than maintaining a transport map.
For instance query for the recipient check parameter
query = select 'OK' from list_table where '%u' in (name_list,concat(name_list,'-request'),concat(name_list,'-editor'),concat(name_list,'-subscribe'),concat(name_list,'-unsubscribe')) and status_list = 'open' and robot_list='%d';

Following the same idea the transport.cf can be done to transport to the sympa binary.

[ikedas]

It's interesting.
However, there seem several points in your suggestion that need to be resolved to publish it as documentation: Such as that list_table is used when optional db_list_cache parameter is enabled.

First, please try running Sympa on your server for a while with your suggested configuration. Then, based on the configuration that actually worked, please draft a configuration procedure similar to the other pages on this site.

If you have any questions, please feel free to ask.

[ikedas]

I think there is one concern with this approach.

It is vulnerable to DOS attack, because the outside party can issue as many queries to the database as they wish through the MTA.

One measure to this is to write a program that generates alias file from the database contents and run it occasionally. (There may be other ways.)

[jblanel]

Hello,

From my point of view it is the MTA's job, as a front service, to protect against Dos through its own mechanism like Anvil or the use of a proxy with connection limitation in the case of postfix. Btw, more "real time" than querying the db and generation of a hashfile, a dedicated replication may also be a solution to protect sympa master db.

JB

[ikedas]

Anyways such consideration should be noted in the instruction.

[jblanel]

So for it has been running on my system for a week, I'll wait for 3 more weeks before writing the documentation if no anomaly is encountered before that. JB

On 2022-09-05 14:55, IKEDA Soji wrote: Anyways such consideration should be noted in the instruction. -- Reply to this email directly, view it on GitHub [1], or unsubscribe [2]. You are receiving this because you authored the thread.Message ID: ***@***.***>

Links: ------ [1] #94 (comment) [2] https://github.com/notifications/unsubscribe-auth/A22ETHJV7WTLU5RSNRVE3OTV4XUULANCNFSM6AAAAAAQAIABGY

[ikedas]

So for it has been running on my system for a week, I'll wait for 3 more weeks before writing the documentation if no anomaly is encountered before that. JB

It is a good opportunity to try to reproduce DOS by yourself. You can generate lots of SMTP delivery requests to random recipients and see what happens. The recipients do not have to be real list addresses (Rather, random addresses will outrun the cache) . Moreover, it is not necessary to actually deliver the messages, but just sending the SMTP RCPT commands will be effective.

[jblanel]

Hello, From my point of view is is the MTA's job, as a front service, to protect against Dos through its own mecanism like Anvil or the use of a proxy with cnx limitation in the case of postfix. Btw, more "real time" than querying the db and geenratign hashfile, a dedicated replication may also be a solution to protect sympa master db. JB

…

On 02/09/2022 05:56, IKEDA Soji wrote: I think there is one concern with this approach. It is vulnerable to DOS attack, because the outside party can issue as many queries to the database as they wish through the MTA. One measure to this is to write a program that generates alias file from the database contents and run it occasionally. (There may be other ways.) — Reply to this email directly, view it on GitHub <#94 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/A22ETHLTWBH6FAXHWNGYLFDV4F3HVANCNFSM6AAAAAAQAIABGY>. You are receiving this because you authored the thread.Message ID: ***@***.***>