Thread: Cannot recreate DB scheme using pg_dump

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

Nishad Prakash <prakashn@uci.edu> writes:
> 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>".

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

>And which PG version are you using, anyway?
>

Oops, my bad.  It's PostgreSQL 7.4 on sparc-sun-solaris2.8, compiled by
GCC 3.0.4.


>
>That's a tad hard to believe: superusers always have ownership rights.
>Could we see a complete example?
>

I've attached the schema dump, and the corresponding psql output when
run by the superuser as "psql -f schema_dump db_name >& psql_output".
If you don't have time to go through that, here's a brief summary:


The first group of errors I get when trying to create a new db (as
superuser) from the schema dump are failures to create indices on
certain tables (I can't see any difference between the tables on which
it succeeds and those on which it fails).  The errors are of the type:


"ERROR: must be owner of relation cookie_log".  The relevant lines in
the schema dump are:

  --
  -- TOC entry 302 (OID 16552096)
  -- Name: cl_ip_idx; Type: INDEX; Schema: public; Owner: nishad
  --

  CREATE INDEX cl_ip_idx ON cookie_log USING hash (ip);

[The definition of the cookie_log table is:

  --
  -- TOC entry 102 (OID 16169191)
  -- Name: cookie_log; Type: TABLE; Schema: public; Owner: nishad
  --

  CREATE TABLE cookie_log (
      ltime timestamp with time zone NOT NULL,
      id integer NOT NULL,
      ip inet NOT NULL,
      "action" text NOT NULL,
      cookie text NOT NULL
  );
]

Other index re-creation errors follow the same pattern.  This is
followed by errors recreating indices upon definitions of primary keys,
and errors recreating both foreign keys constraints and their associated
triggers.  All errors are either "must be owner of relation..." or
"permission denied for relation."  You can find examples of all these in
the attached files by grepping for "ERROR" and looking up the
corresponding line # in the schema dump.  I hope this is helpful; if
not, please let me know how I can present this in a more intelligible
way.


Cheers,

Nishad

prakashn@uci.edu writes:
> I've attached the schema dump, and the corresponding psql output when
> run by the superuser as "psql -f schema_dump db_name >& psql_output".

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