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@xxxxxxxxxxxxxxx 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@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-admin -- Bruce Momjian <bruce@xxxxxxxxxx> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
