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