Thread: pg_dumpall does not save CREATE permission on databases
============================================================================
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
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