An interesting problem has been brought to my attention in pg_dump
(V7.0.2+, I think).
It uses the following code to disable triggers prior to a data load:
UPDATE "pg_class" SET "reltriggers" = 0 WHERE "relname" !~ '^pg_';
which works fine if it is currently connected as a superuser, or as the
datdba. However, if it is connected as anybody else, they will get the error:
pg_class: Permission denied
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 and
the person restoring the database may not know the password for the datdba,
but might have a valid 'superuser' account which they could use instead.
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 would also modify
pg_dump to dump the original datdba, in case a mythical future release does
the 'create database' part as well.
Another option would be to pop up a username prompt as well as the password
prompt when it wants the superuser on a system that requires passwords.
Comments would definitely be appreciated...
----------------------------------------------------------------
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 |/