Skip site navigation (1) Skip section navigation (2)

Re: erroneous restore into pg_catalog schema

From: Andres Freund <andres(at)anarazel(dot)de>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Erik Rijkers <er(at)xs4all(dot)nl>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: erroneous restore into pg_catalog schema
Date: 2013-01-13 21:31:45
Message-ID: 20130113213145.GC27004@awork2.anarazel.de (view raw or flat)
Thread:
Lists: pgsql-hackers
On 2013-01-13 12:29:08 -0500, Tom Lane wrote:
> "Erik Rijkers" <er(at)xs4all(dot)nl> writes:
> > If you dump a table with -t schema.table, and in the receiving database that schema does not
> > exist, pg_restore-9.3devel will restore into the pg_catalog schema:
> > ...
> > Off course the workaround is obvious, but shouldn't this be prevented from happening in the first
> > place?  9.2 refuses to do such a restore, which seems much better.
> 
> I said to myself "huh?  surely we did not change pg_dump's behavior
> here".  But actually it's true, and the culprit is commit 880bfc328,
> "Silently ignore any nonexistent schemas that are listed in
> search_path".  What pg_dump is emitting is
> 
> 	SET search_path = s, pg_catalog;
> 	CREATE TABLE t (...);
> 
> and in HEAD the SET throws no error and instead establishes pg_catalog
> as the target schema for object creation.  Oops.
> 
> So obviously, 880bfc328 was several bricks shy of a load.  The arguments
> for that change in behavior still seem good for schemas *after* the
> first one; but the situation is entirely different for the first schema,
> because that's what the command is attempting to establish as the
> creation schema.

There also is the seemingly independent question of why the heck its
suddently allowed to create (but not drop?) tables in pg_catalog.
9.2 does:
andres=# CREATE TABLE pg_catalog.c AS SELECT 1;
ERROR:  permission denied to create "pg_catalog.c"
DETAIL:  System catalog modifications are currently disallowed.
Time: 54.180 ms

HEAD:
postgres=# CREATE TABLE pg_catalog.test AS SELECT 1;
SELECT 1
Time: 124.112 ms
postgres=# DROP TABLE pg_catalog.test;
ERROR:  permission denied: "test" is a system catalog
Time: 0.461 ms

Greetings,

Andres Freund


In response to

pgsql-hackers by date

Next:From: Tom LaneDate: 2013-01-13 21:37:53
Subject: Re: erroneous restore into pg_catalog schema
Previous:From: Erik RijkersDate: 2013-01-13 21:23:30
Subject: Re: erroneous restore into pg_catalog schema

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group