pg_dump session authorization fails during restore - Mailing list pgsql-admin

From Reece Hart
Subject pg_dump session authorization fails during restore
Date
Msg-id 1052429071.7794.52.camel@tallac
Whole thread Raw
List pgsql-admin
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@in-machina.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@gene.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@in-machina.com, GPG: 0x25EC91A0

Attachment

pgsql-admin by date:

Previous
From: Oliver Kullmann
Date:
Subject: Re: problems with moving a database
Next
From: Mark Wheaton
Date:
Subject: Problems configuring --with-python on RedHat7.2