src/src/lookups/pgsql.c

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