1 /* $NetBSD: dict_pgsql.c,v 1.1.1.2 2011/03/02 19:32:14 tron 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 supressed. 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_errno=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\fR \fBhost2.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 "find_inet.h" 166 #include "myrand.h" 167 #include "events.h" 168 #include "stringops.h" 169 170 /* Global library. */ 171 172 #include "cfg_parser.h" 173 #include "db_common.h" 174 175 /* Application-specific. */ 176 177 #include "dict_pgsql.h" 178 179 #define STATACTIVE (1<<0) 180 #define STATFAIL (1<<1) 181 #define STATUNTRIED (1<<2) 182 183 #define TYPEUNIX (1<<0) 184 #define TYPEINET (1<<1) 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 */ 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 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 = 2*len + 1; 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 254 * input keys have reasonable size limits, better safe than sorry. 255 */ 256 if (buflen <= len) 257 msg_panic("%s: arithmetic overflow in 2*%lu+1", 258 myname, (unsigned long) len); 259 260 /* 261 * XXX Workaround: stop further processing when PQescapeStringConn() 262 * (below) fails. A more proper fix requires invasive changes, not 263 * suitable for a stable release. 264 */ 265 if (active_host->stat == STATFAIL) 266 return; 267 268 /* 269 * Escape the input string, using PQescapeStringConn(), because 270 * the older PQescapeString() is not safe anymore, as stated by the 271 * documentation. 272 * 273 * From current libpq (8.1.4) documentation: 274 * 275 * PQescapeStringConn writes an escaped version of the from string 276 * to the to buffer, escaping special characters so that they cannot 277 * cause any harm, and adding a terminating zero byte. 278 * 279 * ... 280 * 281 * The parameter from points to the first character of the string 282 * that is to be escaped, and the length parameter gives the number 283 * of bytes in this string. A terminating zero byte is not required, 284 * and should not be counted in length. 285 * 286 * ... 287 * 288 * (The parameter) to shall point to a buffer that is able to hold 289 * at least one more byte than twice the value of length, otherwise 290 * the behavior is undefined. 291 * 292 * ... 293 * 294 * If the error parameter is not NULL, then *error is set to zero on 295 * success, nonzero on error ... The output string is still generated 296 * on error, but it can be expected that the server will reject it as 297 * malformed. On error, a suitable message is stored in the conn 298 * object, whether or not error is NULL. 299 */ 300 VSTRING_SPACE(result, buflen); 301 PQescapeStringConn(active_host->db, vstring_end(result), name, len, &err); 302 if (err == 0) { 303 VSTRING_SKIP(result); 304 } else { 305 /* 306 * PQescapeStringConn() failed. According to the docs, we still 307 * have a valid, null-terminated output string, but we need not 308 * rely on this behavior. 309 */ 310 msg_warn("dict pgsql: (host %s) cannot escape input string: %s", 311 active_host->hostname, PQerrorMessage(active_host->db)); 312 active_host->stat = STATFAIL; 313 VSTRING_TERMINATE(result); 314 } 315 } 316 317 /* dict_pgsql_lookup - find database entry */ 318 319 static const char *dict_pgsql_lookup(DICT *dict, const char *name) 320 { 321 const char *myname = "dict_pgsql_lookup"; 322 PGSQL_RES *query_res; 323 DICT_PGSQL *dict_pgsql; 324 PLPGSQL *pldb; 325 static VSTRING *query; 326 static VSTRING *result; 327 int i; 328 int j; 329 int numrows; 330 int numcols; 331 int expansion; 332 const char *r; 333 334 dict_pgsql = (DICT_PGSQL *) dict; 335 pldb = dict_pgsql->pldb; 336 337 #define INIT_VSTR(buf, len) do { \ 338 if (buf == 0) \ 339 buf = vstring_alloc(len); \ 340 VSTRING_RESET(buf); \ 341 VSTRING_TERMINATE(buf); \ 342 } while (0) 343 344 INIT_VSTR(query, 10); 345 INIT_VSTR(result, 10); 346 347 dict_errno = 0; 348 349 /* 350 * Optionally fold the key. 351 */ 352 if (dict->flags & DICT_FLAG_FOLD_FIX) { 353 if (dict->fold_buf == 0) 354 dict->fold_buf = vstring_alloc(10); 355 vstring_strcpy(dict->fold_buf, name); 356 name = lowercase(vstring_str(dict->fold_buf)); 357 } 358 359 /* 360 * If there is a domain list for this map, then only search for 361 * addresses in domains on the list. This can significantly reduce 362 * the load on the server. 363 */ 364 if (db_common_check_domain(dict_pgsql->ctx, name) == 0) { 365 if (msg_verbose) 366 msg_info("%s: Skipping lookup of '%s'", myname, name); 367 return (0); 368 } 369 370 /* 371 * Suppress the actual lookup if the expansion is empty. 372 * 373 * This initial expansion is outside the context of any 374 * specific host connection, we just want to check the 375 * key pre-requisites, so when quoting happens separately 376 * for each connection, we don't bother with quoting... 377 */ 378 if (!db_common_expand(dict_pgsql->ctx, dict_pgsql->query, 379 name, 0, query, 0)) 380 return (0); 381 382 /* do the query - set dict_errno & cleanup if there's an error */ 383 if ((query_res = plpgsql_query(dict_pgsql, name, query, 384 dict_pgsql->dbname, 385 dict_pgsql->username, 386 dict_pgsql->password)) == 0) { 387 dict_errno = DICT_ERR_RETRY; 388 return 0; 389 } 390 391 numrows = PQntuples(query_res); 392 if (msg_verbose) 393 msg_info("%s: retrieved %d rows", myname, numrows); 394 if (numrows == 0) { 395 PQclear(query_res); 396 return 0; 397 } 398 numcols = PQnfields(query_res); 399 400 for (expansion = i = 0; i < numrows && dict_errno == 0; i++) { 401 for (j = 0; j < numcols; j++) { 402 r = PQgetvalue(query_res, i, j); 403 if (db_common_expand(dict_pgsql->ctx, dict_pgsql->result_format, 404 r, name, result, 0) 405 && dict_pgsql->expansion_limit > 0 406 && ++expansion > dict_pgsql->expansion_limit) { 407 msg_warn("%s: %s: Expansion limit exceeded for key: '%s'", 408 myname, dict_pgsql->parser->name, name); 409 dict_errno = DICT_ERR_RETRY; 410 break; 411 } 412 } 413 } 414 PQclear(query_res); 415 r = vstring_str(result); 416 return ((dict_errno == 0 && *r) ? r : 0); 417 } 418 419 /* dict_pgsql_check_stat - check the status of a host */ 420 421 static int dict_pgsql_check_stat(HOST *host, unsigned stat, unsigned type, 422 time_t t) 423 { 424 if ((host->stat & stat) && (!type || host->type & type)) { 425 /* try not to hammer the dead hosts too often */ 426 if (host->stat == STATFAIL && host->ts > 0 && host->ts >= t) 427 return 0; 428 return 1; 429 } 430 return 0; 431 } 432 433 /* dict_pgsql_find_host - find a host with the given status */ 434 435 static HOST *dict_pgsql_find_host(PLPGSQL *PLDB, unsigned stat, unsigned type) 436 { 437 time_t t; 438 int count = 0; 439 int idx; 440 int i; 441 442 t = time((time_t *) 0); 443 for (i = 0; i < PLDB->len_hosts; i++) { 444 if (dict_pgsql_check_stat(PLDB->db_hosts[i], stat, type, t)) 445 count++; 446 } 447 448 if (count) { 449 idx = (count > 1) ? 450 1 + count * (double) myrand() / (1.0 + RAND_MAX) : 1; 451 452 for (i = 0; i < PLDB->len_hosts; i++) { 453 if (dict_pgsql_check_stat(PLDB->db_hosts[i], stat, type, t) && 454 --idx == 0) 455 return PLDB->db_hosts[i]; 456 } 457 } 458 return 0; 459 } 460 461 /* dict_pgsql_get_active - get an active connection */ 462 463 static HOST *dict_pgsql_get_active(PLPGSQL *PLDB, char *dbname, 464 char *username, char *password) 465 { 466 const char *myname = "dict_pgsql_get_active"; 467 HOST *host; 468 int count = RETRY_CONN_MAX; 469 470 /* try the active connections first; prefer the ones to UNIX sockets */ 471 if ((host = dict_pgsql_find_host(PLDB, STATACTIVE, TYPEUNIX)) != NULL || 472 (host = dict_pgsql_find_host(PLDB, STATACTIVE, TYPEINET)) != NULL) { 473 if (msg_verbose) 474 msg_info("%s: found active connection to host %s", myname, 475 host->hostname); 476 return host; 477 } 478 479 /* 480 * Try the remaining hosts. 481 * "count" is a safety net, in case the loop takes more than 482 * RETRY_CONN_INTV and the dead hosts are no longer skipped. 483 */ 484 while (--count > 0 && 485 ((host = dict_pgsql_find_host(PLDB, STATUNTRIED | STATFAIL, 486 TYPEUNIX)) != NULL || 487 (host = dict_pgsql_find_host(PLDB, STATUNTRIED | STATFAIL, 488 TYPEINET)) != NULL)) { 489 if (msg_verbose) 490 msg_info("%s: attempting to connect to host %s", myname, 491 host->hostname); 492 plpgsql_connect_single(host, dbname, username, password); 493 if (host->stat == STATACTIVE) 494 return host; 495 } 496 497 /* bad news... */ 498 return 0; 499 } 500 501 /* dict_pgsql_event - callback: close idle connections */ 502 503 static void dict_pgsql_event(int unused_event, char *context) 504 { 505 HOST *host = (HOST *) context; 506 507 if (host->db) 508 plpgsql_close_host(host); 509 } 510 511 /* 512 * plpgsql_query - process a PostgreSQL query. Return PGSQL_RES* on success. 513 * On failure, log failure and try other db instances. 514 * on failure of all db instances, return 0; 515 * close unnecessary active connections 516 */ 517 518 static PGSQL_RES *plpgsql_query(DICT_PGSQL *dict_pgsql, 519 const char *name, 520 VSTRING *query, 521 char *dbname, 522 char *username, 523 char *password) 524 { 525 PLPGSQL *PLDB = dict_pgsql->pldb; 526 HOST *host; 527 PGSQL_RES *res = 0; 528 ExecStatusType status; 529 530 while ((host = dict_pgsql_get_active(PLDB, dbname, username, password)) != NULL) { 531 /* 532 * The active host is used to escape strings in the 533 * context of the active connection's character encoding. 534 */ 535 dict_pgsql->active_host = host; 536 VSTRING_RESET(query); 537 VSTRING_TERMINATE(query); 538 db_common_expand(dict_pgsql->ctx, dict_pgsql->query, 539 name, 0, query, dict_pgsql_quote); 540 dict_pgsql->active_host = 0; 541 542 /* Check for potential dict_pgsql_quote() failure. */ 543 if (host->stat == STATFAIL) { 544 plpgsql_down_host(host); 545 continue; 546 } 547 548 /* 549 * Submit a command to the server. Be paranoid when processing 550 * the result set: try to enumerate every successful case, and 551 * reject everything else. 552 * 553 * From PostgreSQL 8.1.4 docs: (PQexec) returns a PGresult 554 * pointer or possibly a null pointer. A non-null pointer will 555 * generally be returned except in out-of-memory conditions or 556 * serious errors such as inability to send the command to the 557 * server. 558 */ 559 if ((res = PQexec(host->db, vstring_str(query))) != 0) { 560 /* 561 * XXX Because non-null result pointer does not imply success, 562 * we need to check the command's result status. 563 * 564 * Section 28.3.1: A result of status PGRES_NONFATAL_ERROR 565 * will never be returned directly by PQexec or other query 566 * execution functions; results of this kind are instead 567 * passed to the notice processor. 568 * 569 * PGRES_EMPTY_QUERY is being sent by the server when the 570 * query string is empty. The sanity-checking done by 571 * the Postfix infrastructure makes this case impossible, 572 * so we need not handle this situation explicitly. 573 */ 574 switch ((status = PQresultStatus(res))) { 575 case PGRES_TUPLES_OK: 576 case PGRES_COMMAND_OK: 577 /* Success. */ 578 if (msg_verbose) 579 msg_info("dict_pgsql: successful query from host %s", 580 host->hostname); 581 event_request_timer(dict_pgsql_event, (char *) host, 582 IDLE_CONN_INTV); 583 return (res); 584 case PGRES_FATAL_ERROR: 585 msg_warn("pgsql query failed: fatal error from host %s: %s", 586 host->hostname, PQresultErrorMessage(res)); 587 break; 588 case PGRES_BAD_RESPONSE: 589 msg_warn("pgsql query failed: protocol error, host %s", 590 host->hostname); 591 break; 592 default: 593 msg_warn("pgsql query failed: unknown code 0x%lx from host %s", 594 (unsigned long) status, host->hostname); 595 break; 596 } 597 } else { 598 /* 599 * This driver treats null pointers like fatal, non-null 600 * result pointer errors, as suggested by the PostgreSQL 601 * 8.1.4 documentation. 602 */ 603 msg_warn("pgsql query failed: fatal error from host %s: %s", 604 host->hostname, PQerrorMessage(host->db)); 605 } 606 607 /* 608 * XXX An error occurred. Clean up memory and skip this connection. 609 */ 610 if (res != 0) 611 PQclear(res); 612 plpgsql_down_host(host); 613 } 614 615 return (0); 616 } 617 618 /* 619 * plpgsql_connect_single - 620 * used to reconnect to a single database when one is down or none is 621 * connected yet. Log all errors and set the stat field of host accordingly 622 */ 623 static void plpgsql_connect_single(HOST *host, char *dbname, char *username, char *password) 624 { 625 if ((host->db = PQsetdbLogin(host->name, host->port, NULL, NULL, 626 dbname, username, password)) == NULL 627 || PQstatus(host->db) != CONNECTION_OK) { 628 msg_warn("connect to pgsql server %s: %s", 629 host->hostname, PQerrorMessage(host->db)); 630 plpgsql_down_host(host); 631 return; 632 } 633 634 if (msg_verbose) 635 msg_info("dict_pgsql: successful connection to host %s", 636 host->hostname); 637 638 /* 639 * XXX Postfix does not send multi-byte characters. The following 640 * piece of code is an explicit statement of this fact, and the 641 * database server should not accept multi-byte information after 642 * this point. 643 */ 644 if (PQsetClientEncoding(host->db, "LATIN1") != 0) { 645 msg_warn("dict_pgsql: cannot set the encoding to LATIN1, skipping %s", 646 host->hostname); 647 plpgsql_down_host(host); 648 return; 649 } 650 651 /* Success. */ 652 host->stat = STATACTIVE; 653 } 654 655 /* plpgsql_close_host - close an established PostgreSQL connection */ 656 657 static void plpgsql_close_host(HOST *host) 658 { 659 if (host->db) 660 PQfinish(host->db); 661 host->db = 0; 662 host->stat = STATUNTRIED; 663 } 664 665 /* 666 * plpgsql_down_host - close a failed connection AND set a "stay away from 667 * this host" timer. 668 */ 669 static void plpgsql_down_host(HOST *host) 670 { 671 if (host->db) 672 PQfinish(host->db); 673 host->db = 0; 674 host->ts = time((time_t *) 0) + RETRY_CONN_INTV; 675 host->stat = STATFAIL; 676 event_cancel_timer(dict_pgsql_event, (char *) host); 677 } 678 679 /* pgsql_parse_config - parse pgsql configuration file */ 680 681 static void pgsql_parse_config(DICT_PGSQL *dict_pgsql, const char *pgsqlcf) 682 { 683 const char *myname = "pgsql_parse_config"; 684 CFG_PARSER *p; 685 char *hosts; 686 VSTRING *query; 687 char *select_function; 688 689 p = dict_pgsql->parser = cfg_parser_alloc(pgsqlcf); 690 dict_pgsql->username = cfg_get_str(p, "user", "", 0, 0); 691 dict_pgsql->password = cfg_get_str(p, "password", "", 0, 0); 692 dict_pgsql->dbname = cfg_get_str(p, "dbname", "", 1, 0); 693 dict_pgsql->result_format = cfg_get_str(p, "result_format", "%s", 1, 0); 694 /* 695 * XXX: The default should be non-zero for safety, but that is not 696 * backwards compatible. 697 */ 698 dict_pgsql->expansion_limit = cfg_get_int(dict_pgsql->parser, 699 "expansion_limit", 0, 0, 0); 700 701 if ((dict_pgsql->query = cfg_get_str(p, "query", 0, 0, 0)) == 0) { 702 /* 703 * No query specified -- fallback to building it from components 704 * ( old style "select %s from %s where %s" ) 705 */ 706 query = vstring_alloc(64); 707 select_function = cfg_get_str(p, "select_function", 0, 0, 0); 708 if (select_function != 0) { 709 vstring_sprintf(query, "SELECT %s('%%s')", select_function); 710 myfree(select_function); 711 } else 712 db_common_sql_build_query(query, p); 713 dict_pgsql->query = vstring_export(query); 714 } 715 716 /* 717 * Must parse all templates before we can use db_common_expand() 718 */ 719 dict_pgsql->ctx = 0; 720 (void) db_common_parse(&dict_pgsql->dict, &dict_pgsql->ctx, 721 dict_pgsql->query, 1); 722 (void) db_common_parse(0, &dict_pgsql->ctx, dict_pgsql->result_format, 0); 723 db_common_parse_domain(p, dict_pgsql->ctx); 724 725 /* 726 * Maps that use substring keys should only be used with the full 727 * input key. 728 */ 729 if (db_common_dict_partial(dict_pgsql->ctx)) 730 dict_pgsql->dict.flags |= DICT_FLAG_PATTERN; 731 else 732 dict_pgsql->dict.flags |= DICT_FLAG_FIXED; 733 if (dict_pgsql->dict.flags & DICT_FLAG_FOLD_FIX) 734 dict_pgsql->dict.fold_buf = vstring_alloc(10); 735 736 hosts = cfg_get_str(p, "hosts", "", 0, 0); 737 738 dict_pgsql->hosts = argv_split(hosts, " ,\t\r\n"); 739 if (dict_pgsql->hosts->argc == 0) { 740 argv_add(dict_pgsql->hosts, "localhost", ARGV_END); 741 argv_terminate(dict_pgsql->hosts); 742 if (msg_verbose) 743 msg_info("%s: %s: no hostnames specified, defaulting to '%s'", 744 myname, pgsqlcf, dict_pgsql->hosts->argv[0]); 745 } 746 myfree(hosts); 747 } 748 749 /* dict_pgsql_open - open PGSQL data base */ 750 751 DICT *dict_pgsql_open(const char *name, int open_flags, int dict_flags) 752 { 753 DICT_PGSQL *dict_pgsql; 754 755 if (open_flags != O_RDONLY) 756 msg_fatal("%s:%s map requires O_RDONLY access mode", 757 DICT_TYPE_PGSQL, name); 758 759 dict_pgsql = (DICT_PGSQL *) dict_alloc(DICT_TYPE_PGSQL, name, 760 sizeof(DICT_PGSQL)); 761 dict_pgsql->dict.lookup = dict_pgsql_lookup; 762 dict_pgsql->dict.close = dict_pgsql_close; 763 dict_pgsql->dict.flags = dict_flags; 764 pgsql_parse_config(dict_pgsql, name); 765 dict_pgsql->active_host = 0; 766 dict_pgsql->pldb = plpgsql_init(dict_pgsql->hosts); 767 if (dict_pgsql->pldb == NULL) 768 msg_fatal("couldn't intialize pldb!\n"); 769 return &dict_pgsql->dict; 770 } 771 772 /* plpgsql_init - initalize a PGSQL database */ 773 774 static PLPGSQL *plpgsql_init(ARGV *hosts) 775 { 776 PLPGSQL *PLDB; 777 int i; 778 779 PLDB = (PLPGSQL *) mymalloc(sizeof(PLPGSQL)); 780 PLDB->len_hosts = hosts->argc; 781 PLDB->db_hosts = (HOST **) mymalloc(sizeof(HOST *) * hosts->argc); 782 for (i = 0; i < hosts->argc; i++) 783 PLDB->db_hosts[i] = host_init(hosts->argv[i]); 784 785 return PLDB; 786 } 787 788 789 /* host_init - initialize HOST structure */ 790 791 static HOST *host_init(const char *hostname) 792 { 793 const char *myname = "pgsql host_init"; 794 HOST *host = (HOST *) mymalloc(sizeof(HOST)); 795 const char *d = hostname; 796 797 host->db = 0; 798 host->hostname = mystrdup(hostname); 799 host->stat = STATUNTRIED; 800 host->ts = 0; 801 802 /* 803 * Ad-hoc parsing code. Expect "unix:pathname" or "inet:host:port", where 804 * both "inet:" and ":port" are optional. 805 */ 806 if (strncmp(d, "unix:", 5) == 0 || strncmp(d, "inet:", 5) == 0) 807 d += 5; 808 host->name = mystrdup(d); 809 host->port = split_at_right(host->name, ':'); 810 811 /* This is how PgSQL distinguishes between UNIX and INET: */ 812 if (host->name[0] && host->name[0] != '/') 813 host->type = TYPEINET; 814 else 815 host->type = TYPEUNIX; 816 817 if (msg_verbose > 1) 818 msg_info("%s: host=%s, port=%s, type=%s", myname, host->name, 819 host->port ? host->port : "", 820 host->type == TYPEUNIX ? "unix" : "inet"); 821 return host; 822 } 823 824 /* dict_pgsql_close - close PGSQL data base */ 825 826 static void dict_pgsql_close(DICT *dict) 827 { 828 DICT_PGSQL *dict_pgsql = (DICT_PGSQL *) dict; 829 830 plpgsql_dealloc(dict_pgsql->pldb); 831 cfg_parser_free(dict_pgsql->parser); 832 myfree(dict_pgsql->username); 833 myfree(dict_pgsql->password); 834 myfree(dict_pgsql->dbname); 835 myfree(dict_pgsql->query); 836 myfree(dict_pgsql->result_format); 837 if (dict_pgsql->hosts) 838 argv_free(dict_pgsql->hosts); 839 if (dict_pgsql->ctx) 840 db_common_free_ctx(dict_pgsql->ctx); 841 if (dict->fold_buf) 842 vstring_free(dict->fold_buf); 843 dict_free(dict); 844 } 845 846 /* plpgsql_dealloc - free memory associated with PLPGSQL close databases */ 847 848 static void plpgsql_dealloc(PLPGSQL *PLDB) 849 { 850 int i; 851 852 for (i = 0; i < PLDB->len_hosts; i++) { 853 event_cancel_timer(dict_pgsql_event, (char *) (PLDB->db_hosts[i])); 854 if (PLDB->db_hosts[i]->db) 855 PQfinish(PLDB->db_hosts[i]->db); 856 myfree(PLDB->db_hosts[i]->hostname); 857 myfree(PLDB->db_hosts[i]->name); 858 myfree((char *) PLDB->db_hosts[i]); 859 } 860 myfree((char *) PLDB->db_hosts); 861 myfree((char *) (PLDB)); 862 } 863 864 #endif 865