pg_dump session authorization fails during restore

From: Reece Hart <rkh(at)gene(dot)COM>
To: pgsql-admin(at)postgresql(dot)org, pgsql-bugs(at)postgresql(dot)org
Subject: pg_dump session authorization fails during restore
Date: 2003-05-08 21:24:32
Message-ID: 1052429071.7794.52.camel@tallac
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin pgsql-bugs

I'm having a problem restoring from a backup that may indicate a small
problem with pg_dump. I've included sample output below and a test
script. I'd appreciation confirmation that I'm reading the issue
correctly as well as any news of a fix.

SYSTEM:
postgresql 7.3.2, linux 2.4

PROBLEM:
pg_dump emits SET SESSION AUTHORIZATION before a user-owned schema is
created. During restore, the CREATE SCHEMA fails (presuming the owning
user doesn't have schema creation privileges). All subsequent
restoration to that schema fails as well, of course.

EXAMPLE OUTPUT DURING RESTORE
(you can recreate this with the script below and attached)
$ PGUSER=admin PGPASSWORD=xxx USER=rkh ./pgdtest
[...]
SET SESSION AUTHORIZATION 'rkh';
--
-- TOC entry 2 (OID 28785657)
-- Name: rkh; Type: SCHEMA; Schema: -; Owner: rkh
--
CREATE SCHEMA rkh;
psql:<stdin>:12: ERROR: t2: permission denied
SET search_path = rkh, pg_catalog;
psql:<stdin>:15: ERROR: Namespace "rkh" does not exist
--
-- TOC entry 3 (OID 28785658)
[...]

You can generate the above with the following script (same as attached):
#!/bin/sh -ex
# pgdtest -- script to demonstrate session auth problem when restoring
# non-admin schemas and tables from pg_dump-generated sql

# INVOKE LIKE THIS:
# $ PGUSER=<adminuser> PGPASSWORD=<adminpw> USER=<id> ./pgdtest
# and perhaps with PGHOST
# USER is the non-administrative login to use for testing

# /tmp/t1.pgdump will be created and contains the pgdump output

# Reece Hart <reece(at)in-machina(dot)com>

# t1 will be the source db and we'll restore into t2
createdb t1;
createdb t2;

# create a user-owned schema and table in t1
psql -qa -dt1 -f- <<EOF
create schema $USER authorization $USER;
set session authorization $USER;
create table $USER.testtable (id integer);
\z
EOF

# now backup t1 directly into t2
pg_dump -X use-set-session-authorization t1 \
| tee /tmp/t1.pgdump \
| psql -Uadmin -qa -dt2 -f-

#(end of script)

--
Reece Hart, Ph.D. rkh(at)gene(dot)com, http://www.gene.com/
Genentech, Inc. 650/225-6133 (voice), -5389 (fax)
Bioinformatics and Protein Engineering
1 DNA Way, MS-93 http://www.in-machina.com/~reece/
South San Francisco, CA 94080-4990 reece(at)in-machina(dot)com, GPG: 0x25EC91A0

Attachment Content-Type Size
pgdtest text/x-sh 820 bytes

Browse pgsql-admin by date

  From Date Subject
Next Message Andrew Sullivan 2003-05-08 21:25:10 Re: backup and roll forward recovery
Previous Message Andrew Sullivan 2003-05-08 21:23:26 Re: Support for clustered servers

Browse pgsql-bugs by date

  From Date Subject
Next Message Reece Hart 2003-05-08 21:45:19 pg_dump session authorization fails during restore
Previous Message scott.marlowe 2003-05-08 16:20:19 Re: [PERFORM] [SQL] Unanswered Questions WAS: An unresolved performance