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 > > CREATE TABLE IF NOT EXISTS `recipient_access` ( > `id` int(4) NOT NULL AUTO_INCREMENT, Nit: If you want to reduce the table size, please use smallint. int(4) will only *display* 4 but store 11. > `insdate` timestamp NULL DEFAULT CURRENT_TIMESTAMP, > `address` varchar(64) NOT NULL, > `action` text NOT NULL, > PRIMARY KEY (`id`) > ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; > > with this query: > > dbname = postfix > hosts = 10.0.1.54 > query = SELECT action FROM recipient_access WHERE address='%s' > > ? > > -JK >
