From: postgresql.20.j_random_hacker@spamgourmet.com
To: pgsql-admin@postgresql.org
Subject: Proposal for restoring a dump into a database with a different owner
Date: Sat, 10 May 2008 22:44:39 +1200
Message-ID: <000301c8b28a$d8d15e90$959c7b82@massey.ac.nz>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
Thread-Index: Aciyiti5stOnJO3wTK+RcLdRZqPHOg==

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