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

erroneous restore into pg_catalog schema

From: "Erik Rijkers" <er(at)xs4all(dot)nl>
To: pgsql-hackers(at)postgresql(dot)org
Subject: erroneous restore into pg_catalog schema
Date: 2013-01-13 10:17:31
Message-ID: 8c4600a6f10e7dd823a157eff9985068.squirrel@webmail.xs4all.nl (view raw or flat)
Thread:
Lists: pgsql-hackers
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:

HEAD

$ cat test.sh
#!/bin/sh

db=backupbug;

dropdb   --echo $db;
createdb --echo $db;

echo "drop schema if exists s cascade;" | psql -ad $db
echo "create schema s;" | psql -ad $db
echo "create table s.t as select i from generate_series(1,10) as f(i);" | psql -ad $db

echo '\dt+ pg_catalog.t' | psql -ad $db

pg_dump -F c -t s.t -f st.dump $db

echo "drop schema if exists s cascade;" | psql -ad $db

pg_restore -xOv -F c -d $db st.dump

echo '\dn'     | psql -ad $db
echo '\dt+ s.' | psql -ad $db
echo '\dt+ pg_catalog.t' | psql -ad $db


output:

$ ./test.sh
DROP DATABASE backupbug;
CREATE DATABASE backupbug;
drop schema if exists s cascade;
DROP SCHEMA
create schema s;
CREATE SCHEMA
create table s.t as select i from generate_series(1,1000) as f(i);
SELECT 1000
\dt+ pg_catalog.t
No matching relations found.
drop schema if exists s cascade;
DROP SCHEMA
pg_restore: connecting to database for restore
pg_restore: creating TABLE t
pg_restore: processing data for table "t"
pg_restore: setting owner and privileges for TABLE t
pg_restore: setting owner and privileges for TABLE DATA t
\dn
  List of schemas
  Name  |  Owner
--------+----------
 public | aardvark
(1 row)

\dt+ s.
No matching relations found.
\dt+ pg_catalog.t
                     List of relations
   Schema   | Name | Type  |  Owner   | Size  | Description
------------+------+-------+----------+-------+-------------
 pg_catalog | t    | table | aardvark | 64 kB |
(1 row)

#----------------------

And then adds insult to injury:

backupbug=# drop table pg_catalog.t;
ERROR:  permission denied: "t" is a system catalog

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.

(and yes, I did restore a 65 GB table into the pg_catalog schema of a dev machine; how can I
remove it? I could initdb, but it's 200+ GB; I'd rather not have to rebuild it)

Thanks,

Erik Rijkers




Responses

pgsql-hackers by date

Next:From: Peter GeogheganDate: 2013-01-13 10:44:12
Subject: Re: enhanced error fields
Previous:From: Boszormenyi ZoltanDate: 2013-01-13 09:15:07
Subject: Re: Proposal for Allow postgresql.conf values to be changed via SQL [review]

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