1 /* $Cambridge: exim/src/src/lookups/pgsql.c,v 1.11 2009/11/16 19:50:38 nm4 Exp $ */
3 /*************************************************
4 * Exim - an Internet mail transport agent *
5 *************************************************/
7 /* Copyright (c) University of Cambridge 1995 - 2009 */
8 /* See the file NOTICE for conditions of use and distribution. */
10 /* Thanks to Petr Cech for contributing the original code for these
11 functions. Thanks to Joachim Wieland for the initial patch for the Unix domain
15 #include "lf_functions.h"
17 #include <libpq-fe.h> /* The system header */
19 /* Structure and anchor for caching connections. */
21 typedef struct pgsql_connection {
22 struct pgsql_connection *next;
27 static pgsql_connection *pgsql_connections = NULL;
31 /*************************************************
33 *************************************************/
35 /* See local README for interface description. */
38 pgsql_open(uschar *filename, uschar **errmsg)
40 return (void *)(1); /* Just return something non-null */
45 /*************************************************
47 *************************************************/
49 /* See local README for interface description. */
55 while ((cn = pgsql_connections) != NULL)
57 pgsql_connections = cn->next;
58 DEBUG(D_lookup) debug_printf("close PGSQL connection: %s\n", cn->server);
64 /*************************************************
65 * Notice processor function for pgsql *
66 *************************************************/
68 /* This function is passed to pgsql below, and called for any PostgreSQL
69 "notices". By default they are written to stderr, which is undesirable.
72 arg an opaque user cookie (not used)
79 notice_processor(void *arg, const char *message)
81 arg = arg; /* Keep compiler happy */
82 DEBUG(D_lookup) debug_printf("PGSQL: %s\n", message);
87 /*************************************************
88 * Internal search function *
89 *************************************************/
91 /* This function is called from the find entry point to do the search for a
92 single server. The server string is of the form "server/dbname/user/password".
94 PostgreSQL supports connections through Unix domain sockets. This is usually
95 faster and costs less cpu time than a TCP/IP connection. However it can only be
96 used if the mail server runs on the same machine as the database server. A
97 configuration line for PostgreSQL via Unix domain sockets looks like this:
99 hide pgsql_servers = (/tmp/.s.PGSQL.5432)/db/user/password[:<nextserver>]
101 We enclose the path name in parentheses so that its slashes aren't visually
102 confused with the delimeters for the other pgsql_server settings.
104 For TCP/IP connections, the server is a host name and optional port (with a
108 1) All three '/' must be present.
109 2) If host is omitted the local unix socket is used.
112 query the query string
113 server the server string; this is in dynamic memory and can be updated
114 resultptr where to store the result
115 errmsg where to point an error message
116 defer_break set TRUE if no more servers are to be tried after DEFER
117 do_cache set FALSE if data is changed
119 Returns: OK, FAIL, or DEFER
123 perform_pgsql_search(uschar *query, uschar *server, uschar **resultptr,
124 uschar **errmsg, BOOL *defer_break, BOOL *do_cache)
126 PGconn *pg_conn = NULL;
127 PGresult *pg_result = NULL;
133 unsigned int num_fields, num_tuples;
134 uschar *result = NULL;
135 pgsql_connection *cn;
136 uschar *server_copy = NULL;
139 /* Disaggregate the parameters from the server argument. The order is host or
140 path, database, user, password. We can write to the string, since it is in a
141 nextinlist temporary buffer. The copy of the string that is used for caching
142 has the password removed. This copy is also used for debugging output. */
144 for (i = 2; i >= 0; i--)
146 uschar *pp = Ustrrchr(server, '/');
149 *errmsg = string_sprintf("incomplete pgSQL server data: %s",
150 (i == 2)? server : server_copy);
156 if (i == 2) server_copy = string_copy(server); /* sans password */
159 /* The total server string has now been truncated so that what is left at the
160 start is the identification of the server (host or path). See if we have a
161 cached connection to the server. */
163 for (cn = pgsql_connections; cn != NULL; cn = cn->next)
165 if (Ustrcmp(cn->server, server_copy) == 0)
167 pg_conn = cn->handle;
172 /* If there is no cached connection, we must set one up. */
178 /* For a Unix domain socket connection, the path is in parentheses */
182 uschar *last_slash, *last_dot, *p;
185 while (*p != 0 && *p != ')') p++;
188 last_slash = Ustrrchr(server, '/');
189 last_dot = Ustrrchr(server, '.');
191 DEBUG(D_lookup) debug_printf("PGSQL new connection: socket=%s "
192 "database=%s user=%s\n", server, sdata[0], sdata[1]);
194 /* A valid socket name looks like this: /var/run/postgresql/.s.PGSQL.5432
195 We have to call PQsetdbLogin with '/var/run/postgresql' as the hostname
196 argument and put '5432' into the port variable. */
198 if (last_slash == NULL || last_dot == NULL)
200 *errmsg = string_sprintf("PGSQL invalid filename for socket: %s",
206 /* Terminate the path name and set up the port: we'll have something like
207 server = "/var/run/postgresql" and port = "5432". */
213 /* Host connection; sort out the port */
218 if ((p = Ustrchr(server, ':')) != NULL)
224 if (Ustrchr(server, '/') != NULL)
226 *errmsg = string_sprintf("unexpected slash in pgSQL server hostname: %s",
232 DEBUG(D_lookup) debug_printf("PGSQL new connection: host=%s port=%s "
233 "database=%s user=%s\n", server, port, sdata[0], sdata[1]);
236 /* If the database is the empty string, set it NULL - the query must then
239 if (sdata[0][0] == 0) sdata[0] = NULL;
241 /* Get store for a new handle, initialize it, and connect to the server */
243 pg_conn=PQsetdbLogin(
244 /* host port options tty database user passwd */
245 CS server, CS port, NULL, NULL, CS sdata[0], CS sdata[1], CS sdata[2]);
247 if(PQstatus(pg_conn) == CONNECTION_BAD)
249 store_reset(server_copy);
250 *errmsg = string_sprintf("PGSQL connection failed: %s",
251 PQerrorMessage(pg_conn));
256 /* Set the client encoding to SQL_ASCII, which means that the server will
257 not try to interpret the query as being in any fancy encoding such as UTF-8
258 or other multibyte code that might cause problems with escaping. */
260 PQsetClientEncoding(pg_conn, "SQL_ASCII");
262 /* Set the notice processor to prevent notices from being written to stderr
263 (which is what the default does). Our function (above) just produces debug
266 PQsetNoticeProcessor(pg_conn, notice_processor, NULL);
268 /* Add the connection to the cache */
270 cn = store_get(sizeof(pgsql_connection));
271 cn->server = server_copy;
272 cn->handle = pg_conn;
273 cn->next = pgsql_connections;
274 pgsql_connections = cn;
277 /* Else use a previously cached connection */
281 DEBUG(D_lookup) debug_printf("PGSQL using cached connection for %s\n",
287 pg_result = PQexec(pg_conn, CS query);
288 switch(PQresultStatus(pg_result))
290 case PGRES_EMPTY_QUERY:
291 case PGRES_COMMAND_OK:
292 /* The command was successful but did not return any data since it was
293 * not SELECT but either an INSERT, UPDATE or DELETE statement. Tell the
294 * high level code to not cache this query, and clean the current cache for
295 * this handle by setting *do_cache FALSE. */
296 result = string_copy(US PQcmdTuples(pg_result));
297 offset = Ustrlen(result);
299 DEBUG(D_lookup) debug_printf("PGSQL: command does not return any data "
300 "but was successful. Rows affected: %s\n", result);
302 case PGRES_TUPLES_OK:
306 /* This was the original code:
307 *errmsg = string_sprintf("PGSQL: query failed: %s\n",
308 PQresultErrorMessage(pg_result));
309 This was suggested by a user:
312 *errmsg = string_sprintf("PGSQL: query failed: %s (%s) (%s)\n",
313 PQresultErrorMessage(pg_result),
314 PQresStatus(PQresultStatus(pg_result)), query);
318 /* Result is in pg_result. Find the number of fields returned. If this is one,
319 we don't add field names to the data. Otherwise we do. If the query did not
320 return anything we skip the for loop; this also applies to the case
323 num_fields = PQnfields(pg_result);
324 num_tuples = PQntuples(pg_result);
326 /* Get the fields and construct the result string. If there is more than one
327 row, we insert '\n' between them. */
329 for (i = 0; i < num_tuples; i++)
332 result = string_cat(result, &ssize, &offset, US"\n", 1);
336 result = string_cat(result, &ssize, &offset,
337 US PQgetvalue(pg_result, i, 0), PQgetlength(pg_result, i, 0));
343 for (j = 0; j < num_fields; j++)
345 uschar *tmp = US PQgetvalue(pg_result, i, j);
346 result = lf_quote(US PQfname(pg_result, j), tmp, Ustrlen(tmp), result,
352 /* If result is NULL then no data has been found and so we return FAIL.
353 Otherwise, we must terminate the string which has been built; string_cat()
354 always leaves enough room for a terminating zero. */
359 *errmsg = US"PGSQL: no data found";
364 store_reset(result + offset + 1);
367 /* Get here by goto from various error checks. */
371 /* Free store for any result that was got; don't close the connection, as
374 if (pg_result != NULL) PQclear(pg_result);
376 /* Non-NULL result indicates a sucessful result */
385 DEBUG(D_lookup) debug_printf("%s\n", *errmsg);
386 return yield; /* FAIL or DEFER */
393 /*************************************************
395 *************************************************/
397 /* See local README for interface description. The handle and filename
398 arguments are not used. The code to loop through a list of servers while the
399 query is deferred with a retryable error is now in a separate function that is
400 shared with other SQL lookups. */
403 pgsql_find(void *handle, uschar *filename, uschar *query, int length,
404 uschar **result, uschar **errmsg, BOOL *do_cache)
406 return lf_sqlperform(US"PostgreSQL", US"pgsql_servers", pgsql_servers, query,
407 result, errmsg, do_cache, perform_pgsql_search);
412 /*************************************************
413 * Quote entry point *
414 *************************************************/
416 /* The characters that always need to be quoted (with backslash) are newline,
417 tab, carriage return, backspace, backslash itself, and the quote characters.
418 Percent and underscore are only special in contexts where they can be wild
419 cards, and this isn't usually the case for data inserted from messages, since
420 that isn't likely to be treated as a pattern of any kind. However, pgsql seems
421 to allow escaping "on spec". If you use something like "where id="ab\%cd" it
422 does treat the string as "ab%cd". So we can safely quote percent and
423 underscore. [This is different to MySQL, where you can't do this.]
425 The original code quoted single quotes as \' which is documented as valid in
426 the O'Reilly book "Practical PostgreSQL" (first edition) as an alternative to
427 the SQL standard '' way of representing a single quote as data. However, in
428 June 2006 there was some security issue with using \' and so this has been
431 [Note: There is a function called PQescapeStringConn() that quotes strings.
432 This cannot be used because it needs a PGconn argument (the connection handle).
433 Why, I don't know. Seems odd for just string escaping...]
436 s the string to be quoted
437 opt additional option text or NULL if none
439 Returns: the processed string or NULL for a bad option
443 pgsql_quote(uschar *s, uschar *opt)
450 if (opt != NULL) return NULL; /* No options recognized */
452 while ((c = *t++) != 0)
453 if (Ustrchr("\n\t\r\b\'\"\\%_", c) != NULL) count++;
455 if (count == 0) return s;
456 t = quoted = store_get(Ustrlen(s) + count + 1);
458 while ((c = *s++) != 0)
465 else if (Ustrchr("\n\t\r\b\"\\%_", c) != NULL)
470 case '\n': *t++ = 'n';
472 case '\t': *t++ = 't';
474 case '\r': *t++ = 'r';
476 case '\b': *t++ = 'b';
490 /*************************************************
491 * Version reporting entry point *
492 *************************************************/
494 /* See local README for interface description. */
496 #include "../version.h"
499 pgsql_version_report(FILE *f)
502 fprintf(f, "Library version: PostgreSQL: Exim version %s\n", EXIM_VERSION_STR);
505 /* Version reporting: there appears to be no available information about
506 the client library in libpq-fe.h; once you have a connection object, you
507 can access the server version and the chosen protocol version, but those
508 aren't really what we want. It might make sense to debug_printf those
509 when the connection is established though? */
513 static lookup_info _lookup_info = {
514 US"pgsql", /* lookup name */
515 lookup_querystyle, /* query-style lookup */
516 pgsql_open, /* open function */
517 NULL, /* no check function */
518 pgsql_find, /* find function */
519 NULL, /* no close function */
520 pgsql_tidy, /* tidy function */
521 pgsql_quote, /* quoting function */
522 pgsql_version_report /* version reporting */
526 #define pgsql_lookup_module_info _lookup_module_info
529 static lookup_info *_lookup_list[] = { &_lookup_info };
530 lookup_module_info pgsql_lookup_module_info = { LOOKUP_MODULE_INFO_MAGIC, _lookup_list, 1 };
532 /* End of lookups/pgsql.c */