Обсуждение: Proposal for restoring a dump into a database with a different owner
Proposal for restoring a dump into a database with a different owner
От
postgresql.20.j_random_hacker@spamgourmet.com
Дата:
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
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@spamgourmet.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@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-admin -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
On Mon, Jun 30, 2008 at 1:50 PM, Bruce Momjian <bruce@momjian.us> wrote: > > 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. Currently the way I do that is to dump with the -O option, and then restore to the user account I want everything to belong to.
Bruce Momjian <bruce@momjian.us> writes: > 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. We already have REASSIGN OWNED, though that doesn't entirely do the job because it doesn't worry about reassigning grants. Reassigning grants seems like a big headache for the --map-users proposal too. regards, tom lane