On Tue, Aug 2, 2011 at 5:05 PM, Tom Lane <t...@sss.pgh.pa.us> wrote:
> Phil Sorber <p...@omniti.com> writes:
>> I have included two patches in this email. The first
>> (dump_user_config_last_with_set_role.patch) is an extension of my
>> first patch. In addition to moving the ALTER ROLE statements after the
>> CREATE ROLE statements it also inserts a SET ROLE after every connect.
>> It takes the role parameter from the --role command line option. This
>> fixes the problem of not being able to restore to a database because
>> of lack of permissions. This is similar to the idea proposed here:
>> http://archives.postgresql.org/pgsql-hackers/2010-12/msg01046.php
>
> I don't understand why you think that that will fix anything?
>
> The problem that Florian originally pointed out is that settings
> established by ALTER DATABASE/ROLE could interfere with the restoration
> script's actions. That seems to be just as much of a risk for the
> --role role as the one originally used to connect. I don't see a way
> around that other than not applying those settings until we are done
> reconnecting to the target database.
>
> Also, given that the --role switch is only defined to select the role
> to be used at *dump* time, I'm unconvinced that forcing it to be used
> at *restore* time is a good idea. You'd really need to invent a
> separate switch if you were to go down this path.
>
> regards, tom lane
>
Ok, here is the patch that just moves the ALTER/SET pieces to the end.
Can we get this included in the next commit fest?
diff --git a/src/bin/pg_dump/pg_dumpall.c b/src/bin/pg_dump/pg_dumpall.c
new file mode 100644
index b5f64e8..d3929f0
*** a/src/bin/pg_dump/pg_dumpall.c
--- b/src/bin/pg_dump/pg_dumpall.c
*************** static void dropTablespaces(PGconn *conn
*** 41,48 ****
static void dumpTablespaces(PGconn *conn);
static void dropDBs(PGconn *conn);
static void dumpCreateDB(PGconn *conn);
! static void dumpDatabaseConfig(PGconn *conn, const char *dbname);
! static void dumpUserConfig(PGconn *conn, const char *username);
static void dumpDbRoleConfig(PGconn *conn);
static void makeAlterConfigCommand(PGconn *conn, const char *arrayitem,
const char *type, const char *name, const char *type2,
--- 41,48 ----
static void dumpTablespaces(PGconn *conn);
static void dropDBs(PGconn *conn);
static void dumpCreateDB(PGconn *conn);
! static void dumpDatabaseConfig(PGconn *conn);
! static void dumpUserConfig(PGconn *conn);
static void dumpDbRoleConfig(PGconn *conn);
static void makeAlterConfigCommand(PGconn *conn, const char *arrayitem,
const char *type, const char *name, const char *type2,
*************** main(int argc, char *argv[])
*** 500,517 ****
/* Dump CREATE DATABASE commands */
if (!globals_only && !roles_only && !tablespaces_only)
dumpCreateDB(conn);
! /* Dump role/database settings */
! if (!tablespaces_only && !roles_only)
{
if (server_version >= 90000)
dumpDbRoleConfig(conn);
}
}
- if (!globals_only && !roles_only && !tablespaces_only)
- dumpDatabases(conn);
-
PQfinish(conn);
if (verbose)
--- 500,524 ----
/* Dump CREATE DATABASE commands */
if (!globals_only && !roles_only && !tablespaces_only)
dumpCreateDB(conn);
+ }
! if (!globals_only && !roles_only && !tablespaces_only)
! dumpDatabases(conn);
!
! if (!data_only && !tablespaces_only && server_version >= 70300)
! {
! dumpUserConfig(conn);
!
! if (!roles_only)
{
+ if (!globals_only)
+ dumpDatabaseConfig(conn);
+
if (server_version >= 90000)
dumpDbRoleConfig(conn);
}
}
PQfinish(conn);
if (verbose)
*************** dumpRoles(PGconn *conn)
*** 804,812 ****
buf, "ROLE", rolename);
fprintf(OPF, "%s", buf->data);
-
- if (server_version >= 70300)
- dumpUserConfig(conn, rolename);
}
PQclear(res);
--- 811,816 ----
*************** dumpCreateDB(PGconn *conn)
*** 1358,1366 ****
fprintf(OPF, "%s", buf->data);
- if (server_version >= 70300)
- dumpDatabaseConfig(conn, dbname);
-
free(fdbname);
}
--- 1362,1367 ----
*************** dumpCreateDB(PGconn *conn)
*** 1375,1418 ****
* Dump database-specific configuration
*/
static void
! dumpDatabaseConfig(PGconn *conn, const char *dbname)
{
! PQExpBuffer buf = createPQExpBuffer();
! int count = 1;
! for (;;)
{
! PGresult *res;
! if (server_version >= 90000)
! printfPQExpBuffer(buf, "SELECT setconfig[%d] FROM pg_db_role_setting WHERE "
! "setrole = 0 AND setdatabase = (SELECT oid FROM pg_database WHERE datname = ", count);
! else
! printfPQExpBuffer(buf, "SELECT datconfig[%d] FROM pg_database WHERE datname = ", count);
! appendStringLiteralConn(buf, dbname, conn);
! if (server_version >= 90000)
! appendPQExpBuffer(buf, ")");
! appendPQExpBuffer(buf, ";");
! res = executeQuery(conn, buf->data);
! if (PQntuples(res) == 1 &&
! !PQgetisnull(res, 0, 0))
! {
! makeAlterConfigCommand(conn, PQgetvalue(res, 0, 0),
! "DATABASE", dbname, NULL, NULL);
! PQclear(res);
! count++;
! }
! else
! {
! PQclear(res);
! break;
}
}
! destroyPQExpBuffer(buf);
}
--- 1376,1450 ----
* Dump database-specific configuration
*/
static void
! dumpDatabaseConfig(PGconn *conn)
{
! PGresult *dbres;
! int i;
! bool shown_header = false;
! if (server_version >= 70100)
! dbres = executeQuery(conn,
! "SELECT datname "
! "FROM pg_database d "
! "WHERE datallowconn ORDER BY 1");
! else
! dbres = executeQuery(conn,
! "SELECT datname "
! "FROM pg_database d "
! "ORDER BY 1");
!
! for (i = 0; i < PQntuples(dbres); i++)
{
! char *dbname = PQgetvalue(dbres, i, 0);
! PQExpBuffer buf = createPQExpBuffer();
! int count = 1;
! for (;;)
! {
! PGresult *res;
! if (server_version >= 90000)
! printfPQExpBuffer(buf, "SELECT setconfig[%d] FROM pg_db_role_setting WHERE "
! "setrole = 0 AND setdatabase = (SELECT oid FROM pg_database WHERE datname = ", count);
! else
! printfPQExpBuffer(buf, "SELECT datconfig[%d] FROM pg_database WHERE datname = ", count);
! appendStringLiteralConn(buf, dbname, conn);
! if (server_version >= 90000)
! appendPQExpBuffer(buf, ")");
!
! appendPQExpBuffer(buf, ";");
!
! res = executeQuery(conn, buf->data);
! if (PQntuples(res) == 1 &&
! !PQgetisnull(res, 0, 0))
! {
! if (!shown_header)
! {
! shown_header = true;
! fprintf(OPF, "--\n-- Database Config\n--\n\n");
! }
!
! makeAlterConfigCommand(conn, PQgetvalue(res, 0, 0),
! "DATABASE", dbname, NULL, NULL);
! PQclear(res);
! count++;
! }
! else
! {
! PQclear(res);
! break;
! }
}
+
+ destroyPQExpBuffer(buf);
}
! PQclear(dbres);
!
! if (shown_header)
! fprintf(OPF, "\n\n");
}
*************** dumpDatabaseConfig(PGconn *conn, const c
*** 1421,1464 ****
* Dump user-specific configuration
*/
static void
! dumpUserConfig(PGconn *conn, const char *username)
{
! PQExpBuffer buf = createPQExpBuffer();
! int count = 1;
! for (;;)
{
! PGresult *res;
! if (server_version >= 90000)
! printfPQExpBuffer(buf, "SELECT setconfig[%d] FROM pg_db_role_setting WHERE "
! "setdatabase = 0 AND setrole = "
! "(SELECT oid FROM pg_authid WHERE rolname = ", count);
! else 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);
! appendStringLiteralConn(buf, username, conn);
! if (server_version >= 90000)
! appendPQExpBuffer(buf, ")");
! res = executeQuery(conn, buf->data);
! if (PQntuples(res) == 1 &&
! !PQgetisnull(res, 0, 0))
! {
! makeAlterConfigCommand(conn, PQgetvalue(res, 0, 0),
! "ROLE", username, NULL, NULL);
! PQclear(res);
! count++;
! }
! else
{
! PQclear(res);
! break;
}
}
! destroyPQExpBuffer(buf);
}
--- 1453,1535 ----
* Dump user-specific configuration
*/
static void
! dumpUserConfig(PGconn *conn)
{
! PGresult *userres;
! int i_rolname;
! int i;
! bool shown_header = false;
! if (server_version >= 80100)
! userres = executeQuery(conn,
! "SELECT rolname "
! "FROM pg_authid "
! "ORDER BY 1");
! else
! userres = executeQuery(conn,
! "SELECT usename as rolname "
! "FROM pg_shadow "
! "UNION "
! "SELECT groname as rolname "
! "FROM pg_group "
! "ORDER BY 1");
!
! i_rolname = PQfnumber(userres, "rolname");
!
! for (i = 0; i < PQntuples(userres); i++)
{
! const char *username;
! username = PQgetvalue(userres, i, i_rolname);
! PQExpBuffer buf = createPQExpBuffer();
! int count = 1;
!
! for (;;)
{
! PGresult *res;
!
! if (server_version >= 90000)
! printfPQExpBuffer(buf, "SELECT setconfig[%d] FROM pg_db_role_setting WHERE "
! "setdatabase = 0 AND setrole = "
! "(SELECT oid FROM pg_authid WHERE rolname = ", count);
! else 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);
! appendStringLiteralConn(buf, username, conn);
! if (server_version >= 90000)
! appendPQExpBuffer(buf, ")");
!
! res = executeQuery(conn, buf->data);
! if (PQntuples(res) == 1 &&
! !PQgetisnull(res, 0, 0))
! {
! if (!shown_header)
! {
! shown_header = true;
! fprintf(OPF, "--\n-- Role Config\n--\n\n");
! }
!
! makeAlterConfigCommand(conn, PQgetvalue(res, 0, 0),
! "ROLE", username, NULL, NULL);
! PQclear(res);
! count++;
! }
! else
! {
! PQclear(res);
! break;
! }
}
+
+ destroyPQExpBuffer(buf);
}
! PQclear(userres);
!
! if (shown_header)
! fprintf(OPF, "\n\n");
}
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
