Re: Problem with disabling triggers in pg_dump

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

pg_dump scripts that try to restore ownership have always been
essentially unusable on systems with password authentication, because
it's just not practical to keep entering the passwords for every
\connect (even assuming you know them all). This trigger hack is just
the tip of the iceberg.

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';
although it'd be nicer to invent ALTER commands to handle this.

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.
(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), 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.

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;
?

> 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.

> 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?

regards, tom lane