/* * Dump user-specific configuration */ static void dumpUserConfig(PGconn *conn, const char *username) { PQExpBuffer buf = createPQExpBuffer(); int count = 1; for (;;) { PGresult *res; if (server_version >= 80100) printfPQExpBuffer(buf, "SELECT rolconfig[%d] FROM pg_authid WHERE rolname = ", count); else printfPQExpBuffer(buf, "SELECT useconfig[%d] FROM pg_shadow WHERE usename = ", count); appendStringLiteral(buf, username, true); res = executeQuery(conn, buf->data); if (PQntuples(res) == 1 && !PQgetisnull(res, 0, 0)) { makeAlterConfigCommand(PQgetvalue(res, 0, 0), "ROLE", username); PQclear(res); count++; } else { PQclear(res); break; } } destroyPQExpBuffer(buf); }
/* * Helper function for dumpXXXConfig(). */ static void makeAlterConfigCommand(const char *arrayitem, const char *type, const char *name) { char *pos; char *mine; PQExpBuffer buf = createPQExpBuffer(); mine = strdup(arrayitem); pos = strchr(mine, '='); if (pos == NULL) return; *pos = 0; appendPQExpBuffer(buf, "ALTER %s %s ", type, fmtId(name)); appendPQExpBuffer(buf, "SET %s TO ", fmtId(mine)); /* * Some GUC variable names are 'LIST' type and hence must not be quoted. */ if (pg_strcasecmp(mine, "DateStyle") == 0 || pg_strcasecmp(mine, "search_path") == 0) appendPQExpBuffer(buf, "%s", pos + 1); else appendStringLiteral(buf, pos + 1, false); appendPQExpBuffer(buf, ";\n"); printf("%s", buf->data); destroyPQExpBuffer(buf); free(mine); }
/* * Convert a string value to an SQL string literal and append it to * the given buffer. Encoding and string syntax rules are as indicated * by current settings of the PGconn. */ void appendStringLiteralConn(PQExpBuffer buf, const char *str, PGconn *conn) { size_t length = strlen(str); /* * XXX This is a kluge to silence escape_string_warning in our utility * programs. It should go away someday. */ if (strchr(str, '\\') != NULL && PQserverVersion(conn) >= 80100) { /* ensure we are not adjacent to an identifier */ if (buf->len > 0 && buf->data[buf->len - 1] != ' ') appendPQExpBufferChar(buf, ' '); appendPQExpBufferChar(buf, ESCAPE_STRING_SYNTAX); appendStringLiteral(buf, str, PQclientEncoding(conn), false); return; } /* XXX end kluge */ if (!enlargePQExpBuffer(buf, 2 * length + 2)) return; appendPQExpBufferChar(buf, '\''); buf->len += PQescapeStringConn(conn, buf->data + buf->len, str, length, NULL); appendPQExpBufferChar(buf, '\''); }
/* * Dump database-specific configuration */ static void dumpDatabaseConfig(PGconn *conn, const char *dbname) { PQExpBuffer buf = createPQExpBuffer(); int count = 1; for (;;) { PGresult *res; printfPQExpBuffer(buf, "SELECT datconfig[%d] FROM pg_database WHERE datname = ", count); appendStringLiteral(buf, dbname, true); appendPQExpBuffer(buf, ";"); res = executeQuery(conn, buf->data); if (!PQgetisnull(res, 0, 0)) { makeAlterConfigCommand(PQgetvalue(res, 0, 0), "DATABASE", dbname); PQclear(res); count++; } else { PQclear(res); break; } } destroyPQExpBuffer(buf); }
/* * Format a reloptions array and append it to the given buffer. * * "prefix" is prepended to the option names; typically it's "" or "toast.". * * Returns false if the reloptions array could not be parsed (in which case * nothing will have been appended to the buffer), or true on success. * * Note: this logic should generally match the backend's flatten_reloptions() * (in adt/ruleutils.c). */ bool appendReloptionsArray(PQExpBuffer buffer, const char *reloptions, const char *prefix, int encoding, bool std_strings) { char **options; int noptions; int i; if (!parsePGArray(reloptions, &options, &noptions)) { if (options) free(options); return false; } for (i = 0; i < noptions; i++) { char *option = options[i]; char *name; char *separator; char *value; /* * Each array element should have the form name=value. If the "=" is * missing for some reason, treat it like an empty value. */ name = option; separator = strchr(option, '='); if (separator) { *separator = '\0'; value = separator + 1; } else value = ""; if (i > 0) appendPQExpBufferStr(buffer, ", "); appendPQExpBuffer(buffer, "%s%s=", prefix, fmtId(name)); /* * In general we need to quote the value; but to avoid unnecessary * clutter, do not quote if it is an identifier that would not need * quoting. (We could also allow numbers, but that is a bit trickier * than it looks --- for example, are leading zeroes significant? We * don't want to assume very much here about what custom reloptions * might mean.) */ if (strcmp(fmtId(value), value) == 0) appendPQExpBufferStr(buffer, value); else appendStringLiteral(buffer, value, encoding, std_strings); } if (options) free(options); return true; }
/* * Dump commands to create each database. * * To minimize the number of reconnections (and possibly ensuing * password prompts) required by the output script, we emit all CREATE * DATABASE commands during the initial phase of the script, and then * run pg_dump for each database to dump the contents of that * database. We skip databases marked not datallowconn, since we'd be * unable to connect to them anyway (and besides, we don't want to * dump template0). */ static void dumpCreateDB(PGconn *conn) { PQExpBuffer buf = createPQExpBuffer(); PGresult *res; int i; printf("--\n-- Database creation\n--\n\n"); if (server_version >= 80100) res = executeQuery(conn, "SELECT datname, " "coalesce(rolname, (select rolname from pg_authid where oid=(select datdba from pg_database where datname='template0'))), " "pg_encoding_to_char(d.encoding), " "datistemplate, datacl, datconnlimit, " "(SELECT spcname FROM pg_tablespace t WHERE t.oid = d.dattablespace) AS dattablespace " "FROM pg_database d LEFT JOIN pg_authid u ON (datdba = u.oid) " "WHERE datallowconn ORDER BY 1"); else if (server_version >= 80000) res = executeQuery(conn, "SELECT datname, " "coalesce(usename, (select usename from pg_shadow where usesysid=(select datdba from pg_database where datname='template0'))), " "pg_encoding_to_char(d.encoding), " "datistemplate, datacl, -1 as datconnlimit, " "(SELECT spcname FROM pg_tablespace t WHERE t.oid = d.dattablespace) AS dattablespace " "FROM pg_database d LEFT JOIN pg_shadow u ON (datdba = usesysid) " "WHERE datallowconn ORDER BY 1"); else if (server_version >= 70300) res = executeQuery(conn, "SELECT datname, " "coalesce(usename, (select usename from pg_shadow where usesysid=(select datdba from pg_database where datname='template0'))), " "pg_encoding_to_char(d.encoding), " "datistemplate, datacl, -1 as datconnlimit, " "'pg_default' AS dattablespace " "FROM pg_database d LEFT JOIN pg_shadow u ON (datdba = usesysid) " "WHERE datallowconn ORDER BY 1"); else if (server_version >= 70100) res = executeQuery(conn, "SELECT datname, " "coalesce(" "(select usename from pg_shadow where usesysid=datdba), " "(select usename from pg_shadow where usesysid=(select datdba from pg_database where datname='template0'))), " "pg_encoding_to_char(d.encoding), " "datistemplate, '' as datacl, -1 as datconnlimit, " "'pg_default' AS dattablespace " "FROM pg_database d " "WHERE datallowconn ORDER BY 1"); else { /* * Note: 7.0 fails to cope with sub-select in COALESCE, so just deal * with getting a NULL by not printing any OWNER clause. */ res = executeQuery(conn, "SELECT datname, " "(select usename from pg_shadow where usesysid=datdba), " "pg_encoding_to_char(d.encoding), " "'f' as datistemplate, " "'' as datacl, -1 as datconnlimit, " "'pg_default' AS dattablespace " "FROM pg_database d " "ORDER BY 1"); } for (i = 0; i < PQntuples(res); i++) { char *dbname = PQgetvalue(res, i, 0); char *dbowner = PQgetvalue(res, i, 1); char *dbencoding = PQgetvalue(res, i, 2); char *dbistemplate = PQgetvalue(res, i, 3); char *dbacl = PQgetvalue(res, i, 4); char *dbconnlimit = PQgetvalue(res, i, 5); char *dbtablespace = PQgetvalue(res, i, 6); char *fdbname; fdbname = strdup(fmtId(dbname)); resetPQExpBuffer(buf); /* * Skip the CREATE DATABASE commands for "template1" and "postgres", * since they are presumably already there in the destination cluster. * We do want to emit their ACLs and config options if any, however. */ if (strcmp(dbname, "template1") != 0 && strcmp(dbname, "postgres") != 0) { if (output_clean) appendPQExpBuffer(buf, "DROP DATABASE %s;\n", fdbname); appendPQExpBuffer(buf, "CREATE DATABASE %s", fdbname); appendPQExpBuffer(buf, " WITH TEMPLATE = template0"); if (strlen(dbowner) != 0) appendPQExpBuffer(buf, " OWNER = %s", fmtId(dbowner)); appendPQExpBuffer(buf, " ENCODING = "); appendStringLiteral(buf, dbencoding, true); /* Output tablespace if it isn't default */ if (strcmp(dbtablespace, "pg_default") != 0) appendPQExpBuffer(buf, " TABLESPACE = %s", fmtId(dbtablespace)); if (strcmp(dbconnlimit, "-1") != 0) appendPQExpBuffer(buf, " CONNECTION LIMIT = %s", dbconnlimit); appendPQExpBuffer(buf, ";\n"); if (strcmp(dbistemplate, "t") == 0) { appendPQExpBuffer(buf, "UPDATE pg_database SET datistemplate = 't' WHERE datname = "); appendStringLiteral(buf, dbname, true); appendPQExpBuffer(buf, ";\n"); } } if (!skip_acls && !buildACLCommands(fdbname, "DATABASE", dbacl, dbowner, server_version, buf)) { fprintf(stderr, _("%s: could not parse ACL list (%s) for database \"%s\"\n"), progname, dbacl, fdbname); PQfinish(conn); exit(1); } printf("%s", buf->data); if (server_version >= 70300) dumpDatabaseConfig(conn, dbname); free(fdbname); } PQclear(res); destroyPQExpBuffer(buf); printf("\n\n"); }
/* * Dump tablespaces. */ static void dumpTablespaces(PGconn *conn) { PGresult *res; int i; /* * Get all tablespaces except built-in ones (which we assume are named * pg_xxx) */ res = executeQuery(conn, "SELECT spcname, " "pg_catalog.pg_get_userbyid(spcowner) AS spcowner, " "spclocation, spcacl " "FROM pg_catalog.pg_tablespace " "WHERE spcname !~ '^pg_' " "ORDER BY 1"); if (PQntuples(res) > 0) printf("--\n-- Tablespaces\n--\n\n"); for (i = 0; i < PQntuples(res); i++) { PQExpBuffer buf = createPQExpBuffer(); char *spcname = PQgetvalue(res, i, 0); char *spcowner = PQgetvalue(res, i, 1); char *spclocation = PQgetvalue(res, i, 2); char *spcacl = PQgetvalue(res, i, 3); char *fspcname; /* needed for buildACLCommands() */ fspcname = strdup(fmtId(spcname)); if (output_clean) appendPQExpBuffer(buf, "DROP TABLESPACE %s;\n", fspcname); appendPQExpBuffer(buf, "CREATE TABLESPACE %s", fspcname); appendPQExpBuffer(buf, " OWNER %s", fmtId(spcowner)); appendPQExpBuffer(buf, " LOCATION "); appendStringLiteral(buf, spclocation, true); appendPQExpBuffer(buf, ";\n"); if (!skip_acls && !buildACLCommands(fspcname, "TABLESPACE", spcacl, spcowner, server_version, buf)) { fprintf(stderr, _("%s: could not parse ACL list (%s) for tablespace \"%s\"\n"), progname, spcacl, fspcname); PQfinish(conn); exit(1); } printf("%s", buf->data); free(fspcname); destroyPQExpBuffer(buf); } PQclear(res); printf("\n\n"); }
/* * Dump roles */ static void dumpRoles(PGconn *conn) { PQExpBuffer buf = createPQExpBuffer(); PGresult *res; int i_rolname, i_rolsuper, i_rolinherit, i_rolcreaterole, i_rolcreatedb, i_rolcatupdate, i_rolcanlogin, i_rolconnlimit, i_rolpassword, i_rolvaliduntil; int i; /* note: rolconfig is dumped later */ if (server_version >= 80100) printfPQExpBuffer(buf, "SELECT rolname, rolsuper, rolinherit, " "rolcreaterole, rolcreatedb, rolcatupdate, " "rolcanlogin, rolconnlimit, rolpassword, " "rolvaliduntil " "FROM pg_authid " "ORDER BY 1"); else printfPQExpBuffer(buf, "SELECT usename as rolname, " "usesuper as rolsuper, " "true as rolinherit, " "usesuper as rolcreaterole, " "usecreatedb as rolcreatedb, " "usecatupd as rolcatupdate, " "true as rolcanlogin, " "-1 as rolconnlimit, " "passwd as rolpassword, " "valuntil as rolvaliduntil " "FROM pg_shadow " "UNION ALL " "SELECT groname as rolname, " "false as rolsuper, " "true as rolinherit, " "false as rolcreaterole, " "false as rolcreatedb, " "false as rolcatupdate, " "false as rolcanlogin, " "-1 as rolconnlimit, " "null::text as rolpassword, " "null::abstime as rolvaliduntil " "FROM pg_group " "WHERE NOT EXISTS (SELECT 1 FROM pg_shadow " " WHERE usename = groname) " "ORDER BY 1"); res = executeQuery(conn, buf->data); i_rolname = PQfnumber(res, "rolname"); i_rolsuper = PQfnumber(res, "rolsuper"); i_rolinherit = PQfnumber(res, "rolinherit"); i_rolcreaterole = PQfnumber(res, "rolcreaterole"); i_rolcreatedb = PQfnumber(res, "rolcreatedb"); i_rolcatupdate = PQfnumber(res, "rolcatupdate"); i_rolcanlogin = PQfnumber(res, "rolcanlogin"); i_rolconnlimit = PQfnumber(res, "rolconnlimit"); i_rolpassword = PQfnumber(res, "rolpassword"); i_rolvaliduntil = PQfnumber(res, "rolvaliduntil"); if (PQntuples(res) > 0) printf("--\n-- Roles\n--\n\n"); for (i = 0; i < PQntuples(res); i++) { const char *rolename; rolename = PQgetvalue(res, i, i_rolname); resetPQExpBuffer(buf); if (output_clean) appendPQExpBuffer(buf, "DROP ROLE %s;\n", fmtId(rolename)); /* * We dump CREATE ROLE followed by ALTER ROLE to ensure that the role * will acquire the right properties even if it already exists. (The * above DROP may therefore seem redundant, but it isn't really, * because this technique doesn't get rid of role memberships.) */ appendPQExpBuffer(buf, "CREATE ROLE %s;\n", fmtId(rolename)); appendPQExpBuffer(buf, "ALTER ROLE %s WITH", fmtId(rolename)); if (strcmp(PQgetvalue(res, i, i_rolsuper), "t") == 0) appendPQExpBuffer(buf, " SUPERUSER"); else appendPQExpBuffer(buf, " NOSUPERUSER"); if (strcmp(PQgetvalue(res, i, i_rolinherit), "t") == 0) appendPQExpBuffer(buf, " INHERIT"); else appendPQExpBuffer(buf, " NOINHERIT"); if (strcmp(PQgetvalue(res, i, i_rolcreaterole), "t") == 0) appendPQExpBuffer(buf, " CREATEROLE"); else appendPQExpBuffer(buf, " NOCREATEROLE"); if (strcmp(PQgetvalue(res, i, i_rolcreatedb), "t") == 0) appendPQExpBuffer(buf, " CREATEDB"); else appendPQExpBuffer(buf, " NOCREATEDB"); if (strcmp(PQgetvalue(res, i, i_rolcanlogin), "t") == 0) appendPQExpBuffer(buf, " LOGIN"); else appendPQExpBuffer(buf, " NOLOGIN"); if (strcmp(PQgetvalue(res, i, i_rolconnlimit), "-1") != 0) appendPQExpBuffer(buf, " CONNECTION LIMIT %s", PQgetvalue(res, i, i_rolconnlimit)); if (!PQgetisnull(res, i, i_rolpassword)) { appendPQExpBuffer(buf, " PASSWORD "); appendStringLiteral(buf, PQgetvalue(res, i, i_rolpassword), true); } if (!PQgetisnull(res, i, i_rolvaliduntil)) appendPQExpBuffer(buf, " VALID UNTIL '%s'", PQgetvalue(res, i, i_rolvaliduntil)); appendPQExpBuffer(buf, ";\n"); printf("%s", buf->data); if (server_version >= 70300) dumpUserConfig(conn, rolename); } PQclear(res); printf("\n\n"); destroyPQExpBuffer(buf); }
int main(int argc, char *argv[]) { static struct option long_options[] = { {"host", required_argument, NULL, 'h'}, {"port", required_argument, NULL, 'p'}, {"username", required_argument, NULL, 'U'}, {"password", no_argument, NULL, 'W'}, {"echo", no_argument, NULL, 'e'}, {"quiet", no_argument, NULL, 'q'}, {"createdb", no_argument, NULL, 'd'}, {"no-createdb", no_argument, NULL, 'D'}, {"adduser", no_argument, NULL, 'a'}, {"no-adduser", no_argument, NULL, 'A'}, {"sysid", required_argument, NULL, 'i'}, {"pwprompt", no_argument, NULL, 'P'}, {"encrypted", no_argument, NULL, 'E'}, {"unencrypted", no_argument, NULL, 'N'}, {NULL, 0, NULL, 0} }; const char *progname; int optindex; int c; char *newuser = NULL; char *host = NULL; char *port = NULL; char *username = NULL; bool password = false; bool echo = false; bool quiet = false; int createdb = 0; int adduser = 0; char *sysid = NULL; bool pwprompt = false; int encrypted = 0; /* 0 uses server default */ char *newpassword = NULL; PQExpBufferData sql; PGconn *conn; PGresult *result; progname = get_progname(argv[0]); set_pglocale_pgservice(argv[0], "pgscripts"); handle_help_version_opts(argc, argv, "createuser", help); while ((c = getopt_long(argc, argv, "h:p:U:WeqaAdDi:PEN", long_options, &optindex)) != -1) { switch (c) { case 'h': host = optarg; break; case 'p': port = optarg; break; case 'U': username = optarg; break; case 'W': password = true; break; case 'e': echo = true; break; case 'q': quiet = true; break; case 'a': adduser = +1; break; case 'A': adduser = -1; break; case 'd': createdb = +1; break; case 'D': createdb = -1; break; case 'i': sysid = optarg; break; case 'P': pwprompt = true; break; case 'E': encrypted = +1; break; case 'N': encrypted = -1; break; default: fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname); exit(1); } } switch (argc - optind) { case 0: break; case 1: newuser = argv[optind]; break; default: fprintf(stderr, _("%s: too many command-line arguments (first is \"%s\")\n"), progname, argv[optind + 1]); fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname); exit(1); } if (sysid) { char *endptr; if (strtol(sysid, &endptr, 10) <= 0 || *endptr != '\0') { fprintf(stderr, _("%s: user ID must be a positive number\n"), progname); exit(1); } } if (newuser == NULL) newuser = simple_prompt("Enter name of user to add: ", 128, true); if (pwprompt) { char *pw1, *pw2; pw1 = simple_prompt("Enter password for new user: "******"Enter it again: ", 100, false); if (strcmp(pw1, pw2) != 0) { fprintf(stderr, _("Passwords didn't match.\n")); exit(1); } newpassword = pw1; free(pw2); } if (createdb == 0) { char *reply; reply = simple_prompt("Shall the new user be allowed to create databases? (y/n) ", 1, true); if (check_yesno_response(reply) == 1) createdb = +1; else createdb = -1; } if (adduser == 0) { char *reply; reply = simple_prompt("Shall the new user be allowed to create more new users? (y/n) ", 1, true); if (check_yesno_response(reply) == 1) adduser = +1; else adduser = -1; } initPQExpBuffer(&sql); printfPQExpBuffer(&sql, "CREATE USER %s", fmtId(newuser)); if (sysid) appendPQExpBuffer(&sql, " SYSID %s", sysid); if (newpassword) { if (encrypted == +1) appendPQExpBuffer(&sql, " ENCRYPTED"); if (encrypted == -1) appendPQExpBuffer(&sql, " UNENCRYPTED"); appendPQExpBuffer(&sql, " PASSWORD "); appendStringLiteral(&sql, newpassword, false); } if (createdb == +1) appendPQExpBuffer(&sql, " CREATEDB"); if (createdb == -1) appendPQExpBuffer(&sql, " NOCREATEDB"); if (adduser == +1) appendPQExpBuffer(&sql, " CREATEUSER"); if (adduser == -1) appendPQExpBuffer(&sql, " NOCREATEUSER"); appendPQExpBuffer(&sql, ";\n"); conn = connectDatabase("template1", host, port, username, password, progname); if (echo) printf("%s", sql.data); result = PQexec(conn, sql.data); if (PQresultStatus(result) != PGRES_COMMAND_OK) { fprintf(stderr, _("%s: creation of new user failed: %s"), progname, PQerrorMessage(conn)); PQfinish(conn); exit(1); } PQfinish(conn); if (!quiet) { puts("CREATE USER"); fflush(stdout); } exit(0); }