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

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:
--
Bruce Momjian | http://candle.pha.pa.us
pgman (AT) candle (DOT) 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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Paul Tillotson <ptchristendom (AT) yahoo (DOT) com> writes:
This is fixed as of 7.4.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Tom Lane <tgl (AT) sss (DOT) pgh.pa.us> writes:
Is this a candidate for being back-patched to 7_3_STABLE? IMHO it
would be useful and low-risk.

-Neil


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

Neil Conway <neilc (AT) samurai (DOT) com> writes:
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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Tom Lane <tgl (AT) sss (DOT) pgh.pa.us> writes:
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


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Neil Conway <neilc (AT) samurai (DOT) com> writes:
Yes, I think there will be a 7.3.5 fairly soon.

Go for it.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Tom Lane <tgl (AT) sss (DOT) pgh.pa.us> writes:
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


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster