Unfortunately postgresql support patch for Postfix doesn't support unix domain sockets. using postgresql on sockets instead of ip address could give some security improvements in some situations. here i'm attaching a patch which I created rediffing postfix-2.0.13 (but it works also on 14 and 15) plus the current postgresql patch against Debian's postfix-2.0.14-1 as of 2003/08/13 (which includes sockets support). HTH alessandro Reproducible: Always Steps to Reproduce: 1. 2. 3.
Created attachment 17689 [details] postfix pgsql support patch for sockets
bugzilla gives me errors trying to upload the attachment, so i'm adding it there. p.s.: the patches postfix (I used 2.0.15-r1 plus my patch) compiles successfully on gentoo 1.4 ---patch start--- diff -urN save/README_FILES/PGSQL_README postfix-2.0.13/README_FILES/PGSQL_README --- save/README_FILES/PGSQL_README 2003-09-14 13:37:46.000000000 +0200 +++ postfix-2.0.13/README_FILES/PGSQL_README 2003-04-15 17:28:51.000000000 +0200 @@ -1,25 +1,46 @@ -[Code contributed by Mathieu Arnold] +PostgreSQL map type for Postfix. Currently this code is maintained +by LaMont Jones, <lamont@hp.com>. -We've written code to add a pgsql map type. It utilizes the pgsql -client library, which can be obtained from: +This implementation allows for multiple pgsql databases: you can +use one for a virtual table, one for an access table, and one for +an aliases table if you want. - http://www.postgresql.org/ +You can specify multiple servers for the same database, so that +Postfix can switch to a good database server if one goes bad. -In order to build postfix with pgsql map support, you will need to add --DHAS_PGSQL and -I for the directory containing the postgres headers, and -the libpq library (and libcrypt) to AUXLIBS, for example: +Performance of postfix with pgsql has not been thoroughly tested, +however, we have found it to be stable. Busy mail servers using +pgsql maps will generate lots of concurrent pgsql clients, so the +pgsql server(s) should be run with this fact in mind. Any further +performance information, in addition to any feedback is most welcome. + +This is based upon code written by Scott Cotton and Joshua Marcus, +IC Group, Inc. The PostgreSQL changes were done by Aaron Sethman +<androsyn@ratbox.org>. Updates for Postfix 1.1.x and PostgreSQL +7.1+, and support for calling stored procedures were added by Philip +Warner (pjw@rhyme.com.au). + +Building Postfix with PostgreSQL support +======================================== + +To use pgsql with Postfix on Debian GNU/Linux, you must install +the postfix-pgsql package. -make -f Makefile.init makefiles \ - 'CCARGS=-DHAS_PGSQL -I/some/where/include/postgresql' \ - 'AUXLIBS=/some/where/lib/postgres/libpq.a -lcrypt' +In order to build Postfix with pgsql map support, you specify +-DHAS_PGSQL, the directory with the PostgreSQL header files, and +the location of the libpq library file. -then, just run 'make'. +For example: -Postfix installations which may benefit from using pgsql map types -include sites that have a need for instantaneous updates of -forwarding, and sites that may benefit from having mail exchangers -reference a networked database, possibly working in conjunction with a -customer database of sorts. + make tidy + make -f Makefile.init makefiles \ + 'CCARGS=-DHAS_PGSQL -I/usr/local/include/pgsql' \ + 'AUXLIBS=-L/usr/local/lib -lpq' + +Then just run 'make'. + +Configuring PostgreSQL lookup tables +==================================== Once postfix is built with pgsql support, you can specify a map type in main.cf like this: @@ -27,17 +48,17 @@ alias_maps = pgsql:/etc/postfix/pgsql-aliases.cf The file /etc/postfix/pgsql-aliases.cf specifies lots of information -telling postfix how to reference the postgresql database. An example -postgresql map config file follows: +telling postfix how to reference the pgsql database. An example +pgsql map config file follows: # -# postgresql config file for alias lookups on postfix +# pgsql config file for alias lookups on postfix # comments are ok. # # the user name and password to log into the pgsql server user = someone -password = some_passwordd +password = some_password # the database name on the servers dbname = customer_database @@ -45,44 +66,58 @@ # the table name table = mxaliases -# +# these should be obvious :-) select_field = forw_addr where_field = alias # you may specify additional_conditions here additional_conditions = and status = 'paid' -# the above variables will result in a query of -# the form: +# the above variables will result in a query of the form: +# # select forw_addr from mxaliases where alias = '$lookup' and status = 'paid' +# # ($lookup is escaped so if it contains single quotes or other odd # characters, it will not cause a parse error in the sql). + +# If you just want to use a PostgreSQL function, you can ignore the +# table name, select_field, where_field and additional_conditions, +# and just specify a database function to call: + +#select_function = my_lookup_user_alias + +# this will result in "select my_lookup_user_alias('name')" being +# used as the SQL statement to execute. If select_function is specified +# the table-related fields above will be ignored. +# +# As of 25-Jun-2002, if the function returns a single row and a single +# column AND that value is NULL, then the result will be treated as +# if the key was not in the dictionary. +# +# Future versions of PG will allow functions to return result sets. +# + # # the hosts that postfix will try to connect to # and query from (in the order listed) -hosts = host1.some.domain host2.some.domain - -# end postgresql config file +# specify unix: for unix-domain sockets, inet: for TCP connections (default) +hosts = host1.some.domain host2.some.domain unix:/file/name -Some notes: +# end pgsql config file -This configuration interface setup allows for multiple postgresql -databases: you can use one for a virtual table, one for an access -table, and one for an aliases table if you want. - -Since sites that have a need for multiple mail exchangers may enjoy -the convenience of using a networked mailer database, but do not want -to introduce a single point of failure to their system, we've included -the ability to have postfix reference multiple hosts for access to a -single pgsql map. This will work if sites set up mirrored pgsql -databases on two or more hosts. Whenever queries fail with an error -at one host, the rest of the hosts will be tried in order. Each host -that is in an error state will undergo a reconnection attempt every so -often, and if no pgsql server hosts are reachable, then mail will be -deferred until atleast one of those hosts is reachable. +Using mirrored databases +======================== -Performance of postfix with pgsql has not been thoroughly tested, -however, we have found it to be stable. Busy mail servers using pgsql -maps will generate lots of concurrent pgsql clients, so the pgsql -server(s) should be run with this fact in mind. Any further -performance information, in addition to any feedback is most welcome. +Sites that have a need for multiple mail exchangers may enjoy the +convenience of using a networked mailer database, but do not want +to introduce a single point of failure to their system. + +For this reason we've included the ability to have Postfix reference +multiple hosts for access to a single pgsql map. This will work +if sites set up mirrored pgsql databases on two or more hosts. + +Whenever queries fail with an error at one host, the rest of the +hosts will be tried in order. Each host that is in an error state +will undergo a reconnection attempt every so often, and if no pgsql +server hosts are reachable, then mail will be deferred until at +least one of those hosts is reachable. diff -urN save/src/util/dict_pgsql.c postfix-2.0.13/src/util/dict_pgsql.c --- save/src/util/dict_pgsql.c 2003-09-14 13:37:46.000000000 +0200 +++ postfix-2.0.13/src/util/dict_pgsql.c 2003-07-28 23:50:11.000000000 +0200 @@ -1,45 +1,46 @@ - /*++ /* NAME /* dict_pgsql 3 /* SUMMARY -/* dictionary manager interface to db files +/* dictionary manager interface to Postgresql files /* SYNOPSIS -/* #include <dict.h> /* #include <dict_pgsql.h> /* -/* DICT *dict_pgsql_open(name, dummy, unused_dict_flags) -/* const char *name; -/* int dummy; -/* int unused_dict_flags; +/* DICT *dict_pgsql_open(name, open_flags, dict_flags) +/* const char *name; +/* int open_flags; +/* int dict_flags; /* DESCRIPTION -/* dict_pgsql_open() creates a dictionary of type 'pg'. This +/* dict_pgsql_open() creates a dictionary of type 'pgsql'. This /* dictionary is an interface for the postfix key->value mappings /* to pgsql. The result is a pointer to the installed dictionary, /* or a null pointer in case of problems. /* -/* The pgsql dictionary can manage multiple connections to different -/* sql servers on different hosts. It assumes that the underlying data -/* on each host is identical (mirrored) and maintains one connection -/* at any given time. If any connection fails, any other available -/* ones will be opened and used. The intent of this feature is to eliminate -/* a single point of failure for mail systems that would otherwise rely -/* on a single pgsql server. +/* The pgsql dictionary can manage multiple connections to +/* different sql servers for the same database. It assumes that +/* the underlying data on each server is identical (mirrored) and +/* maintains one connection at any given time. If any connection +/* fails, any other available ones will be opened and used. +/* The intent of this feature is to eliminate a single point of +/* failure for mail systems that would otherwise rely on a single +/* pgsql server. /* /* Arguments: /* .IP name -/* The path of the PostgreSQL configuration file. The file encodes a number of -/* pieces of information: username, password, databasename, table, -/* select_field, where_field, and hosts. For example, if you want the map to -/* reference databases of the name "your_db" and execute a query like this: -/* select forw_addr from aliases where alias like '<some username>' against -/* any database called "vmailer_info" located on hosts host1.some.domain and -/* host2.some.domain, logging in as user "vmailer" and password "passwd" then -/* the configuration file should read: +/* The path of the PostgreSQL configuration file. The file +/* encodes number of pieces of information: username, password, +/* databasename, table, select_field, where_field, and hosts. +/* For example, if you want the map to reference databases of +/* the name "your_db" and execute a query like this: select +/* forw_addr from aliases where alias like '<some username>' +/* against any database called "postfix_info" located on hosts +/* host1.some.domain and host2.some.domain, logging in as user +/* "postfix" and password "passwd" then the configuration file +/* should read: /* -/* user = vmailer +/* user = postfix /* password = passwd -/* DBname = vmailer_info +/* DBname = postfix_info /* table = aliases /* select_field = forw_addr /* where_field = alias @@ -47,17 +48,18 @@ /* /* .IP other_name /* reference for outside use. -/* .IP unusued_flags -/* unused flags +/* .IP open_flags +/* Must be O_RDONLY. +/* .IP dict_flags +/* See dict_open(3). /* SEE ALSO /* dict(3) generic dictionary manager /* AUTHOR(S) -/* Mathieu Arnold -/* Absolight -/* mat@absolight.com -/* -/* based on dict_mysql by -/* +/* Aaron Sethman +/* androsyn@ratbox.org +/* +/* Based upon dict_mysql.c by +/* /* Scott Cotton /* IC Group, Inc. /* scott@icgroup.com @@ -80,6 +82,8 @@ #include <stdlib.h> #include <syslog.h> #include <time.h> + +#include <postgres_ext.h> #include <libpq-fe.h> /* Utility library. */ @@ -92,29 +96,30 @@ #include "split_at.h" #include "find_inet.h" -/* need some structs to help organize things */ -typedef struct -{ +#define STATACTIVE 0 +#define STATFAIL 1 +#define STATUNTRIED 2 +#define RETRY_CONN_INTV 60 /* 1 minute */ + +typedef struct { PGconn *db; char *hostname; int stat; /* STATUNTRIED | STATFAIL | STATCUR */ - time_t ts; /* used for attempting reconnection - * every so often if a host is down */ + time_t ts; /* used for attempting reconnection */ } HOST; -typedef struct -{ +typedef struct { int len_hosts; /* number of hosts */ - HOST *db_hosts; /* the hosts on which the databases - * reside */ + HOST *db_hosts; /* hosts on which databases reside */ } PLPGSQL; -typedef struct -{ +typedef struct { char *username; char *password; char *dbname; char *table; + char *query; /* if set, overrides fields, etc */ + char *select_function; char *select_field; char *where_field; char *additional_conditions; @@ -122,45 +127,132 @@ int len_hosts; } PGSQL_NAME; -typedef struct -{ +typedef struct { DICT dict; PLPGSQL *pldb; PGSQL_NAME *name; } DICT_PGSQL; -#define STATACTIVE 0 -#define STATFAIL 1 -#define STATUNTRIED 2 -#define RETRY_CONN_INTV 60 /* 1 minute */ -/* internal function declarations */ -static PLPGSQL *plpgsql_init (char *hostnames[], int); -static PGresult *plpgsql_query (PLPGSQL *, const char *, char *, char *, +/* Just makes things a little easier for me.. */ +#define PGSQL_RES PGresult - char *); -static void plpgsql_dealloc (PLPGSQL *); -static void plpgsql_down_host (HOST *); -static void plpgsql_connect_single (HOST *, char *, char *, char *); -static int plpgsql_ready_reconn (HOST *); -static const char *dict_pgsql_lookup (DICT *, const char *); -DICT *dict_pgsql_open (const char *, int, int); -static void dict_pgsql_close (DICT *); -static PGSQL_NAME *pgsqlname_parse (const char *); -static HOST host_init (char *); -void pgsql_escape_string (char *escaped, const char *name, int len); +/* internal function declarations */ +static PLPGSQL *plpgsql_init(char *hostnames[], int); +static PGSQL_RES *plpgsql_query(PLPGSQL *, const char *, char *, char *, char *); +static void plpgsql_dealloc(PLPGSQL *); +static void plpgsql_close_host(HOST *); +static void plpgsql_down_host(HOST *); +static void plpgsql_connect_single(HOST *, char *, char *, char *); +static const char *dict_pgsql_lookup(DICT *, const char *); +DICT *dict_pgsql_open(const char *, int, int); +static void dict_pgsql_close(DICT *); +static PGSQL_NAME *pgsqlname_parse(const char *); +static HOST host_init(char *); /********************************************************************** * public interface dict_pgsql_lookup * find database entry return 0 if no alias found, set dict_errno - * on errors to DICT_ERRBO_RETRY and set dict_errno to 0 on success + * on errors to DICT_ERROR_RETRY and set dict_errno to 0 on success *********************************************************************/ -static const char *dict_pgsql_lookup (DICT *dict, const char *name) +static void pgsql_escape_string(char *new, const char *old, unsigned int len) +{ + unsigned int x, + y; + + /* + * XXX We really should be using an escaper that is provided by the PGSQL + * library. The code below seems to be over-kill (see RUS-CERT Advisory + * 2001-08:01), but it's better to be safe than to be sorry -- Wietse + */ + for (x = 0, y = 0; x < len; x++, y++) { + switch (old[x]) { + case '\n': + new[y++] = '\\'; + new[y] = 'n'; + break; + case '\r': + new[y++] = '\\'; + new[y] = 'r'; + break; + case '\'': + new[y++] = '\\'; + new[y] = '\''; + break; + case '"': + new[y++] = '\\'; + new[y] = '"'; + break; + case 0: + new[y++] = '\\'; + new[y] = '0'; + break; + default: + new[y] = old[x]; + break; + } + } + new[y] = 0; +} + +/* + * expand a filter (lookup or result) + */ +static void dict_pgsql_expand_filter(char *filter, char *value, VSTRING *out) +{ + char *myname = "dict_pgsql_expand_filter"; + char *sub, + *end; + + /* + * Yes, replace all instances of %s with the address to look up. Replace + * %u with the user portion, and %d with the domain portion. + */ + sub = filter; + end = sub + strlen(filter); + while (sub < end) { + + /* + * Make sure it's %[sud] and not something else. For backward + * compatibilty, treat anything other than %u or %d as %s, with a + * warning. + */ + if (*(sub) == '%') { + char *u = value; + char *p = strrchr(u, '@'); + + switch (*(sub + 1)) { + case 'd': + if (p) + vstring_strcat(out, p + 1); + break; + case 'u': + if (p) + vstring_strncat(out, u, p - u); + else + vstring_strcat(out, u); + break; + default: + msg_warn + ("%s: Invalid filter substitution format '%%%c'!", + myname, *(sub + 1)); + break; + case 's': + vstring_strcat(out, u); + break; + } + sub++; + } else + vstring_strncat(out, sub, 1); + sub++; + } +} + +static const char *dict_pgsql_lookup(DICT *dict, const char *name) { - PGresult *query_res; - char *field; + PGSQL_RES *query_res; DICT_PGSQL *dict_pgsql; PLPGSQL *pldb; static VSTRING *result; @@ -169,154 +261,163 @@ j, numrows; char *name_escaped = 0; + int isFunctionCall; + int numcols; dict_pgsql = (DICT_PGSQL *) dict; pldb = dict_pgsql->pldb; /* initialization for query */ - query = vstring_alloc (24); - vstring_strcpy (query, ""); - if ( - (name_escaped = - (char *) mymalloc ((sizeof (char) * (strlen (name) * 2) + 1))) == - NULL) - { - msg_fatal ("dict_pgsql_lookup: out of memory."); - } + query = vstring_alloc(24); + vstring_strcpy(query, ""); + if ((name_escaped = (char *) mymalloc((sizeof(char) * (strlen(name) * 2) +1))) == NULL) { + msg_fatal("dict_pgsql_lookup: out of memory."); + } /* prepare the query */ - pgsql_escape_string (name_escaped, name, (unsigned int) strlen (name)); - vstring_sprintf (query, "select %s from %s where %s = '%s' %s", - dict_pgsql->name->select_field, dict_pgsql->name->table, - dict_pgsql->name->where_field, name_escaped, - dict_pgsql->name->additional_conditions); + pgsql_escape_string(name_escaped, name, (unsigned int) strlen(name)); + + /* Build SQL - either a select from table or select a function */ + + isFunctionCall = (dict_pgsql->name->select_function != NULL); + if (isFunctionCall) { + vstring_sprintf(query, "select %s('%s')", + dict_pgsql->name->select_function, + name_escaped); + } else if (dict_pgsql->name->query) { + dict_pgsql_expand_filter(dict_pgsql->name->query, name_escaped, query); + } else { + vstring_sprintf(query, "select %s from %s where %s = '%s' %s", dict_pgsql->name->select_field, + dict_pgsql->name->table, + dict_pgsql->name->where_field, + name_escaped, + dict_pgsql->name->additional_conditions); + } + if (msg_verbose) - msg_info ("dict_pgsql_lookup using sql query: %s", - vstring_str (query)); + msg_info("dict_pgsql_lookup using sql query: %s", vstring_str(query)); + /* free mem associated with preparing the query */ - myfree (name_escaped); + myfree(name_escaped); + /* do the query - set dict_errno & cleanup if there's an error */ - if ((query_res = plpgsql_query (pldb, - vstring_str (query), - dict_pgsql->name->dbname, - dict_pgsql->name->username, - dict_pgsql->name->password)) == 0) - { - dict_errno = DICT_ERR_RETRY; - vstring_free (query); - return 0; - } + if ((query_res = plpgsql_query(pldb, + vstring_str(query), + dict_pgsql->name->dbname, + dict_pgsql->name->username, + dict_pgsql->name->password)) == 0) { + dict_errno = DICT_ERR_RETRY; + vstring_free(query); + return 0; + } dict_errno = 0; /* free the vstring query */ - vstring_free (query); - numrows = PQntuples (query_res); + vstring_free(query); + numrows = PQntuples(query_res); if (msg_verbose) - msg_info ("dict_pgsql_lookup: retrieved %d rows", numrows); - if (numrows == 0) - { - PQclear (query_res); - return 0; - } + msg_info("dict_pgsql_lookup: retrieved %d rows", numrows); + if (numrows == 0) { + PQclear(query_res); + return 0; + } + numcols = PQnfields(query_res); + + if (numcols == 1 && numrows == 1 && isFunctionCall) { + + /* + * We do the above check because PostgreSQL 7.3 will allow functions + * to return result sets + */ + if (PQgetisnull(query_res, 0, 0) == 1) { + + /* + * Functions returning a single row & column that is null are + * deemed to have not found the key. + */ + PQclear(query_res); + return 0; + } + } if (result == 0) - result = vstring_alloc (10); - vstring_strcpy (result, ""); - for (i = 0; i < numrows; i++) - { - if (i > 0) - vstring_strcat (result, ","); - for (j = 0; j < PQnfields (query_res); j++) - { - if (j > 0) - vstring_strcat (result, ","); - field = PQgetvalue (query_res, i, j); - vstring_strcat (result, field); - if (msg_verbose > 1) - msg_info ("dict_pgsql_lookup: retrieved field: %d: %s", j, - field); - } - } - PQclear (query_res); - return vstring_str (result); + result = vstring_alloc(10); + + vstring_strcpy(result, ""); + for (i = 0; i < numrows; i++) { + if (i > 0) + vstring_strcat(result, ","); + for (j = 0; j < numcols; j++) { + if (j > 0) + vstring_strcat(result, ","); + vstring_strcat(result, PQgetvalue(query_res, i, j)); + if (msg_verbose > 1) + msg_info("dict_pgsql_lookup: retrieved field: %d: %s", j, PQgetvalue(query_res, i, j)); + } + } + PQclear(query_res); + return vstring_str(result); } /* - * plpgsql_query - process a PGSQL query. Return PGresult* on success. + * plpgsql_query - process a PostgreSQL query. Return PGSQL_RES* on success. * On failure, log failure and try other db instances. * on failure of all db instances, return 0; * close unnecessary active connections */ -static PGresult *plpgsql_query (PLPGSQL * PLDB, - const char *query, - char *dbname, char *username, char *password) +static PGSQL_RES *plpgsql_query(PLPGSQL *PLDB, + const char *query, + char *dbname, + char *username, + char *password) { int i; HOST *host; - PGresult *res = 0; - ExecStatusType status; + PGSQL_RES *res = 0; - for (i = 0; i < PLDB->len_hosts; i++) - { - /* can't deal with typing or reading PLDB->db_hosts[i] over & over */ - host = &(PLDB->db_hosts[i]); - if (msg_verbose > 1) - msg_info ("dict_pgsql: trying host %s stat %d, last res %p", - host->hostname, host->stat, res); - - /* answer already found */ - if (res != 0 && host->stat == STATACTIVE) - { - if (msg_verbose) - msg_info - ("dict_pgsql: closing unnessary connection to %s", + for (i = 0; i < PLDB->len_hosts; i++) { + /* can't deal with typing or reading PLDB->db_hosts[i] over & over */ + host = &(PLDB->db_hosts[i]); + if (msg_verbose > 1) + msg_info("dict_pgsql: trying host %s stat %d, last res %p", host->hostname, host->stat, res); + + /* answer already found */ + if (res != 0 && host->stat == STATACTIVE) { + if (msg_verbose) + msg_info("dict_pgsql: closing unnessary connection to %s", host->hostname); - plpgsql_down_host (host); - } - /* try to connect for the first time if we don't have a result yet */ - if (res == 0 && host->stat == STATUNTRIED) - { - if (msg_verbose) - msg_info ("dict_pgsql: attempting to connect to host %s", - host->hostname); - plpgsql_connect_single (host, dbname, username, password); - } + plpgsql_close_host(host); + } + /* try to connect for the first time if we don't have a result yet */ + if (res == 0 && host->stat == STATUNTRIED) { + if (msg_verbose) + msg_info("dict_pgsql: attempting to connect to host %s", + host->hostname); + plpgsql_connect_single(host, dbname, username, password); + } - /* - * try to reconnect if we don't have an answer and the host had a - * prob in the past and it's time for it to reconnect - */ - if (res == 0 && host->stat == STATFAIL - && host->ts < time ((time_t *) 0)) - { - if (msg_verbose) - msg_info - ("dict_pgsql: attempting to reconnect to host %s", + /* + * try to reconnect if we don't have an answer and the host had a + * prob in the past and it's time for it to reconnect + */ + if (res == 0 && host->stat == STATFAIL && host->ts < time((time_t *) 0)) { + if (msg_verbose) + msg_info("dict_pgsql: attempting to reconnect to host %s", host->hostname); - plpgsql_connect_single (host, dbname, username, password); - } + plpgsql_connect_single(host, dbname, username, password); + } - /* - * if we don't have a result and the current host is marked active, - * try the query. If the query fails, mark the host STATFAIL - */ - if (res == 0 && host->stat == STATACTIVE) - { - res = PQexec (host->db, query); - status = PQresultStatus (res); - if (res - && (status = PGRES_COMMAND_OK - || status == PGRES_TUPLES_OK)) - { - if (msg_verbose) - msg_info - ("dict_pgsql: successful query from host %s", - host->hostname); - } - else - { - msg_warn ("%s", PQerrorMessage (host->db)); - plpgsql_down_host (host); - } + /* + * if we don't have a result and the current host is marked active, + * try the query. If the query fails, mark the host STATFAIL + */ + if (res == 0 && host->stat == STATACTIVE) { + if ((res = PQexec(host->db, query))) { + if (msg_verbose) + msg_info("dict_pgsql: successful query from host %s", host->hostname); + } else { + msg_warn("%s", PQerrorMessage(host->db)); + plpgsql_down_host(host); } - } + } + } return res; } @@ -325,78 +426,63 @@ * used to reconnect to a single database when one is down or none is * connected yet. Log all errors and set the stat field of host accordingly */ -static void -plpgsql_connect_single (HOST *host, char *dbname, char *username, - char *password) +static void plpgsql_connect_single(HOST *host, char *dbname, char *username, char *password) { char *destination = host->hostname; + char *unix_socket = 0; char *hostname = 0; char *service; - VSTRING *conninfo = vstring_alloc (100); - char *conn; - unsigned port = 0; + char *port = 0; /* - * Ad-hoc parsing code. Expect "unix:pathname" or "inet:host:port", where + * Ad-hoc parsing code. Expect "unix:pathname" or "inet:host:port", where * both "inet:" and ":port" are optional. */ - if (strncmp (destination, "unix:", 5) == 0) - { - vstring_sprintf_append (conninfo, "host=%s ", destination); - } - else - { - if (strncmp (destination, "inet:", 5) == 0) - destination += 5; - hostname = mystrdup (destination); - if ((service = split_at (hostname, ':')) != 0) - { - port = ntohs (find_inet_port (service, "tcp")); - vstring_sprintf_append (conninfo, "host='%s' port='%d'", - hostname, port); - } - else - { - vstring_sprintf_append (conninfo, "host='%s'", hostname); - } - } - - vstring_sprintf_append (conninfo, " dbname='%s' user='%s' password='%s'", dbname, - username, password); - conn = vstring_export (conninfo); - - host->db = PQconnectdb (conn); - - if ((host->db != NULL) && (PQstatus (host->db) == CONNECTION_OK)) - { - if (msg_verbose) - msg_info ("dict_pgsql: successful connection to host %s", - host->hostname); - host->stat = STATACTIVE; - } - else - { - msg_warn ("%s", PQerrorMessage (host->db)); - plpgsql_down_host (host); - } + if (strncmp(destination, "unix:", 5) == 0) { + unix_socket = destination + 5; + } else { + if (strncmp(destination, "inet:", 5) == 0) + destination += 5; + hostname = mystrdup(destination); + if ((service = split_at(hostname, ':')) != 0) + port = service; + } + + if ((host->db = PQsetdbLogin(hostname, port, NULL, NULL, dbname, username, password))) { + if (PQstatus(host->db) == CONNECTION_OK) { + if (msg_verbose) + msg_info("dict_pgsql: successful connection to host %s", + host->hostname); + host->stat = STATACTIVE; + } else + msg_warn("%s", PQerrorMessage(host->db)); + } else { + msg_warn("Unable to connect to database"); + plpgsql_down_host(host); + } if (hostname) - myfree (hostname); - myfree (conn); + myfree(hostname); +} + +/* plpgsql_close_host - close an established PostgreSQL connection */ + +static void plpgsql_close_host(HOST *host) +{ + PQfinish(host->db); + host->db = 0; + host->stat = STATUNTRIED; } /* - * plpgsql_down_host - mark a HOST down update ts if marked down - * for the first time so that we'll know when to retry the connection + * plpgsql_down_host - close a failed connection AND set a "stay away from + * this host" timer. */ -static void plpgsql_down_host (HOST *host) +static void plpgsql_down_host(HOST *host) { - if (host->stat != STATFAIL) - { - host->ts = time ((time_t *) 0) + RETRY_CONN_INTV; - host->stat = STATFAIL; - } - PQfinish (host->db); + PQfinish(host->db); host->db = 0; + host->ts = time((time_t *) 0) + RETRY_CONN_INTV; + host->stat = STATFAIL; } /********************************************************************** @@ -405,33 +491,38 @@ * parse the map's config file * allocate memory **********************************************************************/ -DICT *dict_pgsql_open (const char *name, int unused_open_flags, - int dict_flags) +DICT *dict_pgsql_open(const char *name, int open_flags, int dict_flags) { DICT_PGSQL *dict_pgsql; - int connections; - dict_pgsql = (DICT_PGSQL *) dict_alloc (DICT_TYPE_PGSQL, name, - sizeof (DICT_PGSQL)); + /* + * Sanity checks. + */ + if (open_flags != O_RDONLY) + msg_fatal("%s:%s map requires O_RDONLY access mode", + DICT_TYPE_PGSQL, name); + + dict_pgsql = (DICT_PGSQL *) dict_alloc(DICT_TYPE_PGSQL, name, + sizeof(DICT_PGSQL)); dict_pgsql->dict.lookup = dict_pgsql_lookup; dict_pgsql->dict.close = dict_pgsql_close; + dict_pgsql->name = pgsqlname_parse(name); + dict_pgsql->pldb = plpgsql_init(dict_pgsql->name->hostnames, + dict_pgsql->name->len_hosts); dict_pgsql->dict.flags = dict_flags | DICT_FLAG_FIXED; - dict_pgsql->name = pgsqlname_parse (name); - dict_pgsql->pldb = plpgsql_init (dict_pgsql->name->hostnames, - dict_pgsql->name->len_hosts); if (dict_pgsql->pldb == NULL) - msg_fatal ("couldn't intialize pldb!\n"); - dict_register (name, (DICT *) dict_pgsql); - return (DICT_DEBUG (&dict_pgsql->dict)); + msg_fatal("couldn't intialize pldb!\n"); + dict_register(name, (DICT *) dict_pgsql); + return &dict_pgsql->dict; } /* pgsqlname_parse - parse pgsql configuration file */ -static PGSQL_NAME *pgsqlname_parse (const char *pgsqlcf_path) +static PGSQL_NAME *pgsqlname_parse(const char *pgsqlcf_path) { int i; char *nameval; char *hosts; - PGSQL_NAME *name = (PGSQL_NAME *) mymalloc (sizeof (PGSQL_NAME)); + PGSQL_NAME *name = (PGSQL_NAME *) mymalloc(sizeof(PGSQL_NAME)); ARGV *hosts_argv; VSTRING *opt_dict_name; @@ -442,132 +533,125 @@ * different paths, or the configuration info will cache across different * pgsql maps, which can be confusing. */ - opt_dict_name = vstring_alloc (64); - vstring_sprintf (opt_dict_name, "pgsql opt dict %s", pgsqlcf_path); - dict_load_file (vstring_str (opt_dict_name), pgsqlcf_path); + opt_dict_name = vstring_alloc(64); + vstring_sprintf(opt_dict_name, "pgsql opt dict %s", pgsqlcf_path); + dict_load_file(vstring_str(opt_dict_name), pgsqlcf_path); /* pgsql username lookup */ - if ( - (nameval = - (char *) dict_lookup (vstring_str (opt_dict_name), "user")) == NULL) - name->username = mystrdup (""); + if ((nameval = (char *) dict_lookup(vstring_str(opt_dict_name), "user")) == NULL) + name->username = mystrdup(""); else - name->username = mystrdup (nameval); + name->username = mystrdup(nameval); if (msg_verbose) - msg_info ("pgsqlname_parse(): set username to '%s'", name->username); + msg_info("pgsqlname_parse(): set username to '%s'", name->username); /* password lookup */ - if ( - (nameval = - (char *) dict_lookup (vstring_str (opt_dict_name), - "password")) == NULL) - name->password = mystrdup (""); + if ((nameval = (char *) dict_lookup(vstring_str(opt_dict_name), "password")) == NULL) + name->password = mystrdup(""); else - name->password = mystrdup (nameval); + name->password = mystrdup(nameval); if (msg_verbose) - msg_info ("pgsqlname_parse(): set password to '%s'", name->password); + msg_info("pgsqlname_parse(): set password to '%s'", name->password); /* database name lookup */ - if ( - (nameval = - (char *) dict_lookup (vstring_str (opt_dict_name), - "dbname")) == NULL) - - - - msg_fatal ("%s: pgsql options file does not include database name", - pgsqlcf_path); + if ((nameval = (char *) dict_lookup(vstring_str(opt_dict_name), "dbname")) == NULL) + msg_fatal("%s: pgsql options file does not include database name", pgsqlcf_path); else - name->dbname = mystrdup (nameval); + name->dbname = mystrdup(nameval); if (msg_verbose) - msg_info ("pgsqlname_parse(): set database name to '%s'", - name->dbname); + msg_info("pgsqlname_parse(): set database name to '%s'", name->dbname); /* table lookup */ - if ( - (nameval = - (char *) dict_lookup (vstring_str (opt_dict_name), "table")) == NULL) - msg_fatal ("%s: pgsql options file does not include table name", - pgsqlcf_path); + if ((nameval = (char *) dict_lookup(vstring_str(opt_dict_name), "table")) == NULL) + msg_fatal("%s: pgsql options file does not include table name", pgsqlcf_path); else - name->table = mystrdup (nameval); + name->table = mystrdup(nameval); if (msg_verbose) - msg_info ("pgsqlname_parse(): set table name to '%s'", name->table); - - /* select field lookup */ - if ( - (nameval = - (char *) dict_lookup (vstring_str (opt_dict_name), - "select_field")) == NULL) + msg_info("pgsqlname_parse(): set table name to '%s'", name->table); + name->select_function = NULL; + name->query = NULL; + /* + * See what kind of lookup we have - a traditional 'select' or a function + * call + */ + if ((nameval = (char *) dict_lookup(vstring_str(opt_dict_name), "select_function")) != NULL) { - msg_fatal ("%s: pgsql options file does not include select field", - pgsqlcf_path); - else - name->select_field = mystrdup (nameval); - if (msg_verbose) - msg_info ("pgsqlname_parse(): set select_field to '%s'", - name->select_field); - - /* where field lookup */ - if ( - (nameval = - (char *) dict_lookup (vstring_str (opt_dict_name), - "where_field")) == NULL) - msg_fatal ("%s: pgsql options file does not include where field", - pgsqlcf_path); - else - name->where_field = mystrdup (nameval); - if (msg_verbose) - msg_info ("pgsqlname_parse(): set where_field to '%s'", - name->where_field); - - /* additional conditions */ - if ( - (nameval = - (char *) dict_lookup (vstring_str (opt_dict_name), - "additional_conditions")) == NULL) - name->additional_conditions = mystrdup (""); - else - name->additional_conditions = mystrdup (nameval); - if (msg_verbose) - msg_info ("pgsqlname_parse(): set additional_conditions to '%s'", - name->additional_conditions); + /* We have a 'select %s(%s)' function call. */ + name->select_function = mystrdup(nameval); + if (msg_verbose) + msg_info("pgsqlname_parse(): set function name to '%s'", name->table); + /* query string */ + } else if ((nameval = (char *) dict_lookup(vstring_str(opt_dict_name), "query")) != NULL) { + name->query = mystrdup(nameval); + if (msg_verbose) + msg_info("pgsqlname_parse(): set query to '%s'", name->query); + } else { + + /* + * We have an old style 'select %s from %s...' call, so get the + * fields + */ + + /* table lookup */ + if ((nameval = (char *) dict_lookup(vstring_str(opt_dict_name), "table")) == NULL) + msg_fatal("%s: pgsql options file does not include table name", pgsqlcf_path); + else + name->table = mystrdup(nameval); + if (msg_verbose) + msg_info("pgsqlname_parse(): set table name to '%s'", name->table); + + /* select field lookup */ + if ((nameval = (char *) dict_lookup(vstring_str(opt_dict_name), "select_field")) == NULL) + msg_fatal("%s: pgsql options file does not include select field", pgsqlcf_path); + else + name->select_field = mystrdup(nameval); + if (msg_verbose) + msg_info("pgsqlname_parse(): set select_field to '%s'", name->select_field); + + /* where field lookup */ + if ((nameval = (char *) dict_lookup(vstring_str(opt_dict_name), "where_field")) == NULL) + msg_fatal("%s: pgsql options file does not include where field", pgsqlcf_path); + else + name->where_field = mystrdup(nameval); + if (msg_verbose) + msg_info("pgsqlname_parse(): set where_field to '%s'", name->where_field); + + /* additional conditions */ + if ((nameval = (char *) dict_lookup(vstring_str(opt_dict_name), "additional_conditions")) == NULL) + name->additional_conditions = mystrdup(""); + else + name->additional_conditions = mystrdup(nameval); + if (msg_verbose) + msg_info("pgsqlname_parse(): set additional_conditions to '%s'", name->additional_conditions); + } /* pgsql server hosts */ - if ( - (nameval = - (char *) dict_lookup (vstring_str (opt_dict_name), "hosts")) == NULL) - hosts = mystrdup (""); + if ((nameval = (char *) dict_lookup(vstring_str(opt_dict_name), "hosts")) == NULL) + hosts = mystrdup(""); else - hosts = mystrdup (nameval); + hosts = mystrdup(nameval); /* coo argv interface */ - hosts_argv = argv_split (hosts, " ,\t\r\n"); + hosts_argv = argv_split(hosts, " ,\t\r\n"); - if (hosts_argv->argc == 0) - { /* no hosts specified, + if (hosts_argv->argc == 0) { /* no hosts specified, * default to 'localhost' */ - if (msg_verbose) - msg_info - ("pgsqlname_parse(): no hostnames specified, defaulting to 'localhost'"); - argv_add (hosts_argv, "localhost", ARGV_END); - argv_terminate (hosts_argv); - } + if (msg_verbose) + msg_info("pgsqlname_parse(): no hostnames specified, defaulting to 'localhost'"); + argv_add(hosts_argv, "localhost", ARGV_END); + argv_terminate(hosts_argv); + } name->len_hosts = hosts_argv->argc; - name->hostnames = - - (char **) mymalloc ((sizeof (char *)) * name->len_hosts); + name->hostnames = (char **) mymalloc((sizeof(char *)) * name->len_hosts); i = 0; - for (i = 0; hosts_argv->argv[i] != NULL; i++) - { - name->hostnames[i] = mystrdup (hosts_argv->argv[i]); - if (msg_verbose) - msg_info - ("pgsqlname_parse(): adding host '%s' to list of pgsql server hosts", - name->hostnames[i]); - } - myfree (hosts); - vstring_free (opt_dict_name); - argv_free (hosts_argv); + for (i = 0; hosts_argv->argv[i] != NULL; i++) { + name->hostnames[i] = mystrdup(hosts_argv->argv[i]); + if (msg_verbose) + msg_info("pgsqlname_parse(): adding host '%s' to list of pgsql server hosts", + name->hostnames[i]); + } + myfree(hosts); + vstring_free(opt_dict_name); + argv_free(hosts_argv); return name; } @@ -576,36 +660,31 @@ * plpgsql_init - initalize a PGSQL database. * Return NULL on failure, or a PLPGSQL * on success. */ -static PLPGSQL *plpgsql_init (char *hostnames[], int len_hosts) +static PLPGSQL *plpgsql_init(char *hostnames[], int len_hosts) { PLPGSQL *PLDB; int i; - HOST host; - if ((PLDB = (PLPGSQL *) mymalloc (sizeof (PLPGSQL))) == NULL) - { - msg_fatal ("mymalloc of pldb failed"); - } + if ((PLDB = (PLPGSQL *) mymalloc(sizeof(PLPGSQL))) == NULL) { + msg_fatal("mymalloc of pldb failed"); + } PLDB->len_hosts = len_hosts; - if ((PLDB->db_hosts = (HOST *) mymalloc (sizeof (HOST) * len_hosts)) - == NULL) + if ((PLDB->db_hosts = (HOST *) mymalloc(sizeof(HOST) * len_hosts)) == NULL) return NULL; - - for (i = 0; i < len_hosts; i++) - { - PLDB->db_hosts[i] = host_init (hostnames[i]); - } + for (i = 0; i < len_hosts; i++) { + PLDB->db_hosts[i] = host_init(hostnames[i]); + } return PLDB; } /* host_init - initialize HOST structure */ -static HOST host_init (char *hostname) +static HOST host_init(char *hostname) { HOST host; host.stat = STATUNTRIED; - host.hostname = mystrdup (hostname); + host.hostname = mystrdup(hostname); host.db = 0; host.ts = 0; return host; @@ -615,61 +694,39 @@ * public interface dict_pgsql_close * unregister, disassociate from database, freeing appropriate memory **********************************************************************/ -static void dict_pgsql_close (DICT *dict) +static void dict_pgsql_close(DICT *dict) { int i; DICT_PGSQL *dict_pgsql = (DICT_PGSQL *) dict; - plpgsql_dealloc (dict_pgsql->pldb); - myfree (dict_pgsql->name->username); - myfree (dict_pgsql->name->password); - myfree (dict_pgsql->name->dbname); - myfree (dict_pgsql->name->table); - myfree (dict_pgsql->name->select_field); - myfree (dict_pgsql->name->where_field); - myfree (dict_pgsql->name->additional_conditions); - for (i = 0; i < dict_pgsql->name->len_hosts; i++) - { - myfree (dict_pgsql->name->hostnames[i]); - } - myfree ((char *) dict_pgsql->name->hostnames); - myfree ((char *) dict_pgsql->name); - dict_free (dict); + plpgsql_dealloc(dict_pgsql->pldb); + myfree(dict_pgsql->name->username); + myfree(dict_pgsql->name->password); + myfree(dict_pgsql->name->dbname); + myfree(dict_pgsql->name->table); + myfree(dict_pgsql->name->select_field); + myfree(dict_pgsql->name->where_field); + myfree(dict_pgsql->name->additional_conditions); + for (i = 0; i < dict_pgsql->name->len_hosts; i++) { + myfree(dict_pgsql->name->hostnames[i]); + } + myfree((char *) dict_pgsql->name->hostnames); + myfree((char *) dict_pgsql->name); + dict_free(dict); } /* plpgsql_dealloc - free memory associated with PLPGSQL close databases */ -static void plpgsql_dealloc (PLPGSQL * PLDB) +static void plpgsql_dealloc(PLPGSQL *PLDB) { int i; - for (i = 0; i < PLDB->len_hosts; i++) - { - if (PLDB->db_hosts[i].db) - PQfinish (PLDB->db_hosts[i].db); - myfree (PLDB->db_hosts[i].hostname); - } - myfree ((char *) PLDB->db_hosts); - myfree ((char *) (PLDB)); -} - -/* pgsql_escape_string - replace mysql_escape_string */ -void pgsql_escape_string (char *escaped, const char *name, int len) -{ - int i, - j; - - for (i = 0, j = 0; i <= len; i++, j++) - { - if ((name[i] == '\'') || (name[i] == '\\')) - { - escaped[j] = '\\'; - j++; - } - escaped[j] = name[i]; - } + for (i = 0; i < PLDB->len_hosts; i++) { + if (PLDB->db_hosts[i].db) + PQfinish(PLDB->db_hosts[i].db); + myfree(PLDB->db_hosts[i].hostname); + } + myfree((char *) PLDB->db_hosts); + myfree((char *) (PLDB)); } - - #endif - diff -urN save/src/util/dict_pgsql.h postfix-2.0.13/src/util/dict_pgsql.h --- save/src/util/dict_pgsql.h 2003-09-14 13:37:46.000000000 +0200 +++ postfix-2.0.13/src/util/dict_pgsql.h 2003-04-14 19:31:46.000000000 +0200 @@ -1,11 +1,11 @@ -#ifndef _DICT_PGSQL_H_INCLUDED_ -#define _DICT_PGSQL_H_INCLUDED_ +#ifndef _DICT_PGSQL_INCLUDED_ +#define _DICT_PGSQL_INCLUDED_ /*++ /* NAME /* dict_pgsql 3h /* SUMMARY -/* dictionary manager interface to pgsql databases +/* dictionary manager interface to Postgresql files /* SYNOPSIS /* #include <dict_pgsql.h> /* DESCRIPTION @@ -19,15 +19,16 @@ /* * External interface. */ -#define DICT_TYPE_PGSQL "pgsql" +#define DICT_TYPE_PGSQL "pgsql" -extern DICT *dict_pgsql_open (const char *, int, int); +extern DICT *dict_pgsql_open(const char *name, int unused_flags, int dict_flags); -/* LICENSE -/* .ad -/* .fi -/* The Secure Mailer license must be distributed with this software. /* AUTHOR(S) +/* Aaron Sethman +/* androsyn@ratbox.org +/* +/* Based upon dict_mysql.c by +/* /* Scott Cotton /* IC Group, Inc. /* scott@icgroup.com @@ -38,4 +39,3 @@ /*--*/ #endif - ---patch end---
i forgot: url for the debian files: http://packages.debian.org/experimental/mail/postfix-pgsql.html
Have you tried configuring your postgresql map with a setting like the following? hosts = unix:/path/to/socket This is supposed to work according to the code. And I'm pretty sure all the postgresql patches stem from the same core. Please try it and report back.
Max: uhm...indeed it seems that the postfix-pgsql-2.0.0.2 patch already included in the postfix ebuild support unix sokects despite my notice of the contrary. Sorry about not having noticed I was wrong before. bug can be safely closed.
Thank you for confirming. Closing.
calling for a REOPENing of this bug. I just did a lot fo tests and the postfix-pgsql-2.0.0.2 patch and despite source code evidence, it doesn't support pgsql on unix sockets; infact, having it on a socket will result in errors from postfix, like: Sep 19 17:09:04 farstar postfix/trivial-rewrite[1882]: warning: could not connect to server: No such file or directory??Is the server running locally and accepting??connections on Unix domain socket "/tmp/.s.PGSQL.5432"?? Sep 19 17:09:04 farstar postfix/trivial-rewrite[1882]: warning: could not connect to server: No such file or directory??Is the server running locally and accepting??connections on Unix domain socket "/tmp/.s.PGSQL.5432"?? Sep 19 17:09:04 farstar postfix/trivial-rewrite[1882]: fatal: pgsql:/etc/postfix/virtual.cf: table lookup problem etc etc.... compiling postfix with the debian patch I previously posted in this bug solves the problem.
news: it seems that postfix as of 2003-11-13 onw includes pgsql support. so the debian patch is not needed anymore. reclose the bug: we can wait 2.0.17 and drop postfix-pgsql-2.0.0.2.patch in it. alessandro
This will be supported in postfix 2.1 whenever that is out. For now, we'll just use the current patch as-is.
