Re: [BUGS] pg_dump/all doesn't output schemas correctly

Hello,
 I asked my programmers to look into this. They think they have fixed 
it. I have asked them to provide me
a patch. Should I post the patch here?

Sincerely,

Joshua Drake


Bruce Momjian wrote:

>I can confirm that this bug still exists in current CVS.  The problem is
>that "CREATE SCHEMA AUTHORIZATION test" is translated into "SET SESSION
>AUTHORIZATION 'test'; CREATE SCHEMA test;".
>
>
>While this does allow the schema to be owned by 'test', it assumes
>'test' has permissions to create the schema, which might not be true.
>
>My guess is that the AUTHORIZATION option creates the schema as owned by
>that user --- manual says:
>
>       AUTHORIZATION clause is used, all the created objects will
>       be owned by that user.
>
>but then we forget and just create the schema as that user.  I looked at
>the pg_dump code but can't quite see where the problem lies.
>
>---------------------------------------------------------------------------
>
>Ben Grimm wrote:
>  
>
>>I haven't tried the 7.4 beta, so it may be fixed there - but in
>>7.3.4, pg_dumpall doesn't generate the commands to create schemas 
>>in the right order.  This bug may have been reported before, but 
>>I saw no response to it in the lists.  
>>
>>Try this in a fresh database after an initdb:
>>
>>template1=# create user test nocreatedb nocreateuser;
>>CREATE USER
>>template1=# create database testdb;
>>CREATE DATABASE
>>template1=# \c testdb
>>You are now connected to database testdb.
>>testdb=# create schema authorization test;
>>CREATE SCHEMA
>>testdb=# set session authorization test;
>>SET
>>testdb=# set search_path="test";
>>SET
>>testdb=# create table abc ();
>>CREATE TABLE
>>template1=# \q
>>testdb=# \q
>>
>>$ pg_dumpall -U postgres
>>--
>>-- PostgreSQL database cluster dump
>>--
>>
>>\connect "template1"
>>
>>--
>>-- Users
>>--
>>
>>DELETE FROM pg_shadow WHERE usesysid <> (SELECT datdba FROM pg_database WHERE datname = 'template0');
>>
>>CREATE USER test WITH SYSID 100 NOCREATEDB NOCREATEUSER;
>>
>>
>>--
>>-- Groups
>>--
>>
>>DELETE FROM pg_group;
>>
>>
>>
>>--
>>-- Database creation
>>--
>>
>>CREATE DATABASE testdb WITH OWNER = postgres TEMPLATE = template0 ENCODING = 'SQL_ASCII';
>>
>>
>>\connect template1
>>--
>>-- PostgreSQL database dump
>>--
>>
>>--
>>-- TOC entry 2 (OID 1)
>>-- Name: DATABASE template1; Type: COMMENT; Schema: -; Owner:
>>--
>>
>>COMMENT ON DATABASE template1 IS 'Default template database';
>>
>>
>>\connect testdb
>>--
>>-- PostgreSQL database dump
>>--
>>
>>SET SESSION AUTHORIZATION 'test';
>>
>>--
>>-- TOC entry 2 (OID 16977)
>>-- Name: test; Type: SCHEMA; Schema: -; Owner: test
>>--
>>
>>*************************************************************************
>> This will fail because user 'test' has not been granted
>> create on the database (which pg_dump also fails to output, 
>> but that's a separate bug)  It should create the schema as 
>> the superuser, then switch to the use to create tables within
>> that schema.
>>*************************************************************************
>>
>>CREATE SCHEMA test;   
>>
>>
>>SET search_path = test, pg_catalog;
>>
>>--
>>-- TOC entry 3 (OID 16978)
>>-- Name: abc; Type: TABLE; Schema: test; Owner: test
>>--
>>
>>CREATE TABLE abc (
>>);
>>
>>
>>--
>>-- Data for TOC entry 4 (OID 16978)
>>-- Name: abc; Type: TABLE DATA; Schema: test; Owner: test
>>--
>>
>>COPY abc  FROM stdin;
>>\.
>>
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 7: don't forget to increase your free space map settings
>>
>>    
>>
>
>  
>

-- 
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-222-2783 - jd@commandprompt.com - http://www.commandprompt.com
The most reliable support for the most reliable Open Source database.