Skip site navigation (1) Skip section navigation (2)

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

From: Bruce Momjian <bruce(at)momjian(dot)us>
To: postgresql(dot)20(dot)j_random_hacker(at)spamgourmet(dot)com
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: Proposal for restoring a dump into a database with a different owner
Date: 2008-06-30 19:50:06
Message-ID: 200806301950.m5UJo6p03894@momjian.us (view raw or flat)
Thread:
Lists: pgsql-admin
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. +

In response to

Responses

pgsql-admin by date

Next:From: Scott MarloweDate: 2008-06-30 20:01:36
Subject: Re: Proposal for restoring a dump into a database with a different owner
Previous:From: Tino SchwarzeDate: 2008-06-30 19:17:10
Subject: Re: Configuring Shared Buffers

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group