/* * 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); }
/* * Build ALTER DEFAULT PRIVILEGES command(s) for single pg_default_acl entry. * * type: the object type (TABLES, FUNCTIONS, etc) * nspname: schema name, or NULL for global default privileges * acls: the ACL string fetched from the database * owner: username of privileges owner (will be passed through fmtId) * remoteVersion: version of database * * Returns TRUE if okay, FALSE if could not parse the acl string. * The resulting commands (if any) are appended to the contents of 'sql'. */ bool buildDefaultACLCommands(const char *type, const char *nspname, const char *acls, const char *owner, int remoteVersion, PQExpBuffer sql) { bool result; PQExpBuffer prefix; prefix = createPQExpBuffer(); /* * We incorporate the target role directly into the command, rather than * playing around with SET ROLE or anything like that. This is so that a * permissions error leads to nothing happening, rather than changing * default privileges for the wrong user. */ appendPQExpBuffer(prefix, "ALTER DEFAULT PRIVILEGES FOR ROLE %s ", fmtId(owner)); if (nspname) appendPQExpBuffer(prefix, "IN SCHEMA %s ", fmtId(nspname)); result = buildACLCommands("", NULL, type, acls, owner, prefix->data, remoteVersion, sql); destroyPQExpBuffer(prefix); return result; }
/* * Dump group memberships from a pre-8.1 server. It's annoying that we * can't share any useful amount of code with the post-8.1 case, but * the catalog representations are too different. * * Note: we expect dumpRoles already created all the roles, but there is * no membership yet. */ static void dumpGroups(PGconn *conn) { PQExpBuffer buf = createPQExpBuffer(); PGresult *res; int i; res = executeQuery(conn, "SELECT groname, grolist FROM pg_group ORDER BY 1"); if (PQntuples(res) > 0) printf("--\n-- Role memberships\n--\n\n"); for (i = 0; i < PQntuples(res); i++) { char *groname = PQgetvalue(res, i, 0); char *grolist = PQgetvalue(res, i, 1); PGresult *res2; int j; /* * Array representation is {1,2,3} ... convert to (1,2,3) */ if (strlen(grolist) < 3) continue; grolist = strdup(grolist); grolist[0] = '('; grolist[strlen(grolist) - 1] = ')'; printfPQExpBuffer(buf, "SELECT usename FROM pg_shadow " "WHERE usesysid IN %s ORDER BY 1", grolist); free(grolist); res2 = executeQuery(conn, buf->data); for (j = 0; j < PQntuples(res2); j++) { char *usename = PQgetvalue(res2, j, 0); /* * Don't try to grant a role to itself; can happen if old * installation has identically named user and group. */ if (strcmp(groname, usename) == 0) continue; printf("GRANT %s", fmtId(groname)); printf(" TO %s;\n", fmtId(usename)); } PQclear(res2); } PQclear(res); destroyPQExpBuffer(buf); printf("\n\n"); }
/* * 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; }
/* * Append a psql meta-command that connects to the given database with the * then-current connection's user, host and port. */ void appendPsqlMetaConnect(PQExpBuffer buf, const char *dbname) { const char *s; bool complex; /* * If the name is plain ASCII characters, emit a trivial "\connect "foo"". * For other names, even many not technically requiring it, skip to the * general case. No database has a zero-length name. */ complex = false; for (s = dbname; *s; s++) { if (*s == '\n' || *s == '\r') { fprintf(stderr, _("database name contains a newline or carriage return: \"%s\"\n"), dbname); exit(EXIT_FAILURE); } if (!((*s >= 'a' && *s <= 'z') || (*s >= 'A' && *s <= 'Z') || (*s >= '0' && *s <= '9') || *s == '_' || *s == '.')) { complex = true; } } appendPQExpBufferStr(buf, "\\connect "); if (complex) { PQExpBufferData connstr; initPQExpBuffer(&connstr); appendPQExpBuffer(&connstr, "dbname="); appendConnStrVal(&connstr, dbname); appendPQExpBuffer(buf, "-reuse-previous=on "); /* * As long as the name does not contain a newline, SQL identifier * quoting satisfies the psql meta-command parser. Prefer not to * involve psql-interpreted single quotes, which behaved differently * before PostgreSQL 9.2. */ appendPQExpBufferStr(buf, fmtId(connstr.data)); termPQExpBuffer(&connstr); } else appendPQExpBufferStr(buf, fmtId(dbname)); appendPQExpBufferChar(buf, '\n'); }
/* * Dump role time constraints. * * Note: we expect dumpRoles already created all the roles, but there are * no time constraints yet. */ static void dumpRoleConstraints(PGconn *conn) { PGresult *res; int i; res = executeQuery(conn, "SELECT a.rolname, c.start_day, c.start_time, c.end_day, c.end_time " "FROM pg_authid a, pg_auth_time_constraint c " "WHERE a.oid = c.authid " "ORDER BY 1"); if (PQntuples(res) > 0) fprintf(OPF, "--\n-- Role time constraints\n--\n\n"); for (i = 0; i < PQntuples(res); i++) { char *rolname = PQgetvalue(res, i, 0); char *start_day = PQgetvalue(res, i, 1); char *start_time = PQgetvalue(res, i, 2); char *end_day = PQgetvalue(res, i, 3); char *end_time = PQgetvalue(res, i, 4); fprintf(OPF, "ALTER ROLE %s DENY BETWEEN DAY %s TIME '%s' AND DAY %s TIME '%s';\n", fmtId(rolname), start_day, start_time, end_day, end_time); } PQclear(res); fprintf(OPF, "\n\n"); }
/* * Drop tablespaces. */ static void dropTablespaces(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 " "FROM pg_catalog.pg_tablespace " "WHERE spcname !~ '^pg_' " "ORDER BY 1"); if (PQntuples(res) > 0) fprintf(OPF, "--\n-- Drop tablespaces\n--\n\n"); for (i = 0; i < PQntuples(res); i++) { char *spcname = PQgetvalue(res, i, 0); fprintf(OPF, "DROP TABLESPACE %s;\n", fmtId(spcname)); } PQclear(res); fprintf(OPF, "\n\n"); }
/* * Dump contents of databases. */ static void dumpDatabases(PGconn *conn) { PGresult *res; int i; if (server_version >= 70100) res = executeQuery(conn, "SELECT datname FROM pg_database WHERE datallowconn ORDER BY 1"); else res = executeQuery(conn, "SELECT datname FROM pg_database ORDER BY 1"); for (i = 0; i < PQntuples(res); i++) { int ret; char *dbname = PQgetvalue(res, i, 0); if (verbose) fprintf(stderr, _("%s: dumping database \"%s\"...\n"), progname, dbname); printf("\\connect %s\n\n", fmtId(dbname)); ret = runPgDump(dbname); if (ret != 0) { fprintf(stderr, _("%s: pg_dump failed on database \"%s\", exiting\n"), progname, dbname); exit(1); } } PQclear(res); }
/* * Dump role memberships. This code is used for 8.1 and later servers. * * Note: we expect dumpRoles already created all the roles, but there is * no membership yet. */ static void dumpRoleMembership(PGconn *conn) { PGresult *res; int i; res = executeQuery(conn, "SELECT ur.rolname AS roleid, " "um.rolname AS member, " "a.admin_option, " "ug.rolname AS grantor " "FROM pg_auth_members a " "LEFT JOIN pg_authid ur on ur.oid = a.roleid " "LEFT JOIN pg_authid um on um.oid = a.member " "LEFT JOIN pg_authid ug on ug.oid = a.grantor " "ORDER BY 1,2,3"); if (PQntuples(res) > 0) fprintf(OPF, "--\n-- Role memberships\n--\n\n"); for (i = 0; i < PQntuples(res); i++) { char *roleid = PQgetvalue(res, i, 0); char *member = PQgetvalue(res, i, 1); char *option = PQgetvalue(res, i, 2); fprintf(OPF, "GRANT %s", fmtId(roleid)); fprintf(OPF, " TO %s", fmtId(member)); if (*option == 't') fprintf(OPF, " WITH ADMIN OPTION"); /* * We don't track the grantor very carefully in the backend, so cope * with the possibility that it has been dropped. */ if (!PQgetisnull(res, i, 3)) { char *grantor = PQgetvalue(res, i, 3); fprintf(OPF, " GRANTED BY %s", fmtId(grantor)); } fprintf(OPF, ";\n"); } PQclear(res); fprintf(OPF, "\n\n"); }
/* * fmtQualifiedId - convert a qualified name to the proper format for * the source database. * * Like fmtId, use the result before calling again. * * Since we call fmtId and it also uses getThreadLocalPQExpBuffer() we cannot * use it until we're finished with calling fmtId(). */ const char * fmtQualifiedId(int remoteVersion, const char *schema, const char *id) { PQExpBuffer id_return; PQExpBuffer lcl_pqexp = createPQExpBuffer(); /* Suppress schema name if fetching from pre-7.3 DB */ if (remoteVersion >= 70300 && schema && *schema) { appendPQExpBuffer(lcl_pqexp, "%s.", fmtId(schema)); } appendPQExpBufferStr(lcl_pqexp, fmtId(id)); id_return = getLocalPQExpBuffer(); appendPQExpBufferStr(id_return, lcl_pqexp->data); destroyPQExpBuffer(lcl_pqexp); return id_return->data; }
/* * Build ALTER DEFAULT PRIVILEGES command(s) for single pg_default_acl entry. * * type: the object type (TABLES, FUNCTIONS, etc) * nspname: schema name, or NULL for global default privileges * acls: the ACL string fetched from the database * owner: username of privileges owner (will be passed through fmtId) * remoteVersion: version of database * * Returns TRUE if okay, FALSE if could not parse the acl string. * The resulting commands (if any) are appended to the contents of 'sql'. */ bool buildDefaultACLCommands(const char *type, const char *nspname, const char *acls, const char *racls, const char *initacls, const char *initracls, const char *owner, int remoteVersion, PQExpBuffer sql) { PQExpBuffer prefix; prefix = createPQExpBuffer(); /* * We incorporate the target role directly into the command, rather than * playing around with SET ROLE or anything like that. This is so that a * permissions error leads to nothing happening, rather than changing * default privileges for the wrong user. */ appendPQExpBuffer(prefix, "ALTER DEFAULT PRIVILEGES FOR ROLE %s ", fmtId(owner)); if (nspname) appendPQExpBuffer(prefix, "IN SCHEMA %s ", fmtId(nspname)); if (strlen(initacls) != 0 || strlen(initracls) != 0) { appendPQExpBuffer(sql, "SELECT pg_catalog.binary_upgrade_set_record_init_privs(true);\n"); if (!buildACLCommands("", NULL, type, initacls, initracls, owner, prefix->data, remoteVersion, sql)) return false; appendPQExpBuffer(sql, "SELECT pg_catalog.binary_upgrade_set_record_init_privs(false);\n"); } if (!buildACLCommands("", NULL, type, acls, racls, owner, prefix->data, remoteVersion, sql)) return false; destroyPQExpBuffer(prefix); return true; }
/* * emitShSecLabels * * Format security label data retrieved by the query generated in * buildShSecLabelQuery. */ void emitShSecLabels(PGconn *conn, PGresult *res, PQExpBuffer buffer, const char *target, const char *objname) { int i; for (i = 0; i < PQntuples(res); i++) { char *provider = PQgetvalue(res, i, 0); char *label = PQgetvalue(res, i, 1); /* must use fmtId result before calling it again */ appendPQExpBuffer(buffer, "SECURITY LABEL FOR %s ON %s", fmtId(provider), target); appendPQExpBuffer(buffer, " %s IS ", fmtId(objname)); appendStringLiteralConn(buffer, label, conn); appendPQExpBuffer(buffer, ";\n"); } }
static void reindex_one_database(const char *name, const char *dbname, const char *type, const char *host, const char *port, const char *username, enum trivalue prompt_password, const char *progname, bool echo, bool verbose) { PQExpBufferData sql; PGconn *conn; initPQExpBuffer(&sql); appendPQExpBufferStr(&sql, "REINDEX"); if (verbose) appendPQExpBufferStr(&sql, " (VERBOSE)"); if (strcmp(type, "TABLE") == 0) appendPQExpBuffer(&sql, " TABLE %s", name); else if (strcmp(type, "INDEX") == 0) appendPQExpBuffer(&sql, " INDEX %s", name); else if (strcmp(type, "SCHEMA") == 0) appendPQExpBuffer(&sql, " SCHEMA %s", name); else if (strcmp(type, "DATABASE") == 0) appendPQExpBuffer(&sql, " DATABASE %s", fmtId(name)); appendPQExpBufferChar(&sql, ';'); conn = connectDatabase(dbname, host, port, username, prompt_password, progname, false, false); if (!executeMaintenanceCommand(conn, sql.data, echo)) { if (strcmp(type, "TABLE") == 0) fprintf(stderr, _("%s: reindexing of table \"%s\" in database \"%s\" failed: %s"), progname, name, dbname, PQerrorMessage(conn)); if (strcmp(type, "INDEX") == 0) fprintf(stderr, _("%s: reindexing of index \"%s\" in database \"%s\" failed: %s"), progname, name, dbname, PQerrorMessage(conn)); if (strcmp(type, "SCHEMA") == 0) fprintf(stderr, _("%s: reindexing of schema \"%s\" in database \"%s\" failed: %s"), progname, name, dbname, PQerrorMessage(conn)); else fprintf(stderr, _("%s: reindexing of database \"%s\" failed: %s"), progname, dbname, PQerrorMessage(conn)); PQfinish(conn); exit(1); } PQfinish(conn); termPQExpBuffer(&sql); }
/* * Dump role memberships. This code is used for 8.1 and later servers. * * Note: we expect dumpRoles already created all the roles, but there is * no membership yet. */ static void dumpRoleMembership(PGconn *conn) { PGresult *res; int i; res = executeQuery(conn, "SELECT ur.rolname AS roleid, " "um.rolname AS member, " "ug.rolname AS grantor, " "a.admin_option " "FROM pg_auth_members a " "LEFT JOIN pg_authid ur on ur.oid = a.roleid " "LEFT JOIN pg_authid um on um.oid = a.member " "LEFT JOIN pg_authid ug on ug.oid = a.grantor " "ORDER BY 1,2,3"); if (PQntuples(res) > 0) printf("--\n-- Role memberships\n--\n\n"); for (i = 0; i < PQntuples(res); i++) { char *roleid = PQgetvalue(res, i, 0); char *member = PQgetvalue(res, i, 1); char *grantor = PQgetvalue(res, i, 2); char *option = PQgetvalue(res, i, 3); printf("GRANT %s", fmtId(roleid)); printf(" TO %s", fmtId(member)); if (*option == 't') printf(" WITH ADMIN OPTION"); printf(" GRANTED BY %s;\n", fmtId(grantor)); } PQclear(res); printf("\n\n"); }
/* * Dump contents of databases. */ static void dumpDatabases(PGconn *conn) { PGresult *res; int i; if (server_version >= 70100) res = executeQuery(conn, "SELECT datname FROM pg_database WHERE datallowconn ORDER BY 1"); else res = executeQuery(conn, "SELECT datname FROM pg_database ORDER BY 1"); for (i = 0; i < PQntuples(res); i++) { int ret; char *dbname = PQgetvalue(res, i, 0); if (verbose) fprintf(stderr, _("%s: dumping database \"%s\"...\n"), progname, dbname); fprintf(OPF, "\\connect %s\n\n", fmtId(dbname)); if (filename) fclose(OPF); ret = runPgDump(dbname); if (ret != 0) { fprintf(stderr, _("%s: pg_dump failed on database \"%s\", exiting\n"), progname, dbname); exit(1); } if (filename) { OPF = fopen(filename, PG_BINARY_A); if (!OPF) { fprintf(stderr, _("%s: could not re-open the output file \"%s\": %s\n"), progname, filename, strerror(errno)); exit(1); } } } PQclear(res); }
static void cluster_one_database(const char *dbname, const char *table, const char *host, const char *port, const char *username, bool password, const char *progname, bool echo, bool quiet) { PQExpBufferData sql; PGconn *conn; PGresult *result; initPQExpBuffer(&sql); appendPQExpBuffer(&sql, "CLUSTER"); if (table) appendPQExpBuffer(&sql, " %s", fmtId(table)); appendPQExpBuffer(&sql, ";\n"); conn = connectDatabase(dbname, host, port, username, password, progname); if (echo) printf("%s", sql.data); result = PQexec(conn, sql.data); if (PQresultStatus(result) != PGRES_COMMAND_OK) { if (table) fprintf(stderr, _("%s: clustering of table \"%s\" in database \"%s\" failed: %s"), progname, table, dbname, PQerrorMessage(conn)); else fprintf(stderr, _("%s: clustering of database \"%s\" failed: %s"), progname, dbname, PQerrorMessage(conn)); PQfinish(conn); exit(1); } PQclear(result); PQfinish(conn); termPQExpBuffer(&sql); if (!quiet) { puts("CLUSTER"); fflush(stdout); } }
/* * Dump commands to drop each database. * * This should match the set of databases targeted by dumpCreateDB(). */ static void dropDBs(PGconn *conn) { PGresult *res; int i; if (server_version >= 70100) res = executeQuery(conn, "SELECT datname " "FROM pg_database d " "WHERE datallowconn ORDER BY 1"); else res = executeQuery(conn, "SELECT datname " "FROM pg_database d " "ORDER BY 1"); if (PQntuples(res) > 0) fprintf(OPF, "--\n-- Drop databases\n--\n\n"); for (i = 0; i < PQntuples(res); i++) { char *dbname = PQgetvalue(res, i, 0); /* * Skip "template1" and "postgres"; the restore script is almost * certainly going to be run in one or the other, and we don't know * which. This must agree with dumpCreateDB's choices! */ if (strcmp(dbname, "template1") != 0 && strcmp(dbname, "postgres") != 0) { fprintf(OPF, "DROP DATABASE %s;\n", fmtId(dbname)); } } PQclear(res); fprintf(OPF, "\n\n"); }
/* * Drop roles */ static void dropRoles(PGconn *conn) { PGresult *res; int i_rolname; int i; if (server_version >= 80100) res = executeQuery(conn, "SELECT rolname " "FROM pg_authid " "ORDER BY 1"); else res = executeQuery(conn, "SELECT usename as rolname " "FROM pg_shadow " "UNION " "SELECT groname as rolname " "FROM pg_group " "ORDER BY 1"); i_rolname = PQfnumber(res, "rolname"); if (PQntuples(res) > 0) fprintf(OPF, "--\n-- Drop roles\n--\n\n"); for (i = 0; i < PQntuples(res); i++) { const char *rolename; rolename = PQgetvalue(res, i, i_rolname); fprintf(OPF, "DROP ROLE %s;\n", fmtId(rolename)); } PQclear(res); fprintf(OPF, "\n\n"); }
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'}, {"no-password", no_argument, NULL, 'w'}, {"password", no_argument, NULL, 'W'}, {"echo", no_argument, NULL, 'e'}, {"interactive", no_argument, NULL, 'i'}, {NULL, 0, NULL, 0} }; const char *progname; int optindex; int c; char *dropuser = NULL; char *host = NULL; char *port = NULL; char *username = NULL; enum trivalue prompt_password = TRI_DEFAULT; bool echo = false; bool interactive = false; PQExpBufferData sql; PGconn *conn; PGresult *result; progname = get_progname(argv[0]); set_pglocale_pgservice(argv[0], PG_TEXTDOMAIN("pgscripts")); handle_help_version_opts(argc, argv, "dropuser", help); while ((c = getopt_long(argc, argv, "h:p:U:wWei", long_options, &optindex)) != -1) { switch (c) { case 'h': host = optarg; break; case 'p': port = optarg; break; case 'U': username = optarg; break; case 'w': prompt_password = TRI_NO; break; case 'W': prompt_password = TRI_YES; break; case 'e': echo = true; break; case 'i': interactive = true; break; default: fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname); exit(1); } } switch (argc - optind) { case 0: break; case 1: dropuser = 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 (dropuser == NULL) dropuser = simple_prompt("Enter name of role to drop: ", 128, true); if (interactive) { printf(_("Role \"%s\" will be permanently removed.\n"), dropuser); if (!yesno_prompt("Are you sure?")) exit(0); } initPQExpBuffer(&sql); appendPQExpBuffer(&sql, "DROP ROLE %s;\n", fmtId(dropuser)); conn = connectDatabase("postgres", host, port, username, prompt_password, progname); if (echo) printf("%s", sql.data); result = PQexec(conn, sql.data); if (PQresultStatus(result) != PGRES_COMMAND_OK) { fprintf(stderr, _("%s: removal of role \"%s\" failed: %s"), progname, dropuser, PQerrorMessage(conn)); PQfinish(conn); exit(1); } PQclear(result); PQfinish(conn); exit(0); }
/* * 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 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 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; fprintf(OPF, "--\n-- Database creation\n--\n\n"); 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"); 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 = "); appendStringLiteralConn(buf, dbencoding, conn); /* * Output tablespace if it isn't the default. For default, it * uses the default from the template database. If tablespace is * specified and tablespace creation failed earlier, (e.g. no such * directory), the database creation will fail too. One solution * would be to use 'SET default_tablespace' like we do in pg_dump * for setting non-default database locations. */ 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 = "); appendStringLiteralConn(buf, dbname, conn); 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); } fprintf(OPF, "%s", buf->data); dumpDatabaseConfig(conn, dbname); free(fdbname); } PQclear(res); destroyPQExpBuffer(buf); fprintf(OPF, "\n\n"); }
/* * Dump tablespaces. */ static void dumpTablespaces(PGconn *conn) { PGresult *res; int i; /* * Get all tablespaces execpt built-in ones (named pg_xxx) * * [FIXME] the queries need to be slightly different if the backend isn't * Greenplum, and the dump format should vary depending on if the dump is * --gp-syntax or --no-gp-syntax. */ if (server_version < 80214) { /* Filespaces were introduced in GP 4.0 (server_version 8.2.14) */ return; } else { res = executeQuery(conn, "SELECT spcname, " "pg_catalog.pg_get_userbyid(spcowner) AS spcowner, " "fsname, spcacl, " "pg_catalog.shobj_description(t.oid, 'pg_tablespace') " "FROM pg_catalog.pg_tablespace t, " "pg_catalog.pg_filespace fs " "WHERE t.spcfsoid = fs.oid AND spcname !~ '^pg_' " "ORDER BY 1"); } if (PQntuples(res) > 0) fprintf(OPF, "--\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 *fsname = PQgetvalue(res, i, 2); char *spcacl = PQgetvalue(res, i, 3); char *spccomment = PQgetvalue(res, i, 4); /* needed for buildACLCommands() */ spcname = strdup(fmtId(spcname)); if (output_clean) appendPQExpBuffer(buf, "DROP TABLESPACE %s;\n", spcname); appendPQExpBuffer(buf, "CREATE TABLESPACE %s", spcname); appendPQExpBuffer(buf, " OWNER %s", fmtId(spcowner)); appendPQExpBuffer(buf, " FILESPACE %s;\n", fmtId(fsname)); /* Build Acls */ if (!skip_acls && !buildACLCommands(spcname, "TABLESPACE", spcacl, spcowner, server_version, buf)) { fprintf(stderr, _("%s: could not parse ACL list (%s) for tablespace \"%s\"\n"), progname, spcacl, spcname); PQfinish(conn); exit(1); } /* Set comments */ if (spccomment && strlen(spccomment)) { appendPQExpBuffer(buf, "COMMENT ON TABLESPACE %s IS ", spcname); appendStringLiteralConn(buf, spccomment, conn); appendPQExpBuffer(buf, ";\n"); } fprintf(OPF, "%s", buf->data); free(spcname); destroyPQExpBuffer(buf); } PQclear(res); fprintf(OPF, "\n\n"); }
/* * Dump resource queues */ static void dumpResQueues(PGconn *conn) { PQExpBuffer buf = createPQExpBuffer(); PGresult *res; int i_rsqname, i_resname, i_ressetting; int i; char *prev_rsqname = NULL; bool bWith = false; printfPQExpBuffer(buf, "SELECT rsqname, 'activelimit' as resname, " "rsqcountlimit::text as ressetting, " "1 as ord FROM pg_resqueue " "UNION " "SELECT rsqname, 'costlimit' as resname, " "rsqcostlimit::text as ressetting, " "2 as ord FROM pg_resqueue " "UNION " "SELECT rsqname, 'overcommit' as resname, " "case when rsqovercommit then '1' " "else '0' end as ressetting, " "3 as ord FROM pg_resqueue " "UNION " "SELECT rsqname, 'ignorecostlimit' as resname, " "%s as ressetting, " "4 as ord FROM pg_resqueue " "%s" "order by rsqname, ord", (server_version >= 80205 ? "rsqignorecostlimit::text" : "0 AS"), (server_version >= 80214 ? "UNION " "select rq.rsqname , rt.resname, rc.ressetting, " "rt.restypid as ord from " "pg_resqueue rq, pg_resourcetype rt, " "pg_resqueuecapability rc where " "rq.oid=rc.resqueueid and rc.restypid = rt.restypid " : "") ); res = executeQuery(conn, buf->data); i_rsqname = PQfnumber(res, "rsqname"); i_resname = PQfnumber(res, "resname"); i_ressetting = PQfnumber(res, "ressetting"); if (PQntuples(res) > 0) fprintf(OPF, "--\n-- Resource Queues\n--\n\n"); /* * settings for resource queue are spread over multiple rows, but sorted * by queue name (and ranked in order of resname ) eg: * * rsqname | resname | ressetting | ord * -----------+-----------------+------------+----- pg_default | * activelimit | 20 | 1 pg_default | costlimit | -1 | * 2 pg_default | overcommit | 0 | 3 pg_default | * ignorecostlimit | 0 | 4 * * This format lets us support an arbitrary number of resqueuecapability * entries. So watch for change of rsqname to switch to next CREATE * statement. * */ for (i = 0; i < PQntuples(res); i++) { const char *rsqname; const char *resname; const char *ressetting; rsqname = PQgetvalue(res, i, i_rsqname); resname = PQgetvalue(res, i, i_resname); ressetting = PQgetvalue(res, i, i_ressetting); /* if first CREATE statement, or name changed... */ if (!prev_rsqname || (0 != strcmp(rsqname, prev_rsqname))) { if (prev_rsqname) { /* terminate the WITH if necessary */ if (bWith) appendPQExpBuffer(buf, ") "); appendPQExpBuffer(buf, ";\n"); fprintf(OPF, "%s", buf->data); free(prev_rsqname); } bWith = false; /* save the name */ prev_rsqname = strdup(rsqname); resetPQExpBuffer(buf); /* MPP-6926: cannot DROP or CREATEdefault queue, so ALTER it */ if (0 == strcmp(rsqname, "pg_default")) appendPQExpBuffer(buf, "ALTER RESOURCE QUEUE %s", fmtId(rsqname)); else appendPQExpBuffer(buf, "CREATE RESOURCE QUEUE %s", fmtId(rsqname)); } /* NOTE: currently 3.3-style, but will switch to one WITH clause... */ if (0 == strcmp("activelimit", resname)) { if (strcmp(ressetting, "-1") != 0) appendPQExpBuffer(buf, " ACTIVE THRESHOLD %s", ressetting); } else if (0 == strcmp("costlimit", resname)) { if (strcmp(ressetting, "-1") != 0) appendPQExpBuffer(buf, " COST THRESHOLD %.2f", atof(ressetting)); } else if (0 == strcmp("ignorecostlimit", resname)) { if (!((strcmp(ressetting, "-1") == 0) || (strcmp(ressetting, "0") == 0))) appendPQExpBuffer(buf, " IGNORE THRESHOLD %.2f", atof(ressetting)); } else if (0 == strcmp("overcommit", resname)) { if (strcmp(ressetting, "1") == 0) appendPQExpBuffer(buf, " OVERCOMMIT"); else appendPQExpBuffer(buf, " NOOVERCOMMIT"); } else { /* build the WITH clause */ if (bWith) appendPQExpBuffer(buf, ",\n"); else { bWith = true; appendPQExpBuffer(buf, "\n WITH ("); } buildWithClause(resname, ressetting, buf); } } /* end for */ /* need to write out last statement */ if (prev_rsqname) { /* terminate the WITH if necessary */ if (bWith) appendPQExpBuffer(buf, ") "); appendPQExpBuffer(buf, ";\n"); fprintf(OPF, "%s", buf->data); free(prev_rsqname); } PQclear(res); fprintf(OPF, "\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); }
/* * Build GRANT/REVOKE command(s) for an object. * * name: the object name, in the form to use in the commands (already quoted) * subname: the sub-object name, if any (already quoted); NULL if none * type: the object type (as seen in GRANT command: must be one of * TABLE, SEQUENCE, FUNCTION, LANGUAGE, SCHEMA, DATABASE, TABLESPACE, * FOREIGN DATA WRAPPER, SERVER, or LARGE OBJECT) * acls: the ACL string fetched from the database * owner: username of object owner (will be passed through fmtId); can be * NULL or empty string to indicate "no owner known" * prefix: string to prefix to each generated command; typically empty * remoteVersion: version of database * * Returns TRUE if okay, FALSE if could not parse the acl string. * The resulting commands (if any) are appended to the contents of 'sql'. * * Note: when processing a default ACL, prefix is "ALTER DEFAULT PRIVILEGES " * or something similar, and name is an empty string. * * Note: beware of passing a fmtId() result directly as 'name' or 'subname', * since this routine uses fmtId() internally. */ bool buildACLCommands(const char *name, const char *subname, const char *type, const char *acls, const char *owner, const char *prefix, int remoteVersion, PQExpBuffer sql) { char **aclitems; int naclitems; int i; PQExpBuffer grantee, grantor, privs, privswgo; PQExpBuffer firstsql, secondsql; bool found_owner_privs = false; if (strlen(acls) == 0) return true; /* object has default permissions */ /* treat empty-string owner same as NULL */ if (owner && *owner == '\0') owner = NULL; if (!parsePGArray(acls, &aclitems, &naclitems)) { if (aclitems) free(aclitems); return false; } grantee = createPQExpBuffer(); grantor = createPQExpBuffer(); privs = createPQExpBuffer(); privswgo = createPQExpBuffer(); /* * At the end, these two will be pasted together to form the result. But * the owner privileges need to go before the other ones to keep the * dependencies valid. In recent versions this is normally the case, but * in old versions they come after the PUBLIC privileges and that results * in problems if we need to run REVOKE on the owner privileges. */ firstsql = createPQExpBuffer(); secondsql = createPQExpBuffer(); /* * Always start with REVOKE ALL FROM PUBLIC, so that we don't have to * wire-in knowledge about the default public privileges for different * kinds of objects. */ appendPQExpBuffer(firstsql, "%sREVOKE ALL", prefix); if (subname) appendPQExpBuffer(firstsql, "(%s)", subname); appendPQExpBuffer(firstsql, " ON %s %s FROM PUBLIC;\n", type, name); /* * We still need some hacking though to cover the case where new default * public privileges are added in new versions: the REVOKE ALL will revoke * them, leading to behavior different from what the old version had, * which is generally not what's wanted. So add back default privs if the * source database is too old to have had that particular priv. */ if (remoteVersion < 80200 && strcmp(type, "DATABASE") == 0) { /* database CONNECT priv didn't exist before 8.2 */ appendPQExpBuffer(firstsql, "%sGRANT CONNECT ON %s %s TO PUBLIC;\n", prefix, type, name); } /* Scan individual ACL items */ for (i = 0; i < naclitems; i++) { if (!parseAclItem(aclitems[i], type, name, subname, remoteVersion, grantee, grantor, privs, privswgo)) return false; if (grantor->len == 0 && owner) printfPQExpBuffer(grantor, "%s", owner); if (privs->len > 0 || privswgo->len > 0) { if (owner && strcmp(grantee->data, owner) == 0 && strcmp(grantor->data, owner) == 0) { found_owner_privs = true; /* * For the owner, the default privilege level is ALL WITH * GRANT OPTION (only ALL prior to 7.4). */ if (supports_grant_options(remoteVersion) ? strcmp(privswgo->data, "ALL") != 0 : strcmp(privs->data, "ALL") != 0) { appendPQExpBuffer(firstsql, "%sREVOKE ALL", prefix); if (subname) appendPQExpBuffer(firstsql, "(%s)", subname); appendPQExpBuffer(firstsql, " ON %s %s FROM %s;\n", type, name, fmtId(grantee->data)); if (privs->len > 0) appendPQExpBuffer(firstsql, "%sGRANT %s ON %s %s TO %s;\n", prefix, privs->data, type, name, fmtId(grantee->data)); if (privswgo->len > 0) appendPQExpBuffer(firstsql, "%sGRANT %s ON %s %s TO %s WITH GRANT OPTION;\n", prefix, privswgo->data, type, name, fmtId(grantee->data)); } } else { /* * Otherwise can assume we are starting from no privs. */ if (grantor->len > 0 && (!owner || strcmp(owner, grantor->data) != 0)) appendPQExpBuffer(secondsql, "SET SESSION AUTHORIZATION %s;\n", fmtId(grantor->data)); if (privs->len > 0) { appendPQExpBuffer(secondsql, "%sGRANT %s ON %s %s TO ", prefix, privs->data, type, name); if (grantee->len == 0) appendPQExpBuffer(secondsql, "PUBLIC;\n"); else if (strncmp(grantee->data, "group ", strlen("group ")) == 0) appendPQExpBuffer(secondsql, "GROUP %s;\n", fmtId(grantee->data + strlen("group "))); else appendPQExpBuffer(secondsql, "%s;\n", fmtId(grantee->data)); } if (privswgo->len > 0) { appendPQExpBuffer(secondsql, "%sGRANT %s ON %s %s TO ", prefix, privswgo->data, type, name); if (grantee->len == 0) appendPQExpBuffer(secondsql, "PUBLIC"); else if (strncmp(grantee->data, "group ", strlen("group ")) == 0) appendPQExpBuffer(secondsql, "GROUP %s", fmtId(grantee->data + strlen("group "))); else appendPQExpBuffer(secondsql, "%s", fmtId(grantee->data)); appendPQExpBuffer(secondsql, " WITH GRANT OPTION;\n"); } if (grantor->len > 0 && (!owner || strcmp(owner, grantor->data) != 0)) appendPQExpBuffer(secondsql, "RESET SESSION AUTHORIZATION;\n"); } } } /* * If we didn't find any owner privs, the owner must have revoked 'em all */ if (!found_owner_privs && owner) { appendPQExpBuffer(firstsql, "%sREVOKE ALL", prefix); if (subname) appendPQExpBuffer(firstsql, "(%s)", subname); appendPQExpBuffer(firstsql, " ON %s %s FROM %s;\n", type, name, fmtId(owner)); } destroyPQExpBuffer(grantee); destroyPQExpBuffer(grantor); destroyPQExpBuffer(privs); destroyPQExpBuffer(privswgo); appendPQExpBuffer(sql, "%s%s", firstsql->data, secondsql->data); destroyPQExpBuffer(firstsql); destroyPQExpBuffer(secondsql); free(aclitems); return true; }
/* * 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, i_rolcomment, i_rolqueuename = -1, /* keep compiler quiet */ i_rolcreaterextgpfd = -1, i_rolcreaterexthttp = -1, i_rolcreatewextgpfd = -1, i_rolcreaterexthdfs = -1, i_rolcreatewexthdfs = -1; int i; bool exttab_auth = (server_version >= 80214); bool hdfs_auth = (server_version >= 80215); char *resq_col = resource_queues ? ", (SELECT rsqname FROM pg_resqueue WHERE " " pg_resqueue.oid = rolresqueue) AS rolqueuename " : ""; char *extauth_col = exttab_auth ? ", rolcreaterextgpfd, rolcreaterexthttp, rolcreatewextgpfd" : ""; char *hdfs_col = hdfs_auth ? ", rolcreaterexthdfs, rolcreatewexthdfs " : ""; /* * Query to select role info get resqueue if version support it get * external table auth on gpfdist, gpfdists and http if version support it get * external table auth on gphdfs if version support it note: rolconfig is * dumped later */ printfPQExpBuffer(buf, "SELECT rolname, rolsuper, rolinherit, " "rolcreaterole, rolcreatedb, rolcatupdate, " "rolcanlogin, rolconnlimit, rolpassword, " "rolvaliduntil, " "pg_catalog.shobj_description(oid, 'pg_authid') as rolcomment " " %s %s %s" "FROM pg_authid " "ORDER BY 1", resq_col, extauth_col, hdfs_col); 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"); i_rolcomment = PQfnumber(res, "rolcomment"); if (resource_queues) i_rolqueuename = PQfnumber(res, "rolqueuename"); if (exttab_auth) { i_rolcreaterextgpfd = PQfnumber(res, "rolcreaterextgpfd"); i_rolcreaterexthttp = PQfnumber(res, "rolcreaterexthttp"); i_rolcreatewextgpfd = PQfnumber(res, "rolcreatewextgpfd"); if (hdfs_auth) { i_rolcreaterexthdfs = PQfnumber(res, "rolcreaterexthdfs"); i_rolcreatewexthdfs = PQfnumber(res, "rolcreatewexthdfs"); } } if (PQntuples(res) > 0) fprintf(OPF, "--\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 "); appendStringLiteralConn(buf, PQgetvalue(res, i, i_rolpassword), conn); } if (!PQgetisnull(res, i, i_rolvaliduntil)) appendPQExpBuffer(buf, " VALID UNTIL '%s'", PQgetvalue(res, i, i_rolvaliduntil)); if (resource_queues) { if (!PQgetisnull(res, i, i_rolqueuename)) appendPQExpBuffer(buf, " RESOURCE QUEUE %s", PQgetvalue(res, i, i_rolqueuename)); } if (exttab_auth) { /* we use the same privilege for gpfdist and gpfdists */ if (!PQgetisnull(res, i, i_rolcreaterextgpfd) && strcmp(PQgetvalue(res, i, i_rolcreaterextgpfd), "t") == 0) appendPQExpBuffer(buf, " CREATEEXTTABLE (protocol='gpfdist', type='readable')"); if (!PQgetisnull(res, i, i_rolcreatewextgpfd) && strcmp(PQgetvalue(res, i, i_rolcreatewextgpfd), "t") == 0) appendPQExpBuffer(buf, " CREATEEXTTABLE (protocol='gpfdist', type='writable')"); if (!PQgetisnull(res, i, i_rolcreaterexthttp) && strcmp(PQgetvalue(res, i, i_rolcreaterexthttp), "t") == 0) appendPQExpBuffer(buf, " CREATEEXTTABLE (protocol='http')"); if (hdfs_auth) { if (!PQgetisnull(res, i, i_rolcreaterexthdfs) && strcmp(PQgetvalue(res, i, i_rolcreaterexthdfs), "t") == 0) appendPQExpBuffer(buf, " CREATEEXTTABLE (protocol='gphdfs', type='readable')"); if (!PQgetisnull(res, i, i_rolcreatewexthdfs) && strcmp(PQgetvalue(res, i, i_rolcreatewexthdfs), "t") == 0) appendPQExpBuffer(buf, " CREATEEXTTABLE (protocol='gphdfs', type='writable')"); } } appendPQExpBuffer(buf, ";\n"); if (!PQgetisnull(res, i, i_rolcomment)) { appendPQExpBuffer(buf, "COMMENT ON ROLE %s IS ", fmtId(rolename)); appendStringLiteralConn(buf, PQgetvalue(res, i, i_rolcomment), conn); appendPQExpBuffer(buf, ";\n"); } fprintf(OPF, "%s", buf->data); dumpUserConfig(conn, rolename); } PQclear(res); fprintf(OPF, "\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'}, {"no-password", no_argument, NULL, 'w'}, {"password", no_argument, NULL, 'W'}, {"echo", no_argument, NULL, 'e'}, {"createdb", no_argument, NULL, 'd'}, {"no-createdb", no_argument, NULL, 'D'}, {"superuser", no_argument, NULL, 's'}, {"no-superuser", no_argument, NULL, 'S'}, {"createrole", no_argument, NULL, 'r'}, {"no-createrole", no_argument, NULL, 'R'}, {"inherit", no_argument, NULL, 'i'}, {"no-inherit", no_argument, NULL, 'I'}, {"login", no_argument, NULL, 'l'}, {"no-login", no_argument, NULL, 'L'}, /* adduser is obsolete, undocumented spelling of superuser */ {"adduser", no_argument, NULL, 'a'}, {"no-adduser", no_argument, NULL, 'A'}, {"connection-limit", required_argument, NULL, 'c'}, {"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; enum trivalue prompt_password = TRI_DEFAULT; bool echo = false; char* conn_limit = NULL; bool pwprompt = false; char* newpassword = NULL; /* Tri-valued variables. */ enum trivalue createdb = TRI_DEFAULT; enum trivalue superuser = TRI_DEFAULT; enum trivalue createrole = TRI_DEFAULT; enum trivalue inherit = TRI_DEFAULT; enum trivalue login = TRI_DEFAULT; enum trivalue encrypted = TRI_DEFAULT; struct pqbuf sql; PGconn* conn; PGresult* result; progname = get_progname(argv[0]); set_pglocale_pgservice(argv[0], PG_TEXTDOMAIN("pgscripts")); handle_help_version_opts(argc, argv, "createuser", help); while ((c = getopt_long(argc, argv, "h:p:U:wWedDsSaArRiIlLc:PEN", long_options, &optindex)) != -1) { switch (c) { case 'h': host = optarg; break; case 'p': port = optarg; break; case 'U': username = optarg; break; case 'w': prompt_password = TRI_NO; break; case 'W': prompt_password = TRI_YES; break; case 'e': echo = true; break; case 'd': createdb = TRI_YES; break; case 'D': createdb = TRI_NO; break; case 's': case 'a': superuser = TRI_YES; break; case 'S': case 'A': superuser = TRI_NO; break; case 'r': createrole = TRI_YES; break; case 'R': createrole = TRI_NO; break; case 'i': inherit = TRI_YES; break; case 'I': inherit = TRI_NO; break; case 'l': login = TRI_YES; break; case 'L': login = TRI_NO; break; case 'c': conn_limit = optarg; break; case 'P': pwprompt = true; break; case 'E': encrypted = TRI_YES; break; case 'N': encrypted = TRI_NO; 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 (newuser == NULL) newuser = simple_prompt("Enter name of role to add: ", 128, true); if (pwprompt) { char* pw1; char* pw2; pw1 = simple_prompt("Enter password for new role: ", 100, false); pw2 = simple_prompt("Enter it again: ", 100, false); if (strcmp(pw1, pw2) != 0) { fprintf(stderr, _("Passwords didn't match.\n")); exit(1); } newpassword = pw1; free(pw2); } if (superuser == 0) { if (yesno_prompt("Shall the new role be a superuser?")) superuser = TRI_YES; else superuser = TRI_NO; } if (superuser == TRI_YES) { /* Not much point in trying to restrict a superuser */ createdb = TRI_YES; createrole = TRI_YES; } if (createdb == 0) { if (yesno_prompt("Shall the new role be allowed to create databases?")) createdb = TRI_YES; else createdb = TRI_NO; } if (createrole == 0) { if (yesno_prompt("Shall the new role be allowed to create more new roles?")) createrole = TRI_YES; else createrole = TRI_NO; } if (inherit == 0) inherit = TRI_YES; if (login == 0) login = TRI_YES; conn = connectDatabase("postgres", host, port, username, prompt_password, progname); init_pqbuf(&sql); print_pqbuf(&sql, "CREATE ROLE %s", fmtId(newuser)); if (newpassword) { if (encrypted == TRI_YES) append_pqbuf(&sql, " ENCRYPTED"); if (encrypted == TRI_NO) append_pqbuf(&sql, " UNENCRYPTED"); append_pqbuf(&sql, " PASSWORD "); if (encrypted != TRI_NO) { char* encrypted_password; encrypted_password = PQencryptPassword(newpassword, newuser); if (!encrypted_password) { fprintf(stderr, _("Password encryption failed.\n")); exit(1); } appendStringLiteralConn(&sql, encrypted_password, conn); PQfreemem(encrypted_password); } else { appendStringLiteralConn(&sql, newpassword, conn); } } if (superuser == TRI_YES) append_pqbuf(&sql, " SUPERUSER"); if (superuser == TRI_NO) append_pqbuf(&sql, " NOSUPERUSER"); if (createdb == TRI_YES) append_pqbuf(&sql, " CREATEDB"); if (createdb == TRI_NO) append_pqbuf(&sql, " NOCREATEDB"); if (createrole == TRI_YES) append_pqbuf(&sql, " CREATEROLE"); if (createrole == TRI_NO) append_pqbuf(&sql, " NOCREATEROLE"); if (inherit == TRI_YES) append_pqbuf(&sql, " INHERIT"); if (inherit == TRI_NO) append_pqbuf(&sql, " NOINHERIT"); if (login == TRI_YES) append_pqbuf(&sql, " LOGIN"); if (login == TRI_NO) append_pqbuf(&sql, " NOLOGIN"); if (conn_limit != NULL) append_pqbuf(&sql, " CONNECTION LIMIT %s", conn_limit); append_pqbuf(&sql, ";\n"); if (echo) printf("%s", sql.data); result = PQexec(conn, sql.data); if (PQresultStatus(result) != PGRES_COMMAND_OK) { fprintf(stderr, _("%s: creation of new role failed: %s"), progname, PQerrorMessage(conn)); PQfinish(conn); exit(1); } PQclear(result); PQfinish(conn); exit(0); }
/* * Subroutine to actually try to execute a backslash command. */ static backslashResult exec_command(const char *cmd, PsqlScanState scan_state, PQExpBuffer query_buf) { bool success = true; /* indicate here if the command ran ok or * failed */ backslashResult status = PSQL_CMD_SKIP_LINE; /* * \a -- toggle field alignment This makes little sense but we keep it * around. */ if (strcmp(cmd, "a") == 0) { if (pset.popt.topt.format != PRINT_ALIGNED) success = do_pset("format", "aligned", &pset.popt, pset.quiet); else success = do_pset("format", "unaligned", &pset.popt, pset.quiet); } /* \C -- override table title (formerly change HTML caption) */ else if (strcmp(cmd, "C") == 0) { char *opt = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, true); success = do_pset("title", opt, &pset.popt, pset.quiet); free(opt); } /* * \c or \connect -- connect to database using the specified parameters. * * \c dbname user host port * * If any of these parameters are omitted or specified as '-', the current * value of the parameter will be used instead. If the parameter has no * current value, the default value for that parameter will be used. Some * examples: * * \c - - hst Connect to current database on current port of host * "hst" as current user. \c - usr - prt Connect to current database on * "prt" port of current host as user "usr". \c dbs Connect to * "dbs" database on current port of current host as current user. */ else if (strcmp(cmd, "c") == 0 || strcmp(cmd, "connect") == 0) { char *opt1, *opt2, *opt3, *opt4; opt1 = read_connect_arg(scan_state); opt2 = read_connect_arg(scan_state); opt3 = read_connect_arg(scan_state); opt4 = read_connect_arg(scan_state); success = do_connect(opt1, opt2, opt3, opt4); free(opt1); free(opt2); free(opt3); free(opt4); } /* \cd */ else if (strcmp(cmd, "cd") == 0) { char *opt = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, true); char *dir; if (opt) dir = opt; else { #ifndef WIN32 struct passwd *pw; pw = getpwuid(geteuid()); if (!pw) { psql_error("could not get home directory: %s\n", strerror(errno)); exit(EXIT_FAILURE); } dir = pw->pw_dir; #else /* WIN32 */ /* * On Windows, 'cd' without arguments prints the current * directory, so if someone wants to code this here instead... */ dir = "/"; #endif /* WIN32 */ } if (chdir(dir) == -1) { psql_error("\\%s: could not change directory to \"%s\": %s\n", cmd, dir, strerror(errno)); success = false; } if (pset.dirname) free(pset.dirname); pset.dirname = pg_strdup(dir); canonicalize_path(pset.dirname); if (opt) free(opt); } /* \conninfo -- display information about the current connection */ else if (strcmp(cmd, "conninfo") == 0) { char *db = PQdb(pset.db); char *host = PQhost(pset.db); if (db == NULL) printf(_("You are not connected.\n")); else { if (host == NULL) host = DEFAULT_PGSOCKET_DIR; /* If the host is an absolute path, the connection is via socket */ if (is_absolute_path(host)) printf(_("You are connected to database \"%s\" as user \"%s\" via socket in \"%s\" at port \"%s\".\n"), db, PQuser(pset.db), host, PQport(pset.db)); else printf(_("You are connected to database \"%s\" as user \"%s\" on host \"%s\" at port \"%s\".\n"), db, PQuser(pset.db), host, PQport(pset.db)); } } /* \copy */ else if (pg_strcasecmp(cmd, "copy") == 0) { /* Default fetch-it-all-and-print mode */ instr_time before, after; char *opt = psql_scan_slash_option(scan_state, OT_WHOLE_LINE, NULL, false); if (pset.timing) INSTR_TIME_SET_CURRENT(before); success = do_copy(opt); if (pset.timing && success) { INSTR_TIME_SET_CURRENT(after); INSTR_TIME_SUBTRACT(after, before); printf(_("Time: %.3f ms\n"), INSTR_TIME_GET_MILLISEC(after)); } free(opt); } /* \copyright */ else if (strcmp(cmd, "copyright") == 0) print_copyright(); /* \d* commands */ else if (cmd[0] == 'd') { char *pattern; bool show_verbose, show_system; /* We don't do SQLID reduction on the pattern yet */ pattern = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, true); show_verbose = strchr(cmd, '+') ? true : false; show_system = strchr(cmd, 'S') ? true : false; switch (cmd[1]) { case '\0': case '+': case 'S': /* GPDB: This is a change from old behavior: We used to show just system tables */ if (pattern) success = describeTableDetails(pattern, show_verbose, show_system); else /* standard listing of interesting things */ success = listTables("tvsxr", NULL, show_verbose, show_system); break; case 'a': success = describeAggregates(pattern, show_verbose, show_system); break; case 'b': success = describeTablespaces(pattern, show_verbose); break; case 'c': success = listConversions(pattern, show_system); break; case 'C': success = listCasts(pattern); break; case 'd': if (strncmp(cmd, "ddp", 3) == 0) success = listDefaultACLs(pattern); else success = objectDescription(pattern, show_system); break; case 'D': success = listDomains(pattern, show_system); break; case 'f': /* function subsystem */ switch (cmd[2]) { case '\0': case '+': case 'S': case 'a': case 'n': case 't': case 'w': success = describeFunctions(&cmd[2], pattern, show_verbose, show_system); break; default: status = PSQL_CMD_UNKNOWN; break; } break; case 'g': /* no longer distinct from \du */ success = describeRoles(pattern, show_verbose); break; case 'l': success = do_lo_list(); break; case 'n': success = listSchemas(pattern, show_verbose); break; case 'o': success = describeOperators(pattern, show_system); break; case 'p': success = permissionsList(pattern); break; case 'T': success = describeTypes(pattern, show_verbose, show_system); break; case 't': case 'v': case 'i': case 's': case 'E': /* PostgreSQL use dx for extension, change to dE for foreign table */ /* case 'S': // GPDB: We used to show just system tables for this */ case 'P': /* GPDB: Parent-only tables, no children */ success = listTables(&cmd[1], pattern, show_verbose, show_system); break; case 'r': if (cmd[2] == 'd' && cmd[3] == 's') { char *pattern2 = NULL; if (pattern) pattern2 = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, true); success = listDbRoleSettings(pattern, pattern2); } else //success = PSQL_CMD_UNKNOWN; /* GPDB uses \dr for foreign tables ? */ success = listTables(&cmd[1], pattern, show_verbose, show_system); break; case 'u': success = describeRoles(pattern, show_verbose); break; case 'F': /* text search subsystem */ switch (cmd[2]) { case '\0': case '+': success = listTSConfigs(pattern, show_verbose); break; case 'p': success = listTSParsers(pattern, show_verbose); break; case 'd': success = listTSDictionaries(pattern, show_verbose); break; case 't': success = listTSTemplates(pattern, show_verbose); break; default: status = PSQL_CMD_UNKNOWN; break; } break; case 'x': /* Extensions */ if (show_verbose) success = listExtensionContents(pattern); else success = listExtensions(pattern); break; default: status = PSQL_CMD_UNKNOWN; } if (pattern) free(pattern); } /* * \e or \edit -- edit the current query buffer, or edit a file and make * it the query buffer */ else if (strcmp(cmd, "e") == 0 || strcmp(cmd, "edit") == 0) { if (!query_buf) { psql_error("no query buffer\n"); status = PSQL_CMD_ERROR; } else { char *fname; fname = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, true); expand_tilde(&fname); if (fname) canonicalize_path(fname); if (do_edit(fname, query_buf, NULL)) status = PSQL_CMD_NEWEDIT; else status = PSQL_CMD_ERROR; free(fname); } } /* * \ef -- edit the named function, or present a blank CREATE FUNCTION * template if no argument is given */ else if (strcmp(cmd, "ef") == 0) { if (!query_buf) { psql_error("no query buffer\n"); status = PSQL_CMD_ERROR; } else { char *func; Oid foid = InvalidOid; func = psql_scan_slash_option(scan_state, OT_WHOLE_LINE, NULL, true); if (!func) { /* set up an empty command to fill in */ printfPQExpBuffer(query_buf, "CREATE FUNCTION ( )\n" " RETURNS \n" " LANGUAGE \n" " -- common options: IMMUTABLE STABLE STRICT SECURITY DEFINER\n" "AS $function$\n" "\n$function$\n"); } else if (!lookup_function_oid(pset.db, func, &foid)) { /* error already reported */ status = PSQL_CMD_ERROR; } else if (!get_create_function_cmd(pset.db, foid, query_buf)) { /* error already reported */ status = PSQL_CMD_ERROR; } if (func) free(func); } if (status != PSQL_CMD_ERROR) { bool edited = false; if (!do_edit(0, query_buf, &edited)) status = PSQL_CMD_ERROR; else if (!edited) puts(_("No changes")); else status = PSQL_CMD_NEWEDIT; } } /* \echo and \qecho */ else if (strcmp(cmd, "echo") == 0 || strcmp(cmd, "qecho") == 0) { char *value; char quoted; bool no_newline = false; bool first = true; FILE *fout; if (strcmp(cmd, "qecho") == 0) fout = pset.queryFout; else fout = stdout; while ((value = psql_scan_slash_option(scan_state, OT_NORMAL, "ed, false))) { if (!quoted && strcmp(value, "-n") == 0) no_newline = true; else { if (first) first = false; else fputc(' ', fout); fputs(value, fout); } free(value); } if (!no_newline) fputs("\n", fout); } /* \encoding -- set/show client side encoding */ else if (strcmp(cmd, "encoding") == 0) { char *encoding = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, false); if (!encoding) { /* show encoding */ puts(pg_encoding_to_char(pset.encoding)); } else { /* set encoding */ if (PQsetClientEncoding(pset.db, encoding) == -1) psql_error("%s: invalid encoding name or conversion procedure not found\n", encoding); else { /* save encoding info into psql internal data */ pset.encoding = PQclientEncoding(pset.db); pset.popt.topt.encoding = pset.encoding; SetVariable(pset.vars, "ENCODING", pg_encoding_to_char(pset.encoding)); } free(encoding); } } /* \f -- change field separator */ else if (strcmp(cmd, "f") == 0) { char *fname = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, false); success = do_pset("fieldsep", fname, &pset.popt, pset.quiet); free(fname); } /* \g means send query */ else if (strcmp(cmd, "g") == 0) { char *fname = psql_scan_slash_option(scan_state, OT_FILEPIPE, NULL, false); if (!fname) pset.gfname = NULL; else { expand_tilde(&fname); pset.gfname = pg_strdup(fname); } free(fname); status = PSQL_CMD_SEND; } /* help */ else if (strcmp(cmd, "h") == 0 || strcmp(cmd, "help") == 0) { char *opt = psql_scan_slash_option(scan_state, OT_WHOLE_LINE, NULL, false); size_t len; /* strip any trailing spaces and semicolons */ if (opt) { len = strlen(opt); while (len > 0 && (isspace((unsigned char) opt[len - 1]) || opt[len - 1] == ';')) opt[--len] = '\0'; } helpSQL(opt, pset.popt.topt.pager); free(opt); } /* HTML mode */ else if (strcmp(cmd, "H") == 0 || strcmp(cmd, "html") == 0) { if (pset.popt.topt.format != PRINT_HTML) success = do_pset("format", "html", &pset.popt, pset.quiet); else success = do_pset("format", "aligned", &pset.popt, pset.quiet); } /* \i is include file */ else if (strcmp(cmd, "i") == 0 || strcmp(cmd, "include") == 0) { char *fname = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, true); if (!fname) { psql_error("\\%s: missing required argument\n", cmd); success = false; } else { expand_tilde(&fname); success = (process_file(fname, false) == EXIT_SUCCESS); free(fname); } } /* \l is list databases */ else if (strcmp(cmd, "l") == 0 || strcmp(cmd, "list") == 0) success = listAllDbs(false); else if (strcmp(cmd, "l+") == 0 || strcmp(cmd, "list+") == 0) success = listAllDbs(true); /* * large object things */ else if (strncmp(cmd, "lo_", 3) == 0) { char *opt1, *opt2; opt1 = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, true); opt2 = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, true); if (strcmp(cmd + 3, "export") == 0) { if (!opt2) { psql_error("\\%s: missing required argument\n", cmd); success = false; } else { expand_tilde(&opt2); success = do_lo_export(opt1, opt2); } } else if (strcmp(cmd + 3, "import") == 0) { if (!opt1) { psql_error("\\%s: missing required argument\n", cmd); success = false; } else { expand_tilde(&opt1); success = do_lo_import(opt1, opt2); } } else if (strcmp(cmd + 3, "list") == 0) success = do_lo_list(); else if (strcmp(cmd + 3, "unlink") == 0) { if (!opt1) { psql_error("\\%s: missing required argument\n", cmd); success = false; } else success = do_lo_unlink(opt1); } else status = PSQL_CMD_UNKNOWN; free(opt1); free(opt2); } /* \o -- set query output */ else if (strcmp(cmd, "o") == 0 || strcmp(cmd, "out") == 0) { char *fname = psql_scan_slash_option(scan_state, OT_FILEPIPE, NULL, true); expand_tilde(&fname); success = setQFout(fname); free(fname); } /* \p prints the current query buffer */ else if (strcmp(cmd, "p") == 0 || strcmp(cmd, "print") == 0) { if (query_buf && query_buf->len > 0) puts(query_buf->data); else if (!pset.quiet) puts(_("Query buffer is empty.")); fflush(stdout); } /* \password -- set user password */ else if (strcmp(cmd, "password") == 0) { char *pw1; char *pw2; pw1 = simple_prompt("Enter new password: "******"Enter it again: ", 100, false); if (strcmp(pw1, pw2) != 0) { fprintf(stderr, _("Passwords didn't match.\n")); success = false; } else { char *opt0 = psql_scan_slash_option(scan_state, OT_SQLID, NULL, true); char *user; char *encrypted_password; if (opt0) user = opt0; else user = PQuser(pset.db); encrypted_password = PQencryptPassword(pw1, user); if (!encrypted_password) { fprintf(stderr, _("Password encryption failed.\n")); success = false; } else { PQExpBufferData buf; PGresult *res; initPQExpBuffer(&buf); printfPQExpBuffer(&buf, "ALTER USER %s PASSWORD ", fmtId(user)); appendStringLiteralConn(&buf, encrypted_password, pset.db); res = PSQLexec(buf.data, false); termPQExpBuffer(&buf); if (!res) success = false; else PQclear(res); PQfreemem(encrypted_password); } } free(pw1); free(pw2); } /* \prompt -- prompt and set variable */ else if (strcmp(cmd, "prompt") == 0) { char *opt, *prompt_text = NULL; char *arg1, *arg2; arg1 = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, false); arg2 = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, false); if (!arg1) { psql_error("\\%s: missing required argument\n", cmd); success = false; } else { char *result; if (arg2) { prompt_text = arg1; opt = arg2; } else opt = arg1; if (!pset.inputfile) result = simple_prompt(prompt_text, 4096, true); else { if (prompt_text) { fputs(prompt_text, stdout); fflush(stdout); } result = gets_fromFile(stdin); } if (!SetVariable(pset.vars, opt, result)) { psql_error("\\%s: error\n", cmd); success = false; } free(result); if (prompt_text) free(prompt_text); free(opt); } } /* \pset -- set printing parameters */ else if (strcmp(cmd, "pset") == 0) { char *opt0 = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, false); char *opt1 = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, false); if (!opt0) { psql_error("\\%s: missing required argument\n", cmd); success = false; } else success = do_pset(opt0, opt1, &pset.popt, pset.quiet); free(opt0); free(opt1); } /* \q or \quit */ else if (strcmp(cmd, "q") == 0 || strcmp(cmd, "quit") == 0) status = PSQL_CMD_TERMINATE; /* reset(clear) the buffer */ else if (strcmp(cmd, "r") == 0 || strcmp(cmd, "reset") == 0) { resetPQExpBuffer(query_buf); psql_scan_reset(scan_state); if (!pset.quiet) puts(_("Query buffer reset (cleared).")); } /* \s save history in a file or show it on the screen */ else if (strcmp(cmd, "s") == 0) { char *fname = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, true); #if defined(WIN32) && !defined(__CYGWIN__) /* * XXX This does not work for all terminal environments or for output * containing non-ASCII characters; see comments in simple_prompt(). */ #define DEVTTY "con" #else #define DEVTTY "/dev/tty" #endif expand_tilde(&fname); /* This scrolls off the screen when using /dev/tty */ success = saveHistory(fname ? fname : DEVTTY, -1, false, false); if (success && !pset.quiet && fname) printf(gettext("Wrote history to file \"%s/%s\".\n"), pset.dirname ? pset.dirname : ".", fname); if (!fname) putchar('\n'); free(fname); } /* \set -- generalized set variable/option command */ else if (strcmp(cmd, "set") == 0) { char *opt0 = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, false); if (!opt0) { /* list all variables */ PrintVariables(pset.vars); success = true; } else { /* * Set variable to the concatenation of the arguments. */ char *newval; char *opt; opt = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, false); newval = pg_strdup(opt ? opt : ""); free(opt); while ((opt = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, false))) { newval = realloc(newval, strlen(newval) + strlen(opt) + 1); if (!newval) { psql_error("out of memory\n"); exit(EXIT_FAILURE); } strcat(newval, opt); free(opt); } if (!SetVariable(pset.vars, opt0, newval)) { psql_error("\\%s: error\n", cmd); success = false; } free(newval); } free(opt0); } /* \t -- turn off headers and row count */ else if (strcmp(cmd, "t") == 0) { char *opt = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, true); success = do_pset("tuples_only", opt, &pset.popt, pset.quiet); free(opt); } /* \T -- define html <table ...> attributes */ else if (strcmp(cmd, "T") == 0) { char *value = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, false); success = do_pset("tableattr", value, &pset.popt, pset.quiet); free(value); } /* \timing -- toggle timing of queries */ else if (strcmp(cmd, "timing") == 0) { char *opt = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, false); if (opt) pset.timing = ParseVariableBool(opt); else pset.timing = !pset.timing; if (!pset.quiet) { if (pset.timing) puts(_("Timing is on.")); else puts(_("Timing is off.")); } free(opt); } /* \unset */ else if (strcmp(cmd, "unset") == 0) { char *opt = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, false); if (!opt) { psql_error("\\%s: missing required argument\n", cmd); success = false; } else if (!SetVariable(pset.vars, opt, NULL)) { psql_error("\\%s: error\n", cmd); success = false; } free(opt); } /* \w -- write query buffer to file */ else if (strcmp(cmd, "w") == 0 || strcmp(cmd, "write") == 0) { FILE *fd = NULL; bool is_pipe = false; char *fname = NULL; if (!query_buf) { psql_error("no query buffer\n"); status = PSQL_CMD_ERROR; } else { fname = psql_scan_slash_option(scan_state, OT_FILEPIPE, NULL, true); expand_tilde(&fname); if (!fname) { psql_error("\\%s: missing required argument\n", cmd); success = false; } else { if (fname[0] == '|') { is_pipe = true; fd = popen(&fname[1], "w"); } else { canonicalize_path(fname); fd = fopen(fname, "w"); } if (!fd) { psql_error("%s: %s\n", fname, strerror(errno)); success = false; } } } if (fd) { int result; if (query_buf && query_buf->len > 0) fprintf(fd, "%s\n", query_buf->data); if (is_pipe) result = pclose(fd); else result = fclose(fd); if (result == EOF) { psql_error("%s: %s\n", fname, strerror(errno)); success = false; } } free(fname); } /* \x -- toggle expanded table representation */ else if (strcmp(cmd, "x") == 0) { char *opt = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, true); success = do_pset("expanded", opt, &pset.popt, pset.quiet); free(opt); } /* \z -- list table rights (equivalent to \dp) */ else if (strcmp(cmd, "z") == 0) { char *pattern = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, true); success = permissionsList(pattern); if (pattern) free(pattern); } /* \! -- shell escape */ else if (strcmp(cmd, "!") == 0) { char *opt = psql_scan_slash_option(scan_state, OT_WHOLE_LINE, NULL, false); success = do_shell(opt); free(opt); } /* \? -- slash command help */ else if (strcmp(cmd, "?") == 0) slashUsage(pset.popt.topt.pager); #if 0 /* * These commands don't do anything. I just use them to test the parser. */ else if (strcmp(cmd, "void") == 0 || strcmp(cmd, "#") == 0) { int i = 0; char *value; while ((value = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, true))) { fprintf(stderr, "+ opt(%d) = |%s|\n", i++, value); free(value); } } #endif else status = PSQL_CMD_UNKNOWN; if (!success) status = PSQL_CMD_ERROR; return status; }
int main(int argc, char *argv[]) { static int if_exists = 0; static struct option long_options[] = { {"host", required_argument, NULL, 'h'}, {"port", required_argument, NULL, 'p'}, {"username", required_argument, NULL, 'U'}, {"no-password", no_argument, NULL, 'w'}, {"password", no_argument, NULL, 'W'}, {"echo", no_argument, NULL, 'e'}, {"interactive", no_argument, NULL, 'i'}, {"if-exists", no_argument, &if_exists, 1}, {"maintenance-db", required_argument, NULL, 2}, {NULL, 0, NULL, 0} }; const char *progname; int optindex; int c; char *dbname = NULL; char *maintenance_db = NULL; char *host = NULL; char *port = NULL; char *username = NULL; enum trivalue prompt_password = TRI_DEFAULT; bool echo = false; bool interactive = false; PQExpBufferData sql; PGconn *conn; PGresult *result; progname = get_progname(argv[0]); set_pglocale_pgservice(argv[0], PG_TEXTDOMAIN("pgscripts")); handle_help_version_opts(argc, argv, "dropdb", help); while ((c = getopt_long(argc, argv, "h:p:U:wWei", long_options, &optindex)) != -1) { switch (c) { case 'h': host = pg_strdup(optarg); break; case 'p': port = pg_strdup(optarg); break; case 'U': username = pg_strdup(optarg); break; case 'w': prompt_password = TRI_NO; break; case 'W': prompt_password = TRI_YES; break; case 'e': echo = true; break; case 'i': interactive = true; break; case 0: /* this covers the long options */ break; case 2: maintenance_db = pg_strdup(optarg); break; default: fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname); exit(1); } } switch (argc - optind) { case 0: fprintf(stderr, _("%s: missing required argument database name\n"), progname); fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname); exit(1); case 1: dbname = 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 (interactive) { printf(_("Database \"%s\" will be permanently removed.\n"), dbname); if (!yesno_prompt("Are you sure?")) exit(0); } initPQExpBuffer(&sql); appendPQExpBuffer(&sql, "DROP DATABASE %s%s;", (if_exists ? "IF EXISTS " : ""), fmtId(dbname)); /* Avoid trying to drop postgres db while we are connected to it. */ if (maintenance_db == NULL && strcmp(dbname, "postgres") == 0) maintenance_db = "template1"; conn = connectMaintenanceDatabase(maintenance_db, host, port, username, prompt_password, progname); if (echo) printf("%s\n", sql.data); result = PQexec(conn, sql.data); if (PQresultStatus(result) != PGRES_COMMAND_OK) { fprintf(stderr, _("%s: database removal failed: %s"), progname, PQerrorMessage(conn)); PQfinish(conn); exit(1); } PQclear(result); PQfinish(conn); exit(0); }