1 /* $NetBSD: dict_pgsql.c,v 1.3 2020/03/18 19:05:16 christos Exp $ */
2
3 /*++
4 /* NAME
5 /* dict_pgsql 3
6 /* SUMMARY
7 /* dictionary manager interface to PostgreSQL databases
8 /* SYNOPSIS
9 /* #include <dict_pgsql.h>
10 /*
11 /* DICT *dict_pgsql_open(name, open_flags, dict_flags)
12 /* const char *name;
13 /* int open_flags;
14 /* int dict_flags;
15 /* DESCRIPTION
16 /* dict_pgsql_open() creates a dictionary of type 'pgsql'. This
17 /* dictionary is an interface for the postfix key->value mappings
18 /* to pgsql. The result is a pointer to the installed dictionary,
19 /* or a null pointer in case of problems.
20 /*
21 /* The pgsql dictionary can manage multiple connections to
22 /* different sql servers for the same database. It assumes that
23 /* the underlying data on each server is identical (mirrored) and
24 /* maintains one connection at any given time. If any connection
25 /* fails, any other available ones will be opened and used.
26 /* The intent of this feature is to eliminate a single point of
27 /* failure for mail systems that would otherwise rely on a single
28 /* pgsql server.
29 /* .PP
30 /* Arguments:
31 /* .IP name
32 /* Either the path to the PostgreSQL configuration file (if it
33 /* starts with '/' or '.'), or the prefix which will be used to
34 /* obtain main.cf configuration parameters for this search.
35 /*
36 /* In the first case, the configuration parameters below are
37 /* specified in the file as \fIname\fR=\fIvalue\fR pairs.
38 /*
39 /* In the second case, the configuration parameters are
40 /* prefixed with the value of \fIname\fR and an underscore,
41 /* and they are specified in main.cf. For example, if this
42 /* value is \fIpgsqlsource\fR, the parameters would look like
43 /* \fIpgsqlsource_user\fR, \fIpgsqlsource_table\fR, and so on.
44 /* .IP other_name
45 /* reference for outside use.
46 /* .IP open_flags
47 /* Must be O_RDONLY.
48 /* .IP dict_flags
49 /* See dict_open(3).
50 /*
51 /* .PP
52 /* Configuration parameters:
53 /* .IP user
54 /* Username for connecting to the database.
55 /* .IP password
56 /* Password for the above.
57 /* .IP dbname
58 /* Name of the database.
59 /* .IP query
60 /* Query template. If not defined a default query template is constructed
61 /* from the legacy \fIselect_function\fR or failing that the \fItable\fR,
62 /* \fIselect_field\fR, \fIwhere_field\fR, and \fIadditional_conditions\fR
63 /* parameters. Before the query is issues, variable substitutions are
64 /* performed. See pgsql_table(5).
65 /* .IP domain
66 /* List of domains the queries should be restricted to. If
67 /* specified, only FQDN addresses whose domain parts matching this
68 /* list will be queried against the SQL database. Lookups for
69 /* partial addresses are also suppressed. This can significantly
70 /* reduce the query load on the server.
71 /* .IP result_format
72 /* The format used to expand results from queries. Substitutions
73 /* are performed as described in pgsql_table(5). Defaults to returning
74 /* the lookup result unchanged.
75 /* .IP expansion_limit
76 /* Limit (if any) on the total number of lookup result values. Lookups which
77 /* exceed the limit fail with dict->error=DICT_ERR_RETRY. Note that each
78 /* non-empty (and non-NULL) column of a multi-column result row counts as
79 /* one result.
80 /* .IP select_function
81 /* When \fIquery\fR is not defined, the function to be used instead of
82 /* the default query based on the legacy \fItable\fR, \fIselect_field\fR,
83 /* \fIwhere_field\fR, and \fIadditional_conditions\fR parameters.
84 /* .IP table
85 /* When \fIquery\fR and \fIselect_function\fR are not defined, the name of the
86 /* FROM table used to construct the default query template, see pgsql_table(5).
87 /* .IP select_field
88 /* When \fIquery\fR and \fIselect_function\fR are not defined, the name of the
89 /* SELECT field used to construct the default query template, see pgsql_table(5).
90 /* .IP where_field
91 /* When \fIquery\fR and \fIselect_function\fR are not defined, the name of the
92 /* WHERE field used to construct the default query template, see pgsql_table(5).
93 /* .IP additional_conditions
94 /* When \fIquery\fR and \fIselect_function\fR are not defined, the name of the
95 /* additional text to add to the WHERE field in the default query template (this
96 /* usually begins with "and") see pgsql_table(5).
97 /* .IP hosts
98 /* List of hosts to connect to.
99 /* .PP
100 /* For example, if you want the map to reference databases of
101 /* the name "your_db" and execute a query like this: select
102 /* forw_addr from aliases where alias like '<some username>'
103 /* against any database called "postfix_info" located on hosts
104 /* host1.some.domain and host2.some.domain, logging in as user
105 /* "postfix" and password "passwd" then the configuration file
106 /* should read:
107 /* .PP
108 /* user = postfix
109 /* .br
110 /* password = passwd
111 /* .br
112 /* dbname = postfix_info
113 /* .br
114 /* table = aliases
115 /* .br
116 /* select_field = forw_addr
117 /* .br
118 /* where_field = alias
119 /* .br
120 /* hosts = host1.some.domain host2.some.domain
121 /* .PP
122 /* SEE ALSO
123 /* dict(3) generic dictionary manager
124 /* AUTHOR(S)
125 /* Aaron Sethman
126 /* androsyn@ratbox.org
127 /*
128 /* Based upon dict_mysql.c by
129 /*
130 /* Scott Cotton
131 /* IC Group, Inc.
132 /* scott@icgroup.com
133 /*
134 /* Joshua Marcus
135 /* IC Group, Inc.
136 /* josh@icgroup.com
137 /*--*/
138
139 /* System library. */
140
141 #include "sys_defs.h"
142
143 #ifdef HAS_PGSQL
144 #include <sys/socket.h>
145 #include <netinet/in.h>
146 #include <arpa/inet.h>
147 #include <netdb.h>
148 #include <stdio.h>
149 #include <string.h>
150 #include <stdlib.h>
151 #include <syslog.h>
152 #include <time.h>
153
154 #include <postgres_ext.h>
155 #include <libpq-fe.h>
156
157 /* Utility library. */
158
159 #include "dict.h"
160 #include "msg.h"
161 #include "mymalloc.h"
162 #include "argv.h"
163 #include "vstring.h"
164 #include "split_at.h"
165 #include "myrand.h"
166 #include "events.h"
167 #include "stringops.h"
168
169 /* Global library. */
170
171 #include "cfg_parser.h"
172 #include "db_common.h"
173
174 /* Application-specific. */
175
176 #include "dict_pgsql.h"
177
178 #define STATACTIVE (1<<0)
179 #define STATFAIL (1<<1)
180 #define STATUNTRIED (1<<2)
181
182 #define TYPEUNIX (1<<0)
183 #define TYPEINET (1<<1)
184 #define TYPECONNSTRING (1<<2)
185
186 #define RETRY_CONN_MAX 100
187 #define RETRY_CONN_INTV 60 /* 1 minute */
188 #define IDLE_CONN_INTV 60 /* 1 minute */
189
190 typedef struct {
191 PGconn *db;
192 char *hostname;
193 char *name;
194 char *port;
195 unsigned type; /* TYPEUNIX | TYPEINET | TYPECONNSTRING */
196 unsigned stat; /* STATUNTRIED | STATFAIL | STATCUR */
197 time_t ts; /* used for attempting reconnection */
198 } HOST;
199
200 typedef struct {
201 int len_hosts; /* number of hosts */
202 HOST **db_hosts; /* hosts on which databases reside */
203 } PLPGSQL;
204
205 typedef struct {
206 DICT dict;
207 CFG_PARSER *parser;
208 char *query;
209 char *result_format;
210 void *ctx;
211 int expansion_limit;
212 char *username;
213 char *password;
214 char *dbname;
215 char *table;
216 ARGV *hosts;
217 PLPGSQL *pldb;
218 HOST *active_host;
219 } DICT_PGSQL;
220
221
222 /* Just makes things a little easier for me.. */
223 #define PGSQL_RES PGresult
224
225 /* internal function declarations */
226 static PLPGSQL *plpgsql_init(ARGV *);
227 static PGSQL_RES *plpgsql_query(DICT_PGSQL *, const char *, VSTRING *, char *,
228 char *, char *);
229 static void plpgsql_dealloc(PLPGSQL *);
230 static void plpgsql_close_host(HOST *);
231 static void plpgsql_down_host(HOST *);
232 static void plpgsql_connect_single(HOST *, char *, char *, char *);
233 static const char *dict_pgsql_lookup(DICT *, const char *);
234 DICT *dict_pgsql_open(const char *, int, int);
235 static void dict_pgsql_close(DICT *);
236 static HOST *host_init(const char *);
237
238 /* dict_pgsql_quote - escape SQL metacharacters in input string */
239
dict_pgsql_quote(DICT * dict,const char * name,VSTRING * result)240 static void dict_pgsql_quote(DICT *dict, const char *name, VSTRING *result)
241 {
242 DICT_PGSQL *dict_pgsql = (DICT_PGSQL *) dict;
243 HOST *active_host = dict_pgsql->active_host;
244 char *myname = "dict_pgsql_quote";
245 size_t len = strlen(name);
246 size_t buflen;
247 int err = 1;
248
249 if (active_host == 0)
250 msg_panic("%s: bogus dict_pgsql->active_host", myname);
251
252 /*
253 * We won't get arithmetic overflows in 2*len + 1, because Postfix input
254 * keys have reasonable size limits, better safe than sorry.
255 */
256 if (len > (SSIZE_T_MAX - VSTRING_LEN(result) - 1) / 2)
257 msg_panic("%s: arithmetic overflow in %lu+2*%lu+1",
258 myname, (unsigned long) VSTRING_LEN(result),
259 (unsigned long) len);
260 buflen = 2 * len + 1;
261
262 /*
263 * XXX Workaround: stop further processing when PQescapeStringConn()
264 * (below) fails. A more proper fix requires invasive changes, not
265 * suitable for a stable release.
266 */
267 if (active_host->stat == STATFAIL)
268 return;
269
270 /*
271 * Escape the input string, using PQescapeStringConn(), because the older
272 * PQescapeString() is not safe anymore, as stated by the documentation.
273 *
274 * From current libpq (8.1.4) documentation:
275 *
276 * PQescapeStringConn writes an escaped version of the from string to the to
277 * buffer, escaping special characters so that they cannot cause any
278 * harm, and adding a terminating zero byte.
279 *
280 * ...
281 *
282 * The parameter from points to the first character of the string that is to
283 * be escaped, and the length parameter gives the number of bytes in this
284 * string. A terminating zero byte is not required, and should not be
285 * counted in length.
286 *
287 * ...
288 *
289 * (The parameter) to shall point to a buffer that is able to hold at least
290 * one more byte than twice the value of length, otherwise the behavior
291 * is undefined.
292 *
293 * ...
294 *
295 * If the error parameter is not NULL, then *error is set to zero on
296 * success, nonzero on error ... The output string is still generated on
297 * error, but it can be expected that the server will reject it as
298 * malformed. On error, a suitable message is stored in the conn object,
299 * whether or not error is NULL.
300 */
301 VSTRING_SPACE(result, buflen);
302 PQescapeStringConn(active_host->db, vstring_end(result), name, len, &err);
303 if (err == 0) {
304 VSTRING_SKIP(result);
305 } else {
306
307 /*
308 * PQescapeStringConn() failed. According to the docs, we still have
309 * a valid, null-terminated output string, but we need not rely on
310 * this behavior.
311 */
312 msg_warn("dict pgsql: (host %s) cannot escape input string: %s",
313 active_host->hostname, PQerrorMessage(active_host->db));
314 active_host->stat = STATFAIL;
315 VSTRING_TERMINATE(result);
316 }
317 }
318
319 /* dict_pgsql_lookup - find database entry */
320
dict_pgsql_lookup(DICT * dict,const char * name)321 static const char *dict_pgsql_lookup(DICT *dict, const char *name)
322 {
323 const char *myname = "dict_pgsql_lookup";
324 PGSQL_RES *query_res;
325 DICT_PGSQL *dict_pgsql;
326 static VSTRING *query;
327 static VSTRING *result;
328 int i;
329 int j;
330 int numrows;
331 int numcols;
332 int expansion;
333 const char *r;
334 int domain_rc;
335
336 dict_pgsql = (DICT_PGSQL *) dict;
337
338 #define INIT_VSTR(buf, len) do { \
339 if (buf == 0) \
340 buf = vstring_alloc(len); \
341 VSTRING_RESET(buf); \
342 VSTRING_TERMINATE(buf); \
343 } while (0)
344
345 INIT_VSTR(query, 10);
346 INIT_VSTR(result, 10);
347
348 dict->error = 0;
349
350 /*
351 * Don't frustrate future attempts to make Postfix UTF-8 transparent.
352 */
353 #ifdef SNAPSHOT
354 if ((dict->flags & DICT_FLAG_UTF8_ACTIVE) == 0
355 && !valid_utf8_string(name, strlen(name))) {
356 if (msg_verbose)
357 msg_info("%s: %s: Skipping lookup of non-UTF-8 key '%s'",
358 myname, dict_pgsql->parser->name, name);
359 return (0);
360 }
361 #endif
362
363 /*
364 * Optionally fold the key.
365 */
366 if (dict->flags & DICT_FLAG_FOLD_FIX) {
367 if (dict->fold_buf == 0)
368 dict->fold_buf = vstring_alloc(10);
369 vstring_strcpy(dict->fold_buf, name);
370 name = lowercase(vstring_str(dict->fold_buf));
371 }
372
373 /*
374 * If there is a domain list for this map, then only search for addresses
375 * in domains on the list. This can significantly reduce the load on the
376 * server.
377 */
378 if ((domain_rc = db_common_check_domain(dict_pgsql->ctx, name)) == 0) {
379 if (msg_verbose)
380 msg_info("%s: Skipping lookup of '%s'", myname, name);
381 return (0);
382 }
383 if (domain_rc < 0)
384 DICT_ERR_VAL_RETURN(dict, domain_rc, (char *) 0);
385
386 /*
387 * Suppress the actual lookup if the expansion is empty.
388 *
389 * This initial expansion is outside the context of any specific host
390 * connection, we just want to check the key pre-requisites, so when
391 * quoting happens separately for each connection, we don't bother with
392 * quoting...
393 */
394 if (!db_common_expand(dict_pgsql->ctx, dict_pgsql->query,
395 name, 0, query, 0))
396 return (0);
397
398 /* do the query - set dict->error & cleanup if there's an error */
399 if ((query_res = plpgsql_query(dict_pgsql, name, query,
400 dict_pgsql->dbname,
401 dict_pgsql->username,
402 dict_pgsql->password)) == 0) {
403 dict->error = DICT_ERR_RETRY;
404 return 0;
405 }
406 numrows = PQntuples(query_res);
407 if (msg_verbose)
408 msg_info("%s: retrieved %d rows", myname, numrows);
409 if (numrows == 0) {
410 PQclear(query_res);
411 return 0;
412 }
413 numcols = PQnfields(query_res);
414
415 for (expansion = i = 0; i < numrows && dict->error == 0; i++) {
416 for (j = 0; j < numcols; j++) {
417 r = PQgetvalue(query_res, i, j);
418 if (db_common_expand(dict_pgsql->ctx, dict_pgsql->result_format,
419 r, name, result, 0)
420 && dict_pgsql->expansion_limit > 0
421 && ++expansion > dict_pgsql->expansion_limit) {
422 msg_warn("%s: %s: Expansion limit exceeded for key: '%s'",
423 myname, dict_pgsql->parser->name, name);
424 dict->error = DICT_ERR_RETRY;
425 break;
426 }
427 }
428 }
429 PQclear(query_res);
430 r = vstring_str(result);
431 return ((dict->error == 0 && *r) ? r : 0);
432 }
433
434 /* dict_pgsql_check_stat - check the status of a host */
435
dict_pgsql_check_stat(HOST * host,unsigned stat,unsigned type,time_t t)436 static int dict_pgsql_check_stat(HOST *host, unsigned stat, unsigned type,
437 time_t t)
438 {
439 if ((host->stat & stat) && (!type || host->type & type)) {
440 /* try not to hammer the dead hosts too often */
441 if (host->stat == STATFAIL && host->ts > 0 && host->ts >= t)
442 return 0;
443 return 1;
444 }
445 return 0;
446 }
447
448 /* dict_pgsql_find_host - find a host with the given status */
449
dict_pgsql_find_host(PLPGSQL * PLDB,unsigned stat,unsigned type)450 static HOST *dict_pgsql_find_host(PLPGSQL *PLDB, unsigned stat, unsigned type)
451 {
452 time_t t;
453 int count = 0;
454 int idx;
455 int i;
456
457 t = time((time_t *) 0);
458 for (i = 0; i < PLDB->len_hosts; i++) {
459 if (dict_pgsql_check_stat(PLDB->db_hosts[i], stat, type, t))
460 count++;
461 }
462
463 if (count) {
464 idx = (count > 1) ?
465 1 + count * (double) myrand() / (1.0 + RAND_MAX) : 1;
466
467 for (i = 0; i < PLDB->len_hosts; i++) {
468 if (dict_pgsql_check_stat(PLDB->db_hosts[i], stat, type, t) &&
469 --idx == 0)
470 return PLDB->db_hosts[i];
471 }
472 }
473 return 0;
474 }
475
476 /* dict_pgsql_get_active - get an active connection */
477
dict_pgsql_get_active(PLPGSQL * PLDB,char * dbname,char * username,char * password)478 static HOST *dict_pgsql_get_active(PLPGSQL *PLDB, char *dbname,
479 char *username, char *password)
480 {
481 const char *myname = "dict_pgsql_get_active";
482 HOST *host;
483 int count = RETRY_CONN_MAX;
484
485 /* try the active connections first; prefer the ones to UNIX sockets */
486 if ((host = dict_pgsql_find_host(PLDB, STATACTIVE, TYPEUNIX)) != NULL ||
487 (host = dict_pgsql_find_host(PLDB, STATACTIVE, TYPEINET)) != NULL ||
488 (host = dict_pgsql_find_host(PLDB, STATACTIVE, TYPECONNSTRING)) != NULL) {
489 if (msg_verbose)
490 msg_info("%s: found active connection to host %s", myname,
491 host->hostname);
492 return host;
493 }
494
495 /*
496 * Try the remaining hosts. "count" is a safety net, in case the loop
497 * takes more than RETRY_CONN_INTV and the dead hosts are no longer
498 * skipped.
499 */
500 while (--count > 0 &&
501 ((host = dict_pgsql_find_host(PLDB, STATUNTRIED | STATFAIL,
502 TYPEUNIX)) != NULL ||
503 (host = dict_pgsql_find_host(PLDB, STATUNTRIED | STATFAIL,
504 TYPEINET)) != NULL ||
505 (host = dict_pgsql_find_host(PLDB, STATUNTRIED | STATFAIL,
506 TYPECONNSTRING)) != NULL)) {
507 if (msg_verbose)
508 msg_info("%s: attempting to connect to host %s", myname,
509 host->hostname);
510 plpgsql_connect_single(host, dbname, username, password);
511 if (host->stat == STATACTIVE)
512 return host;
513 }
514
515 /* bad news... */
516 return 0;
517 }
518
519 /* dict_pgsql_event - callback: close idle connections */
520
dict_pgsql_event(int unused_event,void * context)521 static void dict_pgsql_event(int unused_event, void *context)
522 {
523 HOST *host = (HOST *) context;
524
525 if (host->db)
526 plpgsql_close_host(host);
527 }
528
529 /*
530 * plpgsql_query - process a PostgreSQL query. Return PGSQL_RES* on success.
531 * On failure, log failure and try other db instances.
532 * on failure of all db instances, return 0;
533 * close unnecessary active connections
534 */
535
plpgsql_query(DICT_PGSQL * dict_pgsql,const char * name,VSTRING * query,char * dbname,char * username,char * password)536 static PGSQL_RES *plpgsql_query(DICT_PGSQL *dict_pgsql,
537 const char *name,
538 VSTRING *query,
539 char *dbname,
540 char *username,
541 char *password)
542 {
543 PLPGSQL *PLDB = dict_pgsql->pldb;
544 HOST *host;
545 PGSQL_RES *res = 0;
546 ExecStatusType status;
547
548 while ((host = dict_pgsql_get_active(PLDB, dbname, username, password)) != NULL) {
549
550 /*
551 * The active host is used to escape strings in the context of the
552 * active connection's character encoding.
553 */
554 dict_pgsql->active_host = host;
555 VSTRING_RESET(query);
556 VSTRING_TERMINATE(query);
557 db_common_expand(dict_pgsql->ctx, dict_pgsql->query,
558 name, 0, query, dict_pgsql_quote);
559 dict_pgsql->active_host = 0;
560
561 /* Check for potential dict_pgsql_quote() failure. */
562 if (host->stat == STATFAIL) {
563 plpgsql_down_host(host);
564 continue;
565 }
566
567 /*
568 * Submit a command to the server. Be paranoid when processing the
569 * result set: try to enumerate every successful case, and reject
570 * everything else.
571 *
572 * From PostgreSQL 8.1.4 docs: (PQexec) returns a PGresult pointer or
573 * possibly a null pointer. A non-null pointer will generally be
574 * returned except in out-of-memory conditions or serious errors such
575 * as inability to send the command to the server.
576 */
577 if ((res = PQexec(host->db, vstring_str(query))) != 0) {
578
579 /*
580 * XXX Because non-null result pointer does not imply success, we
581 * need to check the command's result status.
582 *
583 * Section 28.3.1: A result of status PGRES_NONFATAL_ERROR will
584 * never be returned directly by PQexec or other query execution
585 * functions; results of this kind are instead passed to the
586 * notice processor.
587 *
588 * PGRES_EMPTY_QUERY is being sent by the server when the query
589 * string is empty. The sanity-checking done by the Postfix
590 * infrastructure makes this case impossible, so we need not
591 * handle this situation explicitly.
592 */
593 switch ((status = PQresultStatus(res))) {
594 case PGRES_TUPLES_OK:
595 case PGRES_COMMAND_OK:
596 /* Success. */
597 if (msg_verbose)
598 msg_info("dict_pgsql: successful query from host %s",
599 host->hostname);
600 event_request_timer(dict_pgsql_event, (void *) host,
601 IDLE_CONN_INTV);
602 return (res);
603 case PGRES_FATAL_ERROR:
604 msg_warn("pgsql query failed: fatal error from host %s: %s",
605 host->hostname, PQresultErrorMessage(res));
606 break;
607 case PGRES_BAD_RESPONSE:
608 msg_warn("pgsql query failed: protocol error, host %s",
609 host->hostname);
610 break;
611 default:
612 msg_warn("pgsql query failed: unknown code 0x%lx from host %s",
613 (unsigned long) status, host->hostname);
614 break;
615 }
616 } else {
617
618 /*
619 * This driver treats null pointers like fatal, non-null result
620 * pointer errors, as suggested by the PostgreSQL 8.1.4
621 * documentation.
622 */
623 msg_warn("pgsql query failed: fatal error from host %s: %s",
624 host->hostname, PQerrorMessage(host->db));
625 }
626
627 /*
628 * XXX An error occurred. Clean up memory and skip this connection.
629 */
630 if (res != 0)
631 PQclear(res);
632 plpgsql_down_host(host);
633 }
634
635 return (0);
636 }
637
638 /*
639 * plpgsql_connect_single -
640 * used to reconnect to a single database when one is down or none is
641 * connected yet. Log all errors and set the stat field of host accordingly
642 */
plpgsql_connect_single(HOST * host,char * dbname,char * username,char * password)643 static void plpgsql_connect_single(HOST *host, char *dbname, char *username, char *password)
644 {
645 if (host->type == TYPECONNSTRING) {
646 host->db = PQconnectdb(host->name);
647 } else {
648 host->db = PQsetdbLogin(host->name, host->port, NULL, NULL,
649 dbname, username, password);
650 }
651 if (host->db == NULL || PQstatus(host->db) != CONNECTION_OK) {
652 msg_warn("connect to pgsql server %s: %s",
653 host->hostname, PQerrorMessage(host->db));
654 plpgsql_down_host(host);
655 return;
656 }
657 if (msg_verbose)
658 msg_info("dict_pgsql: successful connection to host %s",
659 host->hostname);
660
661 /*
662 * The only legitimate encodings for Internet mail are ASCII and UTF-8.
663 */
664 #ifdef SNAPSHOT
665 if (PQsetClientEncoding(host->db, "UTF8") != 0) {
666 msg_warn("dict_pgsql: cannot set the encoding to UTF8, skipping %s",
667 host->hostname);
668 plpgsql_down_host(host);
669 return;
670 }
671 #else
672
673 /*
674 * XXX Postfix does not send multi-byte characters. The following piece
675 * of code is an explicit statement of this fact, and the database server
676 * should not accept multi-byte information after this point.
677 */
678 if (PQsetClientEncoding(host->db, "LATIN1") != 0) {
679 msg_warn("dict_pgsql: cannot set the encoding to LATIN1, skipping %s",
680 host->hostname);
681 plpgsql_down_host(host);
682 return;
683 }
684 #endif
685 /* Success. */
686 host->stat = STATACTIVE;
687 }
688
689 /* plpgsql_close_host - close an established PostgreSQL connection */
690
plpgsql_close_host(HOST * host)691 static void plpgsql_close_host(HOST *host)
692 {
693 if (host->db)
694 PQfinish(host->db);
695 host->db = 0;
696 host->stat = STATUNTRIED;
697 }
698
699 /*
700 * plpgsql_down_host - close a failed connection AND set a "stay away from
701 * this host" timer.
702 */
plpgsql_down_host(HOST * host)703 static void plpgsql_down_host(HOST *host)
704 {
705 if (host->db)
706 PQfinish(host->db);
707 host->db = 0;
708 host->ts = time((time_t *) 0) + RETRY_CONN_INTV;
709 host->stat = STATFAIL;
710 event_cancel_timer(dict_pgsql_event, (void *) host);
711 }
712
713 /* pgsql_parse_config - parse pgsql configuration file */
714
pgsql_parse_config(DICT_PGSQL * dict_pgsql,const char * pgsqlcf)715 static void pgsql_parse_config(DICT_PGSQL *dict_pgsql, const char *pgsqlcf)
716 {
717 const char *myname = "pgsql_parse_config";
718 CFG_PARSER *p = dict_pgsql->parser;
719 char *hosts;
720 VSTRING *query;
721 char *select_function;
722
723 dict_pgsql->username = cfg_get_str(p, "user", "", 0, 0);
724 dict_pgsql->password = cfg_get_str(p, "password", "", 0, 0);
725 dict_pgsql->dbname = cfg_get_str(p, "dbname", "", 1, 0);
726 dict_pgsql->result_format = cfg_get_str(p, "result_format", "%s", 1, 0);
727
728 /*
729 * XXX: The default should be non-zero for safety, but that is not
730 * backwards compatible.
731 */
732 dict_pgsql->expansion_limit = cfg_get_int(dict_pgsql->parser,
733 "expansion_limit", 0, 0, 0);
734
735 if ((dict_pgsql->query = cfg_get_str(p, "query", 0, 0, 0)) == 0) {
736
737 /*
738 * No query specified -- fallback to building it from components (
739 * old style "select %s from %s where %s" )
740 */
741 query = vstring_alloc(64);
742 select_function = cfg_get_str(p, "select_function", 0, 0, 0);
743 if (select_function != 0) {
744 vstring_sprintf(query, "SELECT %s('%%s')", select_function);
745 myfree(select_function);
746 } else
747 db_common_sql_build_query(query, p);
748 dict_pgsql->query = vstring_export(query);
749 }
750
751 /*
752 * Must parse all templates before we can use db_common_expand()
753 */
754 dict_pgsql->ctx = 0;
755 (void) db_common_parse(&dict_pgsql->dict, &dict_pgsql->ctx,
756 dict_pgsql->query, 1);
757 (void) db_common_parse(0, &dict_pgsql->ctx, dict_pgsql->result_format, 0);
758 db_common_parse_domain(p, dict_pgsql->ctx);
759
760 /*
761 * Maps that use substring keys should only be used with the full input
762 * key.
763 */
764 if (db_common_dict_partial(dict_pgsql->ctx))
765 dict_pgsql->dict.flags |= DICT_FLAG_PATTERN;
766 else
767 dict_pgsql->dict.flags |= DICT_FLAG_FIXED;
768 if (dict_pgsql->dict.flags & DICT_FLAG_FOLD_FIX)
769 dict_pgsql->dict.fold_buf = vstring_alloc(10);
770
771 hosts = cfg_get_str(p, "hosts", "", 0, 0);
772
773 dict_pgsql->hosts = argv_split(hosts, CHARS_COMMA_SP);
774 if (dict_pgsql->hosts->argc == 0) {
775 argv_add(dict_pgsql->hosts, "localhost", ARGV_END);
776 argv_terminate(dict_pgsql->hosts);
777 if (msg_verbose)
778 msg_info("%s: %s: no hostnames specified, defaulting to '%s'",
779 myname, pgsqlcf, dict_pgsql->hosts->argv[0]);
780 }
781 myfree(hosts);
782 }
783
784 /* dict_pgsql_open - open PGSQL data base */
785
dict_pgsql_open(const char * name,int open_flags,int dict_flags)786 DICT *dict_pgsql_open(const char *name, int open_flags, int dict_flags)
787 {
788 DICT_PGSQL *dict_pgsql;
789 CFG_PARSER *parser;
790
791 /*
792 * Sanity check.
793 */
794 if (open_flags != O_RDONLY)
795 return (dict_surrogate(DICT_TYPE_PGSQL, name, open_flags, dict_flags,
796 "%s:%s map requires O_RDONLY access mode",
797 DICT_TYPE_PGSQL, name));
798
799 /*
800 * Open the configuration file.
801 */
802 if ((parser = cfg_parser_alloc(name)) == 0)
803 return (dict_surrogate(DICT_TYPE_PGSQL, name, open_flags, dict_flags,
804 "open %s: %m", name));
805
806 dict_pgsql = (DICT_PGSQL *) dict_alloc(DICT_TYPE_PGSQL, name,
807 sizeof(DICT_PGSQL));
808 dict_pgsql->dict.lookup = dict_pgsql_lookup;
809 dict_pgsql->dict.close = dict_pgsql_close;
810 dict_pgsql->dict.flags = dict_flags;
811 dict_pgsql->parser = parser;
812 pgsql_parse_config(dict_pgsql, name);
813 dict_pgsql->active_host = 0;
814 dict_pgsql->pldb = plpgsql_init(dict_pgsql->hosts);
815 if (dict_pgsql->pldb == NULL)
816 msg_fatal("couldn't initialize pldb!\n");
817 dict_pgsql->dict.owner = cfg_get_owner(dict_pgsql->parser);
818 return (DICT_DEBUG (&dict_pgsql->dict));
819 }
820
821 /* plpgsql_init - initialize a PGSQL database */
822
plpgsql_init(ARGV * hosts)823 static PLPGSQL *plpgsql_init(ARGV *hosts)
824 {
825 PLPGSQL *PLDB;
826 int i;
827
828 PLDB = (PLPGSQL *) mymalloc(sizeof(PLPGSQL));
829 PLDB->len_hosts = hosts->argc;
830 PLDB->db_hosts = (HOST **) mymalloc(sizeof(HOST *) * hosts->argc);
831 for (i = 0; i < hosts->argc; i++)
832 PLDB->db_hosts[i] = host_init(hosts->argv[i]);
833
834 return PLDB;
835 }
836
837
838 /* host_init - initialize HOST structure */
839
host_init(const char * hostname)840 static HOST *host_init(const char *hostname)
841 {
842 const char *myname = "pgsql host_init";
843 HOST *host = (HOST *) mymalloc(sizeof(HOST));
844 const char *d = hostname;
845
846 host->db = 0;
847 host->hostname = mystrdup(hostname);
848 host->stat = STATUNTRIED;
849 host->ts = 0;
850
851 /*
852 * Modern syntax: "postgresql://connection-info".
853 */
854 if (strncmp(d, "postgresql:", 11) == 0) {
855 host->type = TYPECONNSTRING;
856 host->name = mystrdup(d);
857 host->port = 0;
858 }
859
860 /*
861 * Historical syntax: "unix:/pathname" and "inet:host:port". Strip the
862 * "unix:" and "inet:" prefixes. Look at the first character, which is
863 * how PgSQL historically distinguishes between UNIX and INET.
864 */
865 else {
866 if (strncmp(d, "unix:", 5) == 0 || strncmp(d, "inet:", 5) == 0)
867 d += 5;
868 host->name = mystrdup(d);
869 if (host->name[0] && host->name[0] != '/') {
870 host->type = TYPEINET;
871 host->port = split_at_right(host->name, ':');
872 } else {
873 host->type = TYPEUNIX;
874 host->port = 0;
875 }
876 }
877 if (msg_verbose > 1)
878 msg_info("%s: host=%s, port=%s, type=%s", myname, host->name,
879 host->port ? host->port : "",
880 host->type == TYPEUNIX ? "unix" :
881 host->type == TYPEINET ? "inet" :
882 "uri");
883 return host;
884 }
885
886 /* dict_pgsql_close - close PGSQL data base */
887
dict_pgsql_close(DICT * dict)888 static void dict_pgsql_close(DICT *dict)
889 {
890 DICT_PGSQL *dict_pgsql = (DICT_PGSQL *) dict;
891
892 plpgsql_dealloc(dict_pgsql->pldb);
893 cfg_parser_free(dict_pgsql->parser);
894 myfree(dict_pgsql->username);
895 myfree(dict_pgsql->password);
896 myfree(dict_pgsql->dbname);
897 myfree(dict_pgsql->query);
898 myfree(dict_pgsql->result_format);
899 if (dict_pgsql->hosts)
900 argv_free(dict_pgsql->hosts);
901 if (dict_pgsql->ctx)
902 db_common_free_ctx(dict_pgsql->ctx);
903 if (dict->fold_buf)
904 vstring_free(dict->fold_buf);
905 dict_free(dict);
906 }
907
908 /* plpgsql_dealloc - free memory associated with PLPGSQL close databases */
909
plpgsql_dealloc(PLPGSQL * PLDB)910 static void plpgsql_dealloc(PLPGSQL *PLDB)
911 {
912 int i;
913
914 for (i = 0; i < PLDB->len_hosts; i++) {
915 event_cancel_timer(dict_pgsql_event, (void *) (PLDB->db_hosts[i]));
916 if (PLDB->db_hosts[i]->db)
917 PQfinish(PLDB->db_hosts[i]->db);
918 myfree(PLDB->db_hosts[i]->hostname);
919 myfree(PLDB->db_hosts[i]->name);
920 myfree((void *) PLDB->db_hosts[i]);
921 }
922 myfree((void *) PLDB->db_hosts);
923 myfree((void *) (PLDB));
924 }
925
926 #endif
927