constify
[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
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
acec9514 127gstring * result = NULL;
0756eb3c
PH
128int yield = DEFER;
129unsigned int num_fields, num_tuples;
0756eb3c 130pgsql_connection *cn;
f3ebb786 131rmark reset_point = store_mark();
0756eb3c
PH
132uschar *server_copy = NULL;
133uschar *sdata[3];
134
135/* Disaggregate the parameters from the server argument. The order is host or
136path, database, user, password. We can write to the string, since it is in a
137nextinlist temporary buffer. The copy of the string that is used for caching
138has the password removed. This copy is also used for debugging output. */
139
d7978c0f 140for (int i = 2; i >= 0; i--)
0756eb3c
PH
141 {
142 uschar *pp = Ustrrchr(server, '/');
bbfb5dcd 143 if (!pp)
0756eb3c
PH
144 {
145 *errmsg = string_sprintf("incomplete pgSQL server data: %s",
146 (i == 2)? server : server_copy);
147 *defer_break = TRUE;
148 return DEFER;
149 }
150 *pp++ = 0;
151 sdata[i] = pp;
152 if (i == 2) server_copy = string_copy(server); /* sans password */
153 }
154
155/* The total server string has now been truncated so that what is left at the
156start is the identification of the server (host or path). See if we have a
157cached connection to the server. */
158
bbfb5dcd 159for (cn = pgsql_connections; cn; cn = cn->next)
0756eb3c
PH
160 if (Ustrcmp(cn->server, server_copy) == 0)
161 {
162 pg_conn = cn->handle;
163 break;
164 }
0756eb3c
PH
165
166/* If there is no cached connection, we must set one up. */
167
bbfb5dcd 168if (!cn)
0756eb3c
PH
169 {
170 uschar *port = US"";
171
172 /* For a Unix domain socket connection, the path is in parentheses */
173
174 if (*server == '(')
175 {
176 uschar *last_slash, *last_dot, *p;
177
178 p = ++server;
bbfb5dcd 179 while (*p && *p != ')') p++;
0756eb3c
PH
180 *p = 0;
181
182 last_slash = Ustrrchr(server, '/');
183 last_dot = Ustrrchr(server, '.');
184
42c7f0b4 185 DEBUG(D_lookup) debug_printf_indent("PGSQL new connection: socket=%s "
0756eb3c
PH
186 "database=%s user=%s\n", server, sdata[0], sdata[1]);
187
188 /* A valid socket name looks like this: /var/run/postgresql/.s.PGSQL.5432
189 We have to call PQsetdbLogin with '/var/run/postgresql' as the hostname
190 argument and put '5432' into the port variable. */
191
bbfb5dcd 192 if (!last_slash || !last_dot)
0756eb3c 193 {
bbfb5dcd 194 *errmsg = string_sprintf("PGSQL invalid filename for socket: %s", server);
0756eb3c
PH
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;
bbfb5dcd 211 if ((p = Ustrchr(server, ':')))
0756eb3c
PH
212 {
213 *p++ = 0;
214 port = p;
215 }
216
bbfb5dcd 217 if (Ustrchr(server, '/'))
0756eb3c
PH
218 {
219 *errmsg = string_sprintf("unexpected slash in pgSQL server hostname: %s",
220 server);
221 *defer_break = TRUE;
222 return DEFER;
223 }
224
42c7f0b4 225 DEBUG(D_lookup) debug_printf_indent("PGSQL new connection: host=%s port=%s "
0756eb3c
PH
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 {
f3ebb786 242 reset_point = store_reset(reset_point);
0756eb3c
PH
243 *errmsg = string_sprintf("PGSQL connection failed: %s",
244 PQerrorMessage(pg_conn));
245 PQfinish(pg_conn);
0756eb3c
PH
246 goto PGSQL_EXIT;
247 }
248
16282d2b
PH
249 /* Set the client encoding to SQL_ASCII, which means that the server will
250 not try to interpret the query as being in any fancy encoding such as UTF-8
251 or other multibyte code that might cause problems with escaping. */
252
253 PQsetClientEncoding(pg_conn, "SQL_ASCII");
254
034d99ab
PH
255 /* Set the notice processor to prevent notices from being written to stderr
256 (which is what the default does). Our function (above) just produces debug
257 output. */
258
259 PQsetNoticeProcessor(pg_conn, notice_processor, NULL);
260
0756eb3c
PH
261 /* Add the connection to the cache */
262
f3ebb786 263 cn = store_get(sizeof(pgsql_connection), FALSE);
0756eb3c
PH
264 cn->server = server_copy;
265 cn->handle = pg_conn;
266 cn->next = pgsql_connections;
267 pgsql_connections = cn;
268 }
269
270/* Else use a previously cached connection */
271
272else
273 {
42c7f0b4 274 DEBUG(D_lookup) debug_printf_indent("PGSQL using cached connection for %s\n",
0756eb3c
PH
275 server_copy);
276 }
277
278/* Run the query */
279
bbfb5dcd
JH
280pg_result = PQexec(pg_conn, CS query);
281switch(PQresultStatus(pg_result))
282 {
283 case PGRES_EMPTY_QUERY:
284 case PGRES_COMMAND_OK:
285 /* The command was successful but did not return any data since it was
286 not SELECT but either an INSERT, UPDATE or DELETE statement. Tell the
287 high level code to not cache this query, and clean the current cache for
288 this handle by setting *do_cache zero. */
289
290 result = string_cat(result, US PQcmdTuples(pg_result));
291 *do_cache = 0;
42c7f0b4 292 DEBUG(D_lookup) debug_printf_indent("PGSQL: command does not return any data "
bbfb5dcd
JH
293 "but was successful. Rows affected: %s\n", string_from_gstring(result));
294 break;
295
296 case PGRES_TUPLES_OK:
297 break;
298
299 default:
300 /* This was the original code:
301 *errmsg = string_sprintf("PGSQL: query failed: %s\n",
302 PQresultErrorMessage(pg_result));
303 This was suggested by a user:
304 */
305
306 *errmsg = string_sprintf("PGSQL: query failed: %s (%s) (%s)\n",
307 PQresultErrorMessage(pg_result),
308 PQresStatus(PQresultStatus(pg_result)), query);
309 goto PGSQL_EXIT;
310 }
0756eb3c
PH
311
312/* Result is in pg_result. Find the number of fields returned. If this is one,
313we don't add field names to the data. Otherwise we do. If the query did not
314return anything we skip the for loop; this also applies to the case
315PGRES_COMMAND_OK. */
316
317num_fields = PQnfields(pg_result);
318num_tuples = PQntuples(pg_result);
319
320/* Get the fields and construct the result string. If there is more than one
321row, we insert '\n' between them. */
322
d7978c0f 323for (int i = 0; i < num_tuples; i++)
0756eb3c 324 {
acec9514
JH
325 if (result)
326 result = string_catn(result, US"\n", 1);
0756eb3c 327
acec9514 328 if (num_fields == 1)
bbfb5dcd 329 result = string_catn(result,
acec9514
JH
330 US PQgetvalue(pg_result, i, 0), PQgetlength(pg_result, i, 0));
331 else
d7978c0f 332 for (int j = 0; j < num_fields; j++)
0756eb3c
PH
333 {
334 uschar *tmp = US PQgetvalue(pg_result, i, j);
acec9514 335 result = lf_quote(US PQfname(pg_result, j), tmp, Ustrlen(tmp), result);
0756eb3c 336 }
0756eb3c
PH
337 }
338
bbfb5dcd 339/* If result is NULL then no data has been found and so we return FAIL. */
0756eb3c 340
acec9514 341if (!result)
0756eb3c
PH
342 {
343 yield = FAIL;
344 *errmsg = US"PGSQL: no data found";
345 }
0756eb3c
PH
346
347/* Get here by goto from various error checks. */
348
349PGSQL_EXIT:
350
351/* Free store for any result that was got; don't close the connection, as
352it is cached. */
353
acec9514 354if (pg_result) PQclear(pg_result);
0756eb3c 355
4c04137d 356/* Non-NULL result indicates a successful result */
0756eb3c 357
acec9514 358if (result)
0756eb3c 359 {
f3ebb786 360 gstring_release_unused(result);
acec9514 361 *resultptr = string_from_gstring(result);
0756eb3c
PH
362 return OK;
363 }
364else
365 {
42c7f0b4 366 DEBUG(D_lookup) debug_printf_indent("%s\n", *errmsg);
0756eb3c
PH
367 return yield; /* FAIL or DEFER */
368 }
369}
370
371
372
373
374/*************************************************
375* Find entry point *
376*************************************************/
377
378/* See local README for interface description. The handle and filename
b7670459
PH
379arguments are not used. The code to loop through a list of servers while the
380query is deferred with a retryable error is now in a separate function that is
381shared with other SQL lookups. */
0756eb3c 382
e6d225ae 383static int
d447dbd1
JH
384pgsql_find(void * handle, const uschar * filename, const uschar * query,
385 int length, uschar ** result, uschar ** errmsg, uint * do_cache)
0756eb3c 386{
b7670459
PH
387return lf_sqlperform(US"PostgreSQL", US"pgsql_servers", pgsql_servers, query,
388 result, errmsg, do_cache, perform_pgsql_search);
0756eb3c
PH
389}
390
391
392
393/*************************************************
394* Quote entry point *
395*************************************************/
396
397/* The characters that always need to be quoted (with backslash) are newline,
398tab, carriage return, backspace, backslash itself, and the quote characters.
0756eb3c 399
5547e2c5
PH
400The original code quoted single quotes as \' which is documented as valid in
401the O'Reilly book "Practical PostgreSQL" (first edition) as an alternative to
402the SQL standard '' way of representing a single quote as data. However, in
403June 2006 there was some security issue with using \' and so this has been
404changed.
405
406[Note: There is a function called PQescapeStringConn() that quotes strings.
407This cannot be used because it needs a PGconn argument (the connection handle).
408Why, I don't know. Seems odd for just string escaping...]
409
0756eb3c
PH
410Arguments:
411 s the string to be quoted
412 opt additional option text or NULL if none
413
414Returns: the processed string or NULL for a bad option
415*/
416
e6d225ae 417static uschar *
0756eb3c
PH
418pgsql_quote(uschar *s, uschar *opt)
419{
420register int c;
421int count = 0;
422uschar *t = s;
423uschar *quoted;
424
425if (opt != NULL) return NULL; /* No options recognized */
426
427while ((c = *t++) != 0)
376d2ec0 428 if (Ustrchr("\n\t\r\b\'\"\\", c) != NULL) count++;
0756eb3c
PH
429
430if (count == 0) return s;
f3ebb786 431t = quoted = store_get(Ustrlen(s) + count + 1, is_tainted(s));
0756eb3c
PH
432
433while ((c = *s++) != 0)
434 {
5547e2c5
PH
435 if (c == '\'')
436 {
437 *t++ = '\'';
438 *t++ = '\'';
439 }
376d2ec0 440 else if (Ustrchr("\n\t\r\b\"\\", c) != NULL)
0756eb3c
PH
441 {
442 *t++ = '\\';
443 switch(c)
444 {
445 case '\n': *t++ = 'n';
446 break;
447 case '\t': *t++ = 't';
448 break;
449 case '\r': *t++ = 'r';
450 break;
451 case '\b': *t++ = 'b';
452 break;
453 default: *t++ = c;
454 break;
455 }
456 }
457 else *t++ = c;
458 }
459
460*t = 0;
461return quoted;
462}
463
6545de78
PP
464
465/*************************************************
466* Version reporting entry point *
467*************************************************/
468
469/* See local README for interface description. */
470
471#include "../version.h"
472
473void
474pgsql_version_report(FILE *f)
475{
476#ifdef DYNLOOKUP
477fprintf(f, "Library version: PostgreSQL: Exim version %s\n", EXIM_VERSION_STR);
478#endif
479
480/* Version reporting: there appears to be no available information about
481the client library in libpq-fe.h; once you have a connection object, you
482can access the server version and the chosen protocol version, but those
483aren't really what we want. It might make sense to debug_printf those
484when the connection is established though? */
485}
486
487
e6d225ae
DW
488static lookup_info _lookup_info = {
489 US"pgsql", /* lookup name */
490 lookup_querystyle, /* query-style lookup */
491 pgsql_open, /* open function */
492 NULL, /* no check function */
493 pgsql_find, /* find function */
494 NULL, /* no close function */
495 pgsql_tidy, /* tidy function */
6545de78
PP
496 pgsql_quote, /* quoting function */
497 pgsql_version_report /* version reporting */
e6d225ae
DW
498};
499
500#ifdef DYNLOOKUP
501#define pgsql_lookup_module_info _lookup_module_info
502#endif
503
504static lookup_info *_lookup_list[] = { &_lookup_info };
505lookup_module_info pgsql_lookup_module_info = { LOOKUP_MODULE_INFO_MAGIC, _lookup_list, 1 };
0756eb3c
PH
506
507/* End of lookups/pgsql.c */