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