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