1 /* $Cambridge: exim/src/src/lookups/pgsql.c,v 1.6 2006/07/14 14:21:27 ph10 Exp $ */
3 /*************************************************
4 * Exim - an Internet mail transport agent *
5 *************************************************/
7 /* Copyright (c) University of Cambridge 1995 - 2006 */
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"
16 #include "pgsql.h" /* The local header */
18 /* We can't just compile this code and allow the library mechanism to omit the
19 functions if they are not wanted, because we need to have the PGSQL header
20 available for compiling. Therefore, compile these functions only if
21 LOOKUP_PGSQL is defined. However, some compilers don't like compiling empty
22 modules, so keep them happy with a dummy when skipping the rest. Make it
23 reference itself to stop picky compilers complaining that it is unused, and put
24 in a dummy argument to stop even pickier compilers complaining about infinite
28 static void dummy(int x) { dummy(x-1); }
32 #include <libpq-fe.h> /* The system header */
34 /* Structure and anchor for caching connections. */
36 typedef struct pgsql_connection {
37 struct pgsql_connection *next;
42 static pgsql_connection *pgsql_connections = NULL;
46 /*************************************************
48 *************************************************/
50 /* See local README for interface description. */
53 pgsql_open(uschar *filename, uschar **errmsg)
55 return (void *)(1); /* Just return something non-null */
60 /*************************************************
62 *************************************************/
64 /* See local README for interface description. */
70 while ((cn = pgsql_connections) != NULL)
72 pgsql_connections = cn->next;
73 DEBUG(D_lookup) debug_printf("close PGSQL connection: %s\n", cn->server);
80 /*************************************************
81 * Internal search function *
82 *************************************************/
84 /* This function is called from the find entry point to do the search for a
85 single server. The server string is of the form "server/dbname/user/password".
87 PostgreSQL supports connections through Unix domain sockets. This is usually
88 faster and costs less cpu time than a TCP/IP connection. However it can only be
89 used if the mail server runs on the same machine as the database server. A
90 configuration line for PostgreSQL via Unix domain sockets looks like this:
92 hide pgsql_servers = (/tmp/.s.PGSQL.5432)/db/user/password[:<nextserver>]
94 We enclose the path name in parentheses so that its slashes aren't visually
95 confused with the delimeters for the other pgsql_server settings.
97 For TCP/IP connections, the server is a host name and optional port (with a
101 1) All three '/' must be present.
102 2) If host is omitted the local unix socket is used.
105 query the query string
106 server the server string; this is in dynamic memory and can be updated
107 resultptr where to store the result
108 errmsg where to point an error message
109 defer_break TRUE if no more servers are to be tried after DEFER
110 do_cache set FALSE if data is changed
112 Returns: OK, FAIL, or DEFER
116 perform_pgsql_search(uschar *query, uschar *server, uschar **resultptr,
117 uschar **errmsg, BOOL *defer_break, BOOL *do_cache)
119 PGconn *pg_conn = NULL;
120 PGresult *pg_result = NULL;
126 unsigned int num_fields, num_tuples;
127 uschar *result = NULL;
128 pgsql_connection *cn;
129 uschar *server_copy = NULL;
132 /* Disaggregate the parameters from the server argument. The order is host or
133 path, database, user, password. We can write to the string, since it is in a
134 nextinlist temporary buffer. The copy of the string that is used for caching
135 has the password removed. This copy is also used for debugging output. */
137 for (i = 2; i >= 0; i--)
139 uschar *pp = Ustrrchr(server, '/');
142 *errmsg = string_sprintf("incomplete pgSQL server data: %s",
143 (i == 2)? server : server_copy);
149 if (i == 2) server_copy = string_copy(server); /* sans password */
152 /* The total server string has now been truncated so that what is left at the
153 start is the identification of the server (host or path). See if we have a
154 cached connection to the server. */
156 for (cn = pgsql_connections; cn != NULL; cn = cn->next)
158 if (Ustrcmp(cn->server, server_copy) == 0)
160 pg_conn = cn->handle;
165 /* If there is no cached connection, we must set one up. */
171 /* For a Unix domain socket connection, the path is in parentheses */
175 uschar *last_slash, *last_dot, *p;
178 while (*p != 0 && *p != ')') p++;
181 last_slash = Ustrrchr(server, '/');
182 last_dot = Ustrrchr(server, '.');
184 DEBUG(D_lookup) debug_printf("PGSQL new connection: socket=%s "
185 "database=%s user=%s\n", server, sdata[0], sdata[1]);
187 /* A valid socket name looks like this: /var/run/postgresql/.s.PGSQL.5432
188 We have to call PQsetdbLogin with '/var/run/postgresql' as the hostname
189 argument and put '5432' into the port variable. */
191 if (last_slash == NULL || last_dot == NULL)
193 *errmsg = string_sprintf("PGSQL invalid filename for socket: %s",
199 /* Terminate the path name and set up the port: we'll have something like
200 server = "/var/run/postgresql" and port = "5432". */
206 /* Host connection; sort out the port */
211 if ((p = Ustrchr(server, ':')) != NULL)
217 if (Ustrchr(server, '/') != NULL)
219 *errmsg = string_sprintf("unexpected slash in pgSQL server hostname: %s",
225 DEBUG(D_lookup) debug_printf("PGSQL new connection: host=%s port=%s "
226 "database=%s user=%s\n", server, port, sdata[0], sdata[1]);
229 /* If the database is the empty string, set it NULL - the query must then
232 if (sdata[0][0] == 0) sdata[0] = NULL;
234 /* Get store for a new handle, initialize it, and connect to the server */
236 pg_conn=PQsetdbLogin(
237 /* host port options tty database user passwd */
238 CS server, CS port, NULL, NULL, CS sdata[0], CS sdata[1], CS sdata[2]);
240 if(PQstatus(pg_conn) == CONNECTION_BAD)
242 store_reset(server_copy);
243 *errmsg = string_sprintf("PGSQL connection failed: %s",
244 PQerrorMessage(pg_conn));
246 *defer_break = FALSE;
250 /* Set the client encoding to SQL_ASCII, which means that the server will
251 not try to interpret the query as being in any fancy encoding such as UTF-8
252 or other multibyte code that might cause problems with escaping. */
254 PQsetClientEncoding(pg_conn, "SQL_ASCII");
256 /* Add the connection to the cache */
258 cn = store_get(sizeof(pgsql_connection));
259 cn->server = server_copy;
260 cn->handle = pg_conn;
261 cn->next = pgsql_connections;
262 pgsql_connections = cn;
265 /* Else use a previously cached connection */
269 DEBUG(D_lookup) debug_printf("PGSQL using cached connection for %s\n",
275 pg_result = PQexec(pg_conn, CS query);
276 switch(PQresultStatus(pg_result))
278 case PGRES_EMPTY_QUERY:
279 case PGRES_COMMAND_OK:
280 /* The command was successful but did not return any data since it was
281 * not SELECT but either an INSERT, UPDATE or DELETE statement. Tell the
282 * high level code to not cache this query, and clean the current cache for
283 * this handle by setting *do_cache FALSE. */
284 result = string_copy(US PQcmdTuples(pg_result));
285 offset = Ustrlen(result);
287 DEBUG(D_lookup) debug_printf("PGSQL: command does not return any data "
288 "but was successful. Rows affected: %s\n", result);
290 case PGRES_TUPLES_OK:
294 /* This was the original code:
295 *errmsg = string_sprintf("PGSQL: query failed: %s\n",
296 PQresultErrorMessage(pg_result));
297 This was suggested by a user:
300 *errmsg = string_sprintf("PGSQL: query failed: %s (%s) (%s)\n",
301 PQresultErrorMessage(pg_result),
302 PQresStatus(PQresultStatus(pg_result)), query);
303 *defer_break = FALSE;
307 /* Result is in pg_result. Find the number of fields returned. If this is one,
308 we don't add field names to the data. Otherwise we do. If the query did not
309 return anything we skip the for loop; this also applies to the case
312 num_fields = PQnfields(pg_result);
313 num_tuples = PQntuples(pg_result);
315 /* Get the fields and construct the result string. If there is more than one
316 row, we insert '\n' between them. */
318 for (i = 0; i < num_tuples; i++)
321 result = string_cat(result, &ssize, &offset, US"\n", 1);
325 result = string_cat(result, &ssize, &offset,
326 US PQgetvalue(pg_result, i, 0), PQgetlength(pg_result, i, 0));
332 for (j = 0; j < num_fields; j++)
334 uschar *tmp = US PQgetvalue(pg_result, i, j);
335 result = lf_quote(US PQfname(pg_result, j), tmp, Ustrlen(tmp), result,
341 /* If result is NULL then no data has been found and so we return FAIL.
342 Otherwise, we must terminate the string which has been built; string_cat()
343 always leaves enough room for a terminating zero. */
348 *errmsg = US"PGSQL: no data found";
353 store_reset(result + offset + 1);
356 /* Get here by goto from various error checks. */
360 /* Free store for any result that was got; don't close the connection, as
363 if (pg_result != NULL) PQclear(pg_result);
365 /* Non-NULL result indicates a sucessful result */
374 DEBUG(D_lookup) debug_printf("%s\n", *errmsg);
375 return yield; /* FAIL or DEFER */
382 /*************************************************
384 *************************************************/
386 /* See local README for interface description. The handle and filename
387 arguments are not used. Loop through a list of servers while the query is
388 deferred with a retryable error. */
391 pgsql_find(void *handle, uschar *filename, uschar *query, int length,
392 uschar **result, uschar **errmsg, BOOL *do_cache)
396 uschar *list = pgsql_servers;
399 DEBUG(D_lookup) debug_printf("PGSQL query: %s\n", query);
401 while ((server = string_nextinlist(&list, &sep, buffer, sizeof(buffer)))
405 int rc = perform_pgsql_search(query, server, result, errmsg, &defer_break,
407 if (rc != DEFER || defer_break) return rc;
410 if (pgsql_servers == NULL)
411 *errmsg = US"no PGSQL servers defined (pgsql_servers option)";
418 /*************************************************
419 * Quote entry point *
420 *************************************************/
422 /* The characters that always need to be quoted (with backslash) are newline,
423 tab, carriage return, backspace, backslash itself, and the quote characters.
424 Percent and underscore are only special in contexts where they can be wild
425 cards, and this isn't usually the case for data inserted from messages, since
426 that isn't likely to be treated as a pattern of any kind. However, pgsql seems
427 to allow escaping "on spec". If you use something like "where id="ab\%cd" it
428 does treat the string as "ab%cd". So we can safely quote percent and
429 underscore. [This is different to MySQL, where you can't do this.]
431 The original code quoted single quotes as \' which is documented as valid in
432 the O'Reilly book "Practical PostgreSQL" (first edition) as an alternative to
433 the SQL standard '' way of representing a single quote as data. However, in
434 June 2006 there was some security issue with using \' and so this has been
437 [Note: There is a function called PQescapeStringConn() that quotes strings.
438 This cannot be used because it needs a PGconn argument (the connection handle).
439 Why, I don't know. Seems odd for just string escaping...]
442 s the string to be quoted
443 opt additional option text or NULL if none
445 Returns: the processed string or NULL for a bad option
449 pgsql_quote(uschar *s, uschar *opt)
456 if (opt != NULL) return NULL; /* No options recognized */
458 while ((c = *t++) != 0)
459 if (Ustrchr("\n\t\r\b\'\"\\%_", c) != NULL) count++;
461 if (count == 0) return s;
462 t = quoted = store_get(Ustrlen(s) + count + 1);
464 while ((c = *s++) != 0)
471 else if (Ustrchr("\n\t\r\b\"\\%_", c) != NULL)
476 case '\n': *t++ = 'n';
478 case '\t': *t++ = 't';
480 case '\r': *t++ = 'r';
482 case '\b': *t++ = 'b';
495 #endif /* PGSQL_LOOKUP */
497 /* End of lookups/pgsql.c */