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