Re: Proposal for restoring a dump into a database with a different owner

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(dot)20(dot)j_random_hacker(at)spamgourmet(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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