pg_dump session authorization fails during restore
От | Reece Hart |
---|---|
Тема | pg_dump session authorization fails during restore |
Дата | |
Msg-id | 1052429071.7794.52.camel@tallac обсуждение исходный текст |
Список | 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
Вложения
В списке pgsql-admin по дате отправления: