1 /*------------------------------------------------------------------------- 2 * 3 * pg_dumpall.c 4 * 5 * Portions Copyright (c) 1996-2019, PostgreSQL Global Development Group 6 * Portions Copyright (c) 1994, Regents of the University of California 7 * 8 * pg_dumpall forces all pg_dump output to be text, since it also outputs 9 * text into the same output stream. 10 * 11 * src/bin/pg_dump/pg_dumpall.c 12 * 13 *------------------------------------------------------------------------- 14 */ 15 16 #include "postgres_fe.h" 17 18 #include <time.h> 19 #include <unistd.h> 20 21 #include "getopt_long.h" 22 23 #include "dumputils.h" 24 #include "pg_backup.h" 25 #include "common/file_utils.h" 26 #include "common/logging.h" 27 #include "fe_utils/connect.h" 28 #include "fe_utils/string_utils.h" 29 30 /* version string we expect back from pg_dump */ 31 #define PGDUMP_VERSIONSTR "pg_dump (PostgreSQL) " PG_VERSION "\n" 32 33 34 static void help(void); 35 36 static void dropRoles(PGconn *conn); 37 static void dumpRoles(PGconn *conn); 38 static void dumpRoleMembership(PGconn *conn); 39 static void dumpGroups(PGconn *conn); 40 static void dropTablespaces(PGconn *conn); 41 static void dumpTablespaces(PGconn *conn); 42 static void dropDBs(PGconn *conn); 43 static void dumpUserConfig(PGconn *conn, const char *username); 44 static void dumpDatabases(PGconn *conn); 45 static void dumpTimestamp(const char *msg); 46 static int runPgDump(const char *dbname, const char *create_opts); 47 static void buildShSecLabels(PGconn *conn, 48 const char *catalog_name, Oid objectId, 49 const char *objtype, const char *objname, 50 PQExpBuffer buffer); 51 static PGconn *connectDatabase(const char *dbname, const char *connstr, const char *pghost, const char *pgport, 52 const char *pguser, trivalue prompt_password, bool fail_on_error); 53 static char *constructConnStr(const char **keywords, const char **values); 54 static PGresult *executeQuery(PGconn *conn, const char *query); 55 static void executeCommand(PGconn *conn, const char *query); 56 static void expand_dbname_patterns(PGconn *conn, SimpleStringList *patterns, 57 SimpleStringList *names); 58 59 static char pg_dump_bin[MAXPGPATH]; 60 static const char *progname; 61 static PQExpBuffer pgdumpopts; 62 static char *connstr = ""; 63 static bool output_clean = false; 64 static bool skip_acls = false; 65 static bool verbose = false; 66 static bool dosync = true; 67 68 static int binary_upgrade = 0; 69 static int column_inserts = 0; 70 static int disable_dollar_quoting = 0; 71 static int disable_triggers = 0; 72 static int if_exists = 0; 73 static int inserts = 0; 74 static int no_tablespaces = 0; 75 static int use_setsessauth = 0; 76 static int no_comments = 0; 77 static int no_publications = 0; 78 static int no_security_labels = 0; 79 static int no_subscriptions = 0; 80 static int no_unlogged_table_data = 0; 81 static int no_role_passwords = 0; 82 static int server_version; 83 static int load_via_partition_root = 0; 84 static int on_conflict_do_nothing = 0; 85 86 static char role_catalog[10]; 87 #define PG_AUTHID "pg_authid" 88 #define PG_ROLES "pg_roles " 89 90 static FILE *OPF; 91 static char *filename = NULL; 92 93 static SimpleStringList database_exclude_patterns = {NULL, NULL}; 94 static SimpleStringList database_exclude_names = {NULL, NULL}; 95 96 #define exit_nicely(code) exit(code) 97 98 int 99 main(int argc, char *argv[]) 100 { 101 static struct option long_options[] = { 102 {"data-only", no_argument, NULL, 'a'}, 103 {"clean", no_argument, NULL, 'c'}, 104 {"encoding", required_argument, NULL, 'E'}, 105 {"file", required_argument, NULL, 'f'}, 106 {"globals-only", no_argument, NULL, 'g'}, 107 {"host", required_argument, NULL, 'h'}, 108 {"dbname", required_argument, NULL, 'd'}, 109 {"database", required_argument, NULL, 'l'}, 110 {"no-owner", no_argument, NULL, 'O'}, 111 {"port", required_argument, NULL, 'p'}, 112 {"roles-only", no_argument, NULL, 'r'}, 113 {"schema-only", no_argument, NULL, 's'}, 114 {"superuser", required_argument, NULL, 'S'}, 115 {"tablespaces-only", no_argument, NULL, 't'}, 116 {"username", required_argument, NULL, 'U'}, 117 {"verbose", no_argument, NULL, 'v'}, 118 {"no-password", no_argument, NULL, 'w'}, 119 {"password", no_argument, NULL, 'W'}, 120 {"no-privileges", no_argument, NULL, 'x'}, 121 {"no-acl", no_argument, NULL, 'x'}, 122 123 /* 124 * the following options don't have an equivalent short option letter 125 */ 126 {"attribute-inserts", no_argument, &column_inserts, 1}, 127 {"binary-upgrade", no_argument, &binary_upgrade, 1}, 128 {"column-inserts", no_argument, &column_inserts, 1}, 129 {"disable-dollar-quoting", no_argument, &disable_dollar_quoting, 1}, 130 {"disable-triggers", no_argument, &disable_triggers, 1}, 131 {"exclude-database", required_argument, NULL, 6}, 132 {"extra-float-digits", required_argument, NULL, 5}, 133 {"if-exists", no_argument, &if_exists, 1}, 134 {"inserts", no_argument, &inserts, 1}, 135 {"lock-wait-timeout", required_argument, NULL, 2}, 136 {"no-tablespaces", no_argument, &no_tablespaces, 1}, 137 {"quote-all-identifiers", no_argument, "e_all_identifiers, 1}, 138 {"load-via-partition-root", no_argument, &load_via_partition_root, 1}, 139 {"role", required_argument, NULL, 3}, 140 {"use-set-session-authorization", no_argument, &use_setsessauth, 1}, 141 {"no-comments", no_argument, &no_comments, 1}, 142 {"no-publications", no_argument, &no_publications, 1}, 143 {"no-role-passwords", no_argument, &no_role_passwords, 1}, 144 {"no-security-labels", no_argument, &no_security_labels, 1}, 145 {"no-subscriptions", no_argument, &no_subscriptions, 1}, 146 {"no-sync", no_argument, NULL, 4}, 147 {"no-unlogged-table-data", no_argument, &no_unlogged_table_data, 1}, 148 {"on-conflict-do-nothing", no_argument, &on_conflict_do_nothing, 1}, 149 {"rows-per-insert", required_argument, NULL, 7}, 150 151 {NULL, 0, NULL, 0} 152 }; 153 154 char *pghost = NULL; 155 char *pgport = NULL; 156 char *pguser = NULL; 157 char *pgdb = NULL; 158 char *use_role = NULL; 159 const char *dumpencoding = NULL; 160 trivalue prompt_password = TRI_DEFAULT; 161 bool data_only = false; 162 bool globals_only = false; 163 bool roles_only = false; 164 bool tablespaces_only = false; 165 PGconn *conn; 166 int encoding; 167 const char *std_strings; 168 int c, 169 ret; 170 int optindex; 171 172 pg_logging_init(argv[0]); 173 pg_logging_set_level(PG_LOG_WARNING); 174 set_pglocale_pgservice(argv[0], PG_TEXTDOMAIN("pg_dump")); 175 progname = get_progname(argv[0]); 176 177 if (argc > 1) 178 { 179 if (strcmp(argv[1], "--help") == 0 || strcmp(argv[1], "-?") == 0) 180 { 181 help(); 182 exit_nicely(0); 183 } 184 if (strcmp(argv[1], "--version") == 0 || strcmp(argv[1], "-V") == 0) 185 { 186 puts("pg_dumpall (PostgreSQL) " PG_VERSION); 187 exit_nicely(0); 188 } 189 } 190 191 if ((ret = find_other_exec(argv[0], "pg_dump", PGDUMP_VERSIONSTR, 192 pg_dump_bin)) < 0) 193 { 194 char full_path[MAXPGPATH]; 195 196 if (find_my_exec(argv[0], full_path) < 0) 197 strlcpy(full_path, progname, sizeof(full_path)); 198 199 if (ret == -1) 200 pg_log_error("The program \"pg_dump\" is needed by %s but was not found in the\n" 201 "same directory as \"%s\".\n" 202 "Check your installation.", 203 progname, full_path); 204 else 205 pg_log_error("The program \"pg_dump\" was found by \"%s\"\n" 206 "but was not the same version as %s.\n" 207 "Check your installation.", 208 full_path, progname); 209 exit_nicely(1); 210 } 211 212 pgdumpopts = createPQExpBuffer(); 213 214 while ((c = getopt_long(argc, argv, "acd:E:f:gh:l:Op:rsS:tU:vwWx", long_options, &optindex)) != -1) 215 { 216 switch (c) 217 { 218 case 'a': 219 data_only = true; 220 appendPQExpBufferStr(pgdumpopts, " -a"); 221 break; 222 223 case 'c': 224 output_clean = true; 225 break; 226 227 case 'd': 228 connstr = pg_strdup(optarg); 229 break; 230 231 case 'E': 232 dumpencoding = pg_strdup(optarg); 233 appendPQExpBufferStr(pgdumpopts, " -E "); 234 appendShellString(pgdumpopts, optarg); 235 break; 236 237 case 'f': 238 filename = pg_strdup(optarg); 239 appendPQExpBufferStr(pgdumpopts, " -f "); 240 appendShellString(pgdumpopts, filename); 241 break; 242 243 case 'g': 244 globals_only = true; 245 break; 246 247 case 'h': 248 pghost = pg_strdup(optarg); 249 break; 250 251 case 'l': 252 pgdb = pg_strdup(optarg); 253 break; 254 255 case 'O': 256 appendPQExpBufferStr(pgdumpopts, " -O"); 257 break; 258 259 case 'p': 260 pgport = pg_strdup(optarg); 261 break; 262 263 case 'r': 264 roles_only = true; 265 break; 266 267 case 's': 268 appendPQExpBufferStr(pgdumpopts, " -s"); 269 break; 270 271 case 'S': 272 appendPQExpBufferStr(pgdumpopts, " -S "); 273 appendShellString(pgdumpopts, optarg); 274 break; 275 276 case 't': 277 tablespaces_only = true; 278 break; 279 280 case 'U': 281 pguser = pg_strdup(optarg); 282 break; 283 284 case 'v': 285 verbose = true; 286 pg_logging_set_level(PG_LOG_INFO); 287 appendPQExpBufferStr(pgdumpopts, " -v"); 288 break; 289 290 case 'w': 291 prompt_password = TRI_NO; 292 appendPQExpBufferStr(pgdumpopts, " -w"); 293 break; 294 295 case 'W': 296 prompt_password = TRI_YES; 297 appendPQExpBufferStr(pgdumpopts, " -W"); 298 break; 299 300 case 'x': 301 skip_acls = true; 302 appendPQExpBufferStr(pgdumpopts, " -x"); 303 break; 304 305 case 0: 306 break; 307 308 case 2: 309 appendPQExpBufferStr(pgdumpopts, " --lock-wait-timeout "); 310 appendShellString(pgdumpopts, optarg); 311 break; 312 313 case 3: 314 use_role = pg_strdup(optarg); 315 appendPQExpBufferStr(pgdumpopts, " --role "); 316 appendShellString(pgdumpopts, use_role); 317 break; 318 319 case 4: 320 dosync = false; 321 appendPQExpBufferStr(pgdumpopts, " --no-sync"); 322 break; 323 324 case 5: 325 appendPQExpBufferStr(pgdumpopts, " --extra-float-digits "); 326 appendShellString(pgdumpopts, optarg); 327 break; 328 329 case 6: 330 simple_string_list_append(&database_exclude_patterns, optarg); 331 break; 332 333 case 7: 334 appendPQExpBufferStr(pgdumpopts, " --rows-per-insert "); 335 appendShellString(pgdumpopts, optarg); 336 break; 337 338 default: 339 fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname); 340 exit_nicely(1); 341 } 342 } 343 344 /* Complain if any arguments remain */ 345 if (optind < argc) 346 { 347 pg_log_error("too many command-line arguments (first is \"%s\")", 348 argv[optind]); 349 fprintf(stderr, _("Try \"%s --help\" for more information.\n"), 350 progname); 351 exit_nicely(1); 352 } 353 354 if (database_exclude_patterns.head != NULL && 355 (globals_only || roles_only || tablespaces_only)) 356 { 357 pg_log_error("option --exclude-database cannot be used together with -g/--globals-only, -r/--roles-only, or -t/--tablespaces-only"); 358 fprintf(stderr, _("Try \"%s --help\" for more information.\n"), 359 progname); 360 exit_nicely(1); 361 } 362 363 /* Make sure the user hasn't specified a mix of globals-only options */ 364 if (globals_only && roles_only) 365 { 366 pg_log_error("options -g/--globals-only and -r/--roles-only cannot be used together"); 367 fprintf(stderr, _("Try \"%s --help\" for more information.\n"), 368 progname); 369 exit_nicely(1); 370 } 371 372 if (globals_only && tablespaces_only) 373 { 374 pg_log_error("options -g/--globals-only and -t/--tablespaces-only cannot be used together"); 375 fprintf(stderr, _("Try \"%s --help\" for more information.\n"), 376 progname); 377 exit_nicely(1); 378 } 379 380 if (if_exists && !output_clean) 381 { 382 pg_log_error("option --if-exists requires option -c/--clean"); 383 exit_nicely(1); 384 } 385 386 if (roles_only && tablespaces_only) 387 { 388 pg_log_error("options -r/--roles-only and -t/--tablespaces-only cannot be used together"); 389 fprintf(stderr, _("Try \"%s --help\" for more information.\n"), 390 progname); 391 exit_nicely(1); 392 } 393 394 /* 395 * If password values are not required in the dump, switch to using 396 * pg_roles which is equally useful, just more likely to have unrestricted 397 * access than pg_authid. 398 */ 399 if (no_role_passwords) 400 sprintf(role_catalog, "%s", PG_ROLES); 401 else 402 sprintf(role_catalog, "%s", PG_AUTHID); 403 404 /* Add long options to the pg_dump argument list */ 405 if (binary_upgrade) 406 appendPQExpBufferStr(pgdumpopts, " --binary-upgrade"); 407 if (column_inserts) 408 appendPQExpBufferStr(pgdumpopts, " --column-inserts"); 409 if (disable_dollar_quoting) 410 appendPQExpBufferStr(pgdumpopts, " --disable-dollar-quoting"); 411 if (disable_triggers) 412 appendPQExpBufferStr(pgdumpopts, " --disable-triggers"); 413 if (inserts) 414 appendPQExpBufferStr(pgdumpopts, " --inserts"); 415 if (no_tablespaces) 416 appendPQExpBufferStr(pgdumpopts, " --no-tablespaces"); 417 if (quote_all_identifiers) 418 appendPQExpBufferStr(pgdumpopts, " --quote-all-identifiers"); 419 if (load_via_partition_root) 420 appendPQExpBufferStr(pgdumpopts, " --load-via-partition-root"); 421 if (use_setsessauth) 422 appendPQExpBufferStr(pgdumpopts, " --use-set-session-authorization"); 423 if (no_comments) 424 appendPQExpBufferStr(pgdumpopts, " --no-comments"); 425 if (no_publications) 426 appendPQExpBufferStr(pgdumpopts, " --no-publications"); 427 if (no_security_labels) 428 appendPQExpBufferStr(pgdumpopts, " --no-security-labels"); 429 if (no_subscriptions) 430 appendPQExpBufferStr(pgdumpopts, " --no-subscriptions"); 431 if (no_unlogged_table_data) 432 appendPQExpBufferStr(pgdumpopts, " --no-unlogged-table-data"); 433 if (on_conflict_do_nothing) 434 appendPQExpBufferStr(pgdumpopts, " --on-conflict-do-nothing"); 435 436 /* 437 * If there was a database specified on the command line, use that, 438 * otherwise try to connect to database "postgres", and failing that 439 * "template1". "postgres" is the preferred choice for 8.1 and later 440 * servers, but it usually will not exist on older ones. 441 */ 442 if (pgdb) 443 { 444 conn = connectDatabase(pgdb, connstr, pghost, pgport, pguser, 445 prompt_password, false); 446 447 if (!conn) 448 { 449 pg_log_error("could not connect to database \"%s\"", pgdb); 450 exit_nicely(1); 451 } 452 } 453 else 454 { 455 conn = connectDatabase("postgres", connstr, pghost, pgport, pguser, 456 prompt_password, false); 457 if (!conn) 458 conn = connectDatabase("template1", connstr, pghost, pgport, pguser, 459 prompt_password, true); 460 461 if (!conn) 462 { 463 pg_log_error("could not connect to databases \"postgres\" or \"template1\"\n" 464 "Please specify an alternative database."); 465 fprintf(stderr, _("Try \"%s --help\" for more information.\n"), 466 progname); 467 exit_nicely(1); 468 } 469 } 470 471 /* 472 * Get a list of database names that match the exclude patterns 473 */ 474 expand_dbname_patterns(conn, &database_exclude_patterns, 475 &database_exclude_names); 476 477 /* 478 * Open the output file if required, otherwise use stdout 479 */ 480 if (filename) 481 { 482 OPF = fopen(filename, PG_BINARY_W); 483 if (!OPF) 484 { 485 pg_log_error("could not open output file \"%s\": %m", 486 filename); 487 exit_nicely(1); 488 } 489 } 490 else 491 OPF = stdout; 492 493 /* 494 * Set the client encoding if requested. 495 */ 496 if (dumpencoding) 497 { 498 if (PQsetClientEncoding(conn, dumpencoding) < 0) 499 { 500 pg_log_error("invalid client encoding \"%s\" specified", 501 dumpencoding); 502 exit_nicely(1); 503 } 504 } 505 506 /* 507 * Get the active encoding and the standard_conforming_strings setting, so 508 * we know how to escape strings. 509 */ 510 encoding = PQclientEncoding(conn); 511 std_strings = PQparameterStatus(conn, "standard_conforming_strings"); 512 if (!std_strings) 513 std_strings = "off"; 514 515 /* Set the role if requested */ 516 if (use_role && server_version >= 80100) 517 { 518 PQExpBuffer query = createPQExpBuffer(); 519 520 appendPQExpBuffer(query, "SET ROLE %s", fmtId(use_role)); 521 executeCommand(conn, query->data); 522 destroyPQExpBuffer(query); 523 } 524 525 /* Force quoting of all identifiers if requested. */ 526 if (quote_all_identifiers && server_version >= 90100) 527 executeCommand(conn, "SET quote_all_identifiers = true"); 528 529 fprintf(OPF, "--\n-- PostgreSQL database cluster dump\n--\n\n"); 530 if (verbose) 531 dumpTimestamp("Started on"); 532 533 /* 534 * We used to emit \connect postgres here, but that served no purpose 535 * other than to break things for installations without a postgres 536 * database. Everything we're restoring here is a global, so whichever 537 * database we're connected to at the moment is fine. 538 */ 539 540 /* Restore will need to write to the target cluster */ 541 fprintf(OPF, "SET default_transaction_read_only = off;\n\n"); 542 543 /* Replicate encoding and std_strings in output */ 544 fprintf(OPF, "SET client_encoding = '%s';\n", 545 pg_encoding_to_char(encoding)); 546 fprintf(OPF, "SET standard_conforming_strings = %s;\n", std_strings); 547 if (strcmp(std_strings, "off") == 0) 548 fprintf(OPF, "SET escape_string_warning = off;\n"); 549 fprintf(OPF, "\n"); 550 551 if (!data_only) 552 { 553 /* 554 * If asked to --clean, do that first. We can avoid detailed 555 * dependency analysis because databases never depend on each other, 556 * and tablespaces never depend on each other. Roles could have 557 * grants to each other, but DROP ROLE will clean those up silently. 558 */ 559 if (output_clean) 560 { 561 if (!globals_only && !roles_only && !tablespaces_only) 562 dropDBs(conn); 563 564 if (!roles_only && !no_tablespaces) 565 dropTablespaces(conn); 566 567 if (!tablespaces_only) 568 dropRoles(conn); 569 } 570 571 /* 572 * Now create objects as requested. Be careful that option logic here 573 * is the same as for drops above. 574 */ 575 if (!tablespaces_only) 576 { 577 /* Dump roles (users) */ 578 dumpRoles(conn); 579 580 /* Dump role memberships --- need different method for pre-8.1 */ 581 if (server_version >= 80100) 582 dumpRoleMembership(conn); 583 else 584 dumpGroups(conn); 585 } 586 587 /* Dump tablespaces */ 588 if (!roles_only && !no_tablespaces) 589 dumpTablespaces(conn); 590 } 591 592 if (!globals_only && !roles_only && !tablespaces_only) 593 dumpDatabases(conn); 594 595 PQfinish(conn); 596 597 if (verbose) 598 dumpTimestamp("Completed on"); 599 fprintf(OPF, "--\n-- PostgreSQL database cluster dump complete\n--\n\n"); 600 601 if (filename) 602 { 603 fclose(OPF); 604 605 /* sync the resulting file, errors are not fatal */ 606 if (dosync) 607 (void) fsync_fname(filename, false); 608 } 609 610 exit_nicely(0); 611 } 612 613 614 static void 615 help(void) 616 { 617 printf(_("%s extracts a PostgreSQL database cluster into an SQL script file.\n\n"), progname); 618 printf(_("Usage:\n")); 619 printf(_(" %s [OPTION]...\n"), progname); 620 621 printf(_("\nGeneral options:\n")); 622 printf(_(" -f, --file=FILENAME output file name\n")); 623 printf(_(" -v, --verbose verbose mode\n")); 624 printf(_(" -V, --version output version information, then exit\n")); 625 printf(_(" --lock-wait-timeout=TIMEOUT fail after waiting TIMEOUT for a table lock\n")); 626 printf(_(" -?, --help show this help, then exit\n")); 627 printf(_("\nOptions controlling the output content:\n")); 628 printf(_(" -a, --data-only dump only the data, not the schema\n")); 629 printf(_(" -c, --clean clean (drop) databases before recreating\n")); 630 printf(_(" -E, --encoding=ENCODING dump the data in encoding ENCODING\n")); 631 printf(_(" -g, --globals-only dump only global objects, no databases\n")); 632 printf(_(" -O, --no-owner skip restoration of object ownership\n")); 633 printf(_(" -r, --roles-only dump only roles, no databases or tablespaces\n")); 634 printf(_(" -s, --schema-only dump only the schema, no data\n")); 635 printf(_(" -S, --superuser=NAME superuser user name to use in the dump\n")); 636 printf(_(" -t, --tablespaces-only dump only tablespaces, no databases or roles\n")); 637 printf(_(" -x, --no-privileges do not dump privileges (grant/revoke)\n")); 638 printf(_(" --binary-upgrade for use by upgrade utilities only\n")); 639 printf(_(" --column-inserts dump data as INSERT commands with column names\n")); 640 printf(_(" --disable-dollar-quoting disable dollar quoting, use SQL standard quoting\n")); 641 printf(_(" --disable-triggers disable triggers during data-only restore\n")); 642 printf(_(" --exclude-database=PATTERN exclude databases whose name matches PATTERN\n")); 643 printf(_(" --extra-float-digits=NUM override default setting for extra_float_digits\n")); 644 printf(_(" --if-exists use IF EXISTS when dropping objects\n")); 645 printf(_(" --inserts dump data as INSERT commands, rather than COPY\n")); 646 printf(_(" --load-via-partition-root load partitions via the root table\n")); 647 printf(_(" --no-comments do not dump comments\n")); 648 printf(_(" --no-publications do not dump publications\n")); 649 printf(_(" --no-role-passwords do not dump passwords for roles\n")); 650 printf(_(" --no-security-labels do not dump security label assignments\n")); 651 printf(_(" --no-subscriptions do not dump subscriptions\n")); 652 printf(_(" --no-sync do not wait for changes to be written safely to disk\n")); 653 printf(_(" --no-tablespaces do not dump tablespace assignments\n")); 654 printf(_(" --no-unlogged-table-data do not dump unlogged table data\n")); 655 printf(_(" --on-conflict-do-nothing add ON CONFLICT DO NOTHING to INSERT commands\n")); 656 printf(_(" --quote-all-identifiers quote all identifiers, even if not key words\n")); 657 printf(_(" --rows-per-insert=NROWS number of rows per INSERT; implies --inserts\n")); 658 printf(_(" --use-set-session-authorization\n" 659 " use SET SESSION AUTHORIZATION commands instead of\n" 660 " ALTER OWNER commands to set ownership\n")); 661 662 printf(_("\nConnection options:\n")); 663 printf(_(" -d, --dbname=CONNSTR connect using connection string\n")); 664 printf(_(" -h, --host=HOSTNAME database server host or socket directory\n")); 665 printf(_(" -l, --database=DBNAME alternative default database\n")); 666 printf(_(" -p, --port=PORT database server port number\n")); 667 printf(_(" -U, --username=NAME connect as specified database user\n")); 668 printf(_(" -w, --no-password never prompt for password\n")); 669 printf(_(" -W, --password force password prompt (should happen automatically)\n")); 670 printf(_(" --role=ROLENAME do SET ROLE before dump\n")); 671 672 printf(_("\nIf -f/--file is not used, then the SQL script will be written to the standard\n" 673 "output.\n\n")); 674 printf(_("Report bugs to <pgsql-bugs@lists.postgresql.org>.\n")); 675 } 676 677 678 /* 679 * Drop roles 680 */ 681 static void 682 dropRoles(PGconn *conn) 683 { 684 PQExpBuffer buf = createPQExpBuffer(); 685 PGresult *res; 686 int i_rolname; 687 int i; 688 689 if (server_version >= 90600) 690 printfPQExpBuffer(buf, 691 "SELECT rolname " 692 "FROM %s " 693 "WHERE rolname !~ '^pg_' " 694 "ORDER BY 1", role_catalog); 695 else if (server_version >= 80100) 696 printfPQExpBuffer(buf, 697 "SELECT rolname " 698 "FROM %s " 699 "ORDER BY 1", role_catalog); 700 else 701 printfPQExpBuffer(buf, 702 "SELECT usename as rolname " 703 "FROM pg_shadow " 704 "UNION " 705 "SELECT groname as rolname " 706 "FROM pg_group " 707 "ORDER BY 1"); 708 709 res = executeQuery(conn, buf->data); 710 711 i_rolname = PQfnumber(res, "rolname"); 712 713 if (PQntuples(res) > 0) 714 fprintf(OPF, "--\n-- Drop roles\n--\n\n"); 715 716 for (i = 0; i < PQntuples(res); i++) 717 { 718 const char *rolename; 719 720 rolename = PQgetvalue(res, i, i_rolname); 721 722 fprintf(OPF, "DROP ROLE %s%s;\n", 723 if_exists ? "IF EXISTS " : "", 724 fmtId(rolename)); 725 } 726 727 PQclear(res); 728 destroyPQExpBuffer(buf); 729 730 fprintf(OPF, "\n\n"); 731 } 732 733 /* 734 * Dump roles 735 */ 736 static void 737 dumpRoles(PGconn *conn) 738 { 739 PQExpBuffer buf = createPQExpBuffer(); 740 PGresult *res; 741 int i_oid, 742 i_rolname, 743 i_rolsuper, 744 i_rolinherit, 745 i_rolcreaterole, 746 i_rolcreatedb, 747 i_rolcanlogin, 748 i_rolconnlimit, 749 i_rolpassword, 750 i_rolvaliduntil, 751 i_rolreplication, 752 i_rolbypassrls, 753 i_rolcomment, 754 i_is_current_user; 755 int i; 756 757 /* note: rolconfig is dumped later */ 758 if (server_version >= 90600) 759 printfPQExpBuffer(buf, 760 "SELECT oid, rolname, rolsuper, rolinherit, " 761 "rolcreaterole, rolcreatedb, " 762 "rolcanlogin, rolconnlimit, rolpassword, " 763 "rolvaliduntil, rolreplication, rolbypassrls, " 764 "pg_catalog.shobj_description(oid, '%s') as rolcomment, " 765 "rolname = current_user AS is_current_user " 766 "FROM %s " 767 "WHERE rolname !~ '^pg_' " 768 "ORDER BY 2", role_catalog, role_catalog); 769 else if (server_version >= 90500) 770 printfPQExpBuffer(buf, 771 "SELECT oid, rolname, rolsuper, rolinherit, " 772 "rolcreaterole, rolcreatedb, " 773 "rolcanlogin, rolconnlimit, rolpassword, " 774 "rolvaliduntil, rolreplication, rolbypassrls, " 775 "pg_catalog.shobj_description(oid, '%s') as rolcomment, " 776 "rolname = current_user AS is_current_user " 777 "FROM %s " 778 "ORDER BY 2", role_catalog, role_catalog); 779 else if (server_version >= 90100) 780 printfPQExpBuffer(buf, 781 "SELECT oid, rolname, rolsuper, rolinherit, " 782 "rolcreaterole, rolcreatedb, " 783 "rolcanlogin, rolconnlimit, rolpassword, " 784 "rolvaliduntil, rolreplication, " 785 "false as rolbypassrls, " 786 "pg_catalog.shobj_description(oid, '%s') as rolcomment, " 787 "rolname = current_user AS is_current_user " 788 "FROM %s " 789 "ORDER BY 2", role_catalog, role_catalog); 790 else if (server_version >= 80200) 791 printfPQExpBuffer(buf, 792 "SELECT oid, rolname, rolsuper, rolinherit, " 793 "rolcreaterole, rolcreatedb, " 794 "rolcanlogin, rolconnlimit, rolpassword, " 795 "rolvaliduntil, false as rolreplication, " 796 "false as rolbypassrls, " 797 "pg_catalog.shobj_description(oid, '%s') as rolcomment, " 798 "rolname = current_user AS is_current_user " 799 "FROM %s " 800 "ORDER BY 2", role_catalog, role_catalog); 801 else if (server_version >= 80100) 802 printfPQExpBuffer(buf, 803 "SELECT oid, rolname, rolsuper, rolinherit, " 804 "rolcreaterole, rolcreatedb, " 805 "rolcanlogin, rolconnlimit, rolpassword, " 806 "rolvaliduntil, false as rolreplication, " 807 "false as rolbypassrls, " 808 "null as rolcomment, " 809 "rolname = current_user AS is_current_user " 810 "FROM %s " 811 "ORDER BY 2", role_catalog); 812 else 813 printfPQExpBuffer(buf, 814 "SELECT 0 as oid, usename as rolname, " 815 "usesuper as rolsuper, " 816 "true as rolinherit, " 817 "usesuper as rolcreaterole, " 818 "usecreatedb as rolcreatedb, " 819 "true as rolcanlogin, " 820 "-1 as rolconnlimit, " 821 "passwd as rolpassword, " 822 "valuntil as rolvaliduntil, " 823 "false as rolreplication, " 824 "false as rolbypassrls, " 825 "null as rolcomment, " 826 "usename = current_user AS is_current_user " 827 "FROM pg_shadow " 828 "UNION ALL " 829 "SELECT 0 as oid, groname as rolname, " 830 "false as rolsuper, " 831 "true as rolinherit, " 832 "false as rolcreaterole, " 833 "false as rolcreatedb, " 834 "false as rolcanlogin, " 835 "-1 as rolconnlimit, " 836 "null::text as rolpassword, " 837 "null::timestamptz as rolvaliduntil, " 838 "false as rolreplication, " 839 "false as rolbypassrls, " 840 "null as rolcomment, " 841 "false AS is_current_user " 842 "FROM pg_group " 843 "WHERE NOT EXISTS (SELECT 1 FROM pg_shadow " 844 " WHERE usename = groname) " 845 "ORDER BY 2"); 846 847 res = executeQuery(conn, buf->data); 848 849 i_oid = PQfnumber(res, "oid"); 850 i_rolname = PQfnumber(res, "rolname"); 851 i_rolsuper = PQfnumber(res, "rolsuper"); 852 i_rolinherit = PQfnumber(res, "rolinherit"); 853 i_rolcreaterole = PQfnumber(res, "rolcreaterole"); 854 i_rolcreatedb = PQfnumber(res, "rolcreatedb"); 855 i_rolcanlogin = PQfnumber(res, "rolcanlogin"); 856 i_rolconnlimit = PQfnumber(res, "rolconnlimit"); 857 i_rolpassword = PQfnumber(res, "rolpassword"); 858 i_rolvaliduntil = PQfnumber(res, "rolvaliduntil"); 859 i_rolreplication = PQfnumber(res, "rolreplication"); 860 i_rolbypassrls = PQfnumber(res, "rolbypassrls"); 861 i_rolcomment = PQfnumber(res, "rolcomment"); 862 i_is_current_user = PQfnumber(res, "is_current_user"); 863 864 if (PQntuples(res) > 0) 865 fprintf(OPF, "--\n-- Roles\n--\n\n"); 866 867 for (i = 0; i < PQntuples(res); i++) 868 { 869 const char *rolename; 870 Oid auth_oid; 871 872 auth_oid = atooid(PQgetvalue(res, i, i_oid)); 873 rolename = PQgetvalue(res, i, i_rolname); 874 875 if (strncmp(rolename, "pg_", 3) == 0) 876 { 877 pg_log_warning("role name starting with \"pg_\" skipped (%s)", 878 rolename); 879 continue; 880 } 881 882 resetPQExpBuffer(buf); 883 884 if (binary_upgrade) 885 { 886 appendPQExpBufferStr(buf, "\n-- For binary upgrade, must preserve pg_authid.oid\n"); 887 appendPQExpBuffer(buf, 888 "SELECT pg_catalog.binary_upgrade_set_next_pg_authid_oid('%u'::pg_catalog.oid);\n\n", 889 auth_oid); 890 } 891 892 /* 893 * We dump CREATE ROLE followed by ALTER ROLE to ensure that the role 894 * will acquire the right properties even if it already exists (ie, it 895 * won't hurt for the CREATE to fail). This is particularly important 896 * for the role we are connected as, since even with --clean we will 897 * have failed to drop it. binary_upgrade cannot generate any errors, 898 * so we assume the current role is already created. 899 */ 900 if (!binary_upgrade || 901 strcmp(PQgetvalue(res, i, i_is_current_user), "f") == 0) 902 appendPQExpBuffer(buf, "CREATE ROLE %s;\n", fmtId(rolename)); 903 appendPQExpBuffer(buf, "ALTER ROLE %s WITH", fmtId(rolename)); 904 905 if (strcmp(PQgetvalue(res, i, i_rolsuper), "t") == 0) 906 appendPQExpBufferStr(buf, " SUPERUSER"); 907 else 908 appendPQExpBufferStr(buf, " NOSUPERUSER"); 909 910 if (strcmp(PQgetvalue(res, i, i_rolinherit), "t") == 0) 911 appendPQExpBufferStr(buf, " INHERIT"); 912 else 913 appendPQExpBufferStr(buf, " NOINHERIT"); 914 915 if (strcmp(PQgetvalue(res, i, i_rolcreaterole), "t") == 0) 916 appendPQExpBufferStr(buf, " CREATEROLE"); 917 else 918 appendPQExpBufferStr(buf, " NOCREATEROLE"); 919 920 if (strcmp(PQgetvalue(res, i, i_rolcreatedb), "t") == 0) 921 appendPQExpBufferStr(buf, " CREATEDB"); 922 else 923 appendPQExpBufferStr(buf, " NOCREATEDB"); 924 925 if (strcmp(PQgetvalue(res, i, i_rolcanlogin), "t") == 0) 926 appendPQExpBufferStr(buf, " LOGIN"); 927 else 928 appendPQExpBufferStr(buf, " NOLOGIN"); 929 930 if (strcmp(PQgetvalue(res, i, i_rolreplication), "t") == 0) 931 appendPQExpBufferStr(buf, " REPLICATION"); 932 else 933 appendPQExpBufferStr(buf, " NOREPLICATION"); 934 935 if (strcmp(PQgetvalue(res, i, i_rolbypassrls), "t") == 0) 936 appendPQExpBufferStr(buf, " BYPASSRLS"); 937 else 938 appendPQExpBufferStr(buf, " NOBYPASSRLS"); 939 940 if (strcmp(PQgetvalue(res, i, i_rolconnlimit), "-1") != 0) 941 appendPQExpBuffer(buf, " CONNECTION LIMIT %s", 942 PQgetvalue(res, i, i_rolconnlimit)); 943 944 945 if (!PQgetisnull(res, i, i_rolpassword) && !no_role_passwords) 946 { 947 appendPQExpBufferStr(buf, " PASSWORD "); 948 appendStringLiteralConn(buf, PQgetvalue(res, i, i_rolpassword), conn); 949 } 950 951 if (!PQgetisnull(res, i, i_rolvaliduntil)) 952 appendPQExpBuffer(buf, " VALID UNTIL '%s'", 953 PQgetvalue(res, i, i_rolvaliduntil)); 954 955 appendPQExpBufferStr(buf, ";\n"); 956 957 if (!no_comments && !PQgetisnull(res, i, i_rolcomment)) 958 { 959 appendPQExpBuffer(buf, "COMMENT ON ROLE %s IS ", fmtId(rolename)); 960 appendStringLiteralConn(buf, PQgetvalue(res, i, i_rolcomment), conn); 961 appendPQExpBufferStr(buf, ";\n"); 962 } 963 964 if (!no_security_labels && server_version >= 90200) 965 buildShSecLabels(conn, "pg_authid", auth_oid, 966 "ROLE", rolename, 967 buf); 968 969 fprintf(OPF, "%s", buf->data); 970 } 971 972 /* 973 * Dump configuration settings for roles after all roles have been dumped. 974 * We do it this way because config settings for roles could mention the 975 * names of other roles. 976 */ 977 for (i = 0; i < PQntuples(res); i++) 978 dumpUserConfig(conn, PQgetvalue(res, i, i_rolname)); 979 980 PQclear(res); 981 982 fprintf(OPF, "\n\n"); 983 984 destroyPQExpBuffer(buf); 985 } 986 987 988 /* 989 * Dump role memberships. This code is used for 8.1 and later servers. 990 * 991 * Note: we expect dumpRoles already created all the roles, but there is 992 * no membership yet. 993 */ 994 static void 995 dumpRoleMembership(PGconn *conn) 996 { 997 PQExpBuffer buf = createPQExpBuffer(); 998 PGresult *res; 999 int i; 1000 1001 printfPQExpBuffer(buf, "SELECT ur.rolname AS roleid, " 1002 "um.rolname AS member, " 1003 "a.admin_option, " 1004 "ug.rolname AS grantor " 1005 "FROM pg_auth_members a " 1006 "LEFT JOIN %s ur on ur.oid = a.roleid " 1007 "LEFT JOIN %s um on um.oid = a.member " 1008 "LEFT JOIN %s ug on ug.oid = a.grantor " 1009 "WHERE NOT (ur.rolname ~ '^pg_' AND um.rolname ~ '^pg_')" 1010 "ORDER BY 1,2,3", role_catalog, role_catalog, role_catalog); 1011 res = executeQuery(conn, buf->data); 1012 1013 if (PQntuples(res) > 0) 1014 fprintf(OPF, "--\n-- Role memberships\n--\n\n"); 1015 1016 for (i = 0; i < PQntuples(res); i++) 1017 { 1018 char *roleid = PQgetvalue(res, i, 0); 1019 char *member = PQgetvalue(res, i, 1); 1020 char *option = PQgetvalue(res, i, 2); 1021 1022 fprintf(OPF, "GRANT %s", fmtId(roleid)); 1023 fprintf(OPF, " TO %s", fmtId(member)); 1024 if (*option == 't') 1025 fprintf(OPF, " WITH ADMIN OPTION"); 1026 1027 /* 1028 * We don't track the grantor very carefully in the backend, so cope 1029 * with the possibility that it has been dropped. 1030 */ 1031 if (!PQgetisnull(res, i, 3)) 1032 { 1033 char *grantor = PQgetvalue(res, i, 3); 1034 1035 fprintf(OPF, " GRANTED BY %s", fmtId(grantor)); 1036 } 1037 fprintf(OPF, ";\n"); 1038 } 1039 1040 PQclear(res); 1041 destroyPQExpBuffer(buf); 1042 1043 fprintf(OPF, "\n\n"); 1044 } 1045 1046 /* 1047 * Dump group memberships from a pre-8.1 server. It's annoying that we 1048 * can't share any useful amount of code with the post-8.1 case, but 1049 * the catalog representations are too different. 1050 * 1051 * Note: we expect dumpRoles already created all the roles, but there is 1052 * no membership yet. 1053 */ 1054 static void 1055 dumpGroups(PGconn *conn) 1056 { 1057 PQExpBuffer buf = createPQExpBuffer(); 1058 PGresult *res; 1059 int i; 1060 1061 res = executeQuery(conn, 1062 "SELECT groname, grolist FROM pg_group ORDER BY 1"); 1063 1064 if (PQntuples(res) > 0) 1065 fprintf(OPF, "--\n-- Role memberships\n--\n\n"); 1066 1067 for (i = 0; i < PQntuples(res); i++) 1068 { 1069 char *groname = PQgetvalue(res, i, 0); 1070 char *grolist = PQgetvalue(res, i, 1); 1071 PGresult *res2; 1072 int j; 1073 1074 /* 1075 * Array representation is {1,2,3} ... convert to (1,2,3) 1076 */ 1077 if (strlen(grolist) < 3) 1078 continue; 1079 1080 grolist = pg_strdup(grolist); 1081 grolist[0] = '('; 1082 grolist[strlen(grolist) - 1] = ')'; 1083 printfPQExpBuffer(buf, 1084 "SELECT usename FROM pg_shadow " 1085 "WHERE usesysid IN %s ORDER BY 1", 1086 grolist); 1087 free(grolist); 1088 1089 res2 = executeQuery(conn, buf->data); 1090 1091 for (j = 0; j < PQntuples(res2); j++) 1092 { 1093 char *usename = PQgetvalue(res2, j, 0); 1094 1095 /* 1096 * Don't try to grant a role to itself; can happen if old 1097 * installation has identically named user and group. 1098 */ 1099 if (strcmp(groname, usename) == 0) 1100 continue; 1101 1102 fprintf(OPF, "GRANT %s", fmtId(groname)); 1103 fprintf(OPF, " TO %s;\n", fmtId(usename)); 1104 } 1105 1106 PQclear(res2); 1107 } 1108 1109 PQclear(res); 1110 destroyPQExpBuffer(buf); 1111 1112 fprintf(OPF, "\n\n"); 1113 } 1114 1115 1116 /* 1117 * Drop tablespaces. 1118 */ 1119 static void 1120 dropTablespaces(PGconn *conn) 1121 { 1122 PGresult *res; 1123 int i; 1124 1125 /* 1126 * Get all tablespaces except built-in ones (which we assume are named 1127 * pg_xxx) 1128 */ 1129 res = executeQuery(conn, "SELECT spcname " 1130 "FROM pg_catalog.pg_tablespace " 1131 "WHERE spcname !~ '^pg_' " 1132 "ORDER BY 1"); 1133 1134 if (PQntuples(res) > 0) 1135 fprintf(OPF, "--\n-- Drop tablespaces\n--\n\n"); 1136 1137 for (i = 0; i < PQntuples(res); i++) 1138 { 1139 char *spcname = PQgetvalue(res, i, 0); 1140 1141 fprintf(OPF, "DROP TABLESPACE %s%s;\n", 1142 if_exists ? "IF EXISTS " : "", 1143 fmtId(spcname)); 1144 } 1145 1146 PQclear(res); 1147 1148 fprintf(OPF, "\n\n"); 1149 } 1150 1151 /* 1152 * Dump tablespaces. 1153 */ 1154 static void 1155 dumpTablespaces(PGconn *conn) 1156 { 1157 PGresult *res; 1158 int i; 1159 1160 /* 1161 * Get all tablespaces except built-in ones (which we assume are named 1162 * pg_xxx) 1163 * 1164 * For the tablespace ACLs, as of 9.6, we extract both the positive (as 1165 * spcacl) and negative (as rspcacl) ACLs, relative to the default ACL for 1166 * tablespaces, which are then passed to buildACLCommands() below. 1167 * 1168 * See buildACLQueries() and buildACLCommands(). 1169 * 1170 * The order in which privileges are in the ACL string (the order they 1171 * have been GRANT'd in, which the backend maintains) must be preserved to 1172 * ensure that GRANTs WITH GRANT OPTION and subsequent GRANTs based on 1173 * those are dumped in the correct order. 1174 * 1175 * Note that we do not support initial privileges (pg_init_privs) on 1176 * tablespaces, so this logic cannot make use of buildACLQueries(). 1177 */ 1178 if (server_version >= 90600) 1179 res = executeQuery(conn, "SELECT oid, spcname, " 1180 "pg_catalog.pg_get_userbyid(spcowner) AS spcowner, " 1181 "pg_catalog.pg_tablespace_location(oid), " 1182 "(SELECT array_agg(acl ORDER BY row_n) FROM " 1183 " (SELECT acl, row_n FROM " 1184 " unnest(coalesce(spcacl,acldefault('t',spcowner))) " 1185 " WITH ORDINALITY AS perm(acl,row_n) " 1186 " WHERE NOT EXISTS ( " 1187 " SELECT 1 " 1188 " FROM unnest(acldefault('t',spcowner)) " 1189 " AS init(init_acl) " 1190 " WHERE acl = init_acl)) AS spcacls) " 1191 " AS spcacl, " 1192 "(SELECT array_agg(acl ORDER BY row_n) FROM " 1193 " (SELECT acl, row_n FROM " 1194 " unnest(acldefault('t',spcowner)) " 1195 " WITH ORDINALITY AS initp(acl,row_n) " 1196 " WHERE NOT EXISTS ( " 1197 " SELECT 1 " 1198 " FROM unnest(coalesce(spcacl,acldefault('t',spcowner))) " 1199 " AS permp(orig_acl) " 1200 " WHERE acl = orig_acl)) AS rspcacls) " 1201 " AS rspcacl, " 1202 "array_to_string(spcoptions, ', ')," 1203 "pg_catalog.shobj_description(oid, 'pg_tablespace') " 1204 "FROM pg_catalog.pg_tablespace " 1205 "WHERE spcname !~ '^pg_' " 1206 "ORDER BY 1"); 1207 else if (server_version >= 90200) 1208 res = executeQuery(conn, "SELECT oid, spcname, " 1209 "pg_catalog.pg_get_userbyid(spcowner) AS spcowner, " 1210 "pg_catalog.pg_tablespace_location(oid), " 1211 "spcacl, '' as rspcacl, " 1212 "array_to_string(spcoptions, ', ')," 1213 "pg_catalog.shobj_description(oid, 'pg_tablespace') " 1214 "FROM pg_catalog.pg_tablespace " 1215 "WHERE spcname !~ '^pg_' " 1216 "ORDER BY 1"); 1217 else if (server_version >= 90000) 1218 res = executeQuery(conn, "SELECT oid, spcname, " 1219 "pg_catalog.pg_get_userbyid(spcowner) AS spcowner, " 1220 "spclocation, spcacl, '' as rspcacl, " 1221 "array_to_string(spcoptions, ', ')," 1222 "pg_catalog.shobj_description(oid, 'pg_tablespace') " 1223 "FROM pg_catalog.pg_tablespace " 1224 "WHERE spcname !~ '^pg_' " 1225 "ORDER BY 1"); 1226 else if (server_version >= 80200) 1227 res = executeQuery(conn, "SELECT oid, spcname, " 1228 "pg_catalog.pg_get_userbyid(spcowner) AS spcowner, " 1229 "spclocation, spcacl, '' as rspcacl, null, " 1230 "pg_catalog.shobj_description(oid, 'pg_tablespace') " 1231 "FROM pg_catalog.pg_tablespace " 1232 "WHERE spcname !~ '^pg_' " 1233 "ORDER BY 1"); 1234 else 1235 res = executeQuery(conn, "SELECT oid, spcname, " 1236 "pg_catalog.pg_get_userbyid(spcowner) AS spcowner, " 1237 "spclocation, spcacl, '' as rspcacl, " 1238 "null, null " 1239 "FROM pg_catalog.pg_tablespace " 1240 "WHERE spcname !~ '^pg_' " 1241 "ORDER BY 1"); 1242 1243 if (PQntuples(res) > 0) 1244 fprintf(OPF, "--\n-- Tablespaces\n--\n\n"); 1245 1246 for (i = 0; i < PQntuples(res); i++) 1247 { 1248 PQExpBuffer buf = createPQExpBuffer(); 1249 Oid spcoid = atooid(PQgetvalue(res, i, 0)); 1250 char *spcname = PQgetvalue(res, i, 1); 1251 char *spcowner = PQgetvalue(res, i, 2); 1252 char *spclocation = PQgetvalue(res, i, 3); 1253 char *spcacl = PQgetvalue(res, i, 4); 1254 char *rspcacl = PQgetvalue(res, i, 5); 1255 char *spcoptions = PQgetvalue(res, i, 6); 1256 char *spccomment = PQgetvalue(res, i, 7); 1257 char *fspcname; 1258 1259 /* needed for buildACLCommands() */ 1260 fspcname = pg_strdup(fmtId(spcname)); 1261 1262 appendPQExpBuffer(buf, "CREATE TABLESPACE %s", fspcname); 1263 appendPQExpBuffer(buf, " OWNER %s", fmtId(spcowner)); 1264 1265 appendPQExpBufferStr(buf, " LOCATION "); 1266 appendStringLiteralConn(buf, spclocation, conn); 1267 appendPQExpBufferStr(buf, ";\n"); 1268 1269 if (spcoptions && spcoptions[0] != '\0') 1270 appendPQExpBuffer(buf, "ALTER TABLESPACE %s SET (%s);\n", 1271 fspcname, spcoptions); 1272 1273 if (!skip_acls && 1274 !buildACLCommands(fspcname, NULL, NULL, "TABLESPACE", 1275 spcacl, rspcacl, 1276 spcowner, "", server_version, buf)) 1277 { 1278 pg_log_error("could not parse ACL list (%s) for tablespace \"%s\"", 1279 spcacl, spcname); 1280 PQfinish(conn); 1281 exit_nicely(1); 1282 } 1283 1284 if (!no_comments && spccomment && spccomment[0] != '\0') 1285 { 1286 appendPQExpBuffer(buf, "COMMENT ON TABLESPACE %s IS ", fspcname); 1287 appendStringLiteralConn(buf, spccomment, conn); 1288 appendPQExpBufferStr(buf, ";\n"); 1289 } 1290 1291 if (!no_security_labels && server_version >= 90200) 1292 buildShSecLabels(conn, "pg_tablespace", spcoid, 1293 "TABLESPACE", spcname, 1294 buf); 1295 1296 fprintf(OPF, "%s", buf->data); 1297 1298 free(fspcname); 1299 destroyPQExpBuffer(buf); 1300 } 1301 1302 PQclear(res); 1303 fprintf(OPF, "\n\n"); 1304 } 1305 1306 1307 /* 1308 * Dump commands to drop each database. 1309 */ 1310 static void 1311 dropDBs(PGconn *conn) 1312 { 1313 PGresult *res; 1314 int i; 1315 1316 /* 1317 * Skip databases marked not datallowconn, since we'd be unable to connect 1318 * to them anyway. This must agree with dumpDatabases(). 1319 */ 1320 res = executeQuery(conn, 1321 "SELECT datname " 1322 "FROM pg_database d " 1323 "WHERE datallowconn " 1324 "ORDER BY datname"); 1325 1326 if (PQntuples(res) > 0) 1327 fprintf(OPF, "--\n-- Drop databases (except postgres and template1)\n--\n\n"); 1328 1329 for (i = 0; i < PQntuples(res); i++) 1330 { 1331 char *dbname = PQgetvalue(res, i, 0); 1332 1333 /* 1334 * Skip "postgres" and "template1"; dumpDatabases() will deal with 1335 * them specially. Also, be sure to skip "template0", even if for 1336 * some reason it's not marked !datallowconn. 1337 */ 1338 if (strcmp(dbname, "template1") != 0 && 1339 strcmp(dbname, "template0") != 0 && 1340 strcmp(dbname, "postgres") != 0) 1341 { 1342 fprintf(OPF, "DROP DATABASE %s%s;\n", 1343 if_exists ? "IF EXISTS " : "", 1344 fmtId(dbname)); 1345 } 1346 } 1347 1348 PQclear(res); 1349 1350 fprintf(OPF, "\n\n"); 1351 } 1352 1353 1354 /* 1355 * Dump user-specific configuration 1356 */ 1357 static void 1358 dumpUserConfig(PGconn *conn, const char *username) 1359 { 1360 PQExpBuffer buf = createPQExpBuffer(); 1361 int count = 1; 1362 bool first = true; 1363 1364 for (;;) 1365 { 1366 PGresult *res; 1367 1368 if (server_version >= 90000) 1369 printfPQExpBuffer(buf, "SELECT setconfig[%d] FROM pg_db_role_setting WHERE " 1370 "setdatabase = 0 AND setrole = " 1371 "(SELECT oid FROM %s WHERE rolname = ", count, role_catalog); 1372 else if (server_version >= 80100) 1373 printfPQExpBuffer(buf, "SELECT rolconfig[%d] FROM %s WHERE rolname = ", count, role_catalog); 1374 else 1375 printfPQExpBuffer(buf, "SELECT useconfig[%d] FROM pg_shadow WHERE usename = ", count); 1376 appendStringLiteralConn(buf, username, conn); 1377 if (server_version >= 90000) 1378 appendPQExpBufferChar(buf, ')'); 1379 1380 res = executeQuery(conn, buf->data); 1381 if (PQntuples(res) == 1 && 1382 !PQgetisnull(res, 0, 0)) 1383 { 1384 /* comment at section start, only if needed */ 1385 if (first) 1386 { 1387 fprintf(OPF, "--\n-- User Configurations\n--\n\n"); 1388 first = false; 1389 } 1390 1391 fprintf(OPF, "--\n-- User Config \"%s\"\n--\n\n", username); 1392 resetPQExpBuffer(buf); 1393 makeAlterConfigCommand(conn, PQgetvalue(res, 0, 0), 1394 "ROLE", username, NULL, NULL, 1395 buf); 1396 fprintf(OPF, "%s", buf->data); 1397 PQclear(res); 1398 count++; 1399 } 1400 else 1401 { 1402 PQclear(res); 1403 break; 1404 } 1405 } 1406 1407 destroyPQExpBuffer(buf); 1408 } 1409 1410 /* 1411 * Find a list of database names that match the given patterns. 1412 * See also expand_table_name_patterns() in pg_dump.c 1413 */ 1414 static void 1415 expand_dbname_patterns(PGconn *conn, 1416 SimpleStringList *patterns, 1417 SimpleStringList *names) 1418 { 1419 PQExpBuffer query; 1420 PGresult *res; 1421 1422 if (patterns->head == NULL) 1423 return; /* nothing to do */ 1424 1425 query = createPQExpBuffer(); 1426 1427 /* 1428 * The loop below runs multiple SELECTs, which might sometimes result in 1429 * duplicate entries in the name list, but we don't care, since all we're 1430 * going to do is test membership of the list. 1431 */ 1432 1433 for (SimpleStringListCell *cell = patterns->head; cell; cell = cell->next) 1434 { 1435 appendPQExpBuffer(query, 1436 "SELECT datname FROM pg_catalog.pg_database n\n"); 1437 processSQLNamePattern(conn, query, cell->val, false, 1438 false, NULL, "datname", NULL, NULL); 1439 1440 res = executeQuery(conn, query->data); 1441 for (int i = 0; i < PQntuples(res); i++) 1442 { 1443 simple_string_list_append(names, PQgetvalue(res, i, 0)); 1444 } 1445 1446 PQclear(res); 1447 resetPQExpBuffer(query); 1448 } 1449 1450 destroyPQExpBuffer(query); 1451 } 1452 1453 /* 1454 * Dump contents of databases. 1455 */ 1456 static void 1457 dumpDatabases(PGconn *conn) 1458 { 1459 PGresult *res; 1460 int i; 1461 1462 /* 1463 * Skip databases marked not datallowconn, since we'd be unable to connect 1464 * to them anyway. This must agree with dropDBs(). 1465 * 1466 * We arrange for template1 to be processed first, then we process other 1467 * DBs in alphabetical order. If we just did them all alphabetically, we 1468 * might find ourselves trying to drop the "postgres" database while still 1469 * connected to it. This makes trying to run the restore script while 1470 * connected to "template1" a bad idea, but there's no fixed order that 1471 * doesn't have some failure mode with --clean. 1472 */ 1473 res = executeQuery(conn, 1474 "SELECT datname " 1475 "FROM pg_database d " 1476 "WHERE datallowconn " 1477 "ORDER BY (datname <> 'template1'), datname"); 1478 1479 if (PQntuples(res) > 0) 1480 fprintf(OPF, "--\n-- Databases\n--\n\n"); 1481 1482 for (i = 0; i < PQntuples(res); i++) 1483 { 1484 char *dbname = PQgetvalue(res, i, 0); 1485 const char *create_opts; 1486 int ret; 1487 1488 /* Skip template0, even if it's not marked !datallowconn. */ 1489 if (strcmp(dbname, "template0") == 0) 1490 continue; 1491 1492 /* Skip any explicitly excluded database */ 1493 if (simple_string_list_member(&database_exclude_names, dbname)) 1494 { 1495 pg_log_info("excluding database \"%s\"", dbname); 1496 continue; 1497 } 1498 1499 pg_log_info("dumping database \"%s\"", dbname); 1500 1501 fprintf(OPF, "--\n-- Database \"%s\" dump\n--\n\n", dbname); 1502 1503 /* 1504 * We assume that "template1" and "postgres" already exist in the 1505 * target installation. dropDBs() won't have removed them, for fear 1506 * of removing the DB the restore script is initially connected to. If 1507 * --clean was specified, tell pg_dump to drop and recreate them; 1508 * otherwise we'll merely restore their contents. Other databases 1509 * should simply be created. 1510 */ 1511 if (strcmp(dbname, "template1") == 0 || strcmp(dbname, "postgres") == 0) 1512 { 1513 if (output_clean) 1514 create_opts = "--clean --create"; 1515 else 1516 { 1517 create_opts = ""; 1518 /* Since pg_dump won't emit a \connect command, we must */ 1519 fprintf(OPF, "\\connect %s\n\n", dbname); 1520 } 1521 } 1522 else 1523 create_opts = "--create"; 1524 1525 if (filename) 1526 fclose(OPF); 1527 1528 ret = runPgDump(dbname, create_opts); 1529 if (ret != 0) 1530 { 1531 pg_log_error("pg_dump failed on database \"%s\", exiting", dbname); 1532 exit_nicely(1); 1533 } 1534 1535 if (filename) 1536 { 1537 OPF = fopen(filename, PG_BINARY_A); 1538 if (!OPF) 1539 { 1540 pg_log_error("could not re-open the output file \"%s\": %m", 1541 filename); 1542 exit_nicely(1); 1543 } 1544 } 1545 1546 } 1547 1548 PQclear(res); 1549 } 1550 1551 1552 1553 /* 1554 * Run pg_dump on dbname, with specified options. 1555 */ 1556 static int 1557 runPgDump(const char *dbname, const char *create_opts) 1558 { 1559 PQExpBuffer connstrbuf = createPQExpBuffer(); 1560 PQExpBuffer cmd = createPQExpBuffer(); 1561 int ret; 1562 1563 appendPQExpBuffer(cmd, "\"%s\" %s %s", pg_dump_bin, 1564 pgdumpopts->data, create_opts); 1565 1566 /* 1567 * If we have a filename, use the undocumented plain-append pg_dump 1568 * format. 1569 */ 1570 if (filename) 1571 appendPQExpBufferStr(cmd, " -Fa "); 1572 else 1573 appendPQExpBufferStr(cmd, " -Fp "); 1574 1575 /* 1576 * Append the database name to the already-constructed stem of connection 1577 * string. 1578 */ 1579 appendPQExpBuffer(connstrbuf, "%s dbname=", connstr); 1580 appendConnStrVal(connstrbuf, dbname); 1581 1582 appendShellString(cmd, connstrbuf->data); 1583 1584 pg_log_info("running \"%s\"", cmd->data); 1585 1586 fflush(stdout); 1587 fflush(stderr); 1588 1589 ret = system(cmd->data); 1590 1591 destroyPQExpBuffer(cmd); 1592 destroyPQExpBuffer(connstrbuf); 1593 1594 return ret; 1595 } 1596 1597 /* 1598 * buildShSecLabels 1599 * 1600 * Build SECURITY LABEL command(s) for a shared object 1601 * 1602 * The caller has to provide object type and identity in two separate formats: 1603 * catalog_name (e.g., "pg_database") and object OID, as well as 1604 * type name (e.g., "DATABASE") and object name (not pre-quoted). 1605 * 1606 * The command(s) are appended to "buffer". 1607 */ 1608 static void 1609 buildShSecLabels(PGconn *conn, const char *catalog_name, Oid objectId, 1610 const char *objtype, const char *objname, 1611 PQExpBuffer buffer) 1612 { 1613 PQExpBuffer sql = createPQExpBuffer(); 1614 PGresult *res; 1615 1616 buildShSecLabelQuery(conn, catalog_name, objectId, sql); 1617 res = executeQuery(conn, sql->data); 1618 emitShSecLabels(conn, res, buffer, objtype, objname); 1619 1620 PQclear(res); 1621 destroyPQExpBuffer(sql); 1622 } 1623 1624 /* 1625 * Make a database connection with the given parameters. An 1626 * interactive password prompt is automatically issued if required. 1627 * 1628 * If fail_on_error is false, we return NULL without printing any message 1629 * on failure, but preserve any prompted password for the next try. 1630 * 1631 * On success, the global variable 'connstr' is set to a connection string 1632 * containing the options used. 1633 */ 1634 static PGconn * 1635 connectDatabase(const char *dbname, const char *connection_string, 1636 const char *pghost, const char *pgport, const char *pguser, 1637 trivalue prompt_password, bool fail_on_error) 1638 { 1639 PGconn *conn; 1640 bool new_pass; 1641 const char *remoteversion_str; 1642 int my_version; 1643 const char **keywords = NULL; 1644 const char **values = NULL; 1645 PQconninfoOption *conn_opts = NULL; 1646 static bool have_password = false; 1647 static char password[100]; 1648 1649 if (prompt_password == TRI_YES && !have_password) 1650 { 1651 simple_prompt("Password: ", password, sizeof(password), false); 1652 have_password = true; 1653 } 1654 1655 /* 1656 * Start the connection. Loop until we have a password if requested by 1657 * backend. 1658 */ 1659 do 1660 { 1661 int argcount = 6; 1662 PQconninfoOption *conn_opt; 1663 char *err_msg = NULL; 1664 int i = 0; 1665 1666 if (keywords) 1667 free(keywords); 1668 if (values) 1669 free(values); 1670 if (conn_opts) 1671 PQconninfoFree(conn_opts); 1672 1673 /* 1674 * Merge the connection info inputs given in form of connection string 1675 * and other options. Explicitly discard any dbname value in the 1676 * connection string; otherwise, PQconnectdbParams() would interpret 1677 * that value as being itself a connection string. 1678 */ 1679 if (connection_string) 1680 { 1681 conn_opts = PQconninfoParse(connection_string, &err_msg); 1682 if (conn_opts == NULL) 1683 { 1684 pg_log_error("%s", err_msg); 1685 exit_nicely(1); 1686 } 1687 1688 for (conn_opt = conn_opts; conn_opt->keyword != NULL; conn_opt++) 1689 { 1690 if (conn_opt->val != NULL && conn_opt->val[0] != '\0' && 1691 strcmp(conn_opt->keyword, "dbname") != 0) 1692 argcount++; 1693 } 1694 1695 keywords = pg_malloc0((argcount + 1) * sizeof(*keywords)); 1696 values = pg_malloc0((argcount + 1) * sizeof(*values)); 1697 1698 for (conn_opt = conn_opts; conn_opt->keyword != NULL; conn_opt++) 1699 { 1700 if (conn_opt->val != NULL && conn_opt->val[0] != '\0' && 1701 strcmp(conn_opt->keyword, "dbname") != 0) 1702 { 1703 keywords[i] = conn_opt->keyword; 1704 values[i] = conn_opt->val; 1705 i++; 1706 } 1707 } 1708 } 1709 else 1710 { 1711 keywords = pg_malloc0((argcount + 1) * sizeof(*keywords)); 1712 values = pg_malloc0((argcount + 1) * sizeof(*values)); 1713 } 1714 1715 if (pghost) 1716 { 1717 keywords[i] = "host"; 1718 values[i] = pghost; 1719 i++; 1720 } 1721 if (pgport) 1722 { 1723 keywords[i] = "port"; 1724 values[i] = pgport; 1725 i++; 1726 } 1727 if (pguser) 1728 { 1729 keywords[i] = "user"; 1730 values[i] = pguser; 1731 i++; 1732 } 1733 if (have_password) 1734 { 1735 keywords[i] = "password"; 1736 values[i] = password; 1737 i++; 1738 } 1739 if (dbname) 1740 { 1741 keywords[i] = "dbname"; 1742 values[i] = dbname; 1743 i++; 1744 } 1745 keywords[i] = "fallback_application_name"; 1746 values[i] = progname; 1747 i++; 1748 1749 new_pass = false; 1750 conn = PQconnectdbParams(keywords, values, true); 1751 1752 if (!conn) 1753 { 1754 pg_log_error("could not connect to database \"%s\"", dbname); 1755 exit_nicely(1); 1756 } 1757 1758 if (PQstatus(conn) == CONNECTION_BAD && 1759 PQconnectionNeedsPassword(conn) && 1760 !have_password && 1761 prompt_password != TRI_NO) 1762 { 1763 PQfinish(conn); 1764 simple_prompt("Password: ", password, sizeof(password), false); 1765 have_password = true; 1766 new_pass = true; 1767 } 1768 } while (new_pass); 1769 1770 /* check to see that the backend connection was successfully made */ 1771 if (PQstatus(conn) == CONNECTION_BAD) 1772 { 1773 if (fail_on_error) 1774 { 1775 pg_log_error("could not connect to database \"%s\": %s", 1776 PQdb(conn) ? PQdb(conn) : "", PQerrorMessage(conn)); 1777 exit_nicely(1); 1778 } 1779 else 1780 { 1781 PQfinish(conn); 1782 1783 free(keywords); 1784 free(values); 1785 PQconninfoFree(conn_opts); 1786 1787 return NULL; 1788 } 1789 } 1790 1791 /* 1792 * Ok, connected successfully. Remember the options used, in the form of a 1793 * connection string. 1794 */ 1795 connstr = constructConnStr(keywords, values); 1796 1797 free(keywords); 1798 free(values); 1799 PQconninfoFree(conn_opts); 1800 1801 /* Check version */ 1802 remoteversion_str = PQparameterStatus(conn, "server_version"); 1803 if (!remoteversion_str) 1804 { 1805 pg_log_error("could not get server version"); 1806 exit_nicely(1); 1807 } 1808 server_version = PQserverVersion(conn); 1809 if (server_version == 0) 1810 { 1811 pg_log_error("could not parse server version \"%s\"", 1812 remoteversion_str); 1813 exit_nicely(1); 1814 } 1815 1816 my_version = PG_VERSION_NUM; 1817 1818 /* 1819 * We allow the server to be back to 8.0, and up to any minor release of 1820 * our own major version. (See also version check in pg_dump.c.) 1821 */ 1822 if (my_version != server_version 1823 && (server_version < 80000 || 1824 (server_version / 100) > (my_version / 100))) 1825 { 1826 pg_log_error("server version: %s; %s version: %s", 1827 remoteversion_str, progname, PG_VERSION); 1828 pg_log_error("aborting because of server version mismatch"); 1829 exit_nicely(1); 1830 } 1831 1832 PQclear(executeQuery(conn, ALWAYS_SECURE_SEARCH_PATH_SQL)); 1833 1834 return conn; 1835 } 1836 1837 /* ---------- 1838 * Construct a connection string from the given keyword/value pairs. It is 1839 * used to pass the connection options to the pg_dump subprocess. 1840 * 1841 * The following parameters are excluded: 1842 * dbname - varies in each pg_dump invocation 1843 * password - it's not secure to pass a password on the command line 1844 * fallback_application_name - we'll let pg_dump set it 1845 * ---------- 1846 */ 1847 static char * 1848 constructConnStr(const char **keywords, const char **values) 1849 { 1850 PQExpBuffer buf = createPQExpBuffer(); 1851 char *connstr; 1852 int i; 1853 bool firstkeyword = true; 1854 1855 /* Construct a new connection string in key='value' format. */ 1856 for (i = 0; keywords[i] != NULL; i++) 1857 { 1858 if (strcmp(keywords[i], "dbname") == 0 || 1859 strcmp(keywords[i], "password") == 0 || 1860 strcmp(keywords[i], "fallback_application_name") == 0) 1861 continue; 1862 1863 if (!firstkeyword) 1864 appendPQExpBufferChar(buf, ' '); 1865 firstkeyword = false; 1866 appendPQExpBuffer(buf, "%s=", keywords[i]); 1867 appendConnStrVal(buf, values[i]); 1868 } 1869 1870 connstr = pg_strdup(buf->data); 1871 destroyPQExpBuffer(buf); 1872 return connstr; 1873 } 1874 1875 /* 1876 * Run a query, return the results, exit program on failure. 1877 */ 1878 static PGresult * 1879 executeQuery(PGconn *conn, const char *query) 1880 { 1881 PGresult *res; 1882 1883 pg_log_info("executing %s", query); 1884 1885 res = PQexec(conn, query); 1886 if (!res || 1887 PQresultStatus(res) != PGRES_TUPLES_OK) 1888 { 1889 pg_log_error("query failed: %s", PQerrorMessage(conn)); 1890 pg_log_error("query was: %s", query); 1891 PQfinish(conn); 1892 exit_nicely(1); 1893 } 1894 1895 return res; 1896 } 1897 1898 /* 1899 * As above for a SQL command (which returns nothing). 1900 */ 1901 static void 1902 executeCommand(PGconn *conn, const char *query) 1903 { 1904 PGresult *res; 1905 1906 pg_log_info("executing %s", query); 1907 1908 res = PQexec(conn, query); 1909 if (!res || 1910 PQresultStatus(res) != PGRES_COMMAND_OK) 1911 { 1912 pg_log_error("query failed: %s", PQerrorMessage(conn)); 1913 pg_log_error("query was: %s", query); 1914 PQfinish(conn); 1915 exit_nicely(1); 1916 } 1917 1918 PQclear(res); 1919 } 1920 1921 1922 /* 1923 * dumpTimestamp 1924 */ 1925 static void 1926 dumpTimestamp(const char *msg) 1927 { 1928 char buf[64]; 1929 time_t now = time(NULL); 1930 1931 if (strftime(buf, sizeof(buf), PGDUMP_STRFTIME_FMT, localtime(&now)) != 0) 1932 fprintf(OPF, "-- %s %s\n\n", msg, buf); 1933 } 1934