1 /* $Cambridge: exim/src/src/lookups/pgsql.c,v 1.7 2006/07/14 14:42:57 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);
79 /*************************************************
80 * Notice processor function for pgsql *
81 *************************************************/
83 /* This function is passed to pgsql below, and called for any PostgreSQL
84 "notices". By default they are written to stderr, which is undesirable.
87 arg an opaque user cookie (not used)
94 notice_processor(void *arg, const char *message)
96 arg = arg; /* Keep compiler happy */
97 DEBUG(D_lookup) debug_printf("PGSQL: %s\n", message);
102 /*************************************************
103 * Internal search function *
104 *************************************************/
106 /* This function is called from the find entry point to do the search for a
107 single server. The server string is of the form "server/dbname/user/password".
109 PostgreSQL supports connections through Unix domain sockets. This is usually
110 faster and costs less cpu time than a TCP/IP connection. However it can only be
111 used if the mail server runs on the same machine as the database server. A
112 configuration line for PostgreSQL via Unix domain sockets looks like this:
114 hide pgsql_servers = (/tmp/.s.PGSQL.5432)/db/user/password[:<nextserver>]
116 We enclose the path name in parentheses so that its slashes aren't visually
117 confused with the delimeters for the other pgsql_server settings.
119 For TCP/IP connections, the server is a host name and optional port (with a
123 1) All three '/' must be present.
124 2) If host is omitted the local unix socket is used.
127 query the query string
128 server the server string; this is in dynamic memory and can be updated
129 resultptr where to store the result
130 errmsg where to point an error message
131 defer_break TRUE if no more servers are to be tried after DEFER
132 do_cache set FALSE if data is changed
134 Returns: OK, FAIL, or DEFER
138 perform_pgsql_search(uschar *query, uschar *server, uschar **resultptr,
139 uschar **errmsg, BOOL *defer_break, BOOL *do_cache)
141 PGconn *pg_conn = NULL;
142 PGresult *pg_result = NULL;
148 unsigned int num_fields, num_tuples;
149 uschar *result = NULL;
150 pgsql_connection *cn;
151 uschar *server_copy = NULL;
154 /* Disaggregate the parameters from the server argument. The order is host or
155 path, database, user, password. We can write to the string, since it is in a
156 nextinlist temporary buffer. The copy of the string that is used for caching
157 has the password removed. This copy is also used for debugging output. */
159 for (i = 2; i >= 0; i--)
161 uschar *pp = Ustrrchr(server, '/');
164 *errmsg = string_sprintf("incomplete pgSQL server data: %s",
165 (i == 2)? server : server_copy);
171 if (i == 2) server_copy = string_copy(server); /* sans password */
174 /* The total server string has now been truncated so that what is left at the
175 start is the identification of the server (host or path). See if we have a
176 cached connection to the server. */
178 for (cn = pgsql_connections; cn != NULL; cn = cn->next)
180 if (Ustrcmp(cn->server, server_copy) == 0)
182 pg_conn = cn->handle;
187 /* If there is no cached connection, we must set one up. */
193 /* For a Unix domain socket connection, the path is in parentheses */
197 uschar *last_slash, *last_dot, *p;
200 while (*p != 0 && *p != ')') p++;
203 last_slash = Ustrrchr(server, '/');
204 last_dot = Ustrrchr(server, '.');
206 DEBUG(D_lookup) debug_printf("PGSQL new connection: socket=%s "
207 "database=%s user=%s\n", server, sdata[0], sdata[1]);
209 /* A valid socket name looks like this: /var/run/postgresql/.s.PGSQL.5432
210 We have to call PQsetdbLogin with '/var/run/postgresql' as the hostname
211 argument and put '5432' into the port variable. */
213 if (last_slash == NULL || last_dot == NULL)
215 *errmsg = string_sprintf("PGSQL invalid filename for socket: %s",
221 /* Terminate the path name and set up the port: we'll have something like
222 server = "/var/run/postgresql" and port = "5432". */
228 /* Host connection; sort out the port */
233 if ((p = Ustrchr(server, ':')) != NULL)
239 if (Ustrchr(server, '/') != NULL)
241 *errmsg = string_sprintf("unexpected slash in pgSQL server hostname: %s",
247 DEBUG(D_lookup) debug_printf("PGSQL new connection: host=%s port=%s "
248 "database=%s user=%s\n", server, port, sdata[0], sdata[1]);
251 /* If the database is the empty string, set it NULL - the query must then
254 if (sdata[0][0] == 0) sdata[0] = NULL;
256 /* Get store for a new handle, initialize it, and connect to the server */
258 pg_conn=PQsetdbLogin(
259 /* host port options tty database user passwd */
260 CS server, CS port, NULL, NULL, CS sdata[0], CS sdata[1], CS sdata[2]);
262 if(PQstatus(pg_conn) == CONNECTION_BAD)
264 store_reset(server_copy);
265 *errmsg = string_sprintf("PGSQL connection failed: %s",
266 PQerrorMessage(pg_conn));
268 *defer_break = FALSE;
272 /* Set the client encoding to SQL_ASCII, which means that the server will
273 not try to interpret the query as being in any fancy encoding such as UTF-8
274 or other multibyte code that might cause problems with escaping. */
276 PQsetClientEncoding(pg_conn, "SQL_ASCII");
278 /* Set the notice processor to prevent notices from being written to stderr
279 (which is what the default does). Our function (above) just produces debug
282 PQsetNoticeProcessor(pg_conn, notice_processor, NULL);
284 /* Add the connection to the cache */
286 cn = store_get(sizeof(pgsql_connection));
287 cn->server = server_copy;
288 cn->handle = pg_conn;
289 cn->next = pgsql_connections;
290 pgsql_connections = cn;
293 /* Else use a previously cached connection */
297 DEBUG(D_lookup) debug_printf("PGSQL using cached connection for %s\n",
303 pg_result = PQexec(pg_conn, CS query);
304 switch(PQresultStatus(pg_result))
306 case PGRES_EMPTY_QUERY:
307 case PGRES_COMMAND_OK:
308 /* The command was successful but did not return any data since it was
309 * not SELECT but either an INSERT, UPDATE or DELETE statement. Tell the
310 * high level code to not cache this query, and clean the current cache for
311 * this handle by setting *do_cache FALSE. */
312 result = string_copy(US PQcmdTuples(pg_result));
313 offset = Ustrlen(result);
315 DEBUG(D_lookup) debug_printf("PGSQL: command does not return any data "
316 "but was successful. Rows affected: %s\n", result);
318 case PGRES_TUPLES_OK:
322 /* This was the original code:
323 *errmsg = string_sprintf("PGSQL: query failed: %s\n",
324 PQresultErrorMessage(pg_result));
325 This was suggested by a user:
328 *errmsg = string_sprintf("PGSQL: query failed: %s (%s) (%s)\n",
329 PQresultErrorMessage(pg_result),
330 PQresStatus(PQresultStatus(pg_result)), query);
331 *defer_break = FALSE;
335 /* Result is in pg_result. Find the number of fields returned. If this is one,
336 we don't add field names to the data. Otherwise we do. If the query did not
337 return anything we skip the for loop; this also applies to the case
340 num_fields = PQnfields(pg_result);
341 num_tuples = PQntuples(pg_result);
343 /* Get the fields and construct the result string. If there is more than one
344 row, we insert '\n' between them. */
346 for (i = 0; i < num_tuples; i++)
349 result = string_cat(result, &ssize, &offset, US"\n", 1);
353 result = string_cat(result, &ssize, &offset,
354 US PQgetvalue(pg_result, i, 0), PQgetlength(pg_result, i, 0));
360 for (j = 0; j < num_fields; j++)
362 uschar *tmp = US PQgetvalue(pg_result, i, j);
363 result = lf_quote(US PQfname(pg_result, j), tmp, Ustrlen(tmp), result,
369 /* If result is NULL then no data has been found and so we return FAIL.
370 Otherwise, we must terminate the string which has been built; string_cat()
371 always leaves enough room for a terminating zero. */
376 *errmsg = US"PGSQL: no data found";
381 store_reset(result + offset + 1);
384 /* Get here by goto from various error checks. */
388 /* Free store for any result that was got; don't close the connection, as
391 if (pg_result != NULL) PQclear(pg_result);
393 /* Non-NULL result indicates a sucessful result */
402 DEBUG(D_lookup) debug_printf("%s\n", *errmsg);
403 return yield; /* FAIL or DEFER */
410 /*************************************************
412 *************************************************/
414 /* See local README for interface description. The handle and filename
415 arguments are not used. Loop through a list of servers while the query is
416 deferred with a retryable error. */
419 pgsql_find(void *handle, uschar *filename, uschar *query, int length,
420 uschar **result, uschar **errmsg, BOOL *do_cache)
424 uschar *list = pgsql_servers;
427 DEBUG(D_lookup) debug_printf("PGSQL query: %s\n", query);
429 while ((server = string_nextinlist(&list, &sep, buffer, sizeof(buffer)))
433 int rc = perform_pgsql_search(query, server, result, errmsg, &defer_break,
435 if (rc != DEFER || defer_break) return rc;
438 if (pgsql_servers == NULL)
439 *errmsg = US"no PGSQL servers defined (pgsql_servers option)";
446 /*************************************************
447 * Quote entry point *
448 *************************************************/
450 /* The characters that always need to be quoted (with backslash) are newline,
451 tab, carriage return, backspace, backslash itself, and the quote characters.
452 Percent and underscore are only special in contexts where they can be wild
453 cards, and this isn't usually the case for data inserted from messages, since
454 that isn't likely to be treated as a pattern of any kind. However, pgsql seems
455 to allow escaping "on spec". If you use something like "where id="ab\%cd" it
456 does treat the string as "ab%cd". So we can safely quote percent and
457 underscore. [This is different to MySQL, where you can't do this.]
459 The original code quoted single quotes as \' which is documented as valid in
460 the O'Reilly book "Practical PostgreSQL" (first edition) as an alternative to
461 the SQL standard '' way of representing a single quote as data. However, in
462 June 2006 there was some security issue with using \' and so this has been
465 [Note: There is a function called PQescapeStringConn() that quotes strings.
466 This cannot be used because it needs a PGconn argument (the connection handle).
467 Why, I don't know. Seems odd for just string escaping...]
470 s the string to be quoted
471 opt additional option text or NULL if none
473 Returns: the processed string or NULL for a bad option
477 pgsql_quote(uschar *s, uschar *opt)
484 if (opt != NULL) return NULL; /* No options recognized */
486 while ((c = *t++) != 0)
487 if (Ustrchr("\n\t\r\b\'\"\\%_", c) != NULL) count++;
489 if (count == 0) return s;
490 t = quoted = store_get(Ustrlen(s) + count + 1);
492 while ((c = *s++) != 0)
499 else if (Ustrchr("\n\t\r\b\"\\%_", c) != NULL)
504 case '\n': *t++ = 'n';
506 case '\t': *t++ = 't';
508 case '\r': *t++ = 'r';
510 case '\b': *t++ = 'b';
523 #endif /* PGSQL_LOOKUP */
525 /* End of lookups/pgsql.c */