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

Paul Tillotson <ptchristendom@yahoo.com> writes:

pg_dumpall does not save all access control permissions on a database.
(This is true for at least the CREATE permission.)

This is fixed as of 7.4.

regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> writes:

Paul Tillotson <ptchristendom@yahoo.com> writes:

pg_dumpall does not save all access control permissions on a database.
(This is true for at least the CREATE permission.)

This is fixed as of 7.4.

Is this a candidate for being back-patched to 7_3_STABLE? IMHO it
would be useful and low-risk.

-Neil

Neil Conway <neilc@samurai.com> writes:

Is this a candidate for being back-patched to 7_3_STABLE? IMHO it
would be useful and low-risk.

Well, it was done as part of a significant set of changes to pg_dumpall:

2003-05-30 18:55 tgl

* src/bin/pg_dump/: dumputils.c, dumputils.h, pg_dump.c,
pg_dumpall.c: Cause pg_dumpall to include GRANT/REVOKE for
database-level permissions in its output. Make it work with server
versions back to 7.0, too.

I'm not sure what it would take to extract the "low risk" parts of that.

regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> writes:

Well, it was done as part of a significant set of changes to
pg_dumpall:

Are there plans for a 7.3.5 release? If not, we needn't worry about
it, IMHO. But if there are, I can take a look at producing a low-risk
version of this changed for application to REL7_3_STABLE.

Is that something people think would be worth doing?

-Neil

Neil Conway <neilc@samurai.com> writes:

Are there plans for a 7.3.5 release?

Yes, I think there will be a 7.3.5 fairly soon.

If not, we needn't worry about
it, IMHO. But if there are, I can take a look at producing a low-risk
version of this changed for application to REL7_3_STABLE.

Go for it.

regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> writes:

Neil Conway <neilc@samurai.com> writes:

If not, we needn't worry about it, IMHO. But if there are, I can
take a look at producing a low-risk version of this changed for
application to REL7_3_STABLE.

Go for it.

Just FYI, I'm really busy with various other things, so I'm not going
to get time to tackle this any time soon. FWIW, I briefly looked at
the original diff, and it doesn't seem trivial to extract a low-risk
version of the change for backpatching. If someone feels strongly this
needs to make it into 7.3.5, speak up, and/or please backpatch it
yourself; otherwise, don't hold up 7.3.5 for it.

-Neil