C99 initialisers
[exim.git] / src / src / lookups / pgsql.c
1 /*************************************************
2 * Exim - an Internet mail transport agent *
3 *************************************************/
4
5 /* Copyright (c) University of Cambridge 1995 - 2018 */
6 /* See the file NOTICE for conditions of use and distribution. */
7
8 /* Thanks to Petr Cech for contributing the original code for these
9 functions. Thanks to Joachim Wieland for the initial patch for the Unix domain
10 socket extension. */
11
12 #include "../exim.h"
13 #include "lf_functions.h"
14
15 #include <libpq-fe.h> /* The system header */
16
17 /* Structure and anchor for caching connections. */
18
19 typedef struct pgsql_connection {
20 struct pgsql_connection *next;
21 uschar *server;
22 PGconn *handle;
23 } pgsql_connection;
24
25 static pgsql_connection *pgsql_connections = NULL;
26
27
28
29 /*************************************************
30 * Open entry point *
31 *************************************************/
32
33 /* See local README for interface description. */
34
35 static void *
36 pgsql_open(const uschar * filename, uschar ** errmsg)
37 {
38 return (void *)(1); /* Just return something non-null */
39 }
40
41
42
43 /*************************************************
44 * Tidy entry point *
45 *************************************************/
46
47 /* See local README for interface description. */
48
49 static void
50 pgsql_tidy(void)
51 {
52 pgsql_connection *cn;
53 while ((cn = pgsql_connections) != NULL)
54 {
55 pgsql_connections = cn->next;
56 DEBUG(D_lookup) debug_printf_indent("close PGSQL connection: %s\n", cn->server);
57 PQfinish(cn->handle);
58 }
59 }
60
61
62 /*************************************************
63 * Notice processor function for pgsql *
64 *************************************************/
65
66 /* This function is passed to pgsql below, and called for any PostgreSQL
67 "notices". By default they are written to stderr, which is undesirable.
68
69 Arguments:
70 arg an opaque user cookie (not used)
71 message the notice
72
73 Returns: nothing
74 */
75
76 static void
77 notice_processor(void *arg, const char *message)
78 {
79 arg = arg; /* Keep compiler happy */
80 DEBUG(D_lookup) debug_printf_indent("PGSQL: %s\n", message);
81 }
82
83
84
85 /*************************************************
86 * Internal search function *
87 *************************************************/
88
89 /* This function is called from the find entry point to do the search for a
90 single server. The server string is of the form "server/dbname/user/password".
91
92 PostgreSQL supports connections through Unix domain sockets. This is usually
93 faster and costs less cpu time than a TCP/IP connection. However it can only be
94 used if the mail server runs on the same machine as the database server. A
95 configuration line for PostgreSQL via Unix domain sockets looks like this:
96
97 hide pgsql_servers = (/tmp/.s.PGSQL.5432)/db/user/password[:<nextserver>]
98
99 We enclose the path name in parentheses so that its slashes aren't visually
100 confused with the delimiters for the other pgsql_server settings.
101
102 For TCP/IP connections, the server is a host name and optional port (with a
103 colon separator).
104
105 NOTE:
106 1) All three '/' must be present.
107 2) If host is omitted the local unix socket is used.
108
109 Arguments:
110 query the query string
111 server the server string; this is in dynamic memory and can be updated
112 resultptr where to store the result
113 errmsg where to point an error message
114 defer_break set TRUE if no more servers are to be tried after DEFER
115 do_cache set FALSE if data is changed
116 opts options list
117
118 Returns: OK, FAIL, or DEFER
119 */
120
121 static int
122 perform_pgsql_search(const uschar *query, uschar *server, uschar **resultptr,
123 uschar **errmsg, BOOL *defer_break, uint *do_cache, const uschar * opts)
124 {
125 PGconn *pg_conn = NULL;
126 PGresult *pg_result = NULL;
127
128 gstring * result = NULL;
129 int yield = DEFER;
130 unsigned int num_fields, num_tuples;
131 pgsql_connection *cn;
132 rmark reset_point = store_mark();
133 uschar *server_copy = NULL;
134 uschar *sdata[3];
135
136 /* Disaggregate the parameters from the server argument. The order is host or
137 path, database, user, password. We can write to the string, since it is in a
138 nextinlist temporary buffer. The copy of the string that is used for caching
139 has the password removed. This copy is also used for debugging output. */
140
141 for (int i = 2; i >= 0; i--)
142 {
143 uschar *pp = Ustrrchr(server, '/');
144 if (!pp)
145 {
146 *errmsg = string_sprintf("incomplete pgSQL server data: %s",
147 (i == 2)? server : server_copy);
148 *defer_break = TRUE;
149 return DEFER;
150 }
151 *pp++ = 0;
152 sdata[i] = pp;
153 if (i == 2) server_copy = string_copy(server); /* sans password */
154 }
155
156 /* The total server string has now been truncated so that what is left at the
157 start is the identification of the server (host or path). See if we have a
158 cached connection to the server. */
159
160 for (cn = pgsql_connections; cn; cn = cn->next)
161 if (Ustrcmp(cn->server, server_copy) == 0)
162 {
163 pg_conn = cn->handle;
164 break;
165 }
166
167 /* If there is no cached connection, we must set one up. */
168
169 if (!cn)
170 {
171 uschar *port = US"";
172
173 /* For a Unix domain socket connection, the path is in parentheses */
174
175 if (*server == '(')
176 {
177 uschar *last_slash, *last_dot, *p;
178
179 p = ++server;
180 while (*p && *p != ')') p++;
181 *p = 0;
182
183 last_slash = Ustrrchr(server, '/');
184 last_dot = Ustrrchr(server, '.');
185
186 DEBUG(D_lookup) debug_printf_indent("PGSQL new connection: socket=%s "
187 "database=%s user=%s\n", server, sdata[0], sdata[1]);
188
189 /* A valid socket name looks like this: /var/run/postgresql/.s.PGSQL.5432
190 We have to call PQsetdbLogin with '/var/run/postgresql' as the hostname
191 argument and put '5432' into the port variable. */
192
193 if (!last_slash || !last_dot)
194 {
195 *errmsg = string_sprintf("PGSQL invalid filename for socket: %s", server);
196 *defer_break = TRUE;
197 return DEFER;
198 }
199
200 /* Terminate the path name and set up the port: we'll have something like
201 server = "/var/run/postgresql" and port = "5432". */
202
203 *last_slash = 0;
204 port = last_dot + 1;
205 }
206
207 /* Host connection; sort out the port */
208
209 else
210 {
211 uschar *p;
212 if ((p = Ustrchr(server, ':')))
213 {
214 *p++ = 0;
215 port = p;
216 }
217
218 if (Ustrchr(server, '/'))
219 {
220 *errmsg = string_sprintf("unexpected slash in pgSQL server hostname: %s",
221 server);
222 *defer_break = TRUE;
223 return DEFER;
224 }
225
226 DEBUG(D_lookup) debug_printf_indent("PGSQL new connection: host=%s port=%s "
227 "database=%s user=%s\n", server, port, sdata[0], sdata[1]);
228 }
229
230 /* If the database is the empty string, set it NULL - the query must then
231 define it. */
232
233 if (sdata[0][0] == 0) sdata[0] = NULL;
234
235 /* Get store for a new handle, initialize it, and connect to the server */
236
237 pg_conn=PQsetdbLogin(
238 /* host port options tty database user passwd */
239 CS server, CS port, NULL, NULL, CS sdata[0], CS sdata[1], CS sdata[2]);
240
241 if(PQstatus(pg_conn) == CONNECTION_BAD)
242 {
243 reset_point = store_reset(reset_point);
244 *errmsg = string_sprintf("PGSQL connection failed: %s",
245 PQerrorMessage(pg_conn));
246 PQfinish(pg_conn);
247 goto PGSQL_EXIT;
248 }
249
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. */
253
254 PQsetClientEncoding(pg_conn, "SQL_ASCII");
255
256 /* Set the notice processor to prevent notices from being written to stderr
257 (which is what the default does). Our function (above) just produces debug
258 output. */
259
260 PQsetNoticeProcessor(pg_conn, notice_processor, NULL);
261
262 /* Add the connection to the cache */
263
264 cn = store_get(sizeof(pgsql_connection), FALSE);
265 cn->server = server_copy;
266 cn->handle = pg_conn;
267 cn->next = pgsql_connections;
268 pgsql_connections = cn;
269 }
270
271 /* Else use a previously cached connection */
272
273 else
274 {
275 DEBUG(D_lookup) debug_printf_indent("PGSQL using cached connection for %s\n",
276 server_copy);
277 }
278
279 /* Run the query */
280
281 pg_result = PQexec(pg_conn, CS query);
282 switch(PQresultStatus(pg_result))
283 {
284 case PGRES_EMPTY_QUERY:
285 case PGRES_COMMAND_OK:
286 /* The command was successful but did not return any data since it was
287 not SELECT but either an INSERT, UPDATE or DELETE statement. Tell the
288 high level code to not cache this query, and clean the current cache for
289 this handle by setting *do_cache zero. */
290
291 result = string_cat(result, US PQcmdTuples(pg_result));
292 *do_cache = 0;
293 DEBUG(D_lookup) debug_printf_indent("PGSQL: command does not return any data "
294 "but was successful. Rows affected: %s\n", string_from_gstring(result));
295 break;
296
297 case PGRES_TUPLES_OK:
298 break;
299
300 default:
301 /* This was the original code:
302 *errmsg = string_sprintf("PGSQL: query failed: %s\n",
303 PQresultErrorMessage(pg_result));
304 This was suggested by a user:
305 */
306
307 *errmsg = string_sprintf("PGSQL: query failed: %s (%s) (%s)\n",
308 PQresultErrorMessage(pg_result),
309 PQresStatus(PQresultStatus(pg_result)), query);
310 goto PGSQL_EXIT;
311 }
312
313 /* Result is in pg_result. Find the number of fields returned. If this is one,
314 we don't add field names to the data. Otherwise we do. If the query did not
315 return anything we skip the for loop; this also applies to the case
316 PGRES_COMMAND_OK. */
317
318 num_fields = PQnfields(pg_result);
319 num_tuples = PQntuples(pg_result);
320
321 /* Get the fields and construct the result string. If there is more than one
322 row, we insert '\n' between them. */
323
324 for (int i = 0; i < num_tuples; i++)
325 {
326 if (result)
327 result = string_catn(result, US"\n", 1);
328
329 if (num_fields == 1)
330 result = string_catn(result,
331 US PQgetvalue(pg_result, i, 0), PQgetlength(pg_result, i, 0));
332 else
333 for (int j = 0; j < num_fields; j++)
334 {
335 uschar *tmp = US PQgetvalue(pg_result, i, j);
336 result = lf_quote(US PQfname(pg_result, j), tmp, Ustrlen(tmp), result);
337 }
338 }
339
340 /* If result is NULL then no data has been found and so we return FAIL. */
341
342 if (!result)
343 {
344 yield = FAIL;
345 *errmsg = US"PGSQL: no data found";
346 }
347
348 /* Get here by goto from various error checks. */
349
350 PGSQL_EXIT:
351
352 /* Free store for any result that was got; don't close the connection, as
353 it is cached. */
354
355 if (pg_result) PQclear(pg_result);
356
357 /* Non-NULL result indicates a successful result */
358
359 if (result)
360 {
361 gstring_release_unused(result);
362 *resultptr = string_from_gstring(result);
363 return OK;
364 }
365 else
366 {
367 DEBUG(D_lookup) debug_printf_indent("%s\n", *errmsg);
368 return yield; /* FAIL or DEFER */
369 }
370 }
371
372
373
374
375 /*************************************************
376 * Find entry point *
377 *************************************************/
378
379 /* See local README for interface description. The handle and filename
380 arguments are not used. The code to loop through a list of servers while the
381 query is deferred with a retryable error is now in a separate function that is
382 shared with other SQL lookups. */
383
384 static int
385 pgsql_find(void * handle, const uschar * filename, const uschar * query,
386 int length, uschar ** result, uschar ** errmsg, uint * do_cache,
387 const uschar * opts)
388 {
389 return lf_sqlperform(US"PostgreSQL", US"pgsql_servers", pgsql_servers, query,
390 result, errmsg, do_cache, opts, perform_pgsql_search);
391 }
392
393
394
395 /*************************************************
396 * Quote entry point *
397 *************************************************/
398
399 /* The characters that always need to be quoted (with backslash) are newline,
400 tab, carriage return, backspace, backslash itself, and the quote characters.
401
402 The original code quoted single quotes as \' which is documented as valid in
403 the O'Reilly book "Practical PostgreSQL" (first edition) as an alternative to
404 the SQL standard '' way of representing a single quote as data. However, in
405 June 2006 there was some security issue with using \' and so this has been
406 changed.
407
408 [Note: There is a function called PQescapeStringConn() that quotes strings.
409 This cannot be used because it needs a PGconn argument (the connection handle).
410 Why, I don't know. Seems odd for just string escaping...]
411
412 Arguments:
413 s the string to be quoted
414 opt additional option text or NULL if none
415
416 Returns: the processed string or NULL for a bad option
417 */
418
419 static uschar *
420 pgsql_quote(uschar *s, uschar *opt)
421 {
422 register int c;
423 int count = 0;
424 uschar *t = s;
425 uschar *quoted;
426
427 if (opt != NULL) return NULL; /* No options recognized */
428
429 while ((c = *t++) != 0)
430 if (Ustrchr("\n\t\r\b\'\"\\", c) != NULL) count++;
431
432 if (count == 0) return s;
433 t = quoted = store_get(Ustrlen(s) + count + 1, is_tainted(s));
434
435 while ((c = *s++) != 0)
436 {
437 if (c == '\'')
438 {
439 *t++ = '\'';
440 *t++ = '\'';
441 }
442 else if (Ustrchr("\n\t\r\b\"\\", c) != NULL)
443 {
444 *t++ = '\\';
445 switch(c)
446 {
447 case '\n': *t++ = 'n';
448 break;
449 case '\t': *t++ = 't';
450 break;
451 case '\r': *t++ = 'r';
452 break;
453 case '\b': *t++ = 'b';
454 break;
455 default: *t++ = c;
456 break;
457 }
458 }
459 else *t++ = c;
460 }
461
462 *t = 0;
463 return quoted;
464 }
465
466
467 /*************************************************
468 * Version reporting entry point *
469 *************************************************/
470
471 /* See local README for interface description. */
472
473 #include "../version.h"
474
475 void
476 pgsql_version_report(FILE *f)
477 {
478 #ifdef DYNLOOKUP
479 fprintf(f, "Library version: PostgreSQL: Exim version %s\n", EXIM_VERSION_STR);
480 #endif
481
482 /* Version reporting: there appears to be no available information about
483 the client library in libpq-fe.h; once you have a connection object, you
484 can access the server version and the chosen protocol version, but those
485 aren't really what we want. It might make sense to debug_printf those
486 when the connection is established though? */
487 }
488
489
490 static lookup_info _lookup_info = {
491 .name = US"pgsql", /* lookup name */
492 .type = lookup_querystyle, /* query-style lookup */
493 .open = pgsql_open, /* open function */
494 .check = NULL, /* no check function */
495 .find = pgsql_find, /* find function */
496 .close = NULL, /* no close function */
497 .tidy = pgsql_tidy, /* tidy function */
498 .quote = pgsql_quote, /* quoting function */
499 .version_report = pgsql_version_report /* version reporting */
500 };
501
502 #ifdef DYNLOOKUP
503 #define pgsql_lookup_module_info _lookup_module_info
504 #endif
505
506 static lookup_info *_lookup_list[] = { &_lookup_info };
507 lookup_module_info pgsql_lookup_module_info = { LOOKUP_MODULE_INFO_MAGIC, _lookup_list, 1 };
508
509 /* End of lookups/pgsql.c */