Postfix MySql result in bolean
Hello, I am using a suppression list in MySql with Postfix. smtpd_recipient_restrictions = check_recipient_access mysql:/etc/postfix/ mysql-virtual-recipient-access.cf query = SELECT access FROM virtual_sender_access WHERE source='%s' the question is, is it possible to use boolean data? like this: SELECT COUNT(1) FROM virtual_sender_access WHERE source = 'u...@gmail.com'? Regards,
Re: Postfix MySql result in bolean
SysAdmin EM: > Hello, > > I am using a suppression list in MySql with Postfix. > > smtpd_recipient_restrictions = check_recipient_access mysql:/etc/postfix/ > mysql-virtual-recipient-access.cf > > query = SELECT access FROM virtual_sender_access WHERE source='%s' > > the question is, is it possible to use boolean data? > > like this: > > SELECT COUNT(1) FROM virtual_sender_access WHERE source = 'u...@gmail.com'? The check_recipient_access lookup result must be as described in http://www.postfix.org/access.5.html Wietse
Re: Postfix MySql result in bolean
SysAdmin EM skrev den 2020-07-13 20:43: SELECT COUNT(1) FROM virtual_sender_access WHERE source = 'u...@gmail.com'? valid sql query, its just not meaningfull to postfix, and you did not provide how postfix should use (1)
smtpd_recipient_restrictions in mongodb?
Hello, I use a suppression list where I block domains and email accounts that don't exist to prevent the reputation of my IP addresses from going down. smtpd_recipient_restrictions = check_recipient_access mysql:/etc/postfix/ mysql-virtual-recipient-access.cf # connection to mysql hosts = 172.x.x.x user = myuser password = mypass dbname = blacklist query = SELECT access FROM virtual_sender_access WHERE source='%s' The table currently has 4 million rows, so I am looking for a faster database engine than MySql. I read this postshttp://www.postfix.org/DATABASE_README.html and http://www.postfix.org/access.5.html but I didn't find any information. Does postfix have support for mongo database? Or what method do you recommend to host a large number of unknown users and invalid domains? Regards,
Re: smtpd_recipient_restrictions in mongodb?
* SysAdmin EM: > I use a suppression list where I block domains and email accounts that > don't exist to prevent the reputation of my IP addresses from going > down. [...] The table currently has 4 million rows, so I am looking > for a faster database engine than MySql. Can you clarify for me: You try to maintain a database for domains that do not exist and email accounts that do not exist? How would that work? Both the number of nonexistent domains and and accounts are, while not infinite, huge. Why not list the domains and accounts that do exist? These numbers are definitely finite. As for your choice of database: I am a fan of MongoDB because of its capability of storing unstructured data, but here your data does have a well-defined, uniform structure. If you use a proper index, MySQL or similar relational databases should be quick as blazes. -Ralph
Re: smtpd_recipient_restrictions in mongodb?
The user database is set up because my clients, some bad users, send mass mailings to non-existent accounts, such as hotmail, gmail, which usually measure the number of IP bounces. Also sometimes there are cases where the PC or mobile device is infected with a virus and they use the email account to send spam, so I add those accounts that they sent to my list because they can see spam trap. the database has maintenance, the accounts within it are cleaned as I check them with another system, I only clean those that exist. here I add some info from the table mysql> desc virtual_sender_access; +---+-+--+-+-+-+ | Field| Type | Null | Key | Default| Extra | +---+-+--+-+-+-+ | id | int(11)| NO | PRI | NULL | auto_increment | | source | varchar(64) | NO | MUL || | | access | varchar(64) | NO ||| | | created_on | timestamp | NO || -00-00 00:00:00 | | | check_bounce | int(11)| NO || NULL | | | last_modified | timestamp | NO || CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | +---+-+--+-+-+-+ 6 rows in set (0.00 sec) mysql> show index from virtual_sender_access ; +---++--+--+-+---+-+--++--++-+ ---+ | Table| Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +---++--+--+-+---+-+--++--++-+ ---+ | virtual_sender_access | 0 | PRIMARY | 1 | id | A|4225535 |NULL | NULL | | BTREE || | | virtual_sender_access | 1 | source | 1 | source | A|4225535 |NULL | NULL | | BTREE || | +---++--+--+-+---+-+--++--++-+ ---+ 2 rows in set (0.00 sec) I would like to know if I can use mongo in the smtpd_recipient_restrictions parameter anyway. Thank you. Regards, El lun., 13 de jul. de 2020 a la(s) 18:05, Ralph Seichter ( ra...@ml.seichter.de) escribió: > * SysAdmin EM: > > > I use a suppression list where I block domains and email accounts that > > don't exist to prevent the reputation of my IP addresses from going > > down. [...] The table currently has 4 million rows, so I am looking > > for a faster database engine than MySql. > > Can you clarify for me: You try to maintain a database for domains that > do not exist and email accounts that do not exist? How would that work? > Both the number of nonexistent domains and and accounts are, while not > infinite, huge. Why not list the domains and accounts that do exist? > These numbers are definitely finite. > > As for your choice of database: I am a fan of MongoDB because of its > capability of storing unstructured data, but here your data does have a > well-defined, uniform structure. If you use a proper index, MySQL or > similar relational databases should be quick as blazes. > > -Ralph >
