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 CREATE TABLE IF NOT EXISTS `recipient_access` ( `id` int(4) NOT NULL AUTO_INCREMENT, `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
