Thread

1. Re: Cannot recreate DB scheme using pg_dump

   Nishad Prakash <prakashn@uci.edu> — 2004-07-19T20:41:31Z

   >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
   
   

2. Re: Cannot recreate DB scheme using pg_dump

   Tom Lane <tgl@sss.pgh.pa.us> — 2004-07-19T21:06:26Z

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