pg_dump roles support
Greetings,
Discussing psql options made me recall an annoying problem that we've
run into. There's no way (unless it was added to 8.3 and I missed it,
but I don't think so) to tell pg_dump 'switch to this role before
doing anything else'. That's very frustrating when you use no-inherit
roles for admins. eg:
create role admin with noinherit;
grant postgres to admin;
grant admin to joesysadmin;
pg_dump -U joesysadmin mydb;
Fails because joesysadmin hasn't got rights to everything directly.
It'd be nice if pg_dump could take a '-r postgres' to 'set role' to
a role which has the necessary permissions before locking all the
tables and whatnot. The same 'set role' would also be included at the
top of the resulting dump file. We could have a seperate flag for
that but I don't think it's necessary.
Comments?
I doubt there'd be very much code involved but I'd be willing to write
a patch if people agree with the general idea/approach.
Thanks,
Stephen
Stephen Frost <sfrost@snowman.net> writes:
create role admin with noinherit;
grant postgres to admin;
grant admin to joesysadmin;
pg_dump -U joesysadmin mydb;
Fails because joesysadmin hasn't got rights to everything directly.
Seems like the correct answer to that is "use a saner role
configuration".
regards, tom lane
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
Stephen Frost <sfrost@snowman.net> writes:
create role admin with noinherit;
grant postgres to admin;
grant admin to joesysadmin;pg_dump -U joesysadmin mydb;
Fails because joesysadmin hasn't got rights to everything directly.
Seems like the correct answer to that is "use a saner role
configuration".
Funny, it's exactly the type of setup described here:
http://www.postgresql.org/docs/8.2/interactive/role-membership.html
Far as I can tell anyway. What would you suggest? The point here is
that joesysadmin shouldn't get full postgres privs on login since most
of the time he won't need them. When he does need them, he can do a
'set role postgres', do what he needs to do and then 'reset role' when
he's done. Minimizing the amount of time with superuser privs is a good
thing in general, I would think.
Thanks,
Stephen
Stephen Frost <sfrost@snowman.net> writes:
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
Seems like the correct answer to that is "use a saner role
configuration".
Far as I can tell anyway. What would you suggest? The point here is
that joesysadmin shouldn't get full postgres privs on login since most
of the time he won't need them.
It's sane to set up a manually-used admin account that way, I agree.
What doesn't follow is that an account configured for manual use should
be used for non-interactive stuff like pg_dump.
regards, tom lane
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
Stephen Frost <sfrost@snowman.net> writes:
Far as I can tell anyway. What would you suggest? The point here is
that joesysadmin shouldn't get full postgres privs on login since most
of the time he won't need them.It's sane to set up a manually-used admin account that way, I agree.
What doesn't follow is that an account configured for manual use should
be used for non-interactive stuff like pg_dump.
I strongly disagree that pg_dump isn't to be used manually, which I
think is what you were getting at. We use it a great deal to dump
individual schemas and copy them between systems. We *don't* use it
anymore for full database dumps (something which was done in the past)
because we use PITR instead. In fact, I encourage my users to use it to
copy schema structures around when they need a seperate area for
development or testing.
What's frustrating is when an admin needs to copy a schema that he
doesn't directly have rights to (another user's schema, or a schema used
by a site or something) he has to login to the database server
(something I'd like to minimize anyway- almost everything can easily be
done from psql), su to root, su to postgres, do the pg_dump on the box,
and then work out copying it off the box.
I suppose I could write my own pg_dump that actually used psql
underneath or add it's functionality to our perl toolkit (it's damn
handy to be able to change permissions on every table in a schema with
one command, and it takes role as an argument, heh), but rewriting the
dependency handling and whatnot isn't something I'm really keen to do.
Thanks,
Stephen
Greetings,
* Stephen Frost (sfrost@snowman.net) wrote:
Discussing psql options made me recall an annoying problem that we've
run into. There's no way (unless it was added to 8.3 and I missed it,
but I don't think so) to tell pg_dump 'switch to this role before
doing anything else'. That's very frustrating when you use no-inherit
roles for admins. eg:
I've looked into using PGOPTIONS to set the role, and it doesn't seem to
be possible because when we're processing the backend command-line
options we're not yet in a transaction state, so variable.c:assign_role
will always come back with NULL and you get:
vardamir:/home/sfrost> PGOPTIONS="-c role=postgres" psql -d networx -h vardamir
psql: FATAL: invalid value for parameter "role": "postgres"
In current CVS the relevant lines in variable.c are around 868. That's
my best guess as to what's happening anyway, I havn't had a chance to
actually hook up a debugger and trace it.
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.
Thanks!
Stephen
Stephen Frost <sfrost@snowman.net> writes:
Discussing psql options made me recall an annoying problem that we've
run into. There's no way (unless it was added to 8.3 and I missed it,
but I don't think so) to tell pg_dump 'switch to this role before
doing anything else'. That's very frustrating when you use no-inherit
roles for admins. eg:
I've looked into using PGOPTIONS to set the role, and it doesn't seem to
be possible because when we're processing the backend command-line
options we're not yet in a transaction state, so variable.c:assign_role
will always come back with NULL and you get:
vardamir:/home/sfrost> PGOPTIONS="-c role=postgres" psql -d networx -h vardamir
psql: FATAL: invalid value for parameter "role": "postgres"
FWIW, I found by experimentation that ALTER USER ... SET ROLE does work
to cause a SET ROLE at login, though that might be a bit useless for
your purposes --- you'd more or less need a dedicated userid for
pg_dump.
regards, tom lane
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
Attachments:
pg_dump.role.patchtext/x-patch; name=pg_dump.role.patchDownload
--- 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,
daveg wrote:
I created a patch to set the role to a specified name just after the db
connection.I was going to do this, but you have beat me to it. You will want to update
the sgml documentation, and pg_dumpall as well.-dg
Ok, here is the next one.
pg_dumpall now just passes the --role option to pg_dump. What do you
think, is it enough
or it should issue the SET ROLE TO ... command in its own session too?
Laszlo Benedek
Attachments:
pg_dump.role.patchtext/x-patch; name=pg_dump.role.patchDownload
diff -ur postgresql-8.3.1.orig/doc/src/sgml/backup.sgml postgresql-8.3.1/doc/src/sgml/backup.sgml
--- postgresql-8.3.1.orig/doc/src/sgml/backup.sgml 2008-03-07 02:46:50.000000000 +0100
+++ postgresql-8.3.1/doc/src/sgml/backup.sgml 2008-08-27 15:29:26.000000000 +0200
@@ -68,7 +68,9 @@
<application>pg_dump</> will by default connect with the database
user name that is equal to the current operating system user name. To override
this, either specify the <option>-U</option> option or set the
- environment variable <envar>PGUSER</envar>. Remember that
+ environment variable <envar>PGUSER</envar>. It is possible to change
+ the current user identifier of the dump session by using the
+ <option>--role</option> option. Remember that
<application>pg_dump</> connections are subject to the normal
client authentication mechanisms (which are described in <xref
linkend="client-authentication">).
diff -ur postgresql-8.3.1.orig/doc/src/sgml/ref/pg_dump.sgml postgresql-8.3.1/doc/src/sgml/ref/pg_dump.sgml
--- postgresql-8.3.1.orig/doc/src/sgml/ref/pg_dump.sgml 2007-12-11 20:57:32.000000000 +0100
+++ postgresql-8.3.1/doc/src/sgml/ref/pg_dump.sgml 2008-08-27 15:58:05.000000000 +0200
@@ -522,6 +522,18 @@
</varlistentry>
<varlistentry>
+ <term><option>--role=<replaceable class="parameter">rolename</replaceable></option></term>
+ <listitem>
+ <para>
+ Specifies the user identifier used by the dump session. This will cause
+ <application>pg_dump</application> to issue a
+ <command>SET ROLE TO <replaceable class="parameter">rolename</replaceable></command>
+ command just after a successful database connection.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><option>-v</></term>
<term><option>--verbose</></term>
<listitem>
diff -ur postgresql-8.3.1.orig/doc/src/sgml/ref/pg_dumpall.sgml postgresql-8.3.1/doc/src/sgml/ref/pg_dumpall.sgml
--- postgresql-8.3.1.orig/doc/src/sgml/ref/pg_dumpall.sgml 2007-12-11 20:57:32.000000000 +0100
+++ postgresql-8.3.1/doc/src/sgml/ref/pg_dumpall.sgml 2008-08-27 15:49:18.000000000 +0200
@@ -248,6 +248,18 @@
</varlistentry>
<varlistentry>
+ <term><option>--role=<replaceable class="parameter">rolename</replaceable></option></term>
+ <listitem>
+ <para>
+ Specifies the user identifier used by the dump session. This option will be passed
+ to <application>pg_dump</> and will cause <application>pg_dump</application> to issue a
+ <command>SET ROLE TO <replaceable class="parameter">rolename</replaceable></command>
+ command just after a successful database connection.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><option>-v</></term>
<term><option>--verbose</></term>
<listitem>
diff -ur postgresql-8.3.1.orig/doc/src/sgml/release.sgml postgresql-8.3.1/doc/src/sgml/release.sgml
--- postgresql-8.3.1.orig/doc/src/sgml/release.sgml 2008-03-14 00:47:59.000000000 +0100
+++ postgresql-8.3.1/doc/src/sgml/release.sgml 2008-08-27 16:06:12.000000000 +0200
@@ -2395,6 +2395,13 @@
<listitem>
<para>
+ Add <literal>--role</> option to <application>pg_dump</application> and
+ <application>pg_dumpall</application> (Benedek Laszlo)
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
Add <literal>--tablespaces-only</> and <literal>--roles-only</>
options to <application>pg_dumpall</application> (Dave Page)
</para>
diff -ur postgresql-8.3.1.orig/src/bin/pg_dump/pg_dump.c postgresql-8.3.1/src/bin/pg_dump/pg_dump.c
--- 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-27 15:10:41.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' + 0x80},
{"help", no_argument, NULL, '?'},
{"version", no_argument, NULL, 'V'},
@@ -437,6 +439,10 @@
/* This covers the long options equivalent to -X xxx. */
break;
+ case 'r' + 0x80: /* role */
+ pgrole = optarg;
+ break;
+
default:
fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
exit(1);
@@ -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,7 @@
printf(_(" --use-set-session-authorization\n"
" use SESSION AUTHORIZATION commands instead of\n"
" ALTER OWNER commands to set ownership\n"));
+ printf(_(" --role set role before dump\n"));
printf(_("\nConnection options:\n"));
printf(_(" -h, --host=HOSTNAME database server host or socket directory\n"));
diff -ur postgresql-8.3.1.orig/src/bin/pg_dump/pg_dumpall.c postgresql-8.3.1/src/bin/pg_dump/pg_dumpall.c
--- postgresql-8.3.1.orig/src/bin/pg_dump/pg_dumpall.c 2008-01-01 20:45:55.000000000 +0100
+++ postgresql-8.3.1/src/bin/pg_dump/pg_dumpall.c 2008-08-27 15:12:00.000000000 +0200
@@ -112,6 +112,7 @@
{"password", no_argument, NULL, 'W'},
{"no-privileges", no_argument, NULL, 'x'},
{"no-acl", no_argument, NULL, 'x'},
+ {"role", required_argument, NULL, 'r' + 0x80},
/*
* the following options don't have an equivalent short option letter
@@ -241,6 +242,14 @@
roles_only = true;
break;
+ case 'r' + 0x80:
+#ifndef WIN32
+ appendPQExpBuffer(pgdumpopts, " --role '%s'", optarg);
+#else
+ appendPQExpBuffer(pgdumpopts, " --role \"%s\"", optarg);
+#endif
+ break;
+
case 's':
schema_only = true;
appendPQExpBuffer(pgdumpopts, " -s");
@@ -505,7 +514,8 @@
printf(_(" --use-set-session-authorization\n"
" use SESSION AUTHORIZATION commands instead of\n"
" OWNER TO commands\n"));
-
+ printf(_(" --role set role before dump\n"));
+
printf(_("\nConnection options:\n"));
printf(_(" -h, --host=HOSTNAME database server host or socket directory\n"));
printf(_(" -l, --database=DBNAME specify an alternative default database\n"));
Import Notes
Reply to msg id not found: 20080827003536.GF28743@sonic.net
=?ISO-8859-1?Q?Benedek_L=E1szl=F3?= <laci@benedekl.tvnetwork.hu> writes:
pg_dumpall now just passes the --role option to pg_dump. What do you
think, is it enough
or it should issue the SET ROLE TO ... command in its own session too?
I think it would have to, in the general case. Consider the possibility
that someone has restricted access to the system catalogs, for instance.
You have missed an important component of Stephen's original proposal,
which was the point that something similar is needed on the restore
side. This is a little bit tricky since the context at restore time
is not necessarily the same as the context at dump time. When using
an archive file it's not a problem: the behavior can be driven off a
--role switch to pg_restore, and this is independent of what pg_dump
did. In a dump to plain text, though, I'm not sure what to do. The
simplest design would have pg_dump's --role switch control both
what it does in its own connection to the source database, and what it
puts into the output script. I'm not sure that's adequate though.
Is it worth having two different switches for the two cases? If we
think it's a corner case to need different role IDs, we could just
leave it like that and tell anyone who needs different behaviors that
they have to go through an archive file and pg_restore. Stephen,
you were the one who wanted this in the first place, what's your
use-cases look like?
Some other review nitpicking:
The documentation part of the patch is well short of acceptable IMHO,
since it gives no hint of what this switch might be good for, and
indeed encourages the user to confuse it with the -U switch by injecting
a mention of it into the middle of a discussion about -U.
It is not normally considered appropriate for individual patches to
edit the release notes; and it's DEFINITELY not appropriate to put
a mention of a feature addition into the wrong section of the release
notes.
+ {"role", required_argument, NULL, 'r' + 0x80},
This is not a good choice of option code IMHO ... what if the value is
stored in a signed char on some machines? If you can't find a free
letter you like, use a small integer code, as you can find being done
elsewhere.
BTW, the patch fails to compile on a strict ANSI C compiler, because
you are using a C++-ism of declaring a variable mid-block.
regards, tom lane
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
=?ISO-8859-1?Q?Benedek_L=E1szl=F3?= <laci@benedekl.tvnetwork.hu> writes:
pg_dumpall now just passes the --role option to pg_dump. What do you
think, is it enough
or it should issue the SET ROLE TO ... command in its own session too?I think it would have to, in the general case. Consider the possibility
that someone has restricted access to the system catalogs, for instance.
I would agree with this. pg_dumpall should do the 'set role' in its
session as well.
You have missed an important component of Stephen's original proposal,
which was the point that something similar is needed on the restore
side. This is a little bit tricky since the context at restore time
is not necessarily the same as the context at dump time. When using
an archive file it's not a problem: the behavior can be driven off a
--role switch to pg_restore, and this is independent of what pg_dump
did. In a dump to plain text, though, I'm not sure what to do. The
simplest design would have pg_dump's --role switch control both
what it does in its own connection to the source database, and what it
puts into the output script. I'm not sure that's adequate though.
This makes sense to me and I feel it's adequate. If necessary, people
can post-process their .sql files using sed or something similar.
That's made reasonably easy by having a 'set role' in the .sql file. I
actively dislike the idea that pg_restore would modify the input stream
from a text file, even if it was passed a --role switch.
Is it worth having two different switches for the two cases? If we
think it's a corner case to need different role IDs, we could just
leave it like that and tell anyone who needs different behaviors that
they have to go through an archive file and pg_restore. Stephen,
you were the one who wanted this in the first place, what's your
use-cases look like?
My primary use cases are performing a pg_dump when logging in as one
user but needing the permissions of another role, followed by loading
the data into another system when logging in as one user and needing to
set role first to another. In at least 90% of those cases, that role is
postgres, and in the other 10% most, if not all, are the same role on
both sides. There are a few cases where we might change the restore-as
role away from the dumped-as role, but we're happy to use pg_restore to
handle that, or take care of changing the role in the .sql file (which
is what we tend to use, honestly) using sed or similar.
Alot of this is driven from the fact that we don't allow admins to
remotely connect directly as postgres (akin to disabling remote root
logins in sshd_config via PermitRootLogin, and for the same reasons).
They must authenticate and connect as their own user first and then use
'set role postgres;' to gain superuser rights. Not being able to have
pg_dump do that set role has been quite frustrating as we use it
extensively for transferring data between systems.
Some other review nitpicking:
I agree with the other comments.
Thanks,
Stephen
Tom Lane wrote:
Some other review nitpicking:
Thank you for your review. I really need all suggestions, since I never
posted any patch to the community before.
The next patch will emit the SET ROLE command in the generated dump,
as you and Stephen said. This will fit in my workflow too, since mostly I
need to restore using the same role as the dump.
Regards, Laszlo Benedek
Hello,
I modified my previous patch supporting the --role option in pg_dump and
pg_dumpall.
The attached patch contains the following things:
- pg_dump and pg_dumpall accepts the --role=rolename parameter, and
sends a SET ROLE
command on their connections
- pg_dumpall passes this option to the called pg_dump process
- pg_dump emits the SET ROLE command into the archive
- sgml documentation of this feature
Please review it
Regards, Laszlo Benedek
Attachments:
pg_dump.role.patchtext/x-patch; name=pg_dump.role.patchDownload
diff --git a/doc/src/sgml/ref/pg_dump.sgml b/doc/src/sgml/ref/pg_dump.sgml
index 2e30906..de139c3 100644
--- a/doc/src/sgml/ref/pg_dump.sgml
+++ b/doc/src/sgml/ref/pg_dump.sgml
@@ -698,6 +698,22 @@ PostgreSQL documentation
</para>
</listitem>
</varlistentry>
+
+ <varlistentry>
+ <term><option>--role=<replaceable class="parameter">rolename</replaceable></option></term>
+ <listitem>
+ <para>
+ Specifies the user identifier used by the dump session. This cause
+ <application>pg_dump</application> to issue a
+ <command>SET ROLE TO <replaceable class="parameter">rolename</replaceable></command>
+ command just after a successful database connection. It is useful in cases when
+ the logged in user specified by the -U option has not enough privileges needed by
+ <application>pg_dump</application> but can switch to a role with the needed rights.
+ The SET ROLE command is reserved in the archive because most of the time this
+ user identifier also needed for the restore to succeed.
+ </para>
+ </listitem>
+ </varlistentry>
</variablelist>
</para>
</refsect1>
diff --git a/doc/src/sgml/ref/pg_dumpall.sgml b/doc/src/sgml/ref/pg_dumpall.sgml
index ec40890..16f3e0b 100644
--- a/doc/src/sgml/ref/pg_dumpall.sgml
+++ b/doc/src/sgml/ref/pg_dumpall.sgml
@@ -129,7 +129,7 @@ PostgreSQL documentation
</para>
</listitem>
</varlistentry>
-
+
<varlistentry>
<term><option>-f <replaceable class="parameter">filename</replaceable></option></term>
<term><option>--file=<replaceable class="parameter">filename</replaceable></option></term>
@@ -183,7 +183,7 @@ PostgreSQL documentation
Do not output commands to set
ownership of objects to match the original database.
By default, <application>pg_dumpall</application> issues
- <command>ALTER OWNER</> or
+ <command>ALTER OWNER</> or
<command>SET SESSION AUTHORIZATION</command>
statements to set ownership of created schema elements.
These statements
@@ -266,7 +266,7 @@ PostgreSQL documentation
</listitem>
</varlistentry>
- <varlistentry>
+ <varlistentry>
<term><option>-v</></term>
<term><option>--verbose</></term>
<listitem>
@@ -354,7 +354,7 @@ PostgreSQL documentation
</para>
</listitem>
</varlistentry>
-
+
<varlistentry>
<term>-l <replaceable>dbname</replaceable></term>
<term>--database=<replaceable>dbname</replaceable></term>
@@ -397,7 +397,7 @@ PostgreSQL documentation
<listitem>
<para>
Force <application>pg_dumpall</application> to prompt for a
- password before connecting to a database.
+ password before connecting to a database.
</para>
<para>
@@ -417,6 +417,21 @@ PostgreSQL documentation
</para>
</listitem>
</varlistentry>
+
+ <varlistentry>
+ <term><option>--role=<replaceable class="parameter">rolename</replaceable></option></term>
+ <listitem>
+ <para>
+ Specifies the user identifier used by the dump session. This option is passed
+ to <application>pg_dump</> too and cause these applications to issue a
+ <command>SET ROLE TO <replaceable class="parameter">rolename</replaceable></command>
+ command just after a successful database connection. It is useful in cases when
+ the logged in user specified by the -U option has not enough privileges needed by
+ <application>pg_dumpall</application> but can switch to a role with the needed rights.
+ The SET ROLE command is reserved in the archive by <application>pg_dump</application>.
+ </para>
+ </listitem>
+ </varlistentry>
</variablelist>
</para>
</refsect1>
@@ -503,6 +518,6 @@ PostgreSQL documentation
Check <xref linkend="app-pgdump"> for details on possible
error conditions.
</para>
- </refsect1>
+ </refsect1>
</refentry>
diff --git a/src/bin/pg_dump/pg_backup.h b/src/bin/pg_dump/pg_backup.h
index c57bb22..cbe4d46 100644
--- a/src/bin/pg_dump/pg_backup.h
+++ b/src/bin/pg_dump/pg_backup.h
@@ -70,6 +70,8 @@ typedef struct _Archive
int encoding; /* libpq code for client_encoding */
bool std_strings; /* standard_conforming_strings */
+ const char *rolename; /* role name */
+
/* error handling */
bool exit_on_error; /* whether to exit on SQL errors... */
int n_errors; /* number of errors (if no die) */
diff --git a/src/bin/pg_dump/pg_backup_archiver.c b/src/bin/pg_dump/pg_backup_archiver.c
index 7bd44f2..6f6ed2f 100644
--- a/src/bin/pg_dump/pg_backup_archiver.c
+++ b/src/bin/pg_dump/pg_backup_archiver.c
@@ -56,6 +56,7 @@ static void _selectOutputSchema(ArchiveHandle *AH, const char *schemaName);
static void _selectTablespace(ArchiveHandle *AH, const char *tablespace);
static void processEncodingEntry(ArchiveHandle *AH, TocEntry *te);
static void processStdStringsEntry(ArchiveHandle *AH, TocEntry *te);
+static void processRolenameEntry(ArchiveHandle *AH, TocEntry *te);
static teReqs _tocEntryRequired(TocEntry *te, RestoreOptions *ropt, bool include_acls);
static void _disableTriggersIfNecessary(ArchiveHandle *AH, TocEntry *te, RestoreOptions *ropt);
static void _enableTriggersIfNecessary(ArchiveHandle *AH, TocEntry *te, RestoreOptions *ropt);
@@ -1979,6 +1980,8 @@ ReadToc(ArchiveHandle *AH)
processEncodingEntry(AH, te);
else if (strcmp(te->desc, "STDSTRINGS") == 0)
processStdStringsEntry(AH, te);
+ else if (strcmp(te->desc, "ROLENAME") == 0)
+ processRolenameEntry(AH, te);
}
}
@@ -2026,15 +2029,39 @@ processStdStringsEntry(ArchiveHandle *AH, TocEntry *te)
te->defn);
}
+static void
+processRolenameEntry(ArchiveHandle *AH, TocEntry *te)
+{
+ /* te->defn should have the form SET role = 'foo'; */
+ char *defn = strdup(te->defn);
+ char *ptr1;
+ char *ptr2 = NULL;
+
+ ptr1 = strchr(defn, '\'');
+ if (ptr1)
+ ptr2 = strchr(++ptr1, '\'');
+ if (ptr2)
+ {
+ *ptr2 = '\0';
+ AH->public.rolename = strdup(ptr1);
+ free(defn);
+ }
+ else
+ free(defn);
+ die_horribly(AH, modulename, "invalid ROLENAME item: %s\n",
+ te->defn);
+}
+
static teReqs
_tocEntryRequired(TocEntry *te, RestoreOptions *ropt, bool include_acls)
{
teReqs res = REQ_ALL;
- /* ENCODING and STDSTRINGS items are dumped specially, so always reject */
+ /* ENCODING, STDSTRINGS and ROLENAME items are dumped specially, so always reject */
if (strcmp(te->desc, "ENCODING") == 0 ||
- strcmp(te->desc, "STDSTRINGS") == 0)
- return 0;
+ strcmp(te->desc, "STDSTRINGS") == 0 ||
+ strcmp(te->desc, "ROLENAME") == 0)
+ return 0;
/* If it's an ACL, maybe ignore it */
if ((!include_acls || ropt->aclsSkip) && strcmp(te->desc, "ACL") == 0)
@@ -2146,6 +2173,11 @@ _doSetFixedOutputState(ArchiveHandle *AH)
ahprintf(AH, "SET standard_conforming_strings = %s;\n",
AH->public.std_strings ? "on" : "off");
+ /* Select the role to be used during restore */
+ if (AH->public.rolename)
+ ahprintf(AH, "SET role = %s;\n",
+ fmtId(AH->public.rolename));
+
/* Make sure function checking is disabled */
ahprintf(AH, "SET check_function_bodies = false;\n");
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 6c0f827..cd9ef9f 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -188,6 +188,7 @@ static int dumpBlobComments(Archive *AH, void *arg);
static void dumpDatabase(Archive *AH);
static void dumpEncoding(Archive *AH);
static void dumpStdStrings(Archive *AH);
+static void dumpRole(Archive *AH);
static const char *getAttrName(int attrnum, TableInfo *tblInfo);
static const char *fmtCopyColumnList(const TableInfo *ti);
static void do_sql_command(PGconn *conn, const char *query);
@@ -229,6 +230,10 @@ main(int argc, char **argv)
static int outputNoTablespaces = 0;
static int use_setsessauth = 0;
+ const char *pgrole = NULL;
+ PQExpBuffer roleQry;
+ PGresult *res;
+
static struct option long_options[] = {
{"data-only", no_argument, NULL, 'a'},
{"blobs", no_argument, NULL, 'b'},
@@ -269,6 +274,7 @@ main(int argc, char **argv)
{"lock-wait-timeout", required_argument, NULL, 2},
{"no-tablespaces", no_argument, &outputNoTablespaces, 1},
{"use-set-session-authorization", no_argument, &use_setsessauth, 1},
+ {"role", required_argument, NULL, 3},
{NULL, 0, NULL, 0}
};
@@ -446,6 +452,10 @@ main(int argc, char **argv)
lockWaitTimeout = optarg;
break;
+ case 3: /* role */
+ pgrole = optarg;
+ break;
+
default:
fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
exit(1);
@@ -555,6 +565,20 @@ main(int argc, char **argv)
}
}
+ /* Set the role if requested */
+ if (pgrole)
+ {
+ roleQry = createPQExpBuffer();
+ appendPQExpBuffer(roleQry, "SET ROLE TO %s;\n", fmtId(pgrole));
+ res = PQexec(g_conn, roleQry->data);
+ check_sql_result(res, g_conn, roleQry->data, PGRES_COMMAND_OK);
+ PQclear(res);
+ destroyPQExpBuffer(roleQry);
+ g_fout->rolename = pgrole;
+ } else {
+ g_fout->rolename = NULL;
+ }
+
/*
* Get the active encoding and the standard_conforming_strings setting, so
* we know how to escape strings.
@@ -586,7 +610,7 @@ main(int argc, char **argv)
*/
if (g_fout->remoteVersion >= 70300)
do_sql_command(g_conn, "SET statement_timeout = 0");
-
+
/*
* Start serializable transaction to dump consistent data.
*/
@@ -715,6 +739,8 @@ main(int argc, char **argv)
/* First the special ENCODING and STDSTRINGS entries. */
dumpEncoding(g_fout);
dumpStdStrings(g_fout);
+ if (pgrole)
+ dumpRole(g_fout);
/* The database item is always next, unless we don't want it at all */
if (include_everything && !dataOnly)
@@ -800,6 +826,7 @@ help(const char *progname)
printf(_(" --use-set-session-authorization\n"
" use SESSION AUTHORIZATION commands instead of\n"
" ALTER OWNER commands to set ownership\n"));
+ printf(_(" --role set role before dump\n"));
printf(_("\nConnection options:\n"));
printf(_(" -h, --host=HOSTNAME database server host or socket directory\n"));
@@ -1800,6 +1827,32 @@ dumpStdStrings(Archive *AH)
/*
+ * dumpRole: put the role change into the archive
+ */
+static void
+dumpRole(Archive *AH)
+{
+ const char *rolename = AH->rolename;
+ PQExpBuffer qry = createPQExpBuffer();
+
+ if (g_verbose)
+ write_msg(NULL, "saving rolename = %s\n", rolename);
+
+ appendPQExpBuffer(qry, "SET role = ");
+ appendStringLiteralAH(qry, rolename, AH);
+ appendPQExpBuffer(qry, ";\n");
+
+ ArchiveEntry(AH, nilCatalogId, createDumpId(),
+ "ROLENAME", NULL, NULL, "",
+ false, "ROLENAME", qry->data, "", NULL,
+ NULL, 0,
+ NULL, NULL);
+
+ destroyPQExpBuffer(qry);
+}
+
+
+/*
* hasBlobs:
* Test whether database contains any large objects
*/
diff --git a/src/bin/pg_dump/pg_dumpall.c b/src/bin/pg_dump/pg_dumpall.c
index b00fb5a..cb8b5a1 100644
--- a/src/bin/pg_dump/pg_dumpall.c
+++ b/src/bin/pg_dump/pg_dumpall.c
@@ -89,6 +89,9 @@ main(int argc, char *argv[])
int c,
ret;
+ const char *pgrole = NULL;
+ PQExpBuffer roleQry;
+
static struct option long_options[] = {
{"data-only", no_argument, NULL, 'a'},
{"clean", no_argument, NULL, 'c'},
@@ -121,6 +124,7 @@ main(int argc, char *argv[])
{"no-tablespaces", no_argument, &no_tablespaces, 1},
{"use-set-session-authorization", no_argument, &use_setsessauth, 1},
{"lock-wait-timeout", required_argument, NULL, 2},
+ {"role", required_argument, NULL, 3},
{NULL, 0, NULL, 0}
};
@@ -289,7 +293,7 @@ main(int argc, char *argv[])
disable_dollar_quoting = 1;
else if (strcmp(optarg, "disable-triggers") == 0)
disable_triggers = 1;
- else if (strcmp(optarg, "no-tablespaces") == 0)
+ else if (strcmp(optarg, "no-tablespaces") == 0)
no_tablespaces = 1;
else if (strcmp(optarg, "use-set-session-authorization") == 0)
use_setsessauth = 1;
@@ -311,6 +315,15 @@ main(int argc, char *argv[])
appendPQExpBuffer(pgdumpopts, optarg);
break;
+ case 3:
+ pgrole = optarg;
+#ifndef WIN32
+ appendPQExpBuffer(pgdumpopts, " --role '%s'", optarg);
+#else
+ appendPQExpBuffer(pgdumpopts, " --role \"%s\"", optarg);
+#endif
+ break;
+
default:
fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
exit(1);
@@ -426,6 +439,15 @@ main(int argc, char *argv[])
if (!std_strings)
std_strings = "off";
+ /* Set the role if requested */
+ if (pgrole)
+ {
+ roleQry = createPQExpBuffer();
+ appendPQExpBuffer(roleQry, "SET ROLE TO %s;\n", fmtId(pgrole));
+ executeCommand(conn, roleQry->data);
+ destroyPQExpBuffer(roleQry);
+ }
+
fprintf(OPF, "--\n-- PostgreSQL database cluster dump\n--\n\n");
if (verbose)
dumpTimestamp("Started on");
@@ -516,6 +538,7 @@ help(void)
printf(_(" --use-set-session-authorization\n"
" use SESSION AUTHORIZATION commands instead of\n"
" OWNER TO commands\n"));
+ printf(_(" --role set role before dump\n"));
printf(_("\nConnection options:\n"));
printf(_(" -h, --host=HOSTNAME database server host or socket directory\n"));
@@ -1527,3 +1550,4 @@ dumpTimestamp(char *msg)
localtime(&now)) != 0)
fprintf(OPF, "-- %s %s\n\n", msg, buf);
}
+
Hello All,
in my last mail
http://www.mail-archive.com/pgsql-hackers@postgresql.org/msg114835.html
I have sent you a patch of pg_dump and pg_dumpall --role option support.
Nobody replied
or commented jet so now I send it again. The attached patch is the same
as the last one,
except it is against the current upstream HEAD.
The patch contains the following things:
- pg_dump and pg_dumpall accepts the --role=rolename parameter, and
sends a SET ROLE
command on their connections
- pg_dumpall passes this option to the called pg_dump process
- pg_dump emits the SET ROLE command into the archive
- sgml documentation of this feature
Summary:
doc/src/sgml/ref/pg_dump.sgml | 16 ++++++++++
doc/src/sgml/ref/pg_dumpall.sgml | 27 +++++++++++++----
src/bin/pg_dump/pg_backup.h | 2 +
src/bin/pg_dump/pg_backup_archiver.c | 38 +++++++++++++++++++++--
src/bin/pg_dump/pg_dump.c | 55
+++++++++++++++++++++++++++++++++-
src/bin/pg_dump/pg_dumpall.c | 26 +++++++++++++++-
6 files changed, 153 insertions(+), 11 deletions(-)
Thank you, best regards:
Laszlo Benedek
Attachments:
pg_dump.role.patchtext/x-patch; name=pg_dump.role.patchDownload
diff --git a/doc/src/sgml/ref/pg_dump.sgml b/doc/src/sgml/ref/pg_dump.sgml
index 2e30906..de139c3 100644
--- a/doc/src/sgml/ref/pg_dump.sgml
+++ b/doc/src/sgml/ref/pg_dump.sgml
@@ -698,6 +698,22 @@ PostgreSQL documentation
</para>
</listitem>
</varlistentry>
+
+ <varlistentry>
+ <term><option>--role=<replaceable class="parameter">rolename</replaceable></option></term>
+ <listitem>
+ <para>
+ Specifies the user identifier used by the dump session. This cause
+ <application>pg_dump</application> to issue a
+ <command>SET ROLE TO <replaceable class="parameter">rolename</replaceable></command>
+ command just after a successful database connection. It is useful in cases when
+ the logged in user specified by the -U option has not enough privileges needed by
+ <application>pg_dump</application> but can switch to a role with the needed rights.
+ The SET ROLE command is reserved in the archive because most of the time this
+ user identifier also needed for the restore to succeed.
+ </para>
+ </listitem>
+ </varlistentry>
</variablelist>
</para>
</refsect1>
diff --git a/doc/src/sgml/ref/pg_dumpall.sgml b/doc/src/sgml/ref/pg_dumpall.sgml
index ec40890..16f3e0b 100644
--- a/doc/src/sgml/ref/pg_dumpall.sgml
+++ b/doc/src/sgml/ref/pg_dumpall.sgml
@@ -129,7 +129,7 @@ PostgreSQL documentation
</para>
</listitem>
</varlistentry>
-
+
<varlistentry>
<term><option>-f <replaceable class="parameter">filename</replaceable></option></term>
<term><option>--file=<replaceable class="parameter">filename</replaceable></option></term>
@@ -183,7 +183,7 @@ PostgreSQL documentation
Do not output commands to set
ownership of objects to match the original database.
By default, <application>pg_dumpall</application> issues
- <command>ALTER OWNER</> or
+ <command>ALTER OWNER</> or
<command>SET SESSION AUTHORIZATION</command>
statements to set ownership of created schema elements.
These statements
@@ -266,7 +266,7 @@ PostgreSQL documentation
</listitem>
</varlistentry>
- <varlistentry>
+ <varlistentry>
<term><option>-v</></term>
<term><option>--verbose</></term>
<listitem>
@@ -354,7 +354,7 @@ PostgreSQL documentation
</para>
</listitem>
</varlistentry>
-
+
<varlistentry>
<term>-l <replaceable>dbname</replaceable></term>
<term>--database=<replaceable>dbname</replaceable></term>
@@ -397,7 +397,7 @@ PostgreSQL documentation
<listitem>
<para>
Force <application>pg_dumpall</application> to prompt for a
- password before connecting to a database.
+ password before connecting to a database.
</para>
<para>
@@ -417,6 +417,21 @@ PostgreSQL documentation
</para>
</listitem>
</varlistentry>
+
+ <varlistentry>
+ <term><option>--role=<replaceable class="parameter">rolename</replaceable></option></term>
+ <listitem>
+ <para>
+ Specifies the user identifier used by the dump session. This option is passed
+ to <application>pg_dump</> too and cause these applications to issue a
+ <command>SET ROLE TO <replaceable class="parameter">rolename</replaceable></command>
+ command just after a successful database connection. It is useful in cases when
+ the logged in user specified by the -U option has not enough privileges needed by
+ <application>pg_dumpall</application> but can switch to a role with the needed rights.
+ The SET ROLE command is reserved in the archive by <application>pg_dump</application>.
+ </para>
+ </listitem>
+ </varlistentry>
</variablelist>
</para>
</refsect1>
@@ -503,6 +518,6 @@ PostgreSQL documentation
Check <xref linkend="app-pgdump"> for details on possible
error conditions.
</para>
- </refsect1>
+ </refsect1>
</refentry>
diff --git a/src/bin/pg_dump/pg_backup.h b/src/bin/pg_dump/pg_backup.h
index c57bb22..cbe4d46 100644
--- a/src/bin/pg_dump/pg_backup.h
+++ b/src/bin/pg_dump/pg_backup.h
@@ -70,6 +70,8 @@ typedef struct _Archive
int encoding; /* libpq code for client_encoding */
bool std_strings; /* standard_conforming_strings */
+ const char *rolename; /* role name */
+
/* error handling */
bool exit_on_error; /* whether to exit on SQL errors... */
int n_errors; /* number of errors (if no die) */
diff --git a/src/bin/pg_dump/pg_backup_archiver.c b/src/bin/pg_dump/pg_backup_archiver.c
index 7bd44f2..6f6ed2f 100644
--- a/src/bin/pg_dump/pg_backup_archiver.c
+++ b/src/bin/pg_dump/pg_backup_archiver.c
@@ -56,6 +56,7 @@ static void _selectOutputSchema(ArchiveHandle *AH, const char *schemaName);
static void _selectTablespace(ArchiveHandle *AH, const char *tablespace);
static void processEncodingEntry(ArchiveHandle *AH, TocEntry *te);
static void processStdStringsEntry(ArchiveHandle *AH, TocEntry *te);
+static void processRolenameEntry(ArchiveHandle *AH, TocEntry *te);
static teReqs _tocEntryRequired(TocEntry *te, RestoreOptions *ropt, bool include_acls);
static void _disableTriggersIfNecessary(ArchiveHandle *AH, TocEntry *te, RestoreOptions *ropt);
static void _enableTriggersIfNecessary(ArchiveHandle *AH, TocEntry *te, RestoreOptions *ropt);
@@ -1979,6 +1980,8 @@ ReadToc(ArchiveHandle *AH)
processEncodingEntry(AH, te);
else if (strcmp(te->desc, "STDSTRINGS") == 0)
processStdStringsEntry(AH, te);
+ else if (strcmp(te->desc, "ROLENAME") == 0)
+ processRolenameEntry(AH, te);
}
}
@@ -2026,15 +2029,39 @@ processStdStringsEntry(ArchiveHandle *AH, TocEntry *te)
te->defn);
}
+static void
+processRolenameEntry(ArchiveHandle *AH, TocEntry *te)
+{
+ /* te->defn should have the form SET role = 'foo'; */
+ char *defn = strdup(te->defn);
+ char *ptr1;
+ char *ptr2 = NULL;
+
+ ptr1 = strchr(defn, '\'');
+ if (ptr1)
+ ptr2 = strchr(++ptr1, '\'');
+ if (ptr2)
+ {
+ *ptr2 = '\0';
+ AH->public.rolename = strdup(ptr1);
+ free(defn);
+ }
+ else
+ free(defn);
+ die_horribly(AH, modulename, "invalid ROLENAME item: %s\n",
+ te->defn);
+}
+
static teReqs
_tocEntryRequired(TocEntry *te, RestoreOptions *ropt, bool include_acls)
{
teReqs res = REQ_ALL;
- /* ENCODING and STDSTRINGS items are dumped specially, so always reject */
+ /* ENCODING, STDSTRINGS and ROLENAME items are dumped specially, so always reject */
if (strcmp(te->desc, "ENCODING") == 0 ||
- strcmp(te->desc, "STDSTRINGS") == 0)
- return 0;
+ strcmp(te->desc, "STDSTRINGS") == 0 ||
+ strcmp(te->desc, "ROLENAME") == 0)
+ return 0;
/* If it's an ACL, maybe ignore it */
if ((!include_acls || ropt->aclsSkip) && strcmp(te->desc, "ACL") == 0)
@@ -2146,6 +2173,11 @@ _doSetFixedOutputState(ArchiveHandle *AH)
ahprintf(AH, "SET standard_conforming_strings = %s;\n",
AH->public.std_strings ? "on" : "off");
+ /* Select the role to be used during restore */
+ if (AH->public.rolename)
+ ahprintf(AH, "SET role = %s;\n",
+ fmtId(AH->public.rolename));
+
/* Make sure function checking is disabled */
ahprintf(AH, "SET check_function_bodies = false;\n");
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 6c0f827..cd9ef9f 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -188,6 +188,7 @@ static int dumpBlobComments(Archive *AH, void *arg);
static void dumpDatabase(Archive *AH);
static void dumpEncoding(Archive *AH);
static void dumpStdStrings(Archive *AH);
+static void dumpRole(Archive *AH);
static const char *getAttrName(int attrnum, TableInfo *tblInfo);
static const char *fmtCopyColumnList(const TableInfo *ti);
static void do_sql_command(PGconn *conn, const char *query);
@@ -229,6 +230,10 @@ main(int argc, char **argv)
static int outputNoTablespaces = 0;
static int use_setsessauth = 0;
+ const char *pgrole = NULL;
+ PQExpBuffer roleQry;
+ PGresult *res;
+
static struct option long_options[] = {
{"data-only", no_argument, NULL, 'a'},
{"blobs", no_argument, NULL, 'b'},
@@ -269,6 +274,7 @@ main(int argc, char **argv)
{"lock-wait-timeout", required_argument, NULL, 2},
{"no-tablespaces", no_argument, &outputNoTablespaces, 1},
{"use-set-session-authorization", no_argument, &use_setsessauth, 1},
+ {"role", required_argument, NULL, 3},
{NULL, 0, NULL, 0}
};
@@ -446,6 +452,10 @@ main(int argc, char **argv)
lockWaitTimeout = optarg;
break;
+ case 3: /* role */
+ pgrole = optarg;
+ break;
+
default:
fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
exit(1);
@@ -555,6 +565,20 @@ main(int argc, char **argv)
}
}
+ /* Set the role if requested */
+ if (pgrole)
+ {
+ roleQry = createPQExpBuffer();
+ appendPQExpBuffer(roleQry, "SET ROLE TO %s;\n", fmtId(pgrole));
+ res = PQexec(g_conn, roleQry->data);
+ check_sql_result(res, g_conn, roleQry->data, PGRES_COMMAND_OK);
+ PQclear(res);
+ destroyPQExpBuffer(roleQry);
+ g_fout->rolename = pgrole;
+ } else {
+ g_fout->rolename = NULL;
+ }
+
/*
* Get the active encoding and the standard_conforming_strings setting, so
* we know how to escape strings.
@@ -586,7 +610,7 @@ main(int argc, char **argv)
*/
if (g_fout->remoteVersion >= 70300)
do_sql_command(g_conn, "SET statement_timeout = 0");
-
+
/*
* Start serializable transaction to dump consistent data.
*/
@@ -715,6 +739,8 @@ main(int argc, char **argv)
/* First the special ENCODING and STDSTRINGS entries. */
dumpEncoding(g_fout);
dumpStdStrings(g_fout);
+ if (pgrole)
+ dumpRole(g_fout);
/* The database item is always next, unless we don't want it at all */
if (include_everything && !dataOnly)
@@ -800,6 +826,7 @@ help(const char *progname)
printf(_(" --use-set-session-authorization\n"
" use SESSION AUTHORIZATION commands instead of\n"
" ALTER OWNER commands to set ownership\n"));
+ printf(_(" --role set role before dump\n"));
printf(_("\nConnection options:\n"));
printf(_(" -h, --host=HOSTNAME database server host or socket directory\n"));
@@ -1800,6 +1827,32 @@ dumpStdStrings(Archive *AH)
/*
+ * dumpRole: put the role change into the archive
+ */
+static void
+dumpRole(Archive *AH)
+{
+ const char *rolename = AH->rolename;
+ PQExpBuffer qry = createPQExpBuffer();
+
+ if (g_verbose)
+ write_msg(NULL, "saving rolename = %s\n", rolename);
+
+ appendPQExpBuffer(qry, "SET role = ");
+ appendStringLiteralAH(qry, rolename, AH);
+ appendPQExpBuffer(qry, ";\n");
+
+ ArchiveEntry(AH, nilCatalogId, createDumpId(),
+ "ROLENAME", NULL, NULL, "",
+ false, "ROLENAME", qry->data, "", NULL,
+ NULL, 0,
+ NULL, NULL);
+
+ destroyPQExpBuffer(qry);
+}
+
+
+/*
* hasBlobs:
* Test whether database contains any large objects
*/
diff --git a/src/bin/pg_dump/pg_dumpall.c b/src/bin/pg_dump/pg_dumpall.c
index b00fb5a..cb8b5a1 100644
--- a/src/bin/pg_dump/pg_dumpall.c
+++ b/src/bin/pg_dump/pg_dumpall.c
@@ -89,6 +89,9 @@ main(int argc, char *argv[])
int c,
ret;
+ const char *pgrole = NULL;
+ PQExpBuffer roleQry;
+
static struct option long_options[] = {
{"data-only", no_argument, NULL, 'a'},
{"clean", no_argument, NULL, 'c'},
@@ -121,6 +124,7 @@ main(int argc, char *argv[])
{"no-tablespaces", no_argument, &no_tablespaces, 1},
{"use-set-session-authorization", no_argument, &use_setsessauth, 1},
{"lock-wait-timeout", required_argument, NULL, 2},
+ {"role", required_argument, NULL, 3},
{NULL, 0, NULL, 0}
};
@@ -289,7 +293,7 @@ main(int argc, char *argv[])
disable_dollar_quoting = 1;
else if (strcmp(optarg, "disable-triggers") == 0)
disable_triggers = 1;
- else if (strcmp(optarg, "no-tablespaces") == 0)
+ else if (strcmp(optarg, "no-tablespaces") == 0)
no_tablespaces = 1;
else if (strcmp(optarg, "use-set-session-authorization") == 0)
use_setsessauth = 1;
@@ -311,6 +315,15 @@ main(int argc, char *argv[])
appendPQExpBuffer(pgdumpopts, optarg);
break;
+ case 3:
+ pgrole = optarg;
+#ifndef WIN32
+ appendPQExpBuffer(pgdumpopts, " --role '%s'", optarg);
+#else
+ appendPQExpBuffer(pgdumpopts, " --role \"%s\"", optarg);
+#endif
+ break;
+
default:
fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
exit(1);
@@ -426,6 +439,15 @@ main(int argc, char *argv[])
if (!std_strings)
std_strings = "off";
+ /* Set the role if requested */
+ if (pgrole)
+ {
+ roleQry = createPQExpBuffer();
+ appendPQExpBuffer(roleQry, "SET ROLE TO %s;\n", fmtId(pgrole));
+ executeCommand(conn, roleQry->data);
+ destroyPQExpBuffer(roleQry);
+ }
+
fprintf(OPF, "--\n-- PostgreSQL database cluster dump\n--\n\n");
if (verbose)
dumpTimestamp("Started on");
@@ -516,6 +538,7 @@ help(void)
printf(_(" --use-set-session-authorization\n"
" use SESSION AUTHORIZATION commands instead of\n"
" OWNER TO commands\n"));
+ printf(_(" --role set role before dump\n"));
printf(_("\nConnection options:\n"));
printf(_(" -h, --host=HOSTNAME database server host or socket directory\n"));
@@ -1527,3 +1550,4 @@ dumpTimestamp(char *msg)
localtime(&now)) != 0)
fprintf(OPF, "-- %s %s\n\n", msg, buf);
}
+
Benedek L�szl� wrote:
Hi,
The patch contains the following things:
- pg_dump and pg_dumpall accepts the --role=rolename parameter, and
sends a SET ROLE command on their connections
Minor comment -- I think you need to quote the role name in the SET
command. Otherwise roles with funny names will fail (try a role with a
space for example)
- sgml documentation of this feature
The SGML patch seems to contain unnecessary whitespace changes; please
clean that up.
+ /* te->defn should have the form SET role = 'foo'; */ + char *defn = strdup(te->defn); + char *ptr1; + char *ptr2 = NULL; + + ptr1 = strchr(defn, '\''); + if (ptr1) + ptr2 = strchr(++ptr1, '\'');
Does this work if the role name contains a ' ?
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
Hi,
Thank you for your review.
On 2008-11-07 21:20, Alvaro Herrera wrote:
The patch contains the following things:
- pg_dump and pg_dumpall accepts the --role=rolename parameter, and
sends a SET ROLE command on their connectionsMinor comment -- I think you need to quote the role name in the SET
command. Otherwise roles with funny names will fail (try a role with a
space for example)
Of course you need to quote the role names with special characters in it.
I tested it this way (from bash):
$ src/bin/pg_dump/pg_dump -h localhost -p 4003 --role "asd ' \" qwe" test
Note the bash style escaping of the string [asd ' " qwe].
It created a dump file with SET role = "asd ' "" qwe"; line in it. Seems
fine for me.
The SGML patch seems to contain unnecessary whitespace changes; please
clean that up.
Maybe you missed an updated version of the patch? Available here:
http://archives.postgresql.org/pgsql-hackers/2008-11/msg00391.php
+ /* te->defn should have the form SET role = 'foo'; */ + char *defn = strdup(te->defn); + char *ptr1; + char *ptr2 = NULL; + + ptr1 = strchr(defn, '\''); + if (ptr1) + ptr2 = strchr(++ptr1, '\'');Does this work if the role name contains a ' ?
Right, this one fails with ' in the role name. An update coming soon closing this issue.
Regards,
Benedek Laszlo
On 2008-11-08 09:25, Benedek László wrote:
Does this work if the role name contains a ' ?
Right, this one fails with ' in the role name. An update coming soon
closing this issue.
Here is an updated patch, which deals with 's in the rolename.
Please review.
doc/src/sgml/ref/pg_dump.sgml | 16 +++++++++
doc/src/sgml/ref/pg_dumpall.sgml | 15 ++++++++
src/bin/pg_dump/pg_backup.h | 2 +
src/bin/pg_dump/pg_backup_archiver.c | 35 ++++++++++++++++++-
src/bin/pg_dump/pg_dump.c | 60
+++++++++++++++++++++++++++++++++-
src/bin/pg_dump/pg_dumpall.c | 23 +++++++++++++
6 files changed, 148 insertions(+), 3 deletions(-)
Thank you, regards
Benedek Laszlo
Attachments:
pg_dump_role.patchtext/x-patch; name=pg_dump_role.patchDownload
diff --git a/doc/src/sgml/ref/pg_dump.sgml b/doc/src/sgml/ref/pg_dump.sgml
index 2e30906..5e4c3e0 100644
--- a/doc/src/sgml/ref/pg_dump.sgml
+++ b/doc/src/sgml/ref/pg_dump.sgml
@@ -698,6 +698,22 @@ PostgreSQL documentation
</para>
</listitem>
</varlistentry>
+
+ <varlistentry>
+ <term><option>--role=<replaceable class="parameter">rolename</replaceable></option></term>
+ <listitem>
+ <para>
+ Specifies the user identifier used by the dump session. This cause
+ <application>pg_dump</application> to issue a
+ <command>SET role = <replaceable class="parameter">rolename</replaceable></command>
+ command just after a successful database connection. It is useful in cases when
+ the logged in user specified by the -U option has not enough privileges needed by
+ <application>pg_dump</application> but can switch to a role with the needed rights.
+ The SET ROLE command is reserved in the archive because most of the time this
+ user identifier also needed for the restore to succeed.
+ </para>
+ </listitem>
+ </varlistentry>
</variablelist>
</para>
</refsect1>
diff --git a/doc/src/sgml/ref/pg_dumpall.sgml b/doc/src/sgml/ref/pg_dumpall.sgml
index ec40890..640723d 100644
--- a/doc/src/sgml/ref/pg_dumpall.sgml
+++ b/doc/src/sgml/ref/pg_dumpall.sgml
@@ -417,6 +417,21 @@ PostgreSQL documentation
</para>
</listitem>
</varlistentry>
+
+ <varlistentry>
+ <term><option>--role=<replaceable class="parameter">rolename</replaceable></option></term>
+ <listitem>
+ <para>
+ Specifies the user identifier used by the dump session. This option is passed
+ to <application>pg_dump</> too and cause these applications to issue a
+ <command>SET role = <replaceable class="parameter">rolename</replaceable></command>
+ command just after a successful database connection. It is useful in cases when
+ the logged in user specified by the -U option has not enough privileges needed by
+ <application>pg_dumpall</application> but can switch to a role with the needed rights.
+ The SET ROLE command is reserved in the archive by <application>pg_dump</application>.
+ </para>
+ </listitem>
+ </varlistentry>
</variablelist>
</para>
</refsect1>
diff --git a/src/bin/pg_dump/pg_backup.h b/src/bin/pg_dump/pg_backup.h
index c57bb22..c9e7e72 100644
--- a/src/bin/pg_dump/pg_backup.h
+++ b/src/bin/pg_dump/pg_backup.h
@@ -70,6 +70,8 @@ typedef struct _Archive
int encoding; /* libpq code for client_encoding */
bool std_strings; /* standard_conforming_strings */
+ char *rolename; /* role name in escaped form */
+
/* error handling */
bool exit_on_error; /* whether to exit on SQL errors... */
int n_errors; /* number of errors (if no die) */
diff --git a/src/bin/pg_dump/pg_backup_archiver.c b/src/bin/pg_dump/pg_backup_archiver.c
index 7bd44f2..53bbfdf 100644
--- a/src/bin/pg_dump/pg_backup_archiver.c
+++ b/src/bin/pg_dump/pg_backup_archiver.c
@@ -56,6 +56,7 @@ static void _selectOutputSchema(ArchiveHandle *AH, const char *schemaName);
static void _selectTablespace(ArchiveHandle *AH, const char *tablespace);
static void processEncodingEntry(ArchiveHandle *AH, TocEntry *te);
static void processStdStringsEntry(ArchiveHandle *AH, TocEntry *te);
+static void processRolenameEntry(ArchiveHandle *AH, TocEntry *te);
static teReqs _tocEntryRequired(TocEntry *te, RestoreOptions *ropt, bool include_acls);
static void _disableTriggersIfNecessary(ArchiveHandle *AH, TocEntry *te, RestoreOptions *ropt);
static void _enableTriggersIfNecessary(ArchiveHandle *AH, TocEntry *te, RestoreOptions *ropt);
@@ -1979,6 +1980,8 @@ ReadToc(ArchiveHandle *AH)
processEncodingEntry(AH, te);
else if (strcmp(te->desc, "STDSTRINGS") == 0)
processStdStringsEntry(AH, te);
+ else if (strcmp(te->desc, "ROLENAME") == 0)
+ processRolenameEntry(AH, te);
}
}
@@ -2026,14 +2029,38 @@ processStdStringsEntry(ArchiveHandle *AH, TocEntry *te)
te->defn);
}
+static void
+processRolenameEntry(ArchiveHandle *AH, TocEntry *te)
+{
+ /* te->defn should have the form SET role = "foo"; */
+ char *defn = strdup(te->defn);
+ char *ptr1;
+ char *ptr2 = NULL;
+
+ ptr1 = strchr(defn, '"');
+ if (ptr1)
+ ptr2 = strrchr(ptr1+1, '"');
+ if (ptr2)
+ {
+ *++ptr2 = '\0';
+ AH->public.rolename = strdup(ptr1);
+ }
+ else
+ die_horribly(AH, modulename, "invalid ROLENAME item: %s\n",
+ te->defn);
+
+ free(defn);
+}
+
static teReqs
_tocEntryRequired(TocEntry *te, RestoreOptions *ropt, bool include_acls)
{
teReqs res = REQ_ALL;
- /* ENCODING and STDSTRINGS items are dumped specially, so always reject */
+ /* ENCODING, STDSTRINGS and ROLENAME items are dumped specially, so always reject */
if (strcmp(te->desc, "ENCODING") == 0 ||
- strcmp(te->desc, "STDSTRINGS") == 0)
+ strcmp(te->desc, "STDSTRINGS") == 0 ||
+ strcmp(te->desc, "ROLENAME") == 0)
return 0;
/* If it's an ACL, maybe ignore it */
@@ -2146,6 +2173,10 @@ _doSetFixedOutputState(ArchiveHandle *AH)
ahprintf(AH, "SET standard_conforming_strings = %s;\n",
AH->public.std_strings ? "on" : "off");
+ /* Select the role to be used during restore */
+ if (AH->public.rolename)
+ ahprintf(AH, "SET role = %s;\n", AH->public.rolename);
+
/* Make sure function checking is disabled */
ahprintf(AH, "SET check_function_bodies = false;\n");
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index b686c28..3320370 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -189,6 +189,7 @@ static int dumpBlobComments(Archive *AH, void *arg);
static void dumpDatabase(Archive *AH);
static void dumpEncoding(Archive *AH);
static void dumpStdStrings(Archive *AH);
+static void dumpRole(Archive *AH);
static const char *getAttrName(int attrnum, TableInfo *tblInfo);
static const char *fmtCopyColumnList(const TableInfo *ti);
static void do_sql_command(PGconn *conn, const char *query);
@@ -230,6 +231,10 @@ main(int argc, char **argv)
static int outputNoTablespaces = 0;
static int use_setsessauth = 0;
+ const char *pgrole = NULL;
+ PQExpBuffer roleQry;
+ PGresult *res;
+
static struct option long_options[] = {
{"data-only", no_argument, NULL, 'a'},
{"blobs", no_argument, NULL, 'b'},
@@ -270,6 +275,7 @@ main(int argc, char **argv)
{"lock-wait-timeout", required_argument, NULL, 2},
{"no-tablespaces", no_argument, &outputNoTablespaces, 1},
{"use-set-session-authorization", no_argument, &use_setsessauth, 1},
+ {"role", required_argument, NULL, 3},
{NULL, 0, NULL, 0}
};
@@ -447,6 +453,10 @@ main(int argc, char **argv)
lockWaitTimeout = optarg;
break;
+ case 3: /* role */
+ pgrole = optarg;
+ break;
+
default:
fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
exit(1);
@@ -556,6 +566,20 @@ main(int argc, char **argv)
}
}
+ /* Set the role if requested */
+ if (pgrole)
+ {
+ roleQry = createPQExpBuffer();
+ g_fout->rolename = strdup(fmtId(pgrole));
+ appendPQExpBuffer(roleQry, "SET role = %s;\n", g_fout->rolename);
+ res = PQexec(g_conn, roleQry->data);
+ check_sql_result(res, g_conn, roleQry->data, PGRES_COMMAND_OK);
+ PQclear(res);
+ destroyPQExpBuffer(roleQry);
+ }
+ else
+ g_fout->rolename = NULL;
+
/*
* Get the active encoding and the standard_conforming_strings setting, so
* we know how to escape strings.
@@ -717,9 +741,11 @@ main(int argc, char **argv)
* order.
*/
- /* First the special ENCODING and STDSTRINGS entries. */
+ /* First the special ENCODING, STDSTRINGS and ROLENAME entries. */
dumpEncoding(g_fout);
dumpStdStrings(g_fout);
+ if (pgrole)
+ dumpRole(g_fout);
/* The database item is always next, unless we don't want it at all */
if (include_everything && !dataOnly)
@@ -758,6 +784,9 @@ main(int argc, char **argv)
CloseArchive(g_fout);
+ if (g_fout->rolename)
+ free(g_fout->rolename);
+
PQfinish(g_conn);
exit(0);
@@ -805,6 +834,7 @@ help(const char *progname)
printf(_(" --use-set-session-authorization\n"
" use SESSION AUTHORIZATION commands instead of\n"
" ALTER OWNER commands to set ownership\n"));
+ printf(_(" --role set role before dump\n"));
printf(_("\nConnection options:\n"));
printf(_(" -h, --host=HOSTNAME database server host or socket directory\n"));
@@ -1805,6 +1835,34 @@ dumpStdStrings(Archive *AH)
/*
+ * dumpRole: put the role change into the archive
+ */
+static void
+dumpRole(Archive *AH)
+{
+ const char *rolename = AH->rolename;
+ PQExpBuffer qry = createPQExpBuffer();
+
+ if (g_verbose)
+ write_msg(NULL, "saving rolename = %s\n", rolename);
+
+ /* force quoted rolename into the archive */
+ if (rolename[0] == '"')
+ appendPQExpBuffer(qry, "SET role = %s;\n", rolename);
+ else
+ appendPQExpBuffer(qry, "SET role = \"%s\";\n", rolename);
+
+ ArchiveEntry(AH, nilCatalogId, createDumpId(),
+ "ROLENAME", NULL, NULL, "",
+ false, "ROLENAME", qry->data, "", NULL,
+ NULL, 0,
+ NULL, NULL);
+
+ destroyPQExpBuffer(qry);
+}
+
+
+/*
* hasBlobs:
* Test whether database contains any large objects
*/
diff --git a/src/bin/pg_dump/pg_dumpall.c b/src/bin/pg_dump/pg_dumpall.c
index b00fb5a..d779a1a 100644
--- a/src/bin/pg_dump/pg_dumpall.c
+++ b/src/bin/pg_dump/pg_dumpall.c
@@ -89,6 +89,9 @@ main(int argc, char *argv[])
int c,
ret;
+ const char *pgrole = NULL;
+ PQExpBuffer roleQry;
+
static struct option long_options[] = {
{"data-only", no_argument, NULL, 'a'},
{"clean", no_argument, NULL, 'c'},
@@ -121,6 +124,7 @@ main(int argc, char *argv[])
{"no-tablespaces", no_argument, &no_tablespaces, 1},
{"use-set-session-authorization", no_argument, &use_setsessauth, 1},
{"lock-wait-timeout", required_argument, NULL, 2},
+ {"role", required_argument, NULL, 3},
{NULL, 0, NULL, 0}
};
@@ -311,6 +315,15 @@ main(int argc, char *argv[])
appendPQExpBuffer(pgdumpopts, optarg);
break;
+ case 3:
+ pgrole = optarg;
+#ifndef WIN32
+ appendPQExpBuffer(pgdumpopts, " --role '%s'", optarg);
+#else
+ appendPQExpBuffer(pgdumpopts, " --role \"%s\"", optarg);
+#endif
+ break;
+
default:
fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
exit(1);
@@ -426,6 +439,15 @@ main(int argc, char *argv[])
if (!std_strings)
std_strings = "off";
+ /* Set the role if requested */
+ if (pgrole)
+ {
+ roleQry = createPQExpBuffer();
+ appendPQExpBuffer(roleQry, "SET role = %s;\n", fmtId(pgrole));
+ executeCommand(conn, roleQry->data);
+ destroyPQExpBuffer(roleQry);
+ }
+
fprintf(OPF, "--\n-- PostgreSQL database cluster dump\n--\n\n");
if (verbose)
dumpTimestamp("Started on");
@@ -516,6 +538,7 @@ help(void)
printf(_(" --use-set-session-authorization\n"
" use SESSION AUTHORIZATION commands instead of\n"
" OWNER TO commands\n"));
+ printf(_(" --role set role before dump\n"));
printf(_("\nConnection options:\n"));
printf(_(" -h, --host=HOSTNAME database server host or socket directory\n"));
=?ISO-8859-1?Q?Benedek_L=E1szl=F3?= <laci@benedekl.tvnetwork.hu> writes:
Here is an updated patch, which deals with 's in the rolename.
Committed with revisions as per subsequent discussion: pg_restore has
its own switch and there's no change in archive contents.
regards, tom lane