I'm trying to create a new database whose schema is the same as one
that already exists. To my surprise, the following steps don't work:

1) pg_dump -s db1 > db1_schema
2) createdb db2
3) psql -f db1_schema db2

Both db1 and db2 are created by the user "Bob", who also owns all the
tables in db1. Now, if step (3) is performed by Bob, then C language
functions are not re-created, for lack of permission. If step (3) is
perfomed by the postgres superuser, then certain indices and foreign
key constraints are not re-created and I see the error "must be owner
of relation <foo>".

Am I doing something wrong? Is there a better way to do this?

Nishad
--
"Underneath the concrete, the dream is still alive" -- Talking Heads


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nishad Prakash <prakashn (AT) uci (DOT) edu> writes:
That's a tad hard to believe: superusers always have ownership rights.
Could we see a complete example?

And which PG version are you using, anyway?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

prakashn (AT) uci (DOT) edu writes:
Okay, I see the problem: pg_dump is losing track of which session
authorization it's currently got selected. This is a consequence of
what the ACL-dumping code has to do to replicate GRANTs granted by
non-owners. The attached patch (against 7.4 branch) seems to fix it.

regards, tom lane

Index: pg_backup_archiver.c
================================================== =================
RCS file: /cvsroot/pgsql-server/src/bin/pg_dump/pg_backup_archiver.c,v
retrieving revision 1.79.2.2
diff -c -r1.79.2.2 pg_backup_archiver.c
*** pg_backup_archiver.c 24 Feb 2004 03:35:43 -0000 1.79.2.2
--- pg_backup_archiver.c 19 Jul 2004 20:56:06 -0000
***************
*** 2262,2267 ****
--- 2262,2278 ----
ahprintf(AH, "%s\n\n", te->defn);
}

+ /*
+ * If it's an ACL entry, it might contain SET SESSION AUTHORIZATION
+ * commands, so we can no longer assume we know the current auth setting.
+ */
+ if (strncmp(te->desc, "ACL", 3) == 0)
+ {
+ if (AH->currUser)
+ free(AH->currUser);
+ AH->currUser = NULL;
+ }
+
return 1;
}


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly