On szo, jan 12, 2013 at 18:07:56 +0100, Tom Hendrikx wrote: > On 12-01-13 17:39, LEVAI Daniel wrote: > > On szo, jan 12, 2013 at 14:11:12 +0100, Bastian Blank wrote: > >> On Sat, Jan 12, 2013 at 01:51:26PM +0100, LEVAI Daniel wrote: > >>> How should I put this... My question is not in regards to how to store > >>> IP networks (w/ CIDR postfix) in PostgreSQL; this is somewhat given. > >> > >> PostgreSQL handles CIDR with some special functions and operators. See > >> http://www.postgresql.org/docs/9.2/interactive/functions-net.html > > > > The type in PostgreSQL is irrelevant. Ignore it. That is not part of the > > question. It is just a string that Postfix queries. For example > > currently (when querying single IP addresses for check_client_access) > > the column in question is a varchar... > > > > It is relevant. client_client_access is an access table. Postfix does > not query the table for an ip adress, it wants an OK reply for the > queried IP address. Read http://www.postfix.org/access.5.html
Absolutely, I got carried away. > If you configure postfix with a query that checks if the ip address is > in the cidr mask in the database, you're done: > > SELECT 'OK' FROM client_access_table WHERE inet '%s' << inet > client_access_column Understood. I know it's only my responsibility to create an eligible query for Postfix to get back the action codes. Nevertheless, thanks for this great advice, it didn't cross my mind! > I'm not sure if you can cast a varchar column to inet type at query > time. If not, you need to do some more database tricks. That was the easy part, I can cast the text col. to inet/cidr with eg. text::cidr. However, hardcoding the cidr query to the pgsql: map is not that great, because Postfix first tries hostnames, and that would result in an error in the cidr/inet query. So the trickery was in creating a function which can decide which query to run (`cidr <<=' or pure key = '%s'), depending on the input string (being an IP address or a hostname). Thanks for the clue-bats! Daniel -- LÉVAI Dániel PGP key ID = 0x83B63A8F Key fingerprint = DBEC C66B A47A DFA2 792D 650C C69B BE4C 83B6 3A8F
