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;
\.


Thu, 4 Sep 2003 07:59:37 -0500
Ben Grimm <ben@zaeon.com>
pg_dump/all doesn't output schemas correctly (v7.3.4)

I tested this bug in 7.4beta2 and it still generates the 'create schema' 
after the 'set session auth' which causes a failure upon restore.

Fortunately with the other bug I reported being fixed that gives me 
the work around of just granting create on the database to the users 
that own the schemas. 


On Thu, 04 Sep 2003, 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

Thu, 4 Sep 2003 22:31:50 -0500
Ben Grimm <bgrimm@zaeon.com>
Re: pg_dump/all doesn't output schemas correctly (v7.3.4)

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
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Wed, 10 Sep 2003 15:43:51 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [BUGS] pg_dump/all doesn't output schemas correctly (v7.3.4)
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.



Tue, 16 Sep 2003 15:22:02 -0700
"Joshua D. Drake" <jd@commandprompt.com>
Re: [BUGS] pg_dump/all doesn't output schemas correctly
"Joshua D. Drake" <jd@commandprompt.com> writes:
>   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?

pgsql-patches is the usual place for patches.

I was planning to work on this myself tomorrow, so you could save me
some time by sending along what you have ...

			regards, tom lane

Wed, 17 Sep 2003 01:21:26 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [BUGS] pg_dump/all doesn't output schemas correctly 

This has been fixed in the current CVS snapshot and will be in the next
7.4 beta.  Thanks.

---------------------------------------------------------------------------

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
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Fri, 26 Sep 2003 13:52:18 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: pg_dump/all doesn't output schemas correctly (v7.3.4)