From: | Paul Tillotson <ptchristendom(at)yahoo(dot)com> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | pg_dumpall does not save CREATE permission on databases |
Date: | 2003-11-06 21:22:49 |
Message-ID: | 20031106212249.46803.qmail@web12204.mail.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
============================================================================
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2003-11-07 00:34:53 | Re: PostgreSQL 7.4RC1 crashes on Panther |
Previous Message | Scott Goodwin | 2003-11-06 21:18:06 | Re: PostgreSQL 7.4RC1 crashes on Panther |