1 /*************************************************
2 * Exim - an Internet mail transport agent *
3 *************************************************/
5 /* Copyright (c) The Exim Maintainers 2020 - 2022 */
6 /* Copyright (c) University of Cambridge 1995 - 2018 */
7 /* See the file NOTICE for conditions of use and distribution. */
8 /* SPDX-License-Identifier: GPL-2.0-or-later */
10 /* Thanks to Petr Cech for contributing the original code for these
11 functions. Thanks to Joachim Wieland for the initial patch for the Unix domain
15 #include "lf_functions.h"
17 #include <libpq-fe.h> /* The system header */
19 /* Structure and anchor for caching connections. */
21 typedef struct pgsql_connection {
22 struct pgsql_connection *next;
27 static pgsql_connection *pgsql_connections = NULL;
31 /*************************************************
33 *************************************************/
35 /* See local README for interface description. */
38 pgsql_open(const uschar * filename, uschar ** errmsg)
40 return (void *)(1); /* Just return something non-null */
45 /*************************************************
47 *************************************************/
49 /* See local README for interface description. */
55 while ((cn = pgsql_connections) != NULL)
57 pgsql_connections = cn->next;
58 DEBUG(D_lookup) debug_printf_indent("close PGSQL connection: %s\n", cn->server);
64 /*************************************************
65 * Notice processor function for pgsql *
66 *************************************************/
68 /* This function is passed to pgsql below, and called for any PostgreSQL
69 "notices". By default they are written to stderr, which is undesirable.
72 arg an opaque user cookie (not used)
79 notice_processor(void *arg, const char *message)
81 arg = arg; /* Keep compiler happy */
82 DEBUG(D_lookup) debug_printf_indent("PGSQL: %s\n", message);
87 /*************************************************
88 * Internal search function *
89 *************************************************/
91 /* This function is called from the find entry point to do the search for a
92 single server. The server string is of the form "server/dbname/user/password".
94 PostgreSQL supports connections through Unix domain sockets. This is usually
95 faster and costs less cpu time than a TCP/IP connection. However it can only be
96 used if the mail server runs on the same machine as the database server. A
97 configuration line for PostgreSQL via Unix domain sockets looks like this:
99 hide pgsql_servers = (/tmp/.s.PGSQL.5432)/db/user/password[:<nextserver>]
101 We enclose the path name in parentheses so that its slashes aren't visually
102 confused with the delimiters for the other pgsql_server settings.
104 For TCP/IP connections, the server is a host name and optional port (with a
108 1) All three '/' must be present.
109 2) If host is omitted the local unix socket is used.
112 query the query string
113 server the server string; this is in dynamic memory and can be updated
114 resultptr where to store the result
115 errmsg where to point an error message
116 defer_break set TRUE if no more servers are to be tried after DEFER
117 do_cache set FALSE if data is changed
120 Returns: OK, FAIL, or DEFER
124 perform_pgsql_search(const uschar *query, uschar *server, uschar **resultptr,
125 uschar **errmsg, BOOL *defer_break, uint *do_cache, const uschar * opts)
127 PGconn *pg_conn = NULL;
128 PGresult *pg_result = NULL;
130 gstring * result = NULL;
132 unsigned int num_fields, num_tuples;
133 pgsql_connection *cn;
134 rmark reset_point = store_mark();
135 uschar *server_copy = NULL;
138 /* Disaggregate the parameters from the server argument. The order is host or
139 path, database, user, password. We can write to the string, since it is in a
140 nextinlist temporary buffer. The copy of the string that is used for caching
141 has the password removed. This copy is also used for debugging output. */
143 for (int i = 2; i >= 0; i--)
145 uschar *pp = Ustrrchr(server, '/');
148 *errmsg = string_sprintf("incomplete pgSQL server data: %s",
149 (i == 2)? server : server_copy);
155 if (i == 2) server_copy = string_copy(server); /* sans password */
158 /* The total server string has now been truncated so that what is left at the
159 start is the identification of the server (host or path). See if we have a
160 cached connection to the server. */
162 for (cn = pgsql_connections; cn; cn = cn->next)
163 if (Ustrcmp(cn->server, server_copy) == 0)
165 pg_conn = cn->handle;
169 /* If there is no cached connection, we must set one up. */
175 /* For a Unix domain socket connection, the path is in parentheses */
179 uschar *last_slash, *last_dot, *p;
182 while (*p && *p != ')') p++;
185 last_slash = Ustrrchr(server, '/');
186 last_dot = Ustrrchr(server, '.');
188 DEBUG(D_lookup) debug_printf_indent("PGSQL new connection: socket=%s "
189 "database=%s user=%s\n", server, sdata[0], sdata[1]);
191 /* A valid socket name looks like this: /var/run/postgresql/.s.PGSQL.5432
192 We have to call PQsetdbLogin with '/var/run/postgresql' as the hostname
193 argument and put '5432' into the port variable. */
195 if (!last_slash || !last_dot)
197 *errmsg = string_sprintf("PGSQL invalid filename for socket: %s", server);
202 /* Terminate the path name and set up the port: we'll have something like
203 server = "/var/run/postgresql" and port = "5432". */
209 /* Host connection; sort out the port */
214 if ((p = Ustrchr(server, ':')))
220 if (Ustrchr(server, '/'))
222 *errmsg = string_sprintf("unexpected slash in pgSQL server hostname: %s",
228 DEBUG(D_lookup) debug_printf_indent("PGSQL new connection: host=%s port=%s "
229 "database=%s user=%s\n", server, port, sdata[0], sdata[1]);
232 /* If the database is the empty string, set it NULL - the query must then
235 if (sdata[0][0] == 0) sdata[0] = NULL;
237 /* Get store for a new handle, initialize it, and connect to the server */
239 pg_conn=PQsetdbLogin(
240 /* host port options tty database user passwd */
241 CS server, CS port, NULL, NULL, CS sdata[0], CS sdata[1], CS sdata[2]);
243 if(PQstatus(pg_conn) == CONNECTION_BAD)
245 reset_point = store_reset(reset_point);
246 *errmsg = string_sprintf("PGSQL connection failed: %s",
247 PQerrorMessage(pg_conn));
252 /* Set the client encoding to SQL_ASCII, which means that the server will
253 not try to interpret the query as being in any fancy encoding such as UTF-8
254 or other multibyte code that might cause problems with escaping. */
256 PQsetClientEncoding(pg_conn, "SQL_ASCII");
258 /* Set the notice processor to prevent notices from being written to stderr
259 (which is what the default does). Our function (above) just produces debug
262 PQsetNoticeProcessor(pg_conn, notice_processor, NULL);
264 /* Add the connection to the cache */
266 cn = store_get(sizeof(pgsql_connection), GET_UNTAINTED);
267 cn->server = server_copy;
268 cn->handle = pg_conn;
269 cn->next = pgsql_connections;
270 pgsql_connections = cn;
273 /* Else use a previously cached connection */
277 DEBUG(D_lookup) debug_printf_indent("PGSQL using cached connection for %s\n",
283 pg_result = PQexec(pg_conn, CS query);
284 switch(PQresultStatus(pg_result))
286 case PGRES_EMPTY_QUERY:
287 case PGRES_COMMAND_OK:
288 /* The command was successful but did not return any data since it was
289 not SELECT but either an INSERT, UPDATE or DELETE statement. Tell the
290 high level code to not cache this query, and clean the current cache for
291 this handle by setting *do_cache zero. */
293 result = string_cat(result, US PQcmdTuples(pg_result));
295 DEBUG(D_lookup) debug_printf_indent("PGSQL: command does not return any data "
296 "but was successful. Rows affected: %Y\n", result);
299 case PGRES_TUPLES_OK:
303 /* This was the original code:
304 *errmsg = string_sprintf("PGSQL: query failed: %s\n",
305 PQresultErrorMessage(pg_result));
306 This was suggested by a user:
309 *errmsg = string_sprintf("PGSQL: query failed: %s (%s) (%s)\n",
310 PQresultErrorMessage(pg_result),
311 PQresStatus(PQresultStatus(pg_result)), query);
315 /* Result is in pg_result. Find the number of fields returned. If this is one,
316 we don't add field names to the data. Otherwise we do. If the query did not
317 return anything we skip the for loop; this also applies to the case
320 num_fields = PQnfields(pg_result);
321 num_tuples = PQntuples(pg_result);
323 /* Get the fields and construct the result string. If there is more than one
324 row, we insert '\n' between them. */
326 for (int i = 0; i < num_tuples; i++)
329 result = string_catn(result, US"\n", 1);
332 result = string_catn(result,
333 US PQgetvalue(pg_result, i, 0), PQgetlength(pg_result, i, 0));
335 for (int j = 0; j < num_fields; j++)
337 uschar *tmp = US PQgetvalue(pg_result, i, j);
338 result = lf_quote(US PQfname(pg_result, j), tmp, Ustrlen(tmp), result);
340 if (!result) result = string_get(1);
343 /* If result is NULL then no data has been found and so we return FAIL. */
348 *errmsg = US"PGSQL: no data found";
351 /* Get here by goto from various error checks. */
355 /* Free store for any result that was got; don't close the connection, as
358 if (pg_result) PQclear(pg_result);
360 /* Non-NULL result indicates a successful result */
364 gstring_release_unused(result);
365 *resultptr = string_from_gstring(result);
370 DEBUG(D_lookup) debug_printf_indent("%s\n", *errmsg);
371 return yield; /* FAIL or DEFER */
378 /*************************************************
380 *************************************************/
382 /* See local README for interface description. The handle and filename
383 arguments are not used. The code to loop through a list of servers while the
384 query is deferred with a retryable error is now in a separate function that is
385 shared with other SQL lookups. */
388 pgsql_find(void * handle, const uschar * filename, const uschar * query,
389 int length, uschar ** result, uschar ** errmsg, uint * do_cache,
392 return lf_sqlperform(US"PostgreSQL", US"pgsql_servers", pgsql_servers, query,
393 result, errmsg, do_cache, opts, perform_pgsql_search);
398 /*************************************************
399 * Quote entry point *
400 *************************************************/
402 /* The characters that always need to be quoted (with backslash) are newline,
403 tab, carriage return, backspace, backslash itself, and the quote characters.
405 The original code quoted single quotes as \' which is documented as valid in
406 the O'Reilly book "Practical PostgreSQL" (first edition) as an alternative to
407 the SQL standard '' way of representing a single quote as data. However, in
408 June 2006 there was some security issue with using \' and so this has been
411 [Note: There is a function called PQescapeStringConn() that quotes strings.
412 This cannot be used because it needs a PGconn argument (the connection handle).
413 Why, I don't know. Seems odd for just string escaping...]
416 s the string to be quoted
417 opt additional option text or NULL if none
418 idx lookup type index
420 Returns: the processed string or NULL for a bad option
424 pgsql_quote(uschar * s, uschar * opt, unsigned idx)
427 uschar * t = s, * quoted;
429 if (opt) return NULL; /* No options recognized */
432 if (Ustrchr("\n\t\r\b\'\"\\", c) != NULL) count++;
434 t = quoted = store_get_quoted(Ustrlen(s) + count + 1, s, idx);
443 else if (Ustrchr("\n\t\r\b\"\\", c) != NULL)
448 case '\n': *t++ = 'n'; break;
449 case '\t': *t++ = 't'; break;
450 case '\r': *t++ = 'r'; break;
451 case '\b': *t++ = 'b'; break;
452 default: *t++ = c; break;
463 /*************************************************
464 * Version reporting entry point *
465 *************************************************/
467 /* See local README for interface description. */
469 #include "../version.h"
472 pgsql_version_report(gstring * g)
475 g = string_fmt_append(g, "Library version: PostgreSQL: Exim version %s\n", EXIM_VERSION_STR);
478 /* Version reporting: there appears to be no available information about
479 the client library in libpq-fe.h; once you have a connection object, you
480 can access the server version and the chosen protocol version, but those
481 aren't really what we want. It might make sense to debug_printf those
482 when the connection is established though? */
488 static lookup_info _lookup_info = {
489 .name = US"pgsql", /* lookup name */
490 .type = lookup_querystyle, /* query-style lookup */
491 .open = pgsql_open, /* open function */
492 .check = NULL, /* no check function */
493 .find = pgsql_find, /* find function */
494 .close = NULL, /* no close function */
495 .tidy = pgsql_tidy, /* tidy function */
496 .quote = pgsql_quote, /* quoting function */
497 .version_report = pgsql_version_report /* version reporting */
501 #define pgsql_lookup_module_info _lookup_module_info
504 static lookup_info *_lookup_list[] = { &_lookup_info };
505 lookup_module_info pgsql_lookup_module_info = { LOOKUP_MODULE_INFO_MAGIC, _lookup_list, 1 };
507 /* End of lookups/pgsql.c */