On Wed, February 15, 2012 6:08 pm, Brian Evans - Postfix List wrote: > On 2/15/2012 11:57 AM, Jack Knowlton wrote: >> On Wed, February 15, 2012 5:37 pm, /dev/rob0 wrote: >>> On Wed, Feb 15, 2012 at 03:50:00PM +0100, Jack Knowlton wrote: >>>> I'm looking into implementing a check_recipient_access as a table >>>> inside a MySQL database. It's basically a list of users that have >>>> been banned from the system and for whom I don't want a simple 550 >>>> user unknown bounce. Currently the list is a flat file hash map: >>>> >>>> u...@domain.com REJECT This particular user has been banned. >>>> us...@domain.com REJECT This particular user has been banned. >>>> >>>> First of all, what should be the table structure? Are "REJECT" and >>>> "This particular user has been banned." two separate fields? >>> You might wish to review these: >>> http://www.postfix.org/SMTPD_ACCESS_README.html >>> http://www.postfix.org/access.5.html >>> >>> The first word is the access(5) action, and the rest of it is the >>> reject message given to the client. >>> >>>> Secondly, what should query performed by postfix look like? >>> Obviously depends on your schema and RDBMS. Here's mine for sqlite: >>> >>> query = SELECT >>> CASE WHEN A1.active!=0 AND A1.rclass IS NOT NULL >>> THEN substr((100 + A1.rclass), 2, 2) >>> ELSE substr((100 + D1.rclass), 2, 2) >>> END >>> FROM "Address" AS A1 >>> JOIN "Domain" AS D1 ON A1.domain=D1.id >>> WHERE A1.localpart IS '%u' AND D1.name IS '%d' >>> result_format = RC%s >>> >>> Returns RCxx where xx is a two-digit number, and where RCxx has been >>> defined in main.cf and also listed in smtpd_restriction_classes. The >>> Address and Domain tables each have a column "rclass" containing an >>> integer in the range 0-99. >>> >>> You can greatly simplify this by listing the whole address and the >>> entire actual restriction as columns in your table. See >>> mysql_table(5) for Postfix-specific hints, and talk to the MySQL >>> people for support of their software. >> >> Thanks! >> >> Do you think this could work > > Yes it can work as long as the FIRST word of `action` is a valid > access(5) return value. > > It is recommended to test with 'postmap -q value > mysql:/path/to/tabledef' before implementing. > > Also, consider using proxy:mysql:/path/to/tabledef in production to > consolidate connections and not overload your database. > http://www.postfix.org/proxymap.8.html >
Very much obliged, it works great! Same thing can be done for check_sender_access, right? And what about storing a CIDR table into a database - is it possible? -JK
