1 /*************************************************
2 * Exim - an Internet mail transport agent *
3 *************************************************/
5 /* Copyright (c) University of Cambridge 1995 - 2018 */
6 /* Copyright (c) The Exim Maintainers 2020 */
7 /* See the file NOTICE for conditions of use and distribution. */
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
14 #include "lf_functions.h"
16 #include <libpq-fe.h> /* The system header */
18 /* Structure and anchor for caching connections. */
20 typedef struct pgsql_connection {
21 struct pgsql_connection *next;
26 static pgsql_connection *pgsql_connections = NULL;
30 /*************************************************
32 *************************************************/
34 /* See local README for interface description. */
37 pgsql_open(const uschar * filename, uschar ** errmsg)
39 return (void *)(1); /* Just return something non-null */
44 /*************************************************
46 *************************************************/
48 /* See local README for interface description. */
54 while ((cn = pgsql_connections) != NULL)
56 pgsql_connections = cn->next;
57 DEBUG(D_lookup) debug_printf_indent("close PGSQL connection: %s\n", cn->server);
63 /*************************************************
64 * Notice processor function for pgsql *
65 *************************************************/
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.
71 arg an opaque user cookie (not used)
78 notice_processor(void *arg, const char *message)
80 arg = arg; /* Keep compiler happy */
81 DEBUG(D_lookup) debug_printf_indent("PGSQL: %s\n", message);
86 /*************************************************
87 * Internal search function *
88 *************************************************/
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".
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:
98 hide pgsql_servers = (/tmp/.s.PGSQL.5432)/db/user/password[:<nextserver>]
100 We enclose the path name in parentheses so that its slashes aren't visually
101 confused with the delimiters for the other pgsql_server settings.
103 For TCP/IP connections, the server is a host name and optional port (with a
107 1) All three '/' must be present.
108 2) If host is omitted the local unix socket is used.
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
115 defer_break set TRUE if no more servers are to be tried after DEFER
116 do_cache set FALSE if data is changed
119 Returns: OK, FAIL, or DEFER
123 perform_pgsql_search(const uschar *query, uschar *server, uschar **resultptr,
124 uschar **errmsg, BOOL *defer_break, uint *do_cache, const uschar * opts)
126 PGconn *pg_conn = NULL;
127 PGresult *pg_result = NULL;
129 gstring * result = NULL;
131 unsigned int num_fields, num_tuples;
132 pgsql_connection *cn;
133 rmark reset_point = store_mark();
134 uschar *server_copy = NULL;
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. */
142 for (int i = 2; i >= 0; i--)
144 uschar *pp = Ustrrchr(server, '/');
147 *errmsg = string_sprintf("incomplete pgSQL server data: %s",
148 (i == 2)? server : server_copy);
154 if (i == 2) server_copy = string_copy(server); /* sans password */
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. */
161 for (cn = pgsql_connections; cn; cn = cn->next)
162 if (Ustrcmp(cn->server, server_copy) == 0)
164 pg_conn = cn->handle;
168 /* If there is no cached connection, we must set one up. */
174 /* For a Unix domain socket connection, the path is in parentheses */
178 uschar *last_slash, *last_dot, *p;
181 while (*p && *p != ')') p++;
184 last_slash = Ustrrchr(server, '/');
185 last_dot = Ustrrchr(server, '.');
187 DEBUG(D_lookup) debug_printf_indent("PGSQL new connection: socket=%s "
188 "database=%s user=%s\n", server, sdata[0], sdata[1]);
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. */
194 if (!last_slash || !last_dot)
196 *errmsg = string_sprintf("PGSQL invalid filename for socket: %s", server);
201 /* Terminate the path name and set up the port: we'll have something like
202 server = "/var/run/postgresql" and port = "5432". */
208 /* Host connection; sort out the port */
213 if ((p = Ustrchr(server, ':')))
219 if (Ustrchr(server, '/'))
221 *errmsg = string_sprintf("unexpected slash in pgSQL server hostname: %s",
227 DEBUG(D_lookup) debug_printf_indent("PGSQL new connection: host=%s port=%s "
228 "database=%s user=%s\n", server, port, sdata[0], sdata[1]);
231 /* If the database is the empty string, set it NULL - the query must then
234 if (sdata[0][0] == 0) sdata[0] = NULL;
236 /* Get store for a new handle, initialize it, and connect to the server */
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]);
242 if(PQstatus(pg_conn) == CONNECTION_BAD)
244 reset_point = store_reset(reset_point);
245 *errmsg = string_sprintf("PGSQL connection failed: %s",
246 PQerrorMessage(pg_conn));
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. */
255 PQsetClientEncoding(pg_conn, "SQL_ASCII");
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
261 PQsetNoticeProcessor(pg_conn, notice_processor, NULL);
263 /* Add the connection to the cache */
265 cn = store_get(sizeof(pgsql_connection), FALSE);
266 cn->server = server_copy;
267 cn->handle = pg_conn;
268 cn->next = pgsql_connections;
269 pgsql_connections = cn;
272 /* Else use a previously cached connection */
276 DEBUG(D_lookup) debug_printf_indent("PGSQL using cached connection for %s\n",
282 pg_result = PQexec(pg_conn, CS query);
283 switch(PQresultStatus(pg_result))
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. */
292 result = string_cat(result, US PQcmdTuples(pg_result));
294 DEBUG(D_lookup) debug_printf_indent("PGSQL: command does not return any data "
295 "but was successful. Rows affected: %s\n", string_from_gstring(result));
298 case PGRES_TUPLES_OK:
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:
308 *errmsg = string_sprintf("PGSQL: query failed: %s (%s) (%s)\n",
309 PQresultErrorMessage(pg_result),
310 PQresStatus(PQresultStatus(pg_result)), query);
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
319 num_fields = PQnfields(pg_result);
320 num_tuples = PQntuples(pg_result);
322 /* Get the fields and construct the result string. If there is more than one
323 row, we insert '\n' between them. */
325 for (int i = 0; i < num_tuples; i++)
328 result = string_catn(result, US"\n", 1);
331 result = string_catn(result,
332 US PQgetvalue(pg_result, i, 0), PQgetlength(pg_result, i, 0));
334 for (int j = 0; j < num_fields; j++)
336 uschar *tmp = US PQgetvalue(pg_result, i, j);
337 result = lf_quote(US PQfname(pg_result, j), tmp, Ustrlen(tmp), result);
339 if (!result) result = string_get(1);
342 /* If result is NULL then no data has been found and so we return FAIL. */
347 *errmsg = US"PGSQL: no data found";
350 /* Get here by goto from various error checks. */
354 /* Free store for any result that was got; don't close the connection, as
357 if (pg_result) PQclear(pg_result);
359 /* Non-NULL result indicates a successful result */
363 gstring_release_unused(result);
364 *resultptr = string_from_gstring(result);
369 DEBUG(D_lookup) debug_printf_indent("%s\n", *errmsg);
370 return yield; /* FAIL or DEFER */
377 /*************************************************
379 *************************************************/
381 /* See local README for interface description. The handle and filename
382 arguments are not used. The code to loop through a list of servers while the
383 query is deferred with a retryable error is now in a separate function that is
384 shared with other SQL lookups. */
387 pgsql_find(void * handle, const uschar * filename, const uschar * query,
388 int length, uschar ** result, uschar ** errmsg, uint * do_cache,
391 return lf_sqlperform(US"PostgreSQL", US"pgsql_servers", pgsql_servers, query,
392 result, errmsg, do_cache, opts, perform_pgsql_search);
397 /*************************************************
398 * Quote entry point *
399 *************************************************/
401 /* The characters that always need to be quoted (with backslash) are newline,
402 tab, carriage return, backspace, backslash itself, and the quote characters.
404 The original code quoted single quotes as \' which is documented as valid in
405 the O'Reilly book "Practical PostgreSQL" (first edition) as an alternative to
406 the SQL standard '' way of representing a single quote as data. However, in
407 June 2006 there was some security issue with using \' and so this has been
410 [Note: There is a function called PQescapeStringConn() that quotes strings.
411 This cannot be used because it needs a PGconn argument (the connection handle).
412 Why, I don't know. Seems odd for just string escaping...]
415 s the string to be quoted
416 opt additional option text or NULL if none
418 Returns: the processed string or NULL for a bad option
422 pgsql_quote(uschar * s, uschar * opt)
425 uschar * t = s, * quoted;
427 if (opt) return NULL; /* No options recognized */
430 if (Ustrchr("\n\t\r\b\'\"\\", c) != NULL) count++;
432 if (count == 0) return s;
433 t = quoted = store_get(Ustrlen(s) + count + 1, is_tainted(s));
442 else if (Ustrchr("\n\t\r\b\"\\", c) != NULL)
447 case '\n': *t++ = 'n'; break;
448 case '\t': *t++ = 't'; break;
449 case '\r': *t++ = 'r'; break;
450 case '\b': *t++ = 'b'; break;
451 default: *t++ = c; break;
462 /*************************************************
463 * Version reporting entry point *
464 *************************************************/
466 /* See local README for interface description. */
468 #include "../version.h"
471 pgsql_version_report(gstring * g)
474 g = string_fmt_append(g, "Library version: PostgreSQL: Exim version %s\n", EXIM_VERSION_STR);
477 /* Version reporting: there appears to be no available information about
478 the client library in libpq-fe.h; once you have a connection object, you
479 can access the server version and the chosen protocol version, but those
480 aren't really what we want. It might make sense to debug_printf those
481 when the connection is established though? */
487 static lookup_info _lookup_info = {
488 .name = US"pgsql", /* lookup name */
489 .type = lookup_querystyle, /* query-style lookup */
490 .open = pgsql_open, /* open function */
491 .check = NULL, /* no check function */
492 .find = pgsql_find, /* find function */
493 .close = NULL, /* no close function */
494 .tidy = pgsql_tidy, /* tidy function */
495 .quote = pgsql_quote, /* quoting function */
496 .version_report = pgsql_version_report /* version reporting */
500 #define pgsql_lookup_module_info _lookup_module_info
503 static lookup_info *_lookup_list[] = { &_lookup_info };
504 lookup_module_info pgsql_lookup_module_info = { LOOKUP_MODULE_INFO_MAGIC, _lookup_list, 1 };
506 /* End of lookups/pgsql.c */