Re: [HACKERS] pg_dump roles support

--- postgresql-8.3.1.orig/src/bin/pg_dump/pg_dump.c 2008-01-30 19:35:55.000000000 +0100
+++ postgresql-8.3.1/src/bin/pg_dump/pg_dump.c 2008-08-26 12:26:56.000000000 +0200
@@ -208,6 +208,7 @@
const char *pgport = NULL;
const char *username = NULL;
const char *dumpencoding = NULL;
+ const char *pgrole = NULL;
const char *std_strings;
bool oids = false;
TableInfo *tblinfo;
@@ -258,6 +259,7 @@
{"no-acl", no_argument, NULL, 'x'},
{"compress", required_argument, NULL, 'Z'},
{"encoding", required_argument, NULL, 'E'},
+ {"role", required_argument, NULL, 'r'},
{"help", no_argument, NULL, '?'},
{"version", no_argument, NULL, 'V'},

@@ -302,7 +304,7 @@
}
}

- while ((c = getopt_long(argc, argv, "abcCdDE:f:F:h:in:N:oOp:RsS:t:T:U:vWxX:Z:",
+ while ((c = getopt_long(argc, argv, "abcCdDE:f:F:h:in:N:oOp:r:RsS:t:T:U:vWxX:Z:",
long_options, &optindex)) != -1)
{
switch (c)
@@ -374,6 +376,10 @@
pgport = optarg;
break;

+ case 'r': /* role */
+ pgrole = optarg;
+ break;
+
case 'R':
/* no-op, still accepted for backwards compatibility */
break;
@@ -539,6 +545,18 @@
exit(1);
}
}
+
+ /* Set the role if requested */
+ if (pgrole)
+ {
+ PQExpBuffer roleQry = createPQExpBuffer();
+ appendPQExpBuffer(roleQry, "SET ROLE TO %s;\n", fmtId(pgrole));
+ PGresult *res = PQexec(g_conn, roleQry->data);
+ check_sql_result(res, g_conn, roleQry->data, PGRES_COMMAND_OK);
+
+ PQclear(res);
+ destroyPQExpBuffer(roleQry);
+ }

/*
* Get the active encoding and the standard_conforming_strings setting, so
@@ -771,6 +789,8 @@
printf(_(" --use-set-session-authorization\n"
" use SESSION AUTHORIZATION commands instead of\n"
" ALTER OWNER commands to set ownership\n"));
+ printf(_(" -r, --role set role before dump\n"));
+

printf(_("\nConnection options:\n"));
printf(_(" -h, --host=HOSTNAME database server host or socket directory\n"));
Hello,

Stephen Frost wrote:
> As I discuss above, it'd be really nice have a --role or similar option
> to ask pg_dump to set role to a particular user before dumping the
> database.

I created a patch to set the role to a specified name just after the db connection.
Please review it for possible upstream inclusion.

Regards,
Laszlo Benedek