Thread: error: permission denied for schema pg_catalog && pg_temp_nn tables

Hi there,

 

    Gotta rubish problem, which actually appears to happen in older versions (7.3.5), but I am using a 7.4.2 server.

   

    Now, I need to create a new db, for testing purposes, and I've done a dump from the prd db using pg_dump. After that, I create the new db and I run the restore. It breaks, crying about the pg_catalog permissions. Ok, going with a `cat schema.sql | psql dbname`. In this case, it will end, but complaining several times about the same issue, pg_catalog and permission denied, and...it also complains about tables that don't exist anymore.  But actually, the relation exists, through a search in pg_class.

 

   Damn, maybe the dump wrote bad that schema dump?

 

   The file looks fine, as every schema dump I ever read before (~1500 lines). After this I've put my hands in the db, and searched for schemas in the db. Found tons of pg_temp_nn (up to 92) schemas. Something aboute temporary tables, but not much more info arround. The case is that the db, actually is running without users connected, and no batch jobs are running, pg_stat_activity shows an empy and great scenery, where nothing goes on. A restart of the service didn't help. Tables are still there. Note that this db holds too much views, and that is the only difference with other db's where the pg_temp_nn are normal (< 5).

 

  Now, in the irc (freenode, #postgresql), someone suggested an upgrade of the db software (to a major version, let's say 7.4.9). I've tryed the dump && restore with a 7.4.8 and it didn't work (note that the newer soft is on another box, so hw issue is out of the road today).  Same issue, same error, cat  && psql failing also.

 

Will 8.x sort this out?

If not:

 

Is this a problem with the order in the perms set ? (dump is failing?)

 

Any ideas? I need a thread old-guru here, someone with the brain enough strong to remember this issue in previous versios, or to clarify my scenery. I could be missing something, dunno.

 

Thanks in advance, (..and let me know your thoughts! :) )

 

Cheers,

Guido

--
"Adopting the position that you are smarter than an automatic
optimization algorithm is generally a good way to achieve less
performance, not more" - Tom Lane.