Обсуждение: Proposal for restoring a dump into a database with a different owner

Hi,

I have the same problem as Andreas Haumer did in this thread:
http://archives.postgresql.org/pgsql-admin/2008-01/msg00128.php -- I want to
be able to easily (i.e. programmatically) copy a database from one place to
another, changing the owners of all contained objects in the process.

While I very much appreciate Tom Lane's fast and helpful responses to
Andreas on that thread, it doesn't quite address my problem: there is no
simple, automatable 1- or 2-step process that can accomplish this (without
Andreas's (admittedly neat) trick of temporarily changing the destination
user to superuser status).  The best I've been able to do is hack up a Perl
script that parses the output of pg_restore -l, directing
superuser-requiring operations to one file and non-superuser-requiring
operations to another; but afterwards the superuser-requiring operations
still have to have the owners of the objects they produce manually
reassigned.

My instincts (which could be wrong...) tell me that this is actually a
fairly common problem.  So, I suggest the following enhancement to
pg_restore: add a --map-users command-line option that accepts the name of a
file containing two usernames on each line, <from> and <to>.  Then (provided
-O was not specified) when producing ALTER ... OWNER TO commands, simply
replace every <from> user listed in this file with the corresponding <to>
user.

Another niggle is that the COMMENT ON DATABASE command, produced by
pg_restore when run without the -d option, always refers to the name of the
original database, which will cause an error if the new DB has a different
name.  It would be nice to have an option (or other means) to remedy this.

It seems to me that these things would be pretty simple to implement and
sufficiently general to tackle this problem neatly, without opening up any
security holes (you would still need to be *some* DB superuser for the ALTER
... OWNER TO commands to work).

Does this sound sensible?  If Tom or another high-ranking PostgreSQLer okays
it in principle, I suppose I could try developing a patch for pg_restore
myself.  (Never done this before but there's a first time for everything...)

TIA,
Tim White

I see you didn't get a response this request.  I am thinking it would be
better to implement some form of massive change ownership option that
can be done to change ownership after the dump is restored.

---------------------------------------------------------------------------

postgresql.20.j_random_hacker@spamgourmet.com wrote:
> Hi,
>
> I have the same problem as Andreas Haumer did in this thread:
> http://archives.postgresql.org/pgsql-admin/2008-01/msg00128.php -- I want to
> be able to easily (i.e. programmatically) copy a database from one place to
> another, changing the owners of all contained objects in the process.
>
> While I very much appreciate Tom Lane's fast and helpful responses to
> Andreas on that thread, it doesn't quite address my problem: there is no
> simple, automatable 1- or 2-step process that can accomplish this (without
> Andreas's (admittedly neat) trick of temporarily changing the destination
> user to superuser status).  The best I've been able to do is hack up a Perl
> script that parses the output of pg_restore -l, directing
> superuser-requiring operations to one file and non-superuser-requiring
> operations to another; but afterwards the superuser-requiring operations
> still have to have the owners of the objects they produce manually
> reassigned.
>
> My instincts (which could be wrong...) tell me that this is actually a
> fairly common problem.  So, I suggest the following enhancement to
> pg_restore: add a --map-users command-line option that accepts the name of a
> file containing two usernames on each line, <from> and <to>.  Then (provided
> -O was not specified) when producing ALTER ... OWNER TO commands, simply
> replace every <from> user listed in this file with the corresponding <to>
> user.
>
> Another niggle is that the COMMENT ON DATABASE command, produced by
> pg_restore when run without the -d option, always refers to the name of the
> original database, which will cause an error if the new DB has a different
> name.  It would be nice to have an option (or other means) to remedy this.
>
> It seems to me that these things would be pretty simple to implement and
> sufficiently general to tackle this problem neatly, without opening up any
> security holes (you would still need to be *some* DB superuser for the ALTER
> ... OWNER TO commands to work).
>
> Does this sound sensible?  If Tom or another high-ranking PostgreSQLer okays
> it in principle, I suppose I could try developing a patch for pg_restore
> myself.  (Never done this before but there's a first time for everything...)
>
> TIA,
> Tim White
>
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

On Mon, Jun 30, 2008 at 1:50 PM, Bruce Momjian <bruce@momjian.us> wrote:
>
> I see you didn't get a response this request.  I am thinking it would be
> better to implement some form of massive change ownership option that
> can be done to change ownership after the dump is restored.

Currently the way I do that is to dump with the -O option, and then
restore to the user account I want everything to belong to.

Bruce Momjian <bruce@momjian.us> writes:
> I see you didn't get a response this request.  I am thinking it would be
> better to implement some form of massive change ownership option that
> can be done to change ownership after the dump is restored.

We already have REASSIGN OWNED, though that doesn't entirely do the job
because it doesn't worry about reassigning grants.  Reassigning grants
seems like a big headache for the --map-users proposal too.

            regards, tom lane