pg_dumpall does not save CREATE permission on databases - Mailing list pgsql-bugs

============================================================================
                        POSTGRESQL BUG REPORT TEMPLATE
============================================================================


Your name               : Paul Tillotson
Your email address      : ptchristendom at yahoo dot com


System Configuration
---------------------
  Architecture (example: Intel Pentium)         : AMD athlon something

  Operating System (example: Linux 2.0.26 ELF)  : FreeBSD

  PostgreSQL version (example: PostgreSQL-7.3.4): PostgreSQL-7.3.4

  Compiler used (example:  gcc 2.95.2)          : gcc

template1=# select version();
                               version
---------------------------------------------------------------------
 PostgreSQL 7.3.4 on i386-portbld-freebsd4.8, compiled by GCC 2.95.4

Please enter a FULL description of your problem:
------------------------------------------------

pg_dumpall does not save all access control permissions on a database.
(This is true for at least the CREATE permission.)
This causes the restore script to fail when, for example, it tries to create a
schema which is owned by a different user than the database which it resides
in.


Please describe a way to repeat the problem.   Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------
DO THIS IN PSQL:

template1=# create database foobar;
template1=# create user mrfoobar;
template1=# grant create on database foobar to mrfoobar;
template1=# select datname, datacl from pg_database;
  datname  |          datacl
-----------+--------------------------
 foobar    | {=T,pgsql=CT,mrfoobar=C}
 template1 | {=,pgsql=CT}
 template0 | {=,pgsql=CT}
(3 rows)

THEN DO THIS FROM YOUR SHELL. NOTICE THAT THERE IS NO LINE
OF THE FORM "GRANT CREATE ON ...."

james% pg_dumpall
--
-- PostgreSQL database cluster dump
--

\connect "template1"

--
-- Users
--

DELETE FROM pg_shadow WHERE usesysid <> (SELECT datdba FROM pg_database WHERE
datname = 'template0');

CREATE USER mrfoobar WITH SYSID 100 NOCREATEDB NOCREATEUSER;


--
-- Groups
--

DELETE FROM pg_group;



--
-- Database creation
--

CREATE DATABASE foobar WITH OWNER = pgsql TEMPLATE = template0 ENCODING =
'SQL_ASCII';


\connect foobar
--
-- PostgreSQL database dump
--

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

If you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------

pg_dumpall should read the from the datacl column from the pg_database table
and
write lines like this into the dump script when appropriate:
GRANT <priv> ON DATABASE <database> TO <username>;


__________________________________
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree