/*
* 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);
}
