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