Re: Problem with disabling triggers in pg_dump - Mailing list pgsql-hackers
From | Philip Warner |
---|---|
Subject | Re: Problem with disabling triggers in pg_dump |
Date | |
Msg-id | 3.0.5.32.20000726014301.024fd430@mail.rhyme.com.au Whole thread Raw |
In response to | Re: Problem with disabling triggers in pg_dump (Tom Lane <tgl@sss.pgh.pa.us>) |
Responses |
Re: Re: Problem with disabling triggers in pg_dump
(Tom Lane <tgl@sss.pgh.pa.us>)
|
List | pgsql-hackers |
At 11:17 25/07/00 -0400, Tom Lane wrote: >Philip Warner <pjw@rhyme.com.au> writes: >> The obvious solution is to reconnect as the datdba before running the code. >> But that option may not be possible because passwords may be enabled > >We have talked about ways to solve the real problem. One way is to >run the entire restore script as superuser, doing something other than >\connect to set ownership of created objects. You could do that now >with something like > create table newtable ... > update pg_class set relowner = xxx where relname = 'newtable'; I considered this, but lack of confidence as to the ramifications of updating pg_class + the fact that it also requires superuser access, put me off. But maybe I should put an 'Ownersip' phase in pg_restore (at the end, obviosly). >although it'd be nicer to invent ALTER commands to handle this. I agree, but not for 7.0.2. Doing something as apparently simple might be a good way for me to see how the process of dealing with a command works from psql->backend->psql, which is something I'll need for 'insert...returning'. >Another issue to think about is that it should be possible to run >restore scripts as a non-superuser, with the restriction that all the >created objects end up being owned by you not by their original owners. This was part of the motivation; if a DBA creates a db and can set it's ownership to the appropriate user, then the user should be able to restore into it. >(Compare the behavior of "tar x" when run as superuser or not.) >This has not worked in the past (because those \connect commands can't >be ignored), I don't follow...do you mean that psql barfs, or do you mean that there has been no way to prevent them being output? >but it would work with an ALTER-based approach, because >the ALTERs would simply fail. With a slightly smarter pg_restore, >there'd be an option not to emit the ALTERs in the first place, but >this is inessential. --ignore-ownership or similar on pg_restore is about 10 lines of code. >The thing that really bothers me about this reltriggers hack is that >it doesn't work if the script is being run as non-superuser. I don't >see why it's necessary anyway; shouldn't the order of operations be > create table; > load data; > create triggers and indexes; Yes, for a full restore that is true. But for a partial restore (where I have assumed triggers may exist), it's nice to disable the triggers...but (and this is a horrible idea), pg_restore could use pg_dump to dump the triggers to a null archive, drop them, then restore them at the end of the restore op... >> So, how does this sound: add another arg to pg_restore, --superuser=name, >> which allows the person restoring the database to specify the superuser >> account to use, and if none is specified, then use the account it finds in >> the dba field of the database it is restoring to. > >I think pg_restore ought to run under the same userid that would be >established for a plain psql session --- ie, -u or $PGUSER or $USER. It does; but with a direct db connection it reconnects appropriately as each user (with password prompts if necessary). The idea was that for the reltriggers hack it knows it needs to connect as a superuser, so I could allow the user to specify a valid one with a known password. >> I would also modify >> pg_dump to dump the original datdba, in case a mythical future release does >> the 'create database' part as well. > >Uh, how does pg_dumpall enter into this? It doesn't really; pg_dumpall inserts text into the output stream and creates one huge script for restoring all databases. It also messes with pg_* tables, so I can't really make the two work together. The current version of pg_restore that I am working on has a --create (-C) option which will issue the appropriate 'create database' commands. But pg_dumpall is unaffected. Sounds like I need to add --ignore-owner. At *some* point in the future, I'll try to put together a pg_backupall & pg_restoreall, but that's not really high on my priorities - I tend to backup DBs individually. ---------------------------------------------------------------- Philip Warner | __---_____ Albatross Consulting Pty. Ltd. |----/ - \ (A.C.N. 008 659 498) | /(@) ______---_ Tel: (+61) 0500 83 82 81 | _________ \ Fax: (+61) 0500 83 82 82 | ___________ | Http://www.rhyme.com.au | / \| | --________-- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
pgsql-hackers by date: