In postfix, how to implement access rules with an sql query

Question

I have succesfully set up my postfix to lookup sql tables for servicing virtual domains. What I would like to do is REJECT and IMMEDIATELY DISCONNECT any client that:

1.attempts MAIL FROM: with a domain matching any of my virtual domains

AND

2.is not a local client (connection comes from my_networks).

(Legit users of my domains that want to send mail, use 587 submission service with other rules)

Point 2 is done by beginning smtpd_sender_restrictions with permit_mynetworks. Then I want to append check_sender_access = ????

I dont know what I should put in ???? to consult an sql query. I know how to do it with an access hash table.I dont want to use the hash , because the valid domains are already in SQL and anytime I add a domain, I dont want to have to update the access hash table.

The problem is that I should either REJECT the mail sender if the sql query returns any value, or OK the mail sender if the query returns no rows. I dont know how to express that pattern - action using sql.

Thanks.

Answer 1

You should be able to achieve that using this:

smtpd_sender_restrictions = permit_mynetworks
                            reject_sender_login_mismatch

This will allow any MAIL FROM only from $mynetworks or if the client is logged in and listed as the owner of the MAIL FROM via smtpd_sender_login_maps. You can then for example create a "fake" lookup-table via mysql that always returns a non-existing SASL loginname for your virtual-domains.

In main.cf

smtpd_sender_login_maps = proxy:mysql:/etc/postfix/mysql-sender-login-maps.cf

and mysql-sender-login-maps.cf like this

user = database_user
password = users_password
query = SELECT 'NON_EXISTING_SASL_LOGINNAME' FROM maildb.mail_users WHERE email='%s' UNION SELECT 'NON_EXISTING_SASL_LOGINNAME' FROM maildb.mail_domains WHERE CONCAT('@', domain) = '%s'

You need Postfix 2.2 or above for that, else you'll have to build this using db_name, table and select_field in mysql-sender-login-maps.cf. You'll need to return that only for existing MAIL FROM addresses and domains else noone will be able to deliver mail to your server at all.

Answer 2

Maybe you want to look at http://workaround.org/ispmail/squeeze/postfix-database-configuration . This is part of a bigger howto, building a databasecentric mailserver.