Re: Problem with disabling triggers in pg_dump

At 11:17 25/07/00 -0400, Tom Lane wrote:
>Philip Warner <pjw(at)rhyme(dot)com(dot)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 |/