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> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: postgresql.20.j_random_hacker@spamgourmet.com
Subject: Re: Proposal for restoring a dump into a database with a different owner
In-Reply-To: <000301c8b28a$d8d15e90$959c7b82@massey.ac.nz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox