We've seen several complaints now about pg_dump failing to restore
schemas that were created by a superuser via a command likeCREATE SCHEMA joe AUTHORIZATION joe
The pg_dump script tries to do\c - joeCREATE SCHEMA joe
which fails if joe doesn't have privileges to create a schema.
I think the cleanest solution for this involves switching to SET SESSION
AUTHORIZATION as the default (indeed only) method for userid changes in
pg_dump. Then the script fragment for restoring a schema can look likeRESET SESSION AUTHORIZATION;CREATE SCHEMA joe
AUTHORIZATIONjoe;
which will work if the script run was started by a superuser. It will
also work if the script was started by joe himself, if he's not
superuser but does have CREATE SCHEMA privileges.
Another thing we've been hearing about is restore failures due to
forward references in SQL functions. Although the planned general
solution for forward references in pg_dump scripts involves using
pg_depend information to sort the objects correctly, that's really
not going to help for SQL functions, at least not unless we add more
pg_depend entries than are there now.
The idea that I'm toying with to solve this is to add a GUC variable
called something like "validate_function_bodies", which would normally
be true but would get set false at the start of a pg_dump script.
The false setting would suppress fmgr_sql_validator's attempt to
syntax-check the function body string.
Comments?
regards, tom lane