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

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@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