Added extra information about errors to the PostgreSQL error string.
[exim.git] / src / src / lookups / pgsql.c
CommitLineData
e08c430f 1/* $Cambridge: exim/src/src/lookups/pgsql.c,v 1.3 2005/09/13 11:27:45 ph10 Exp $ */
0756eb3c
PH
2
3/*************************************************
4* Exim - an Internet mail transport agent *
5*************************************************/
6
c988f1f4 7/* Copyright (c) University of Cambridge 1995 - 2005 */
0756eb3c
PH
8/* See the file NOTICE for conditions of use and distribution. */
9
10/* Thanks to Petr Cech for contributing the original code for these
11functions. Thanks to Joachim Wieland for the initial patch for the Unix domain
12socket extension. */
13
14#include "../exim.h"
15#include "lf_functions.h"
16#include "pgsql.h" /* The local header */
17
18/* We can't just compile this code and allow the library mechanism to omit the
19functions if they are not wanted, because we need to have the PGSQL header
20available for compiling. Therefore, compile these functions only if
21LOOKUP_PGSQL is defined. However, some compilers don't like compiling empty
22modules, so keep them happy with a dummy when skipping the rest. Make it
23reference itself to stop picky compilers complaining that it is unused, and put
24in a dummy argument to stop even pickier compilers complaining about infinite
25loops. */
26
27#ifndef LOOKUP_PGSQL
28static void dummy(int x) { dummy(x-1); }
29#else
30
31
32#include <libpq-fe.h> /* The system header */
33
34/* Structure and anchor for caching connections. */
35
36typedef struct pgsql_connection {
37 struct pgsql_connection *next;
38 uschar *server;
39 PGconn *handle;
40} pgsql_connection;
41
42static pgsql_connection *pgsql_connections = NULL;
43
44
45
46/*************************************************
47* Open entry point *
48*************************************************/
49
50/* See local README for interface description. */
51
52void *
53pgsql_open(uschar *filename, uschar **errmsg)
54{
55return (void *)(1); /* Just return something non-null */
56}
57
58
59
60/*************************************************
61* Tidy entry point *
62*************************************************/
63
64/* See local README for interface description. */
65
66void
67pgsql_tidy(void)
68{
69pgsql_connection *cn;
70while ((cn = pgsql_connections) != NULL)
71 {
72 pgsql_connections = cn->next;
73 DEBUG(D_lookup) debug_printf("close PGSQL connection: %s\n", cn->server);
74 PQfinish(cn->handle);
75 }
76}
77
78
79
80/*************************************************
81* Internal search function *
82*************************************************/
83
84/* This function is called from the find entry point to do the search for a
85single server. The server string is of the form "server/dbname/user/password".
86
87PostgreSQL supports connections through Unix domain sockets. This is usually
88faster and costs less cpu time than a TCP/IP connection. However it can only be
89used if the mail server runs on the same machine as the database server. A
90configuration line for PostgreSQL via Unix domain sockets looks like this:
91
92hide pgsql_servers = (/tmp/.s.PGSQL.5432)/db/user/password[:<nextserver>]
93
94We enclose the path name in parentheses so that its slashes aren't visually
95confused with the delimeters for the other pgsql_server settings.
96
97For TCP/IP connections, the server is a host name and optional port (with a
98colon separator).
99
100NOTE:
101 1) All three '/' must be present.
102 2) If host is omitted the local unix socket is used.
103
104Arguments:
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
111
112Returns: OK, FAIL, or DEFER
113*/
114
115static int
116perform_pgsql_search(uschar *query, uschar *server, uschar **resultptr,
117 uschar **errmsg, BOOL *defer_break, BOOL *do_cache)
118{
119PGconn *pg_conn = NULL;
120PGresult *pg_result = NULL;
121
122int i;
123int ssize = 0;
124int offset = 0;
125int yield = DEFER;
126unsigned int num_fields, num_tuples;
127uschar *result = NULL;
128pgsql_connection *cn;
129uschar *server_copy = NULL;
130uschar *sdata[3];
131
132/* Disaggregate the parameters from the server argument. The order is host or
133path, database, user, password. We can write to the string, since it is in a
134nextinlist temporary buffer. The copy of the string that is used for caching
135has the password removed. This copy is also used for debugging output. */
136
137for (i = 2; i >= 0; i--)
138 {
139 uschar *pp = Ustrrchr(server, '/');
140 if (pp == NULL)
141 {
142 *errmsg = string_sprintf("incomplete pgSQL server data: %s",
143 (i == 2)? server : server_copy);
144 *defer_break = TRUE;
145 return DEFER;
146 }
147 *pp++ = 0;
148 sdata[i] = pp;
149 if (i == 2) server_copy = string_copy(server); /* sans password */
150 }
151
152/* The total server string has now been truncated so that what is left at the
153start is the identification of the server (host or path). See if we have a
154cached connection to the server. */
155
156for (cn = pgsql_connections; cn != NULL; cn = cn->next)
157 {
158 if (Ustrcmp(cn->server, server_copy) == 0)
159 {
160 pg_conn = cn->handle;
161 break;
162 }
163 }
164
165/* If there is no cached connection, we must set one up. */
166
167if (cn == NULL)
168 {
169 uschar *port = US"";
170
171 /* For a Unix domain socket connection, the path is in parentheses */
172
173 if (*server == '(')
174 {
175 uschar *last_slash, *last_dot, *p;
176
177 p = ++server;
178 while (*p != 0 && *p != ')') p++;
179 *p = 0;
180
181 last_slash = Ustrrchr(server, '/');
182 last_dot = Ustrrchr(server, '.');
183
184 DEBUG(D_lookup) debug_printf("PGSQL new connection: socket=%s "
185 "database=%s user=%s\n", server, sdata[0], sdata[1]);
186
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. */
190
191 if (last_slash == NULL || last_dot == NULL)
192 {
193 *errmsg = string_sprintf("PGSQL invalid filename for socket: %s",
194 server);
195 *defer_break = TRUE;
196 return DEFER;
197 }
198
199 /* Terminate the path name and set up the port: we'll have something like
200 server = "/var/run/postgresql" and port = "5432". */
201
202 *last_slash = 0;
203 port = last_dot + 1;
204 }
205
206 /* Host connection; sort out the port */
207
208 else
209 {
210 uschar *p;
211 if ((p = Ustrchr(server, ':')) != NULL)
212 {
213 *p++ = 0;
214 port = p;
215 }
216
217 if (Ustrchr(server, '/') != NULL)
218 {
219 *errmsg = string_sprintf("unexpected slash in pgSQL server hostname: %s",
220 server);
221 *defer_break = TRUE;
222 return DEFER;
223 }
224
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]);
227 }
228
229 /* If the database is the empty string, set it NULL - the query must then
230 define it. */
231
232 if (sdata[0][0] == 0) sdata[0] = NULL;
233
234 /* Get store for a new handle, initialize it, and connect to the server */
235
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]);
239
240 if(PQstatus(pg_conn) == CONNECTION_BAD)
241 {
242 store_reset(server_copy);
243 *errmsg = string_sprintf("PGSQL connection failed: %s",
244 PQerrorMessage(pg_conn));
245 PQfinish(pg_conn);
246 *defer_break = FALSE;
247 goto PGSQL_EXIT;
248 }
249
250 /* Add the connection to the cache */
251
252 cn = store_get(sizeof(pgsql_connection));
253 cn->server = server_copy;
254 cn->handle = pg_conn;
255 cn->next = pgsql_connections;
256 pgsql_connections = cn;
257 }
258
259/* Else use a previously cached connection */
260
261else
262 {
263 DEBUG(D_lookup) debug_printf("PGSQL using cached connection for %s\n",
264 server_copy);
265 }
266
267/* Run the query */
268
269 pg_result = PQexec(pg_conn, CS query);
270 switch(PQresultStatus(pg_result))
271 {
272 case PGRES_EMPTY_QUERY:
273 case PGRES_COMMAND_OK:
274 /* The command was successful but did not return any data since it was
275 * not SELECT but either an INSERT, UPDATE or DELETE statement. Tell the
276 * high level code to not cache this query, and clean the current cache for
277 * this handle by setting *do_cache FALSE. */
278 result = string_copy(US PQcmdTuples(pg_result));
279 offset = Ustrlen(result);
280 *do_cache = FALSE;
281 DEBUG(D_lookup) debug_printf("PGSQL: command does not return any data "
282 "but was successful. Rows affected: %s\n", result);
283
284 case PGRES_TUPLES_OK:
285 break;
286
287 default:
e08c430f 288 /* This was the original code:
0756eb3c
PH
289 *errmsg = string_sprintf("PGSQL: query failed: %s\n",
290 PQresultErrorMessage(pg_result));
e08c430f
PH
291 This was suggested by a user:
292 */
293
294 *errmsg = string_sprintf("PGSQL: query failed: %s (%s) (%s)\n",
295 PQresultErrorMessage(pg_result),
296 PQresStatus(PQresultStatus(pg_result)), query);
0756eb3c
PH
297 *defer_break = FALSE;
298 goto PGSQL_EXIT;
299 }
300
301/* Result is in pg_result. Find the number of fields returned. If this is one,
302we don't add field names to the data. Otherwise we do. If the query did not
303return anything we skip the for loop; this also applies to the case
304PGRES_COMMAND_OK. */
305
306num_fields = PQnfields(pg_result);
307num_tuples = PQntuples(pg_result);
308
309/* Get the fields and construct the result string. If there is more than one
310row, we insert '\n' between them. */
311
312for (i = 0; i < num_tuples; i++)
313 {
314 if (result != NULL)
315 result = string_cat(result, &ssize, &offset, US"\n", 1);
316
317 if (num_fields == 1)
318 {
319 result = string_cat(result, &ssize, &offset,
320 US PQgetvalue(pg_result, i, 0), PQgetlength(pg_result, i, 0));
321 }
322
323 else
324 {
325 int j;
326 for (j = 0; j < num_fields; j++)
327 {
328 uschar *tmp = US PQgetvalue(pg_result, i, j);
329 result = lf_quote(US PQfname(pg_result, j), tmp, Ustrlen(tmp), result,
330 &ssize, &offset);
331 }
332 }
333 }
334
335/* If result is NULL then no data has been found and so we return FAIL.
336Otherwise, we must terminate the string which has been built; string_cat()
337always leaves enough room for a terminating zero. */
338
339if (result == NULL)
340 {
341 yield = FAIL;
342 *errmsg = US"PGSQL: no data found";
343 }
344else
345 {
346 result[offset] = 0;
347 store_reset(result + offset + 1);
348 }
349
350/* Get here by goto from various error checks. */
351
352PGSQL_EXIT:
353
354/* Free store for any result that was got; don't close the connection, as
355it is cached. */
356
357if (pg_result != NULL) PQclear(pg_result);
358
359/* Non-NULL result indicates a sucessful result */
360
361if (result != NULL)
362 {
363 *resultptr = result;
364 return OK;
365 }
366else
367 {
368 DEBUG(D_lookup) debug_printf("%s\n", *errmsg);
369 return yield; /* FAIL or DEFER */
370 }
371}
372
373
374
375
376/*************************************************
377* Find entry point *
378*************************************************/
379
380/* See local README for interface description. The handle and filename
381arguments are not used. Loop through a list of servers while the query is
382deferred with a retryable error. */
383
384int
385pgsql_find(void *handle, uschar *filename, uschar *query, int length,
386 uschar **result, uschar **errmsg, BOOL *do_cache)
387{
388int sep = 0;
389uschar *server;
390uschar *list = pgsql_servers;
391uschar buffer[512];
392
393DEBUG(D_lookup) debug_printf("PGSQL query: %s\n", query);
394
395while ((server = string_nextinlist(&list, &sep, buffer, sizeof(buffer)))
396 != NULL)
397 {
398 BOOL defer_break;
399 int rc = perform_pgsql_search(query, server, result, errmsg, &defer_break,
400 do_cache);
401 if (rc != DEFER || defer_break) return rc;
402 }
403
404if (pgsql_servers == NULL)
405 *errmsg = US"no PGSQL servers defined (pgsql_servers option)";
406
407return DEFER;
408}
409
410
411
412/*************************************************
413* Quote entry point *
414*************************************************/
415
416/* The characters that always need to be quoted (with backslash) are newline,
417tab, carriage return, backspace, backslash itself, and the quote characters.
418Percent and underscore are only special in contexts where they can be wild
419cards, and this isn't usually the case for data inserted from messages, since
420that isn't likely to be treated as a pattern of any kind. However, pgsql seems
421to allow escaping "on spec". If you use something like "where id="ab\%cd" it
422does treat the string as "ab%cd". So we can safely quote percent and
423underscore. [This is different to MySQL, where you can't do this.]
424
425Arguments:
426 s the string to be quoted
427 opt additional option text or NULL if none
428
429Returns: the processed string or NULL for a bad option
430*/
431
432uschar *
433pgsql_quote(uschar *s, uschar *opt)
434{
435register int c;
436int count = 0;
437uschar *t = s;
438uschar *quoted;
439
440if (opt != NULL) return NULL; /* No options recognized */
441
442while ((c = *t++) != 0)
443 if (Ustrchr("\n\t\r\b\'\"\\%_", c) != NULL) count++;
444
445if (count == 0) return s;
446t = quoted = store_get(Ustrlen(s) + count + 1);
447
448while ((c = *s++) != 0)
449 {
450 if (Ustrchr("\n\t\r\b\'\"\\%_", c) != NULL)
451 {
452 *t++ = '\\';
453 switch(c)
454 {
455 case '\n': *t++ = 'n';
456 break;
457 case '\t': *t++ = 't';
458 break;
459 case '\r': *t++ = 'r';
460 break;
461 case '\b': *t++ = 'b';
462 break;
463 default: *t++ = c;
464 break;
465 }
466 }
467 else *t++ = c;
468 }
469
470*t = 0;
471return quoted;
472}
473
474#endif /* PGSQL_LOOKUP */
475
476/* End of lookups/pgsql.c */