Re: pg_dumpall does not save CREATE permission on databases

This is fixed in 7.4.X and in fact 7.4 pg_dumpall will work on a 7.3.X
database.

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

Paul Tillotson wrote:
> ============================================================================
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
>

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