C99 initialisers
[exim.git] / src / src / lookups / pgsql.c
CommitLineData
0756eb3c
PH
1/*************************************************
2* Exim - an Internet mail transport agent *
3*************************************************/
4
f9ba5e22 5/* Copyright (c) University of Cambridge 1995 - 2018 */
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 *
d447dbd1 36pgsql_open(const uschar * filename, uschar ** errmsg)
0756eb3c
PH
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;
42c7f0b4 56 DEBUG(D_lookup) debug_printf_indent("close PGSQL connection: %s\n", cn->server);
0756eb3c
PH
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 */
42c7f0b4 80DEBUG(D_lookup) debug_printf_indent("PGSQL: %s\n", message);
034d99ab
PH
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
4c04137d 100confused with the delimiters for the other pgsql_server settings.
0756eb3c
PH
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 115 do_cache set FALSE if data is changed
0b4dfe7a 116 opts options list
0756eb3c
PH
117
118Returns: OK, FAIL, or DEFER
119*/
120
121static int
55414b25 122perform_pgsql_search(const uschar *query, uschar *server, uschar **resultptr,
0b4dfe7a 123 uschar **errmsg, BOOL *defer_break, uint *do_cache, const uschar * opts)
0756eb3c
PH
124{
125PGconn *pg_conn = NULL;
126PGresult *pg_result = NULL;
127
acec9514 128gstring * result = NULL;
0756eb3c
PH
129int yield = DEFER;
130unsigned int num_fields, num_tuples;
0756eb3c 131pgsql_connection *cn;
f3ebb786 132rmark reset_point = store_mark();
0756eb3c
PH
133uschar *server_copy = NULL;
134uschar *sdata[3];
135
136/* Disaggregate the parameters from the server argument. The order is host or
137path, database, user, password. We can write to the string, since it is in a
138nextinlist temporary buffer. The copy of the string that is used for caching
139has the password removed. This copy is also used for debugging output. */
140
d7978c0f 141for (int i = 2; i >= 0; i--)
0756eb3c
PH
142 {
143 uschar *pp = Ustrrchr(server, '/');
bbfb5dcd 144 if (!pp)
0756eb3c
PH
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
157start is the identification of the server (host or path). See if we have a
158cached connection to the server. */
159
bbfb5dcd 160for (cn = pgsql_connections; cn; cn = cn->next)
0756eb3c
PH
161 if (Ustrcmp(cn->server, server_copy) == 0)
162 {
163 pg_conn = cn->handle;
164 break;
165 }
0756eb3c
PH
166
167/* If there is no cached connection, we must set one up. */
168
bbfb5dcd 169if (!cn)
0756eb3c
PH
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;
bbfb5dcd 180 while (*p && *p != ')') p++;
0756eb3c
PH
181 *p = 0;
182
183 last_slash = Ustrrchr(server, '/');
184 last_dot = Ustrrchr(server, '.');
185
42c7f0b4 186 DEBUG(D_lookup) debug_printf_indent("PGSQL new connection: socket=%s "
0756eb3c
PH
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
bbfb5dcd 193 if (!last_slash || !last_dot)
0756eb3c 194 {
bbfb5dcd 195 *errmsg = string_sprintf("PGSQL invalid filename for socket: %s", server);
0756eb3c
PH
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;
bbfb5dcd 212 if ((p = Ustrchr(server, ':')))
0756eb3c
PH
213 {
214 *p++ = 0;
215 port = p;
216 }
217
bbfb5dcd 218 if (Ustrchr(server, '/'))
0756eb3c
PH
219 {
220 *errmsg = string_sprintf("unexpected slash in pgSQL server hostname: %s",
221 server);
222 *defer_break = TRUE;
223 return DEFER;
224 }
225
42c7f0b4 226 DEBUG(D_lookup) debug_printf_indent("PGSQL new connection: host=%s port=%s "
0756eb3c
PH
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 {
f3ebb786 243 reset_point = store_reset(reset_point);
0756eb3c
PH
244 *errmsg = string_sprintf("PGSQL connection failed: %s",
245 PQerrorMessage(pg_conn));
246 PQfinish(pg_conn);
0756eb3c
PH
247 goto PGSQL_EXIT;
248 }
249
16282d2b
PH
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
034d99ab
PH
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
0756eb3c
PH
262 /* Add the connection to the cache */
263
f3ebb786 264 cn = store_get(sizeof(pgsql_connection), FALSE);
0756eb3c
PH
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
273else
274 {
42c7f0b4 275 DEBUG(D_lookup) debug_printf_indent("PGSQL using cached connection for %s\n",
0756eb3c
PH
276 server_copy);
277 }
278
279/* Run the query */
280
bbfb5dcd
JH
281pg_result = PQexec(pg_conn, CS query);
282switch(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;
42c7f0b4 293 DEBUG(D_lookup) debug_printf_indent("PGSQL: command does not return any data "
bbfb5dcd
JH
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 }
0756eb3c
PH
312
313/* Result is in pg_result. Find the number of fields returned. If this is one,
314we don't add field names to the data. Otherwise we do. If the query did not
315return anything we skip the for loop; this also applies to the case
316PGRES_COMMAND_OK. */
317
318num_fields = PQnfields(pg_result);
319num_tuples = PQntuples(pg_result);
320
321/* Get the fields and construct the result string. If there is more than one
322row, we insert '\n' between them. */
323
d7978c0f 324for (int i = 0; i < num_tuples; i++)
0756eb3c 325 {
acec9514
JH
326 if (result)
327 result = string_catn(result, US"\n", 1);
0756eb3c 328
acec9514 329 if (num_fields == 1)
bbfb5dcd 330 result = string_catn(result,
acec9514
JH
331 US PQgetvalue(pg_result, i, 0), PQgetlength(pg_result, i, 0));
332 else
d7978c0f 333 for (int j = 0; j < num_fields; j++)
0756eb3c
PH
334 {
335 uschar *tmp = US PQgetvalue(pg_result, i, j);
acec9514 336 result = lf_quote(US PQfname(pg_result, j), tmp, Ustrlen(tmp), result);
0756eb3c 337 }
0756eb3c
PH
338 }
339
bbfb5dcd 340/* If result is NULL then no data has been found and so we return FAIL. */
0756eb3c 341
acec9514 342if (!result)
0756eb3c
PH
343 {
344 yield = FAIL;
345 *errmsg = US"PGSQL: no data found";
346 }
0756eb3c
PH
347
348/* Get here by goto from various error checks. */
349
350PGSQL_EXIT:
351
352/* Free store for any result that was got; don't close the connection, as
353it is cached. */
354
acec9514 355if (pg_result) PQclear(pg_result);
0756eb3c 356
4c04137d 357/* Non-NULL result indicates a successful result */
0756eb3c 358
acec9514 359if (result)
0756eb3c 360 {
f3ebb786 361 gstring_release_unused(result);
acec9514 362 *resultptr = string_from_gstring(result);
0756eb3c
PH
363 return OK;
364 }
365else
366 {
42c7f0b4 367 DEBUG(D_lookup) debug_printf_indent("%s\n", *errmsg);
0756eb3c
PH
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
b7670459
PH
380arguments are not used. The code to loop through a list of servers while the
381query is deferred with a retryable error is now in a separate function that is
382shared with other SQL lookups. */
0756eb3c 383
e6d225ae 384static int
d447dbd1 385pgsql_find(void * handle, const uschar * filename, const uschar * query,
67a57a5a
JH
386 int length, uschar ** result, uschar ** errmsg, uint * do_cache,
387 const uschar * opts)
0756eb3c 388{
b7670459 389return lf_sqlperform(US"PostgreSQL", US"pgsql_servers", pgsql_servers, query,
0b4dfe7a 390 result, errmsg, do_cache, opts, perform_pgsql_search);
0756eb3c
PH
391}
392
393
394
395/*************************************************
396* Quote entry point *
397*************************************************/
398
399/* The characters that always need to be quoted (with backslash) are newline,
400tab, carriage return, backspace, backslash itself, and the quote characters.
0756eb3c 401
5547e2c5
PH
402The original code quoted single quotes as \' which is documented as valid in
403the O'Reilly book "Practical PostgreSQL" (first edition) as an alternative to
404the SQL standard '' way of representing a single quote as data. However, in
405June 2006 there was some security issue with using \' and so this has been
406changed.
407
408[Note: There is a function called PQescapeStringConn() that quotes strings.
409This cannot be used because it needs a PGconn argument (the connection handle).
410Why, I don't know. Seems odd for just string escaping...]
411
0756eb3c
PH
412Arguments:
413 s the string to be quoted
414 opt additional option text or NULL if none
415
416Returns: the processed string or NULL for a bad option
417*/
418
e6d225ae 419static uschar *
0756eb3c
PH
420pgsql_quote(uschar *s, uschar *opt)
421{
422register int c;
423int count = 0;
424uschar *t = s;
425uschar *quoted;
426
427if (opt != NULL) return NULL; /* No options recognized */
428
429while ((c = *t++) != 0)
376d2ec0 430 if (Ustrchr("\n\t\r\b\'\"\\", c) != NULL) count++;
0756eb3c
PH
431
432if (count == 0) return s;
f3ebb786 433t = quoted = store_get(Ustrlen(s) + count + 1, is_tainted(s));
0756eb3c
PH
434
435while ((c = *s++) != 0)
436 {
5547e2c5
PH
437 if (c == '\'')
438 {
439 *t++ = '\'';
440 *t++ = '\'';
441 }
376d2ec0 442 else if (Ustrchr("\n\t\r\b\"\\", c) != NULL)
0756eb3c
PH
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;
463return quoted;
464}
465
6545de78
PP
466
467/*************************************************
468* Version reporting entry point *
469*************************************************/
470
471/* See local README for interface description. */
472
473#include "../version.h"
474
475void
476pgsql_version_report(FILE *f)
477{
478#ifdef DYNLOOKUP
479fprintf(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
483the client library in libpq-fe.h; once you have a connection object, you
484can access the server version and the chosen protocol version, but those
485aren't really what we want. It might make sense to debug_printf those
486when the connection is established though? */
487}
488
489
e6d225ae 490static lookup_info _lookup_info = {
9f400174
JH
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 */
e6d225ae
DW
500};
501
502#ifdef DYNLOOKUP
503#define pgsql_lookup_module_info _lookup_module_info
504#endif
505
506static lookup_info *_lookup_list[] = { &_lookup_info };
507lookup_module_info pgsql_lookup_module_info = { LOOKUP_MODULE_INFO_MAGIC, _lookup_list, 1 };
0756eb3c
PH
508
509/* End of lookups/pgsql.c */